CURSOR Code To Iterate Through Databases In SQL Server

Most of us in SQL world would have come across cursors for some help in functionality here and there in the code. Cursors are used as a part of interpreting the result set on a set of rows. The set of rows called the result set is declared in a cursor for processing the output. There are several instances in the industry where there is a requirement to generate the result set at row level. One possible way is by using cursors. But, let’s check the performance, advantages, and disadvantages of cursors later.

Further, in order to get the result set at row level, we have an approach to iterate through all databases using the cursor as below.

DECLARE db_names CURSOR
FOR
select name from sys.databases
DECLARE @db_name varchar(100)
DECLARE @sql NVARCHAR(max)
OPEN db_names
FETCH NEXT FROM db_names INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ' USE  '+@db_name+
GO 
<your code here>
GO
'
Print @sql
FETCH NEXT FROM db_names INTO @db_name
END
CLOSE db_names
DEALLOCATE db_names


OUTPUT:

The output gives us a print command, which can be used to execute in a new instance in the SQL Server. If in case, the requirement is to execute the @sql parameter directly, then please comment on the print command and uncomment the Exec command. 

Cursor Code Output








Note: Cursors are not the best practice for looping through your SELECT rows. We must always keep in mind to implement them in production thinking their future performance portfolio.

You may also find this interesting: While loop to Iterate through all databases

Difference Between Clustered Index And Non-Clustered Index In SQL Server

Indexes are purely used to increase the performance of your queries. The creation of indexes can be something resembled that of an index page on a textbook that can help you directly go to the page with reference.

A similar concept applies while creating indexes in SQL Server. Additionally, an index associated with a table is in-turn latched up with the disk structure. Whenever, there is a call for an index, the associated on-disk pointer retrieves the data quickly for high performance. 

If your table is without an index, then there happens to be a HEAP index. HEAP is nothing but a table without a clustered index. In this situation, the data is stored randomly causing the whole table scan whenever data retrieval operation happens. Keeping the HEAP index apart, let’s dive to see the differences between a clustered and a non-clustered index.

A table can consist of two types of indexes:

1. Clustered Index
2. Non-Clustered Index

Clustered Index

A clustered index is mapped on to a table that helps in sorting and storing the data rows with respect to key values. The only possible way of storing the data in a sorted manner is while creating a clustered index. And this is the actual reason behind why a table can have just a single clustered index.

Let’s check the scenario of creating a clustered index on a table.

C1. Create a table as [Clustered_Index_TEST]

create table [Clustered_Index_TEST]
(
ID int constraint PK_Clustered_Index_TEST_ID primary key (ID)
,[Students] nvarchar(50) null
,[Marks] int
)

C2. Insert some records in [Clustered_Index_TEST] table

insert into [Clustered_Index_TEST]
values (2,'Jason','97') 

insert into [Clustered_Index_TEST]
values (1,'James','54') 

insert into [Clustered_Index_TEST]
values (3,'Adams','68') 

insert into [Clustered_Index_TEST]
values (6,'Tyler','71')

C3. Select records from [Clustered_Index_TEST] table where ID=2 for an example

select * from [Clustered_Index_TEST]
where ID =2

Clustered Index 1





Clustered Index Execution Plan












Observations: If you notice in the execution plan, the number of reads on the table is 1 and Logical Operation is Clustered Index Seek. We can conclude here saying that the clustered index on the ID column searched is going to be fetched directly using the index rather than a complete table scan. 

Further, the table’s ID column was inserted in a jumbled manner, but the clustered index helped in sorting them.

POINT TO NOTE: Every table in SQL Server created should have a mandatory clustered index created for better performance. 

Non-Clustered Index

Non-clustered Index is mapped on a table wherein the data rows are not in a sorted manner. The non-clustered indexes have a separate structure that holds key values. Every time there is a call for a non-clustered index, the respective key values fetch the data row that the index holds.

This scenario can resemble that of a website link. The link of a website resides at one place and the content page behind the link resides at another. Every time, we click the link (non-clustered index with key-value), the content page of the link is fetched (data rows where the non-clustered index with key points).

Let’s check the scenario of creating non-clustered index on a table.

NC1. Create a table as [NonClustered_Index_TEST]

