Create dynamic PIVOT queries in SQL Server.

In this Script we will look how we can dynamically create the pivot Script to handle the unknown values. USE AdventureWorks2012; GO CREATE TABLE dbo.Players ( FormatID INT, PlayerID INT , Name NVARCHAR(255) NOT NULL, Formats Varchar(50), Average Int ); INSERT dbo.Players VALUES (1,1, N’MS Dhoni’,’ODI’,55), (1,2, N’Rohit Sharma’,’ODI’,40), (1,3, N’Virat kholi’,’ODI’,60), (1,4, N’ABD’,’ODI’,60), (2,1, … More Create dynamic PIVOT queries in SQL Server.

Why developers are using SET ANSI_NULLS ON/OFF setting in SQL Stored Procedures.

Letz understand SET ANSI_NULLS ON /OFF with an example. CREATE TABLE dbo.Players(FirstName VARCHAR(50),LastName Varchar(50)) INSERT INTO dbo.Players VALUES(‘RAHUL’,’DRAVID’), (‘DHONI ‘,’MS’), (‘LAXMAN’, NULL) SELECT * FROM Players SET ANSI_NULLS ON GO SELECT * FROM dbo.Players WITH(NOLOCK) WHERE LastName = NULL SELECT * FROM dbo.Players WITH(NOLOCK) WHERE LastName <> NULL RESULT FirstName LastName ————————————————– —————————– (0 row(s) … More Why developers are using SET ANSI_NULLS ON/OFF setting in SQL Stored Procedures.

XML as a Input Parameter in Stored Procedure.

IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = object_id(N’XMLDOC’)) DROP PROCEDURE XMLDOC GO Create Procedure XMLDOC @XMLDoc xml as begin DECLARE @XmlDocumentHandle INT EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XMLDoc,'<ROOT xmlns:xyz=”urn:MyNamespace”/>’ select * FROM OPENXML (@XmlDocumentHandle,’/root/Resurce’,1)–with student WITH ( ID int, Name varchar(30), Technology varchar(30), EXP numeric(3,1) ) EXEC sp_xml_removedocument @XmlDocumentHandle end exec XMLDOC @XMLDoc = … More XML as a Input Parameter in Stored Procedure.

Things to know before going to join new project.

1. Get List of Stored Procedure In a Database  select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE=’PROCEDURE’ 2. Find All Number of  Functions  In a Database SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ‘FUNCTION’. 3. Number of Triggers In a Database. SELECT sysobjects.name AS trigger_name ,USER_NAME(sysobjects.uid) AS trigger_owner ,s.name AS table_schema ,OBJECT_NAME(parent_obj) AS table_name … More Things to know before going to join new project.

Delete repeated Ids from Table – Performance Improvement.

CREATE TABLE dbo.Data( FriendlyFunctionCode INT, MemberFirmId VARCHAR(100),FunctionLevel3Desc VARCHAR(100)) GO INSERT INTO dbo.Data VALUES(1,’Value1′,’Value2′) ,(1,’Value2′,’Value3′) ,(2,’Value4′,’Value5′) SELECT * FROM dbo.Data ;WITH CTE AS ( SELECT FriendlyFunctionCode,MemberFirmId, FunctionLevel3Desc, Row_number()OVER (PARTITION BY FriendlyFunctionCode ORDER BY (SELECT 1) ) AS Rn FROM dbo.Data) SELECT * FROM CTE WHERE Rn = 1 DROP TABLE Data

TSQL – How to Find duplicate records in a SQL Server Table.

CREATE TABLE dbo.Customer( ID INT, FirstName VARCHAR(100),LastName VARCHAR(100),Age INT) GO INSERT INTO dbo.Customer VALUES(1,’Rajesh’,’Yadav’,27) ,(1,’Rahul’,’sarode’,26) ,(2,’Raza’,’M’,32) ,(3,’Sukhjeet’,’Singh’,27) ,(4,’Sukhjeet’,’Singh’,28) ,(5,’Rahul’,’sarode’,26) SELECT * FROM dbo.Customer ;WITH CTE AS ( SELECT FirstName, LastName, Row_number() OVER ( PARTITION BY FirstName, LastName ORDER BY ( SELECT 1) ) AS Rn FROM dbo.Customer) SELECT * FROM CTE WHERE Rn > 1 … More TSQL – How to Find duplicate records in a SQL Server Table.

How To Pass Optional Parameters in SQL Server Stored Procedure.

A common struggle is how to pass optional parameters to a stored procedure.Let’s talk about a simple example. You have an employees table that has the employee id, first name and last name. The table is defined as follows: create table [dbo].[employees] ( [id] int identity(1, 1) not null, [firstName] varchar(255) null, [lastName] varchar(255) null, … More How To Pass Optional Parameters in SQL Server Stored Procedure.