Saturday

SQL Server: Top 10 Queries of SQL Server

SQL Server: Top 10 Queries of SQL Server
23:41 by Anjan Kant

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

Thursday

Entity Framework: Store update insert or delete statement affected an unexpected number of rows (0)

Entity Framework: Store update insert or delete statement affected an unexpected number of rows (0)
21:38 by Anjan Kant

Introduction I have already explained very known an entity framework error Validation failed for one or more entities, today providing a resolution of another entity framework error. Yesterday, I was working on MVC application using Entity Framework, meanwhile I was setting up my code then a new error comes off me "Store update, insert, or delete statement affected an unexpected number of rows". After my deep study, then came up a perfect solution to remove this issue permanently from my solution. I have explained all steps as given below.

Error Description

Store update, insert, or delete statement affected an unexpected number of rows (0).
Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

Store update insert or delete statement affected an unexpected number of rows (0)
Fix Entity Framework Error

Steps to Remove Error [Store update, insert, or delete statement affected an unexpected]

Set update mode to add, it was earlier set to modified state using entity framework. If table primary key greater than zero then it will modified otherwise it will add a new record in database table.

Wrong Statement

I had written given below wrong statement to add a new fresh record into my database table record, it was wrongly added state to Modified.
_context.Entry(model).State = EntityState.Modified;

Corrected Statement

Now, I corrected this statement, I just checked that primary key contains a value means a record need to update otherwise primary key value 0 means my database table needed to add a new fresh record in to my database table record.

if (model.TablePK > 0)
{
   _context.Entry(model).State = EntityState.Modified;
}
else
{
   _context.Entry(model).State = EntityState.Add;
}

Conclusion

While we coding seriously in our app then suddenly can trap up in few unexpected issues i.e. Store update, insert, or delete statement affected an unexpected. I have provided complete to recover unexpected issue. Hope it will help to all who eventually got in this issue i.e. Store update, insert, or delete statement affected an unexpected.

Suggested Reading