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 find a view in database where its used in SQL Server

To find where a specific view is used in a SQL Server database, you can query the system catalog views. Here's a query to achieve this:

SELECT 
    referencing_schema_name, 
    referencing_entity_name
FROM 
    sys.dm_sql_referencing_entities('YourSchema.YourView', 'OBJECT');
  

Replace YourSchema with the schema of your view and YourView with the name of the view you want to find. This query will return the schema and name of the objects that reference the specified view.

Execute this query in your SQL Server management tool to find where a specific view is used in your database.

Hope this help!!

Removing Cached login and password list in SQL Server Management Studio

You need to look in following location based on the SSMS Instance you have in your local PC.

Since mine is 19.0 version, Below is my path.

C:\Users\sconrey\AppData\Roaming\Microsoft\SQL Server Management Studio\19.0

Open UserSettings.xml in Notepad ++ or any editor of your choice.

Find the User you would like to remove and delete the Entire Element tag related to that User.

<ServerTypeItem>
    <Servers>   
        <Element>
 

        </Element>
    </Servers>
< /ServerTypeItem>

You need to remove complete Element tag from the file and save it. Please make sure during this process. SSMS should be closed, if not your changes will not eb updated.

Top 10 FREE courses to learn SQL in 2023

FREE Certificates

❯ DBMS, SQL http://learn.saylor.org/course/view.php?id=93

❯ SQL http://openclassrooms.com/en/courses/2071486-retrieve-data-using-sql

❯ MySQL http://coursera.org/learn/database-structures-and-management-with-mysql

Paid Certificate

❯ SQL Server http://learn.microsoft.com/training/paths/program-transact-sql/ *

Free Badge

❯ PostgreSQL http://coursera.org/specializations/postgresql-for-everybody

Paid Certificate

❯ Oracle http://mygreatlearning.com/academy/learn-for-free/courses/oracle-sql

❯ RDBMS, SQL http://online.stanford.edu/courses/soe-ydatabases0005-databases-relational-databases-and-sql

Paid Certificate

❯ RDBMS, SQL http://udacity.com/course/database-systems-concepts-design—ud150

No Certificate

❯ PostgreSQL http://freecodecamp.org/learn/relational-database/

❯ Big Query http://kaggle.com/learn/intro-to

Happy SQL

How to implement impersonation in SQL Server

To implement impersonation in SQL Server, you can follow these steps:

1. Create a Login:
First, create a SQL Server login for the user you want to impersonate. Use the `CREATE LOGIN` statement to create the login and provide the necessary authentication credentials.

Example:

CREATE LOGIN [ImpersonatedUser] WITH PASSWORD = 'password';
  

2. Create a User:
Next, create a user in the target database associated with the login you created in the previous step. Use the `CREATE USER` statement to create the user and map it to the login.

Example:  

CREATE USER [ImpersonatedUser] FOR LOGIN [ImpersonatedUser];
  

3. Grant Permissions:
Grant the necessary permissions to the user being impersonated. Use the `GRANT` statement to assign the required privileges to the user.

Example:

GRANT SELECT, INSERT, UPDATE ON dbo.TableName TO [ImpersonatedUser];
  

4. Impersonate the User:
To initiate impersonation, use the `EXECUTE AS USER` statement followed by the username of the user you want to impersonate. This will switch the execution context to the specified user.

Example:

EXECUTE AS USER = 'ImpersonatedUser';
  

5. Execute Statements:
Within the impersonated context, execute the desired SQL statements or actions. These statements will be performed with the permissions and privileges of the impersonated user.

Example:

SELECT * FROM dbo.TableName;
-- Perform other actions as needed
  

6. Revert Impersonation:
After completing the necessary actions, revert back to the original security context using the `REVERT` statement. This will switch the execution context back to the original user.

Example:

REVERT;
  

By following these steps, you can implement impersonation in SQL Server. Ensure that you grant the appropriate permissions to the user being impersonated and consider security implications when assigning privileges.

Here is the full syntax:

EXECUTE AS LOGIN = 'DomainName\impersonatedUser'
EXEC  uspInsertUpdateGridSettings @param1, @param2
REVERT;
  

Additionally, be mindful of auditing and logging to track and monitor impersonated actions for accountability and security purposes.

How to get comma separated values from SQL

There are few types where you can get comma separated values form SQL SERVER using SQL

1. XML PATH method:

SELECT 
   STUFF((SELECT ', ' + column_name
          FROM table_name
          WHERE conditions
          FOR XML PATH('')), 1, 2, '') AS csv_values;
  

2. COALESCE and FOR XML method:

