#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 …

Re-using Key values (finding the gaps)


Suppose you do physical deletes of data and are in a position to re-use the 'keys' for a table. You can use the technique below to find gaps in the key for your table (assuming you are using a single numerical key). -- LET'S USE TEMPDBUSE tempdb;GO -- DROP THE TABLE IF IT ALREADY EXISTSIF …

SQL Server 2005: INCLUDE argument with Nonclustered Indexes.


There is a new INCLUDE argument available in SQL Server 2005 when creating nonclustered indexes. According to the MSDN documentation, INCLUDE is used to: Specify the nonkey columns to be added to the leaf level of the nonclustered index. In a nutshell, this new clause allows us to better target columns we need in a …

Some New Books – bring on the learning…


I bought two new books today: Inside Microsoft SQL Server 2005: T-SQL Querying and Inside SQL Server 2005: T-SQL Programming, both by Itzik Ben-Gan. I spent a morning in one of his Black Hat SQL sessions a few years ago and have been following his works ever since. I've started with T-SQL Querying as it's …