COUNT without GROUP BY

I found a novel way of doing a record count today, without using a GROUP BY.
Normally I would have joined another results set containing a GROUP BY, but it appears you can use the OVER window function without a PARTITION BY / ORDER BY statement.

The syntax is COUNT(*) OVER () AS [RecordCount]

I use it below to return the count of addresses in AdventureWorks.

SELECT DISTINCT 
      a.[City]
   ,sp.Name As StateProvince
   ,cr.Name AS CountryRegion
   ,COUNT(*) OVER (PARTITION BY sp.[StateProvinceID]) AS AddressesInThisProvince
   ,COUNT(*) OVER (PARTITION BY cr.CountryRegionCode) AS AddressesInThisCountry
   ,COUNT(*) OVER () AS TotalAddresses 
  FROM [AdventureWorks2014].[Person].[Address] a
  INNER JOIN [AdventureWorks2014].[Person].[StateProvince] sp
  ON a.[StateProvinceID] = sp.[StateProvinceID]
  INNER JOIN [AdventureWorks2014].[Person].[CountryRegion] cr
  ON sp.CountryRegionCode = cr.CountryRegionCode
  WHERE cr.Name = 'Germany'
  ORDER BY 1,2,3

TSQL : Correct Compatibility Levels

I'm always finding databases on sites that were not put into the correct compatibility mode when server migrations/upgrades occurred.
This script sorts them all out at once.

DECLARE @ServerVersion INT
SELECT @ServerVersion = 10 * (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))

-- loop databases setting compatibility mode correctly
DECLARE GET_DATABASES CURSOR
READ_ONLY
FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != CAST(@ServerVersion AS VARCHAR(10))
DECLARE @DATABASENAME NVARCHAR(255)
DECLARE @COUNTER INT
SET @COUNTER = 1
OPEN GET_DATABASES
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- change database compatibility
EXECUTE sp_dbcmptlevel @DATABASENAME , @ServerVersion
PRINT  @DATABASENAME + ' changed'
SET @COUNTER = @COUNTER + 1
END
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
END
CLOSE GET_DATABASES
DEALLOCATE GET_DATABASES

adapted from 'Database Compatibility Levels : How to change all at Once' to detect the server version

Using NOEXEC for conditional processing

NOEXEC allows you to do conditional processing even if you have GO statements splitting your script into batches.

DECLARE @runit INT
SET @runit =0

SET NOEXEC OFF

PRINT '1'

IF @runit = 0
 BEGIN
 PRINT  'Skipping Next Section'
 SET NOEXEC ON
 END
GO

PRINT '2'
GO
PRINT '3'
GO
PRINT '4'
GO

SET NOEXEC OFF
PRINT '5'
GO

Run the script twice, changing the value of @runit to 1 the second time. Cool huh?

MSDN : NOEXEC

TSQL : Updating jobs that originated from an MSX server

If you try to update a SQL Agent Job or Maintainence plan that was set up using an MSX server (Master/Target environment) you get this error.

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

If an MSX server has been decommissioned, renamed or is unrecoverable you have a genuine need to override this message. Providing you have permissions on the local server, you can update the entries in msdb directly.

This query updates all jobs, making their 'owner' (originating_server). the local server.

UPDATE msdb.dbo.sysjobs
SET originating_server = CONVERT(nvarchar, SERVERPROPERTY('servername'))
WHERE originating_server <> CONVERT(nvarchar, SERVERPROPERTY('servername'))

Once run, you can update the jobs once more.

ref : MSX Error

TSQL : Development / UAT Server Prep

A simple script to loop all databases, setting them to SIMPLE recovery mode and SHRINKing any logs.

Very useful for restoring UAT / DEV databases from live environments.

sp_msforeachdb @command1 = '
 USE [?];
 IF DB_NAME() <> ''tempdb''
 BEGIN
  PRINT ''---''
  PRINT DB_NAME()
  PRINT ''---''
  DECLARE @databasename VARCHAR(1000)
  SET @databasename = DB_NAME()
  
  DECLARE @sqlcmd NVARCHAR(1000)
  SET @sqlcmd = ''ALTER DATABASE ['' + @databasename+ ''] SET RECOVERY SIMPLE ''
  EXECUTE (@sqlcmd)
  
  DECLARE @logfilename VARCHAR(1000)
  SELECT @logfilename = RTRIM(name) from sysfiles where fileid = 2
  SELECT @logfilename
  DBCC SHRINKFILE (@logfilename , 0, TRUNCATEONLY)
 END '

TSQL : SQL 2000 : List Primary Key Columns

