Concatenating row values in T-SQL

Concatenating column values from multiple rows.
Northwind, as always🙂
From this

to this
 

WITH CTE ( CategoryId, product_list, product_name, length )
AS ( SELECT CategoryId, CAST( ” AS VARCHAR(MAX) ), CAST( ” AS VARCHAR(MAX) ), 0
FROM Northwind..Products
GROUP BY CategoryId
UNION ALL
SELECT p.CategoryId, CAST( product_list +
CASE WHEN length = 0 THEN ” ELSE ‘, ‘ END + ProductName AS VARCHAR(MAX) ),
CAST( ProductName AS VARCHAR(MAX)), length + 1
FROM CTE c
INNER JOIN Northwind..Products p
ON c.CategoryId = p.CategoryId
WHERE p.ProductName > c.product_name )
SELECT CategoryId, product_list
FROM ( SELECT CategoryId, product_list,
RANK() OVER ( PARTITION BY CategoryId ORDER BY length DESC )
FROM CTE ) D ( CategoryId, product_list, rank )
WHERE rank = 1 ;

Get Memory Check with CLR

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Management
Imports System.Diagnostics
Imports System.Data.Sql
Imports System.Collections
Imports System.Runtime.InteropServices
Imports System.Threading

Partial Public Class StoredProcedures
_
Public Shared Sub Performance()
Dim record As New SqlDataRecord(New SqlMetaData(“ProcessGroup”, SqlDbType.NVarChar, 200), _
New SqlMetaData(“ProcessID”, SqlDbType.NVarChar, 200), _
New SqlMetaData(“ProcessName”, SqlDbType.NVarChar, 200), _
New SqlMetaData(“Memory Usage”, SqlDbType.NVarChar, 200))

SqlContext.Pipe.SendResultsStart(record)

Dim ps() As Process

ps = Process.GetProcesses()
Dim p As Process
For Each p In ps
If p.ProcessName.Contains(“sqlservr”) Then
record.SetString(0, “SqlServer Current instance”)
record.SetString(1, p.Id.ToString)
record.SetString(2, p.ProcessName)
record.SetString(3, p.WorkingSet64.ToString)
ElseIf p.ProcessName.ToLower.Contains(“sqlagent”) Then
record.SetString(0, “SqlServer Agent Current instance”)
record.SetString(1, p.Id.ToString)
record.SetString(2, p.ProcessName)
record.SetString(3, p.WorkingSet64.ToString)

ElseIf p.ProcessName.Contains(“sqlbrowser”) Or _
p.ProcessName.Contains(“sqlwriter”) Or _
p.ProcessName.Contains(“ReportingServicesService”) Or _
p.ProcessName.Contains(“msdtc”) Or _
p.ProcessName.Contains(“msftesql”) Or _
p.ProcessName.Contains(“msmdsrv”) Then
record.SetString(0, “Other processes associated with current sql server instance”)
record.SetString(1, p.Id.ToString)
record.SetString(2, p.ProcessName)
record.SetString(3, p.WorkingSet64.ToString)

ElseIf p.ProcessName.ToLower.Contains(“idle”) Then
record.SetString(0, “Idle”)
record.SetString(1, p.Id)
record.SetString(2, p.ProcessName)
record.SetString(3, p.WorkingSet64.ToString)

Else
record.SetString(0, “Other”)
record.SetString(1, p.Id)
record.SetString(2, p.ProcessName)
record.SetString(3, p.WorkingSet64.ToString)

End If
‘ Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record)
Next

SqlContext.Pipe.SendResultsEnd()

End Sub

End Class

assembly_bits

http://msdn.microsoft.com/en-us/library/ms189524%28v=SQL.90%29.aspx

ALTER DATABASE test SET TRUSTWORTHY ON;

CREATE ASSEMBLY [System.Management] AUTHORIZATION [dbo]
FROM ‘C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll’
WITH PERMISSION_SET = UNSAFE;

CREATE ASSEMBLY [PRF]
AUTHORIZATION [dbo]
FROM 0x…….
WITH PERMISSION_SET = UNSAFE;

GO
CREATE PROCEDURE [dbo].[Performance]
AS EXTERNAL NAME [PRF].[PRF.StoredProcedures].[Performance];
USE TEST;
exec performance;

Resource Wait Types

–//////////////////////////////////////////////////////////////////////////////////////////////////////////
— Resource Wait Types
–//////////////////////////////////////////////////////////////////////////////////////////////////////////

— Memory Waits———————————————————————————————
when @wait_type in (N’RESOURCE_SEMAPHORE’, N’CMEMTHREAD’, N’SOS_RESERVEDMEMBLOCKLIST’) then N’Memory’

— Disk I/O Waits——————————————————————————————-
when @wait_type LIKE ‘PAGEIOLATCH_%’
OR @wait_type IN (‘IO_COMPLETION’, ‘ASYNC_IO_COMPLETION’,’WRITELOG’ ) then ‘Disk I/O Waits’

