SQL Server Code,Tips and Tricks, Performance Tuning

So how does an index work?

In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list.

Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

• The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.
• The leaf layer of a nonclustered index is made up of index pages instead of data pages.

Nonclustered indexes can be defined on a table or view with a clustered index or a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value.
The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

• If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).
• If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.

Keep your clustered indexes narrow

CREATE TABLE Test1(id int, somecol char(36), somecol2 char(36))
GO
INSERT Test1 
SELECT number,newid(),newid() 
FROM master..spt_values
WHERE type = 'P'

CREATE CLUSTERED INDEX cx on Test1(id)

CREATE NONCLUSTERED INDEX ix1 on Test1(somecol)
CREATE NONCLUSTERED INDEX ix2 on Test1(somecol2)

SELECT
DB_NAME(DATABASE_ID) AS [DatabaseName],
OBJECT_NAME(OBJECT_ID) AS TableName,
SI.NAME AS IndexName,
INDEX_TYPE_DESC AS IndexType,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID AND DPS.INDEX_ID = SI.INDID
AND OBJECT_NAME(OBJECT_ID) = 'Test1'
GO

DatabaseName TableName IndexName IndexType PageCounts
tempdb Test1 cx CLUSTERED INDEX 22
tempdb Test1 ix1 NONCLUSTERED INDEX 12
tempdb Test1 ix2 NONCLUSTERED INDEX 12

EXEC sp_spaceused 'Test1'

name rows reserved data index_size unused
Test1 2048 472 KB 176 KB 240 KB 56 KB

CREATE CLUSTERED INDEX cx on Test1(id,somecol,somecol2)
WITH DROP_EXISTING

SELECT
DB_NAME(DATABASE_ID) AS [DatabaseName],
OBJECT_NAME(OBJECT_ID) AS TableName,
SI.NAME AS IndexName,
INDEX_TYPE_DESC AS IndexType,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID AND DPS.INDEX_ID = SI.INDID
AND OBJECT_NAME(OBJECT_ID) = 'Test1'
GO

DatabaseName TableName IndexName IndexType PageCounts
tempdb Test1 cx CLUSTERED INDEX 22
tempdb Test1 ix1 NONCLUSTERED INDEX 21
tempdb Test1 ix2 NONCLUSTERED INDEX 21

EXEC sp_spaceused 'Test1'

name rows reserved data index_size unused
Test1 2048 600 KB 176 KB 384 KB 40 KB

SET SHOWPLAN_TEXT ON
GO
SELECT count(*) FROM Test1
GO
SET SHOWPLAN_TEXT OFF
GO

|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
|--Index Scan(OBJECT:([ReportServer].[dbo].[Test1].[ix2]))

We have several domains at my job, we have a dev domain, a prod domain and then several other domains. Recently we had some domain changes where we would connect to a different domain when logging in from the laptops.

When opening up SSMS and connecting to the SQL Server instances, we now need to use a fully qualified domain name (FQDN) and also specify the port number and the instance name. This could seem a little confusing if you have never done this

Here is what it looks like if for example your instance is listening on port 8000, the instance name is SQL01, the server name is DevSQL01 and the domain name is SQLRules.net

You would put the following in server name

DevSQL01.SQLRules.net,8000\SQL01

To make is easier to see... here it is again with different colors for the different parts

DevSQL01.SQLRules.net,8000\SQL01

The server name is DevSQL01

The domain name isSQLRules.net

The port number is 8000

The instance name in SQL01

The fully qualified domain name (FQDN) in this case is of course DevSQL01.SQLRules.net

Hopefully this helps someone in the future

Very similar to my After 20+ years in IT .. I finally discovered this... post, I discovered yet another command I should have know about

Originally I was not going to write this post, but after I found out that several other people didn't know this, I figured what the heck, why not, maybe someone will think this is cool as well

Open up a command prompt or powershell command window, , navigate to a folder, type in tree... hit enter

Here is what I see

I was watching a Pluralsight course and the person typed in the tree command.. and I was like whoaaaa.. How do I not know this? Perhaps maybe because I don't use the command window all that much? Anyway I thought that this was pretty cool