SELECT
sysobjects.name AS TableName
,sysindexes.name AS PKName
,syscolumns.colid AS ColumnOrder
,index_col(object_name(sysindexes.id), sysindexes.indid,syscolumns.colid) AS ColumnName
FROM sysobjects 
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id 
INNER JOIN syscolumns
ON sysindexes.id = syscolumns.id
WHERE syscolumns.colid <= sysindexes.keycnt
AND sysindexes.indid = 1
--AND sysobjects.name = 'tablename'
ORDER BY sysobjects.name ,sysindexes.name  

SQL 2000 : Useful TSQL

What is the name of the primary key?

DECLARE @VALUE NVARCHAR(255)
SET @VALUE= (SELECT NAME
               FROM SYSOBJECTS
              WHERE XTYPE = 'PK'
                AND PARENT_OBJ = (OBJECT_ID('MY_TABLE'))
            )
SELECT @VALUE

Check for the existence of column? (With drop statement too!)

IF EXISTS ( SELECT a.name, b.name
                  FROM sysobjects a
                  INNER JOIN syscolumns b
                  ON a.id=b.id
                  WHERE a.xtype='u'
                  AND a.name = 'MY_TABLE'
                  AND b.name= 'MY_COLUMN'
BEGIN
      ALTER TABLE MY_TABLE DROP COLUMN MY_COLUMN
END

ALTER USER ... WITH LOGIN to fix orphaned users

sp_change_users_login is deprecated.

From sql 2005 SP2, ALTER USER .... WITH LOGIN comes into play to achieve the same, i.e. remapping orphaned users to logins

ALTER USER Username WITH LOGIN = LoginName

I like to keep usernames and logins name the same where possible, hence -

ALTER USER Doermouse WITH LOGIN = Doermouse

MSDN : ALTER USER


Here is what works in SQL 2000 / 2005 -

Lists usernames that are not mapped to logins
exec sp_change_users_login 'report'

Map db username to server login if names match -

exec sp_change_users_login 'update_one', 'username'

Maps db username to server login if names match, If no login exists, it creates one with the password given.

exec sp_change_users_login 'auto_fix', 'username' , 'password'

Links -

USP_FixUsers - Works for all users in a db
USP_FixOrphans - Works for all users in all dbs on a server
Mapping SQL Server Logins to Database Users
Fix Orphaned Users SQL 2005
MSDN : Sp_change_users_login
MSDN : Deprecated Database Engine Features in SQL Server 2008 R2

TSQL : Identity Columns that do little else!

Tables with Identity Columns, but NO clustered index

SELECT   SCHEMA_NAME(schema_id) AS SchemaName 
  ,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
  AND OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY 1, 2

Tables with Identity Columns, but NO primary key

SELECT   SCHEMA_NAME(schema_id) AS SchemaName 
  ,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
  AND OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY 1, 2

Script : Rename Unnamed Primary Keys (sql 2000 compatible)

Everyone has done it, created a constraint without naming it.
On the surface it doesn't matter. It's the name of an internal object.
The problem surfaces when you use schema comparison tools and find your live and uat environments are incorrectly reported as being different.

To demonstrate -

ALTER TABLE [dbo].[mytable] ADD PRIMARY KEY CLUSTERED 
(
 [id] ASC
)

SQL randomly names the Primary Key PK__mytable__3213E83F0BC6C43E
Deleting the key and adding it again yields a name of PK__mytable__3213E83F0EA330E9

To prevent this behaviour, explicitly name the constraint like this -

ALTER TABLE [dbo].[Table_2] ADD CONSTRAINT [PKCI_id] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)

If you've already had these keys get into production, you can locate and rename them programatically like this -

set @tablename = 'mytable'
set @newconstraintname = 'PKCI_myid'
 
-- find constraint name
select @constraintname = O.name
from sysobjects AS O
left join sysobjects AS T
    on O.parent_obj = T.id
where T.name = @tablename
  and O.xtype = 'PK'
 
SELECT @constraintname
 
