SQL Server Tips

SQL Server Concepts and Best Practices [Part 1]

Hi Folks,

It’s been a while that I don’t post anything here, so it’s time to start a series of SQL Server concepts and Best Practices. Most of them, I’ve already shared in my repository on GitHub.
So, take a peek when you get a chance. https://github.com/danilocbraga/SQLServer

MAX vs Top 1 – which one is better?

When there is a clustered index on the table and the column to be queried, both the MAX() operator and the query SELECT TOP 1 will have almost identical performance.

When there is no clustered index (Heap) on the table and the column to be queried, the MAX() operator offers the better performance. So, consider using MAX rather than SELECT TOP 1.

CTE vs #temp table


* Are unindexable (but can use existing indexes on referenced objects)
* Cannot have constraints
* Are essentially disposable VIEWs
* Persist only until the next query is run
* Can be recursive
* Do not have dedicated stats (rely on stats on the underlying objects)

Temp Tables…

* Are real materialized tables that exist in tempdb
* Can be indexed
* Can have constraints
* Persist for the life of the current CONNECTION
* Can be referenced by other queries or subprocedures
* Have dedicated stats generated by the engine

As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred. Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it’s just a disposable view. You can do some neat things with them but speeding up a query isn’t really one of them.

When to use char/varchar/nchar/nvarchar?

  • nchar and nvarchar can store Unicode characters.
  • char and varchar cannot store Unicode characters.
  • char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don’t use up all that space.
  • varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar. nchar and nvarchar will take up twice as much storage space, so it may be wise to use them only if you need Unicode support.

It is worth highlighting the importance to keep your code (variables) using the same data type definition as the column definition. Mismatch data types will probably result in Non-Sargable queries.

Using the same column name in different tables but with different data types

Probably anybody will assume that columns with the same name in different tables have the same data type. As a result, they won’t verify data types. Different types is an accident waiting to happen.

Check that before creating a new column with the same name:

SELECT sh.name+'.'+o.name AS ObjectName,
s.name as ColumnName,
CASE WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
ELSE t.name END AS DataType
,CASE WHEN s.is_nullable=1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable     
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
WHERE O.name IN (select table_name from information_schema.tables)
--AND s.name = 'NewColumn'
ORDER BY sh.name+'.'+o.name,s.column_id

Float and real datatypes

Why we should not use float and real datatypes to store money in SQL Server? The main problem with them is that they are approximate numerics, so they don’t store exact values.

Take a look at this:

image2017-6-29 10-35-6

The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types. Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types. https://technet.microsoft.com/en-us/library/ms187912(v=sql.105).aspx

Decimal is always a better choice even compared to money datatype, where we might have issues in cases of division.

image2017-6-29 10-35-47

I hope I could help you somehow.
See you soon!


How to list all objects and indexes [SQL Server]

Today I had to list which objects belongs to which filegroup.
It’s a simple and useful script that you can take from system catalogues: sys.filegroups, sys.indexes, sys.database_files and sys.data_spaces.

SELECT Object_name(i.[object_id]) AS [ObjectName],
       i.[index_id]               AS [IndexID],
       i.[name]                   AS [IndexName],
       i.[type_desc]              AS [IndexType],
       f.[name]                   AS [FileGroup],
       d.[physical_name]          AS [DatabaseFileName]
FROM   [sys].[indexes] i
       LEFT JOIN sys.partition_schemes pf
              ON pf.[data_space_id] = i.[data_space_id]
       LEFT JOIN [sys].[filegroups] f
              ON f.[data_space_id] = i.[data_space_id]
       LEFT JOIN [sys].[database_files] d
              ON f.[data_space_id] = d.[data_space_id]
       LEFT JOIN [sys].[data_spaces] s
              ON f.[data_space_id] = s.[data_space_id]
WHERE  Objectproperty(i.[object_id], 'IsUserTable') = 1
ORDER  BY Object_name(i.[object_id]),

Take a look this sample


In the next post I’m going to share How Move Index to other FileGroup.

How to monitor Timeout and Changes Roles from Availability Groups with Alerts


Agent alert can send alerts by email when occur some action/timeout between databases in your Availability Group Environment. It allows you to become proactive.

The following script shows how to create these alerts.

EXEC msdb .dbo. sp_add_operator @name =N'AlertTeam',
               @enabled=1 ,
               @weekday_pager_start_time=90000 ,
               @weekday_pager_end_time=180000 ,
               @saturday_pager_start_time=90000 ,
               @saturday_pager_end_time=180000 ,
               @sunday_pager_start_time=90000 ,
               @sunday_pager_end_time=180000 ,
               @pager_days=0 ,
               @email_address=N'email@email.com.br' ,

-- 1480 - AG Role Change (failover)
EXEC msdb .dbo. sp_add_alert
        @name = N'AG Role Change',
        @message_id = 1480,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
EXEC msdb .dbo. sp_add_notification
        @alert_name = N'AG Role Change',
        @operator_name = N'AlertTeam',
        @notification_method = 1;

-- 35264 - AG Data Movement - Resumed
EXEC msdb .dbo. sp_add_alert
        @name = N'AG Data Movement - Suspended',
        @message_id = 35264,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
EXEC msdb .dbo. sp_add_notification
        @alert_name = N'AG Data Movement - Suspended',
        @operator_name = N'AlertTeam',
        @notification_method = 1;

-- 35265 - AG Data Movement - Resumed
EXEC msdb .dbo. sp_add_alert
        @name = N'AG Data Movement - Resumed',
        @message_id = 35265,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