create table [NonClustered_Index_TEST]
(
ID int
,[Students] nvarchar(50) null
,[Marks] int
) 

NC2. Insert some records in [NonClustered_Index_TEST] table

insert into [Clustered_Index_TEST]
values (2,'Jason','97') 

insert into [Clustered_Index_TEST]
values (1,'James','54') 

insert into [Clustered_Index_TEST]
values (3,'Adams','68') 

insert into [Clustered_Index_TEST]
values (6,'Tyler','71')

NC3. Create a non-clustered index on ID column 

create nonclustered index Ind_NCI_TEST_ID on NonClustered_Index_TEST(ID)

NC4.  Select records from [NonClustered_Index_TEST] table where ID=2 for an example

select * from  [NonClustered_Index_TEST]
where ID =2

Non Clustered Index





Non Clustered Index Execution Plan












Observation: If you notice from the execution plan, the number of reads on the table is 4 and the logical operation performed is Table Scan. It means that every time a record is fetched from the table, the SQL Server will scan the entire table rather than just one record. 

POINT TO NOTE: Please have the practice to create a clustered index on a table. If exists already as a HEAP index on a table, please convert them to the clustered index for high performance.

You may find this helpful: 'Difference between Shared lock, Exclusive lock, and Update lock'


Reset Identity Column Values In SQL Server

IDENTITY Property is a feature used with the CREATE or ALTER TABLE commands and is used to generate unique values for a column. The main reason behind using IDENTITY property is to keep the columns unique.

Generally, in the industry’s production environment, when the tables are highly loaded, there are high chances the data can just get mixed up. An IDENTITY property on a table can simply be easy to identify the column even on a table holding thousands of records.

General Syntax:

IDENTITY [ (seed , increment) ]

Seed – Seed is the value assigned to the initial row for a table

Increment – Increment is the value assigned to the IDENTITY value that will be an incremental addition to the previous row count

The value passed default when seed and the increment are not assigned while creating IDENTITY property is (1,1).

A common error encountered before fixing the IDENTITY RESEED is as below:

 Let’s check the steps to reseed the IDENTITY value. 

Step 1: Create a table as [IDENTITY_TEST]

create table [IDENTITY_TEST]
(ID int identity(1,1) not null,
number int,
subnumber int
)

Step 2: Insert records into [IDENTITY_TEST] table

Identity Reseed 1




Step 3: Check the max of ID on [IDENTITY_TEST] table

select max(ID) from IDENTITY_TEST

Identity Reseed 2




Step 4: Check IDENTITY column value

select IDENT_CURRENT('IDENTITY_TEST')

Here the identity scope value should be 6 but is giving an output as 2. The reason could be multiple and is mentioned at the end of the article. 

Identity Reseed 3




Step 5: Reset IDENTITY SCOPE to the current max ID value

DBCC CHECKIDENT ('IDENTITY_TEST', RESEED, 6);

Identity Reseed 4

If you would notice that the IDENTITY property was anonymously set to ID=2. The possible reasons for IDENTITY property to go out of order are when any one of below happens on your table:

1. Server restart or Database failure – this is part of the SQL Server’s performance reasons where the Identity value gets cached.
2. 
When there is a BULK LOAD on your table.
3. 
Heavy DML transactions (Insert/Update/Delete operation).
4. 
Transaction replication out of Synch.
5. 
If a transaction is initiated and left uncommit. 

Note: Some of the IDENTITY scope values being cached to the older numbers are moreover unavoidable and has to be manually reset with the above steps.

You may also refer: 'Steps to add/remove identity property in SQL Server'

Check Fragmentation and Fix Fragmentation In SQL Server

The fragmentation is one of the most common issues that the DBAs face while working on the production environment. The fragmentation can play a vital role in query performance. Personally speaking, fragmentation can simply freak out on big tables and can sometimes be hectic to deal with. However, let’s learn about the fragmentation in detail.

Fragmentation: To make the definition much simpler, the fragmentation is something that is broken up. In SQL Server, the fragmentation means that the data stored on the disk is in the form of non-contiguous. Additionally, we have three types of fragmentation that are diversified:

1. Internal Fragmentation: Internal fragmentation happens when the records on the page are stored in a non-contiguous manner. Basically, this is encountered when there are huge DML commands (INSERT, UPDATE, and DELETE) happening on the table and the underlying indexes. As there are more modifications happening on a table, which eventually affects the page to unequally filled. This scenario can cause leveraging issues.

2. External fragmentation: External fragmentation happens when the extents are stored in a non-contagious manner. This is basically on a disk where the table extents are not stored in an ordered format. When the table extent pointers are scattered on the disk from one place to another, there can be degraded in performance due to high disk rotations. 

3. Logical Fragmentation: Logical fragmentation happens when every page containing its own logical sequence is disrupted. Every page has a pointer at the leaf level, and they point to the next page. The logical sequence that is out of order fall under this category.

To check the fragmentation on a single database but for all the tables:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'Your DB Name'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

My DB name here: Distribution


To check the fragmentation on a single database and for a single table:

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N' Your DB Name');
SET @object_id = OBJECT_ID(N'Your Table Name');
IF @object_id IS NULL
BEGIN
PRINT N'Invalid object';
END

ELSE

BEGIN
SELECT IPS.Index_type_desc,
I.NAME,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'DETAILED') AS IPS
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC

END

My DB name here: Distribution
My Table name here: One of the distribution table 


From the results, you may notice two important key columns as ‘avg_fragmentation_in_percentage’ and ‘avg_page_space_used_in_percentage.

1. avg_fragmentation_in_percentage: It represents the external fragmentation that we discussed earlier. The lower the value the higher is the performance.

2. avg_page_space_used_in_percentage: It represents the internal fragmentation that we discussed earlier as well. The higher values mean that the pages are more filled at each page level. The higher values mean that it's going to perform better.

Fix the fragmentation:

There are two partitions made while fixing the index fragmentation. The indexes with fragmentation percentage below 30 and the other with a percentage above 30.

If < 30 percent fragmentation levels: Perform a REORG operation on the index to reorder the logical sequence.

Query:

Single index reorg:

ALTER INDEX [your index name] ON [dbo].[your table name] REORGANIZE

Reorg for all indexes in a table:

ALTER INDEX ALL ON [your table name] REORGANIZE

Note: Performing an ALTER INDEX ALL has some conditions behind, wherein it works only on statistics related to the index. Manual and Automatic statistics created are not updated on the table.

If > 30 percent fragmentation levels: Perform a REBUILD operation as the fragmentations are high enough that a REORG operation won’t really help in fixing the issue. 

Query:

Single index rebuild:

ALTER INDEX [your index name] ON [dbo].[your table name] REBUILD

Single index rebuild with ONLINE option:

ALTER INDEX [your index name] ON [dbo].[your table name] REBUILD WITH (ONLINE = ON)

Rebuild all indexes in a table:

ALTER INDEX ALL ON [dbo].[findingQn] REBUILD

Rebuild all indexes in a table with ONLINE option:

ALTER INDEX ALL ON [dbo].[findingQn] REBUILD WITH (ONLINE = ON)

Note: Please run the ONLINE REBUILD during OFF hours of your production servers as they can shoot up your CPU levels significantly. 

How To Find The Missing Logins Between Servers In SQL Server

This happened to discover when I was working on server migration. The logins were all moved from server1 to server2 using the underlying scripts, but we wanted a method to verify them to make sure none of them are missed.

I had come up with a plan to find the missing logins on either of the servers and it worked perfectly. Here is the script that can help you as well to verify the logins where they are missed.

Format:

SELECT p1.name AS [Server1], p2.name AS [Server2]
FROM sys.syslogins p1
full outer join [Your Secondary Server Linked Server].master.sys.syslogins p2 on p1.name = p2.name
where p1.name is null or p2.name is null

Query:

SELECT p1.name AS [Server1], p2.name AS [Server2]
FROM sys.syslogins p1
full outer join [Server2].master.sys.syslogins p2 on p1.name = p2.name
where p1.name is null or p2.name is null

Missing Login

From the result, you may notice that either of the two columns have NULL values. The login corresponding to the NULL values is the missing logins to fix.

For your reference to explore more on an extended stored procedure for logins: xp_logininfo