As you can see tree list all the directories and sub directories in a tree like structure. This is great to quickly see all the directories in one shot

The same command will work in Powershell

Finally, here is an image of the command window output as well as the explorer navigation pane side by side

Hopefully this will be useful to someone

WITH TIES Returns two or more rows that tie for last place in the limited results set. You must use this argument with the ORDER BY clause. WITH TIES might cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows.You can specify the TOP clause with the WITH TIES argument only in SELECT statements, and only if you've also specified the ORDER BY clause. The returned order of tying records is arbitrary. ORDER BY doesn't affect this rule.

CREATE TABLE #TopExample(GradeAverage int, Student varchar(100))
INSERT #TopExample VALUES(99.00,'Plato'),
 (98,'Socrates'),
 (95,'Diogenes the Cynic'),
 (94,'Antisthenes'),
 (94,'Demetrius'),
 (50,'Denis')

SELECT TOP 4 GradeAverage, Student 
FROM #TopExample 
ORDER BY GradeAverage DESC

GradeAverage Student
99 Plato
98 Socrates
95 Diogenes the Cynic
94 Demetrius

SELECT TOP 4 WITH TIES GradeAverage, Student 
FROM #TopExample 
ORDER BY GradeAverage DESC

GradeAverage Student
99 Plato
98 Socrates
95 Diogenes the Cynic
94 Demetrius
94 Antisthenes

;WITH c AS (SELECT DENSE_RANK() OVER (ORDER BY GradeAverage DESC) AS dens, 
 GradeAverage,Student 
 FROM #TopExample)
SELECT GradeAverage, Student 
FROM c WHERE dens <=4
ORDER BY GradeAverage DESC 

GradeAverage Student
99 Plato
98 Socrates
95 Diogenes the Cynic
94 Demetrius
94 Antisthenes

CREATE TABLE #TopExample(GradeAverage int, Student varchar(100))
INSERT #TopExample VALUES(99.00,'Plato'),
 (98.00,'Socrates'),
 (95.00,'Diogenes the Cynic'),
 (94.00,'Antisthenes'),
 (94.00,'Demetrius'),
 (50.00,'Denis')
SELECT TOP 4 GradeAverage, Student 
FROM #TopExample 
ORDER BY GradeAverage DESC
SELECT TOP 4 WITH TIES GradeAverage, Student 
FROM #TopExample 
ORDER BY GradeAverage DESC
;WITH c AS (SELECT DENSE_RANK() OVER (ORDER BY GradeAverage DESC) AS dens, 
 GradeAverage,Student 
 FROM #TopExample)
SELECT GradeAverage, Student 
FROM c WHERE dens <=4
ORDER BY GradeAverage DESC 
DROP TABLE #TopExample

And here is what it all looks like in SSMS, code and output

PostgreSQL adds FETCH FIRST WITH TIES.. just like TOP n WITH TIES in SQL Server Query in SSMS 

WAITFOR TIME '09:57:16'
PRINT 'DONE ' 

WAITFOR TIME '09:57'
PRINT 'DONE ' 

SELECT CONVERT(time, getdate()) --'09:57:16.9600000'
WAITFOR TIME '09:57:16.9600000'

WAITFOR TIME '09:57:16'

1
2

SELECT PARSENAME('09:57:16.9600000',2), 
PARSENAME('09:57:16.9600000',1)

 
1
2
 
SELECT CONVERT(time, getdate()) ,--'09:57:16.9600000'
CONVERT(time(0), getdate()) --'09:57:16

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

DECLARE @DelayTime time(0)= GETDATE()
PRINT @DelayTime
SELECT @DelayTime =DATEADD(second,10,@DelayTime)
PRINT @DelayTime
DECLARE @d varchar(100) = @DelayTime -- you have to use varchar in command
WAITFOR TIME @d 
-- Run your command here
PRINT 'DONE ' + CONVERT(varchar(100),CONVERT(time(0), getdate()))

1
2
3
4

INSERT #temp(SomeCol, SomeTimeStamp)
SELECT COUNT(*), GETDATE() FROM sometable 
WAITFOR DELAY '00:01:00'
GO 20