Monday, September 9, 2024

How do I find duplicate rows in a table?

 To find duplicate rows based on certain columns, you can use a query with the GROUP BY and HAVING clauses:

SELECT Column1, Column2, COUNT(*) FROM YourTableName GROUP BY Column1, Column2 HAVING COUNT(*) > 1;

Replace Column1, Column2 with the columns you want to check for duplicates.

How do I get the list of columns in a table with their data types in SQL Server?

 Use the following query to retrieve a list of all columns and their data types:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTableName';

This will return all column names, data types, and their maximum length for the specified table.

How do I rebuild or reorganize indexes in SQL Server?

 To rebuild all indexes on a table:

ALTER INDEX ALL ON YourTableName REBUILD;

To reorganize all indexes on a table:

ALTER INDEX ALL ON YourTableName REORGANIZE;

Rebuilding indexes reorganizes the entire index tree, while reorganizing is a lighter operation that defragments the leaf level of the index.

How do I view all indexes on a table in SQL Server?

 You can use the following query to list all indexes on a specific table:

SELECT i.name AS IndexName, i.type_desc AS IndexType, c.name AS ColumnName FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID('YourTableName');

Replace 'YourTableName' with the actual table name. This query returns all indexes and their corresponding columns for the specified table.

How do I get the last executed queries in SQL Server?

 You can retrieve the last executed queries using the following query:

SELECT deqs.creation_time, dest.text AS QueryText, deqs.execution_count, deqs.total_worker_time, deqs.total_physical_reads, deqs.total_logical_reads FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.creation_time DESC;

This gives you a list of recently executed queries along with execution statistics.

How do I find blocked sessions and the session that is blocking them?

 To identify blocked sessions and the session causing the block:

SELECT blocking_session_id AS BlockingSession, session_id AS BlockedSession, wait_type, wait_time, wait_resource FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

This query shows all sessions that are currently blocked and the blocking session’s ID.

How can I backup a database in SQL Server?

 You can backup a database using the following command:

BACKUP DATABASE YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName.bak';

This command creates a backup of the YourDatabaseName database to the specified location.

How do I enable a query execution plan in SQL Server?

  To enable the execution plan for a query, run:

SET SHOWPLAN_ALL ON;

Then run your query. The execution plan will be displayed as part of the result. To turn off the execution plan, use:

SET SHOWPLAN_ALL OFF;

How do I find the largest tables in a SQL Server database?

 You can use the following query to find the largest tables by data size:

SELECT t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB 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 WHERE t.NAME NOT LIKE 'dt%' AND i.OBJECT_ID > 255 GROUP BY t.Name, p.Rows ORDER BY TotalSpaceKB DESC;


This query returns the largest tables by row count and space usage.

How can I retrieve a list of all users in a specific SQL Server database?

 You can get the list of all users in a database using this query:

SELECT name AS Username FROM sys.database_principals WHERE type IN ('S', 'U') -- 'S' for SQL user, 'U' for Windows user AND name NOT LIKE '##%'; -- Exclude system users

This lists all users in the current database.