SQL Server: Top 10 Queries of SQL Server

Introduction

T-SQL most top queries demonstrating today in this article. T-SQL is most important while dealing with database. MS SQL Server has provided  numerous T-SQL queries to check database performance, data manipulation queries, data definitions queries, roles/security check, transaction level etc.

Database Table with Data

CREATE TABLE [dbo].[tblPersonalDetail](
 [TablePK] [int] IDENTITY(1,1) NOT NULL,
 [First_Name] [nvarchar](100) NULL,
 [Last_Name] [nvarchar](100) NULL,
 [EmailID] [nchar](100) NULL,
 [Address] [nvarchar](200) NULL,
 [CellNo] [int] NULL,
 CONSTRAINT [PK_tblPersonalDetail] PRIMARY KEY CLUSTERED 
(
 [TablePK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tblPersonalDetail] ON
INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID],[Address], [CellNo]) VALUES (1, N'David', N'Moore', N'david.moore@you.com', N'new lane drive USA', 456789456)
INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID],[Address], [CellNo]) VALUES (2, N'Marry', N'Champ', N'marrychamp@you.com', N'straight drive, Lane I', 15478945)
INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID],[Address], [CellNo]) VALUES (3, N'Allan', N'Lamb', N'allan@you.com', N'Main Lane, London', 12312313)
INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID], [Address], [CellNo]) VALUES (4, N'Arya', N'Bhatt', N'arya@abc.com', N'Nalanda University', 12313154)
SET IDENTITY_INSERT [dbo].[tblPersonalDetail] OFF

Table Data View

SQL Next Record: How to get next record in SQL Server Table

SELECT TOP 1 LEAD(PD.TablePK) OVER (ORDER BY PD.TablePK) NextValue from 
 [tblPersonalDetail] PD where PD.TablePK >=1 order by PD.TablePK asc;

SQL Previous Record: Go to Previous Record in Table using SQL Server

SELECT TOP 1 MAX(TablePK)Previous_Record FROM tblPersonalDetail WHERE TablePK < 3;

SQL Server: How to check Total Rows, Spaced Used, Indexed and unused Size?

EXEC sp_spaceused @updateusage = N'TRUE';

GO

EXEC sp_spaceused N'dbo.tblPersonalDetail';

Highest Salary: How to get 3rd Highest Value using SQL Server

SELECT TOP 1 TablePK from (SELECT DISTINCT TOP 3 TablePK FROM tblPersonalDetail 
 order by TablePK desc) a order by TablePK

SQL Defined Tables: List all user defined tables using SQL Server

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

SQL Columns: List all columns in our database using INFORMATION_SCHEMA

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Other INFORMATION_SCHEMA.ViewName: Here are listing of other Information_Schema metadata which allows you to retrieve rows of specific database from Master database.

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.DOMAINS
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.VIEWS

SQL Date Formats: To see the date formats for your culture using MS SQL Server

EXEC sp_helplanguage

Fig: Date Formats

XML Showplan: This statement returns showplan in XML format by the plan handle.

USE master;
GO
exec sp_who;
GO
SELECT * FROM sys.dm_exec_requests

                                     Fig: XML Showplan

Select Last SQL Records: In this query how we can select last records using Top Statement in T-SQL

SELECT Top 4 * FROM dbo.tblPersonalDetail Order By TablePK desc

Conclusion

In this article, I have demonstrated T-SQL top 10 queries in SQL Server. Which shows data from Master database of the SQL Server and few queries manipulate of specific database. Whenever we are working on top db administrator then we need to fire administrator level queries to tune up our database.

Relevant Reading

• Could not open a connection to SQL Server
• How to Schedule Backup in SQL Server
• How to Design Entity Relationship Diagram
• DDL vs DML