-- rename if found
if not @constraintname is null
begin
    set @sql = 'sp_rename ''' + @constraintname + ''' , ''' + @newconstraintname + ''' , ''OBJECT'' ;'
    select @sql
    execute sp_executesql @sql
end

NB : This post is deliberately targeted at SQL 2000 (hence the use of the sysobjects table)

TSQL : SQL Server Version detection

As part of rollout scripts I am automating the detection of of the SQL Server version.
The current use for this is to decide whether indexes should carry included columns (a 2005+ feature), but I can think of many more.

IF (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER)) >=9
BEGIN
PRINT 'SQL 2005 or greater detected'
END

Other properties such as Edition could be used to determine whether edition specific features e.g Page Compression, Resource Governor (SQL 2008 Enterprise)

SELECT
 CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER) AS MajorVersion
,SERVERPROPERTY('ProductVersion') AS ProductVersion
,CASE LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1)
 WHEN 8 THEN 'SQL 2000'
 WHEN 9 THEN 'SQL 2005'
 WHEN 10 THEN 'SQL 2008'
END AS ProductVersion
,SERVERPROPERTY('Edition') AS Edition
,SERVERPROPERTY('ProductLevel') AS ProductLevel

Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

A script to demo all of the above, as a reminder of Windowing functions...

SELECT
  name
 ,type_desc
 ,COUNT(*) OVER(PARTITION BY NULL) AS CountAllRecords
 ,ROW_NUMBER() OVER (ORDER BY name) AS RowNumberByName
 ,RANK()  OVER (ORDER BY type_desc) AS RankbyType
 ,DENSE_RANK()  OVER (ORDER BY type_desc) AS DenseRankbyType
 ,RANK() OVER (ORDER BY LEFT(Name,1)) AS RankByFirstCharacterofName
 ,DENSE_RANK() OVER (ORDER BY LEFT(Name,1)) AS DenseRankByFirstCharacterofName
 ,ROW_NUMBER() OVER (PARTITION BY LEFT(Name,1) ORDER BY LEFT(Name,1)) AS RowNumberPartitionedbyLeft1
FROM sys.objects
ORDER BY name

(the deprecated) TEXT datatype

Argh! Have come up against the deprecated text datatype in a database i’m reporting on.
It’s a migrated SQL 2000 product in 8.0 Compatibility mode!

I cannot use equals (=) in a WHERE clause against the text column

So when I want to write

SELECT * FROM ProductUpdates WHERE Build = ‘9.71’

I get ...

Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.

I have to use PATINDEX to get around it, like this –

SELECT * FROM ProductUpdates  WHERE PATINDEX('9.71',Build) > 0

Or CAST the column like this

SELECT * FROM ProductUpdates  WHERE CAST(Build AS VARCHAR(MAX)) = ‘9.71’

Although this second approach would ruin the sargability of the query, i.e. the ability to use an index seek on the column.

Link : Using equal operator in transact-SQL for ntext datatype column

TSQL : Returning the SQL Server IP Address

I recently wrote some audit scripts and wanted to find the IP Address of the server from TSQL. The majority of the replies on twitter correlated with my google findings, i.e. to use extended stored procedure xp_cmdshell to run an operating system command.

Leaving security concerns aside , here's a reminder on enabling xp_cmdshell

Using xp_cmdshell to retrieve server ip address -

create table #cmdresults(ip varchar(255))
insert into #cmdresults exec xp_cmdshell'ipconfig | find "IP Address"'
select ltrim(rtrim(substring(ip,charindex(':',ip)+1,len(ip)))) from #cmdresults where ip is not null
drop table #cmdresults

On moving it to my server, I found it did not work. The difference being that I needed to look for 'IPv4 Address', not 'IP Address' on my Windows 2008 installs where entries for IPv4 and IPv6 could both exist.

A simpler version was suggested by Kendra Little (@Kendra_Little | blog) was to look at the connections dmv, like this

Using DMV to return session ip address -

SELECT local_net_address FROM sys.dm_exec_connections WHERE session_id=@@SPID 

This returns the connected client IP, so it only returns the server IP if you are working on your server (or an rdp session on it).

For the purpose of what I needed (reports emailed from the sql server), it is perfectly adequate (and tidier than xp_cmdshell).

Finding Transactions / Second

The Performance Counters dmv can be used to determine Transactions per second.
Transactions is a cummulative counter however, so here is what I do to interpret it.

Use the script below, replacing 'database_name' as appropriate.
You may wish to change the delay (10s used here) to a more appropriate value (remembering to change the calculation that follows too).

SELECT cntr_value, *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
AND OBJECT_NAME = 'SQLServer:Databases'
AND instance_name = 'database_name'
  
WAITFOR DELAY '00:00:10'
  
SELECT cntr_value, *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
AND OBJECT_NAME = 'SQLServer:Databases'
AND instance_name = 'database_name'

The queries returned 581820652 and 581821012 respectively.

581821012 - 581820652 = 360

360 Transactions in a 10 second period

Therefore, 360 /10 = 36

36 Transactions per second.

TSQL : Removing a filegroup

Check no objects sit on the filegroup.
(If they do, then remove them)

SELECT i.*
FROM sys.indexes i
LEFT JOIN sys.filegroups AS fg
ON fg.data_space_id = i.data_space_id
WHERE fg.name = 'myfilegroup'

When all objects are gone, remove the file...

ALTER DATABASE mydatabase
REMOVE FILE mydatabaseFile

When the file has gone, remove the filegroup...

ALTER DATABASE mydatabase
REMOVE FILEGROUP mydatabaseFileGroup

Using @@IDENTITY to return an identity value

Returning an identity value...

-- given a table like this..

CREATE TABLE Division
(DivisionId INT IDENTITY(1,1) PRIMARY KEY,
DivisionName varchar(100))

-- retrieve ID like this...

INSERT INTO Division (DivisionName)
VALUES (@DivisionName)
SELECT @DivisionID = @@IDENTITY

RETRY mechanism with WHILE loop (tsql template)

This code demonstrates a RETRY mechanism with a WHILE loop.
The loop continues until the task succeeds or @MaxAttempts is reached.
If @MaxAttempts is reached and the task still fails, an email is sent

BEGIN

SET NOCOUNT ON

DECLARE @MaxAttempts INT
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)
DECLARE @ErrorMessage NVARCHAR(4000)  
DECLARE @ErrorSeverity INT  
DECLARE @EmailMessage VARCHAR(100)

SET @ErrorMessage = 'run_attempt'
SET @Counter = 0
SET @maxAttempts = 3


-- @ErrorMessage will be NULL after a successful execution of the program.
-- Checking for IS NOT NULL will mean the loop continues until success.

WHILE (@ErrorMessage IS NOT NULL) AND (@ErrorMessage NOT LIKE 'Warning: Null value%') AND (@Counter <= @MaxAttempts )

BEGIN   

 -- Increment counter and display run number to the screen
 SELECT @Counter = @Counter + 1 
 SET @CounterText = CONVERT(VARCHAR(30),@Counter,23)
 RAISERROR (@CounterText, 10, 1) WITH NOWAIT   
 
 
 BEGIN TRY
 
  -- Run the program you want to 'retry' 
  EXEC myschema.mystoredProc
  SELECT  @ErrorMessage = ERROR_MESSAGE() ,  @ErrorSeverity = ERROR_SEVERITY()  
 
 END TRY
 
 BEGIN CATCH

  IF @Counter = @MaxAttempts
  BEGIN
  
    SET @EmailMessage = 'mystoredProc  : Run Attempt : ' + CONVERT(VARCHAR(30),@Counter,23) + ' failed '

    EXEC msdb.dbo.sp_send_dbmail 
       @profile_name='SQL Profile'
     , @recipients='recipent@domain.com'
     , @body=@EmailMessage
     , @subject=@EmailMessage
     , @importance='High'
     
  END
 
 END CATCH

END  

END

Logging Errors to SQL logs via RAISERROR WITH LOG

Logging errors to SQL logs via RAISERROR >

1) The Code -

RAISERROR('Test of custom error logging', 18, 1) WITH LOG

2) Management Studio Results

3) Corresponding entry in the SQL Server log

Expanding on this we can pass the genuine error message through, like this -
1) The Code -

BEGIN TRY
   BEGIN TRANSACTION 

 -- Do action that we want rolled back if an error occurs
 DELETE Person.Address WHERE AddressID = 1  

   COMMIT
END TRY

BEGIN CATCH
  IF @@TRANCOUNT > 0
     ROLLBACK

 DECLARE @ErrorMessage NVARCHAR(4000)
 DECLARE @ErrorSeverity INT

 SELECT
  @ErrorMessage = ERROR_MESSAGE(),
  @ErrorSeverity = ERROR_SEVERITY()

 RAISERROR(@ErrorMessage, @ErrorSeverity, 1) WITH LOG
 
END CATCH

2) The SQL log -

You can do a variety of Error logging methods here in this way -
1 ) send email
2 ) write to table
3 ) log to sql via RAISERROR (as abover)

You have access to the following error functions -

ERROR_NUMBER() - returns the number of the error.
ERROR_SEVERITY() - returns the severity.
ERROR_STATE() - returns the error state number.
ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() - returns the line number inside the routine that caused the error.
ERROR_MESSAGE() - returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.


link : http://technet.microsoft.com/en-us/library/ms175976.asp

Missing IDs in an integer sequence (the gap problem)

I encountered a missing integer ID problem today and rather than reinventing the wheel found the following script to deal with it.

Finding (all the) gaps in an identity column (or any integer based column for that matter) using Sql 2005

I have added OPTION (MAXRECURSION 0) to the end of the SELECT statement so that the CTE can be called as many times as necessary.

declare @i int;
 SELECT @i = MAX(pkid) FROM t1;
 WITH tmp (gapId) AS (
   SELECT DISTINCT a.pkid + 1
   FROM t1 a
   WHERE NOT EXISTS( SELECT * FROM t1 b
        WHERE b.pkid  = a.pkid + 1)
   AND a.pkid < @i
   UNION ALL
   SELECT a.gapId + 1
   FROM tmp a
   WHERE NOT EXISTS( SELECT * FROM t1 b
        WHERE b.pkid  = a.gapId + 1)
   AND a.gapId < @i
 )
 SELECT gapId
 FROM tmp
 ORDER BY gapId
OPTION (MAXRECURSION 0) ;

A good article on other sequence generation scripts is Creating a Number (Sequentially incrementing values) table in T-SQL