SELECT 
   STUFF((
      SELECT ', ' + column_name
      FROM table_name
      WHERE conditions
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS csv_values;
  

3. FOR XML PATH method

SELECT 
   STUFF((SELECT ',' + column_name
          FROM table_name
          WHERE conditions
          FOR XML PATH('')), 1, 1, '') AS csv_values;
  

In these examples, replace column_name with the actual column name and table_name with the appropriate table name. Customize the WHERE clause to filter the desired rows if necessary.

When executing these queries, a single row with a single column will be returned, containing the comma-separated values from the specified column. Please note that the XML-related methods convert the values to XML and then manipulate them, resulting in a string of comma-separated values.

Hope this helps!!

How to Find Tables that Contain a Specific Column in SQL Server?

Basic concept to understand about SQL Server is that of catalog views, which are effectively database tables (catalogs in this case) that display system-wide information about the SQL Server Database Engine.

All catalog views are accessed via a SELECT SQL statement FROM a specific catalog within the sys. namespace.
For example, the following statement can be used to view information about all database tables in the system via the sys.tables catalog

use mobility
go
select  
        s.[name]            'Schema',
        t.[name]            'Table',
        c.[name]            'Column',
        d.[name]            'Data Type',
        c.[max_length]      'Length',
        d.[max_length]      'Max Length',
        d.[precision]       'Precision',
        c.[is_identity]     'Is Id',
        c.[is_nullable]     'Is Nullable',
        c.[is_computed]     'Is Computed',
        d.[is_user_defined] 'Is UserDefined',
        t.[modify_date]     'Date Modified',
        t.[create_date]     'Date created'
from        sys.schemas s
inner join  sys.tables  t
on s.schema_id = t.schema_id
inner join  sys.columns c
on t.object_id = c.object_id
inner join  sys.types   d
on c.user_type_id = d.user_type_id
where c.name like '%ProjectManagerID%'
  

Hope this helps!!

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
  

What is the Query to display the failed SQL Jobs

We can get this information by the standard reports available in SQL Server, right click on SQL server agent> Standard reports and select the desired report to see Job History

It can be frustrating to find recently failed jobs in the job history in SQL Server Management Studio. A quicker way to do it is to just run a query to see what jobs have failed recently.

Below Query will give you list of failed jobs, you can also filter by name.

select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
   ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
   ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE jh.run_status = 0
--and name = 'jobName'
order by run_date desc
  

If you want to check results based on data range, you can use below query to find desired results.

-- Variable Declarations 
DECLARE @FinalDate INT;
SET @FinalDate = CONVERT(int
    , CONVERT(varchar(10), DATEADD(DAY, -2, GETDATE()), 112)
    ) -- last two days date as Integer in YYYYMMDD format

-- Final Logic 

SELECT  j.[name],  
        s.step_name,  
        h.step_id,  
        h.step_name,  
        h.run_date,  
        h.run_time,  
        h.sql_severity,  
        h.message,   
        h.server  
FROM    msdb.dbo.sysjobhistory h  
        INNER JOIN msdb.dbo.sysjobs j  
            ON h.job_id = j.job_id  
        INNER JOIN msdb.dbo.sysjobsteps s  
            ON j.job_id = s.job_id 
                AND h.step_id = s.step_id  
WHERE    h.run_status = 0 -- Failure  
         AND h.run_date > @FinalDate  
ORDER BY h.instance_id DESC;
  

Hope this helps!

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!

SSIS: Cannot convert between unicode and non-unicode string data types

Best thing to resolve this to add Data Conversion transformations to convert string columns from non-Unicode (DT_STR) to Unicode (DT_WSTR) strings.

You need to do this for all the string columns.

Add a data conversion task between OLEDB and EXCEL Destinations and do string conversations like below for the columns you are using

'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.

Quick note: How to find queries, SP's or jobs that use a linked server?

Here is the query below that can pull all above results.

Declare @VName varchar(256)
Declare Findlinked cursor
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
Select name
   From sys.servers
   Where is_linked = 1
      
Open Findlinked;
Fetch next from Findlinked into @VName;

while @@FETCH_STATUS = 0
Begin
   SELECT OBJECT_NAME(object_id) 
      FROM sys.sql_modules 
      WHERE Definition LIKE '%'+@VName +'%' 
      AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;
      
   Fetch next from Findlinked into @VName;
End

Close Findlinked
Deallocate Findlinked

SSIS Jobs are different, Here is one for SSIS jobs with Job Name and Details

Declare @VName varchar(256)
Declare Findlinked cursor
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
Select name AS name
   From sys.servers
   Where is_linked = 1
      
Open Findlinked;
Fetch next from Findlinked into @VName;

while @@FETCH_STATUS = 0
Begin
   SELECT OBJECT_NAME(object_id) as ProcedureName 
      FROM sys.sql_modules 
      WHERE Definition LIKE '%'+@VName +'%' 
      AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;
      
   Fetch next from Findlinked into @VName;
END
Close Findlinked

Open Findlinked;
Fetch next from Findlinked into @VName;

while @@FETCH_STATUS = 0
Begin
   SELECT j.name AS JobName,js.command 
      FROM msdb.dbo.sysjobsteps js
         INNER JOIN msdb.dbo.sysjobs j
            ON j.job_id = js.job_id
      WHERE js.command LIKE '%'+@VName +'%'
   Fetch next from Findlinked into @VName;
END

Close Findlinked
Deallocate Findlinked

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).

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