SQL Query Analyzer 2005


No such thing unfortunately. If you have moved to SQL server 2005, your world of Enterprise Manager and SQL Query Analyzer has been replaced with SQL Server Management Studio (SSMS). This is a great new IDE, but now I need to go through several clicks just to write a query... Start SSMS (Click: 1), Let's …

Planning ahead… Deprecation


Spending a little time planning and researching can often extend a products economically sustainable lifespan by a substantial amount. Let’s take a quick look at this from a SQL perspective in this fictional example: Project A comes along and you need to maximise the lifespan of the application and provide a clear upgrade path to …

New SQL Blog – Optimise Optimize Optimise


Love query optimisation? The SQL Server Query Processor team have started a new blog "Tips, Tricks, and Advice from the SQL Server Query Processing Team". They also link to an old blog I hadn't come across before which appears to have a ton of information! It's going to be a late night! On a side …

#Tables, Table Variables, CTE’s – what’s the difference?


Here is a handy table that explains some of the differences between the three. The full sized version is available here. I'd say that in the past I've used Table Variables about 60 to 70% of the time when I've needed some form of temporary data. With Common Table Expressions (CTE) in SQL server 2005 …

CREATE INDEX ….. [ ASC | DESC ]


I came across a good post the other day that highlighted something I'd not seen or used in the CREATE INDEX statement. Columns can be specified to be sorted either ASC or DESC. From BOL: [ ASC | DESC ] Determines the ascending or descending sort direction for the particular index column. The default is …

Using .WRITE on VARCHAR(MAX)? – Part 1


Are you using .WRITE when updating the contents of a VARCHAR(MAX) or NVARCHAR(MAX) column and are you replacing the whole contents (using @Offset = 0 and @Length = NULL)? UPDATE  MyTableSET     col1.WRITE ('Lorem ipsum’, 0, NULL) or simply replace the value: UPDATE  MyTableSET     col1 = 'Lorem ipsum’ Depending on your app, you may want to …

Microsoft SQL Server 2005 Query DeskSheet


I've been doing some reading lately and made some notes (to myself) on various querying aspects of SQL 2005. As a reference, I've created a 'DeskSheet' so I have something to quicky refer back to... Here's the first version of it in pdf format. I'll be posting a Microsoft SQL Server 2005 Programming DeskSheet in …