Understanding Indexing in SQL Server: Types and Usage

What is an Index?   

An index in SQL Server is a data structure associated with a table or view that speeds up the retrieval of rows based on the values in one or more columns. It serves as a well-organized reference guide, allowing SQL Server to efficiently locate rows that match query criteria without scanning the entire table.

Types of Indexes:

1. Clustered Index: Determines the physical order of data in a table, affecting the order of data when modified.
2. Non-clustered Index: Creates a separate structure with sorted references to actual data rows, useful for enhancing SELECT query performance.
3. Unique Index: Ensures uniqueness of values in the indexed column(s) across the table, aiding in data integrity.
4. Covering Index: Includes all columns needed to fulfill a query, minimizing I/O operations and improving query performance.
5. Filtered Index: Includes only a subset of rows in the table based on a WHERE clause, useful for optimizing queries targeting specific subsets of data.
6. Spatial Index: Specialized for spatial data types, facilitating efficient spatial queries such as distance calculations and intersections.
7. Columnstore Indexes: Organizes data by columns, beneficial for analytical queries involving aggregations and scans across large datasets.

Usage of Indexes:

 Faster Data Retrieval: Provides a shortcut to desired rows, reducing the time to locate and retrieve data, particularly helpful for SELECT queries.  
Optimizing Joins: Indexes on join columns enhance performance by quickly identifying matching rows.  
Sorting and Grouping: Speed up ORDER BY and GROUP BY operations by efficiently retrieving and organizing data.  
Constraint Enforcement: Unique indexes ensure data integrity by preventing duplicate values in indexed columns.  
Covering Queries: Minimizes I/O operations and speeds up query execution by scanning the index alone.  
Reducing I/O Operations: Efficient use of indexes minimizes I/O operations required to satisfy a query.

Best Practices for Indexing:

1. Selective Indexing: Focus on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses to avoid unnecessary overhead.
2. Regular Maintenance: Monitor and maintain indexes regularly, including rebuilding or reorganizing to minimize fragmentation.
3. Avoid Over-Indexing: Strike a balance between performance gains and maintenance overhead to avoid diminishing returns.
4. Consider Clustered Index Carefully: Choose based on typical table queries and access patterns.
5. Use Indexing Tools: Leverage tools such as the Database Engine Tuning Advisor to recommend appropriate indexes based on query performance analysis.
6. Understand Query Execution Plans: Analyse plans to identify areas where indexes can optimize query performance.

Conclusion:  

Indexes in SQL Server play a crucial role in enhancing query speed by enabling quicker data retrieval and minimizing the need for full-table scans. Selecting the right type of index and adhering to best practices, including regular maintenance and thorough understanding of database access patterns, are vital for extracting maximum benefits from indexing. 

How to check active transactions in SQL Server

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database.

Some times, if there are any issues we will get into this deadlock stage and we don't get any responses from database. In those cases, if you want know what are active connections going on at that point of time, will help identifying issue.

if you want to know more details about active sessions like session ID, Host Name, Login Name, Transaction ID, Transaction Name, Transaction Begin Time,Database ID,Database Name etc.

Use the below query for details,

SELECT
trans.session_id AS [SESSION ID],
execSession.host_name AS [HOST NAME],login_name AS [Login NAME],
trans.transaction_id AS [TRANSACTION ID],
tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION 
BEGIN TIME],
tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS execSession
ON trans.session_id = execSession.session_id
WHERE execSession.session_id IS NOT NULL
  

SQL SERVER – How to find table rows count?

At some point in time we’ve all had to find out how many rows are in a table. The first answer you’ll usually get when you ask someone how to do it is select count(*) from table.

Here is the simple and accurate query to get this information from SQL Server

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name
order by  [TotalRowCount] desc

Get Current TimeZone Name in SQL Server

Here is how we can get current time zone from sql server

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone

Quick note: How to get backup history of a database?

Here is how you can get via SQL Query, This works on any SQL Server version

USE database_name
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

Hope this helps!

'TRUNCATE_ONLY' is not a recognized BACKUP option.

In Microsoft SQL server 2000 and 2005, as part of the ‘BACKUP LOG’ command, there was an option to truncate the log file, without storing a backup to file. In Microsoft SQL Server 2008, this option has been removed, and you can now no longer use the ‘TRUNCATE_ONLY’ option when performing a transaction log backup.

The truncate only option is used to truncate the log file. This is generally done so you can then shrink the transaction log file, and recover the disk space back to the file system.

I ran into this issue, when we are migrating our servers from 2005 to higher version.

Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.

