One of the common task that I have come across is the need to convert a list of results into a comma separated list.  There are many ways to use this, one if which is in reports. Download the AsCSV.sql sample here. Often times I have told developers, here is how you do it, and if you Google on it …

Converting part of a result set to a comma separated list Read more »

I needed a way to list of of the indexes for an entire database, but I was running into a problem using sys.indexes that I could only see the indexes for the current database. Below is the solution that I put together to list off of the indexes in the entire database. CREATE TABLE ##allindexes ( databse_id INTEGER, table_id INTEGER, index_id INTEGER, table_name      VARCHAR(1024) ); EXECUTE Sp_msforeachdb …

Finding the names of all indexes on a SQL Server Read more »

Default Values and Computed Columns In SQL Server 2008, I was presented with the following error when attempting to create a table that has a default value that references other columns in the same table. Msg 128, Level 15, State 1, Line 6 The name “Subtotal” is not permitted in this context. Valid expressions are constants,  constant expressions, and (in …

MSG 128, Level 15, State 1 – not permitted in this context Read more »

The question came up as to which version of sql server is being run, and how do you determine the exact version number of the SQL server. Here is what I have come up with. From TSQL if you are connected to the SQL Server, you can run the following command: SELECT ‘SQL Server ‘ + CAST(SERVERPROPERTY(‘productversion’) AS VARCHAR) + …

Determining the version of SQL server from TSQL Read more »

The following applies to SQL Server 2008. How big of an allocation does max represent in the case of varchar(max) and varbinary(max)? Up to 2 ^ 31 – 1 bytes or just about 2gb.   Can I specify a varchar(9000) or varbinary(12000)? No. If you are going to specify an actual number instead of (max) then your limit is 8000. …

varchar(max) and varbinary(max) Questions and Answers Read more »