Friday, May 21, 2010

Generate Comma Separated List – SQL Server

In one to many relationships of two tables, sometimes we need to write a query which can return comma separated list of all child values for each parent.
Here are two different methods by which you can easily create a Comma Separated List, the first runs from sql 2000 onwards and the second on is for SQL 2005 onwards.

USE tempdb
GO
CREATE TABLE [dbo].[student](
	StudentID INT IDENTITY,
	Name   varchar(50) ,
) ON [PRIMARY]
GO
INSERT INTO Student (name)
SELECT 'One' 
UNION ALL
SELECT 'Two'
UNION ALL 
SELECT 'Three'
UNION ALL
SELECT 'Four'


 



SQL Server 2000 Onwards Version



DECLARE @Csv VARCHAR(1000) 
SELECT @csv = COALESCE(@Csv+',', '')+NAME
FROM [dbo].[student]
SELECT @Csv


SQL Server 2005 Onwards version



SELECT CASE WHEN LEN(name) > 0 THEN LEFT(name, LEN(name)-1) ELSE '' END AS name 
FROM (
	SELECT p.name +',' 
	FROM dbo.student p
	FOR XML PATH('')
)tmp (name)

Thursday, May 13, 2010

Add Column With Default Column Constraint to Table

I found many questions regarding the inline named default constraints in many user groups, its actually pretty simple

CREATE TABLE dbo.tempabc
(
[col1] [varchar] (16),
[col2] [int] NULL CONSTRAINT df__tempabc__Col2 DEFAULT ((0))
) ON [PRIMARY]
GO

Alter column set default

CREATE TABLE dbo.tempabc
(
[col1] [varchar] (16),
[col2] [int] NULL CONSTRAINT df__tempabc__Col2 DEFAULT ((0))
) ON [PRIMARY]
GO
ALTER TABLE dbo.tempabc ALTER COLUMN Col1 VARCHAR(32) NOT NULL
ALTER TABLE dbo.tempabc ADD CONSTRAINT df__tempabc__Col1 DEFAULT 'abc' FOR Col1
 

Wednesday, May 12, 2010

Script to find the No of reads/ writes on DB files

SELECT  DB_NAME(mf.database_id) AS databaseName
       ,mf.physical_name
       ,num_of_reads
       ,num_of_bytes_read
       ,io_stall_read_ms
       ,num_of_writes
       ,num_of_bytes_written
       ,io_stall_write_ms
       ,io_stall
       ,size_on_disk_bytes
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN    sys.master_files AS mf ON mf.database_id = divfs.database_id
                                  AND mf.file_id = divfs.file_id