To truncate the transaction log file in Microsoft SQL Server 2008 or 2012 and above, without making an actual transaction log backup (possibly due to free space limitations), you need to change the recovery model of the database to “Simple”, and then change it back to “Full” (or “Bulked Logged” if that’s what it was previously).

USE ps;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ps
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ps_log, 1);  
-- here 2 is the file ID for trasaction log file
--you can also mention the log file name (dbname_log)
GO

-- Reset the database recovery model.
ALTER DATABASE ps
SET RECOVERY FULL;
GO

Hope this helps.

Determining which version and edition of SQL Server Database Engine is running

Open SQL Server Management Studio (SSMS) and connect to SQL Server. Run below query to find version and edition of SQL server.

SELECT  
    SERVERPROPERTY('productversion') as 'Product Version', 
    SERVERPROPERTY('productlevel') as 'Product Level',  
    SERVERPROPERTY('edition') as 'Product Edition',
    SERVERPROPERTY('buildclrversion') as 'CLR Version',
    SERVERPROPERTY('collation') as 'Default Collation',
    SERVERPROPERTY('instancename') as 'Instance',
    SERVERPROPERTY('lcid') as 'LCID',
    SERVERPROPERTY('servername') as 'Server Name'

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error"

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error

or

ole db provider "microsoft.ace.oledb.12.0" for linked server "(null)" returned message "the select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.".

Here is the quick resolution for this fix, try run below commands in SQL Server

USE [master] 
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

This resolved my problem!

Configure the remote query timeout Server Configuration Option

Use below query to increase default time out or disable time out time.

USE ps;  
GO  
EXEC sp_configure 'remote query timeout', 0 ;  
GO  
RECONFIGURE ;  
GO 

The remote query timeout option specifies how long, in seconds, a remote operation can take before SQL Server times out. The default value for this option is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine. To disable the time-out, set the value to 0. A query will wait until it completes.

For more information, see Server Configuration Options (SQL Server).

SSMS: Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)

We will see this when we connect to SQL Server 2012 from SQL Server 2008/2008 R2

Cause
This issue occurs because of an error in SSMS 2008 R2.

Solution
The problem is generated due to an error in SQL Server Management Studio 2008 & 2008 R2.

Fix
To solve this, apply the latest service pack available.

Quick Note: How to find out which Service Pack is installed on SQL Server?

-- SQL Server 2000/2005/2008/2008R2/2012
SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

-- SQL Server 6.5/7.0
SELECT @@VERSION

How to get size of all tables in database

Here is how you can get table sizes via SQL query, this works from SQL 2005 and above

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

Alternatively if you want to get size of one table you can do it by using sp_spaceused, this can get you information on the disk space used by a table, indexed view, or the whole database.

USE psdb  
GO

EXEC sp_spaceused contact
GO

You can use the sp_spaceused command to get all tables in database by using the below command.

USE psdb  
GO

sp_msforeachtable 'EXEC sp_spaceused [?]' 
GO

Hope this useful

How to Get First and Last Day of a Year from SQL

Here is how we get first day and last day of the year.

DECLARE @Year int 
set @Year = 2018
select DATEADD(year,0,DATEADD(year,@Year-1900,0)) 
/*First Day of the year*/
select DATEADD(day,-1,DATEADD(year,1,DATEADD(year,@Year-1900,0))) 
/*Last Day of the year*/

We can also get Year dynamically from SQL by passing current date. This works form SQL 2005 and above.

SELECT Year(getdate())

EXCEPT and INTERSECT in T-SQL

The UNION, EXCEPT and INTERSECT operators of SQL enable you to combine more than one SELECT statement to form a single result set. The UNION operator returns all rows.

EXCEPT
Returns any distinct values from the query to the left of the EXCEPT operator that are not also returned from the right query.
INTERSECT
Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

The INTERSECT operator returns all rows that are in both result sets. The EXCEPT operator returns the rows that are only in the first result set but not in the second

EXCEPT operator is another most important feature in SQL Server which is used to returns distinct rows by comparing the results of two input queries. Both SQL queries within the EXCEPT query, the number and the order of the columns must be the same in the result sets within similar data types.  EXCEPT operator is a very quick and easy way to find differences, especially when needing to get all differences including null.

When using INTERSECT operator the number and the order of the columns must be the same in all queries as well data type must be compatible.

For more Information check MSDN site

Download: Microsoft® SQL Server® 2008 R2 SP1 - Express Edition

Microsoft SQL Server 2008 R2 Express with Service Pack1 is a free, feature-rich edition of SQL Server that is ideal for learning, developing and powering desktop, web and small server applications, and for redistribution by ISVs

Download