–Blocking Waits——————————————————————————————–
when @wait_type LIKE ‘LCK_%’ then ‘Blocking Waits’
–CPU——————————————————————————————————-
when @wait_type in (‘CXPACKET’, ‘SOS_SCHEDULER_YIELD’) then ‘CPU’

–Network Waits———————————————————————————————
when @wait_type in (‘ASYNC_NETWORK_IO’, ‘DBMIRROR_SEND’) then ‘Network Waits’

–//////////////////////////////////////////////////////////////////////////////////////////////////////////
— System Waits
when @wait_type IN (‘FT_IFTS_SCHEDULER_IDLE_WAIT’,’LOGMGR_QUEUE’,’CHECKPOINT_QUEUE’,
‘SLEEP_TASK’,’BROKER_TO_FLUSH’,’LAZYWRITER_SLEEP’,’REQUEST_FOR_DEADLOCK_SEARCH’,
‘SQLTRACE_BUFFER_FLUSH’,’XE_TIMER_EVENT’) then ‘System Waits’
–//////////////////////////////////////////////////////////////////////////////////////////////////////////
— Preemptive Wait Types
when @wait_type LIKE ‘PREEMPTIVE%’ then ‘Preemptive Wait Types’

long-running queries

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Duration (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28, 2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count
, CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count
AS DECIMAL(28, 2)) AS [Average Duration (s)]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
--, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY qs.total_elapsed_time DESC

Reclaim Space using DBCC CLEANTABLE

Client asked for advice when his portal database, 

image

grown to half TB,  became unmanageable.

Looking at sys.dm_db_index_physical_stats

gave the following:

image

taking into account that rebuilding the indexes is this case could be very timely resource-intensive operation,

DBCC CLEANTABLE

-----------------------------------------------------------------------------
-- Reclaim space from dropped variable length columns and text columns.      
-----------------------------------------------------------------------------

RAISERROR('Reclaiming space from dropped variable length columns and text columns...', 10, 1) WITH NOWAIT

DECLARE @TableName  sysname       = ''
DECLARE @SchemaName sysname       = ''

DECLARE cur CURSOR FOR 
    SELECT SCHEMA_NAME(schema_id)   AS 'SchemaName' 
         , name                     AS 'TableName'
      FROM sys.tables
     WHERE is_ms_shipped = 0
       AND type_desc     = 'USER_TABLE'
  ORDER BY 'SchemaName', 'TableName'

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName        

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @TableName = @SchemaName + '.' + @TableName
    RAISERROR('   Examining table %s', 10, 1, @TableName) WITH NOWAIT
    DBCC CLEANTABLE(0, @TableName, 100000) WITH NO_INFOMSGS
    FETCH NEXT FROM cur INTO @SchemaName, @TableName        
END

CLOSE cur
DEALLOCATE cur

RAISERROR('Done.', 10, 1) WITH NOWAIT

Learn to work correctly (the hard way :))

Tags

Example of database trigger that will force to create tables with PRIMARY KEY

———————————————————————
— DDL Triggers
———————————————————————
USE master;
GO
IF DB_ID(‘testdb’) IS NOT NULL
DROP DATABASE testdb;
GO
CREATE DATABASE testdb;
GO
USE testdb;
GO

— Table must contain a primary key
CREATE TRIGGER trg_create_table_with_pk ON DATABASE FOR CREATE_TABLE
AS

DECLARE @eventdata AS XML, @objectname AS NVARCHAR(257),
@msg AS NVARCHAR(500);

SET @eventdata = eventdata();
SET @objectname =
N'[‘ + CAST(@eventdata.query(‘data(//SchemaName)’) AS SYSNAME)
+ N’].[‘ +
CAST(@eventdata.query(‘data(//ObjectName)’) AS SYSNAME) + N’]’

IF OBJECTPROPERTY(OBJECT_ID(@objectname), ‘TableHasPrimaryKey’) = 0
BEGIN
SET @msg = N’Table ‘ + @objectname + ‘ does not contain a primary key.’
+ CHAR(10) + N’Table creation rolled back.’;
RAISERROR(@msg, 16, 1);
ROLLBACK;
RETURN;
END
GO

— test trigger
CREATE TABLE dbo.T1(col1 INT NOT NULL);
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);

— Cleanup
DROP TRIGGER trg_create_table_with_pk ON DATABASE;
DROP TABLE dbo.T1;

ProgramBadness :)

Tags

SELECT
convert(varchar(50), program_name) as Program,
count(*) as CliCount,
sum(cpu) as CPUSum,
sum(datediff(second, login_time, getdate())) as SecSum,
convert(float, sum(cpu)) / convert(float, sum(datediff(second, login_time, getdate()))) as Score,
convert(float, sum(cpu)) / convert(float, sum(datediff(second, login_time, getdate()))) / count(*) as ProgramBadnessFactor
FROM master..sysprocesses
WHERE spid > 50
GROUP BY
convert(varchar(50), program_name)
ORDER BY score DESC

Follow

Get every new post delivered to your Inbox.