Search This Blog & Web

Friday, May 22, 2009

Returning Comma seperated Values in SQL Server 2005

Declare @vComma Varchar(max);

Select @vComma = COALESCE(@vComma+';','') + column1 from table1

select @vComma

I have 5 values in Unique table against Val column and this command will return all values as ; seperated


Tuesday, May 19, 2009

Finding missing relationship

-- Possible bad Indexes (writes > reads)

DECLARE @dbid int
SELECT @dbid = db_id()
SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id, 'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups, 'Difference' = user_updates - (user_seeks + user_scans + user_lookups)
FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1 AND s.database_id = @dbid AND user_updates > (user_seeks + user_scans + user_lookups)
ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC;

--- Index Read/Write stats for a single table
DECLARE @dbid int
SELECT @dbid = db_id()
SELECT objectname = object_name(s.object_id), indexname = i.name, i.index_id, reads = user_seeks + user_scans + user_lookups, writes = user_updates
FROM sys.dm_db_index_usage_stats AS s, sys.indexes AS i
WHERE objectproperty(s.object_id,'IsUserTable') = 1 AND s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id = @dbid AND object_name(s.object_id) IN( 'tablename')
ORDER BY object_name(s.object_id), writes DESC, reads DESC;

-- Show existing indexes for this table EXEC sp_HelpIndex 'tablename'

-- Missing Indexes SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',mid.equality_columns, mid.inequality_columns, mid.included_columns,migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impactFROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)ON mig.index_handle = mid.index_handleORDER BY index_advantage DESC;

-- Missing indexes for a single tableSELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',mid.equality_columns, mid.inequality_columns, mid.included_columns,migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impactFROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)ON mig.index_handle = mid.index_handleWHERE statement = '[databasename].[dbo].[tablename]' -- Specify one tableORDER BY index_advantage DESC;

-- Examine current indexesEXEC sp_HelpIndex 'dbo.tablename'

Friday, May 15, 2009

Convert Date time function in SQL Server 2005

I find on many websites and do many exercieses following are the output summary for those.

-- return datetime portion without time (best is from top to bottom)
1. SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
2. SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
3. SELECT CONVERT(DATETIME, CONVERT(INT, GETDATE()))
4. CAST(CONVERT(varchar, GetDate(), 101)