EXEC msdb .dbo. sp_add_notification
        @alert_name = N'AG Data Movement - Resumed',
        @operator_name = N'AlertTeam',
        @notification_method = 1;

-- 35206 - AG Timeout to Secondary Replica
EXEC msdb .dbo . sp_add_alert
        @name = N'AG Timeout to Secondary Replica',
        @message_id = 35206,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
EXEC msdb .dbo . sp_add_notification
        @alert_name = N'AG Timeout to Secondary Replica',
        @operator_name = N'AlertTeam',
        @notification_method = 1;
-- 35202 - AG Timeout to Secondary Replica
EXEC msdb .dbo . sp_add_alert
        @name = N'AG Connection has been successfully established',
        @message_id = 35202,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
EXEC msdb .dbo . sp_add_notification
        @alert_name = N'AG Connection has been successfully established',
        @operator_name = N'AlertTeam',
        @notification_method = 1;

You can also configure to execute another SQL Agent job to perform your required action.
I recommend you to execute a Job with the Stored Procedure inside the link below to show how is the synchronism status.

I hope this post can help

Repository of White Papers – SQL Server

Like a good reader,
I highly recommend that you read these white papers.
These links contain much more knowledge that I can provide here.


That’s it! Have fun.

Lookup All File Sizes, Status, and FileName for all Databases

This script can help you find some details in all databases.

IF EXISTS(SELECT NULL FROM msdb..sysobjects where name = 'DBFileSizes' AND type ='U')
     TRUNCATE TABLE dbo.DBFileSizes
     [DBname] nvarchar(128) NULL,
     [fileid] [smallint] NULL,
     [groupid] [smallint] NULL,
     [size] [int] NOT NULL,
     [maxsize] [int] NOT NULL,
     [growth] [int] NOT NULL,
     [status] [int] NULL,
     [perf] [int] NULL,
     [name] [sysname] NOT NULL,
     [filename] [nvarchar](260) NOT NULL )
EXEC master..sp_msforeachdb &amp;quot;INSERT INTO msdb.dbo.DBFileSizes
[DBname], * FROM [?]..[sysfiles]&amp;quot;
     FROM dbo.DBFileSizes
DROP TABLE dbo.DBFileSizes


How do I find a value anywhere in a SQL Server Database

The query below will search all fields in all tables for a specific value.

--Variable Delaration
Declare @keytosearch varchar(max), @Database_Selected varchar(50)

set @keytosearch ='%danilo%'
set @Database_Selected= 'Northwind'

Declare @Table varchar(100), @Table_Name Cursor, @Count_Column int, @Result nvarchar(4000),@ID int,@ID_inserted int,@Count_Table int, @data_type varchar(10)

set @ID_inserted=0  
set @Count_Table=0

DECLARE @column varchar(max), @Column_Name CURSOR
--Variable Delaration end

--Second Cursor start
declare @informationName varchar(50), @SysName varchar(50), @Var varchar(5)

set @informationName=@Database_Selected+'.'+'information_schema.COLUMNS'
Set @SysName=@Database_Selected+'.'+'sys.objects'
Set @Var='u'

--Database Selected start
Create Table #SearchResult(ID int identity(1,1) not null, TableName varchar(50), ColumnName varchar(50))
Create Table #SearchTestResult(ID int identity(1,1) not null, ColumnName varchar(max))
Create Table #Table_Name(ID int identity(1,1) not null, table_name varchar(50))

  drop table Column_Nam

insert into #Table_Name exec ('Select name from '+@SysName +' where type= '''+@Var+'''')

--First Cursor open
SET @Table_Name = CURSOR FOR Select table_name from #Table_Name

open @Table_Name

Fetch Next from @Table_Name into @Table

  set @Count_Table =@Count_Table+1
  --Second cursor opened
  --print 'Select column_name from  '+@informationName +' where table_name= '''+@Table+''''s
  Exec('SELECT column_name,data_type INTO Column_Nam FROM  '+ @informationName +' where table_name = '''+@Table+'''')
  SET @Column_Name = CURSOR FOR (select column_name from Column_Nam )

  OPEN @Column_Name FETCH NEXT FROM @Column_Name INTO @column

  set @Table=@Database_Selected+'.[dbo].['+@Table+']'
    set @data_type= (SELECT top 1 data_type FROM Column_Nam Where column_name= @column)

    if @data_type is not null and (@data_type='varchar' or @data_type='nvarchar' or @data_type='Text')
      set @Result=null
      if @column like '%-%' begin  set @Result =('SELECT ''' + @column +''' FROM ' + @Table 
                +' Where ''' + @column + ''' Like '''+@keytosearch+'''') end
          set @Result =('SELECT ' + @column +' FROM ' + @Table  +' Where ' + @column + ' Like '''+@keytosearch+'''')

      insert into #SearchTestResult exec(@Result)

      set @ID=0
      set @ID=(Select ID from #SearchTestResult where ID=Scope_identity())

       if @ID is not null
        set @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)
            if @ID_inserted = @ID
              print ''
             insert into #SearchResult values (@Table,@column)
    FETCH NEXT FROM @Column_Name INTO @column
  CLOSE @Column_Name 
  DEALLOCATE @Column_Name
  --Second cursor closed

  drop table Column_Nam

  Fetch Next from @Table_Name into @Table

close @Table_Name 

Deallocate @Table_Name
--First Cursor Closed

Select * from #SearchResult

Enjoy it!

MSDB Scripts

Many times, you need to get informations from MSDB database.
I’m going to share some simple scripts that can help you.

Increase SQL Agent History Retention
If you have many jobs that run frequently, you may want to increase the retention to use it later on to troubleshoot.