Labels

slider

Recent

Navigation

SQL Server: Top 10 Queries of SQL Server

Top 10 Queries of SQL Server, sql top 5 query, sql top 10 queries, sql server top 10 queries, top query in sql, top query in sql server, top query in sql example

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

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 NEXT RECORD

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 Previous Record

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';
check sql space used

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'
INFORMATION SCHEMA TABLES

SQL Columns: List all columns in our database using INFORMATION_SCHEMA

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
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

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
XML Showplan

                                     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
Select Top Query

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

Share

Anjan kant

Outstanding journey in Microsoft Technologies (ASP.Net, C#, SQL Programming, WPF, Silverlight, WCF etc.), client side technologies AngularJS, KnockoutJS, Javascript, Ajax Calls, Json and Hybrid apps etc. I love to devote free time in writing, blogging, social networking and adventurous life

Post A Comment:

3 comments:

  1. Hi,
    Nice article, well compiled post with queries . Am looking forward for more sql related post.
    Thanks for sharing

    ReplyDelete
    Replies
    1. sure, I am going to post more latest tutorials on SQL Server.

      Delete
  2. Well explained articles in very simplified manner

    ReplyDelete