MS SQL SERVER – jbcEdge

TSQL – Using Pivot Table

Posted on July 18, 2020July 18, 2020 by admin

A Pivot table in TSQL is useful to spread row data as columns. Row values can be defined into multiple columns.

A row in SQL Server represents an entity and a column as an attribute of the entity.

To build a pivot table, you will need to define three columns.

Column 1 – Row grouping
Column 2 – Create new columns based on the values
Column 3 – Aggregated values for the row and columns

Steps to build a pivot table:

Select based dataset
Create temporary result set using Common Expression Table (CTE)
Apply PIVOT operator

Example

This is the original table

Rate_Id	Desc	R01	R02	R03	R04
1	Plan	14.10	25.20	28.50	40.50
2	Plan	21.50	39.50	41.80	60.90

This will be the final result

Plan	Silver	Gold
Plan-1	14.10	21.50
Plan-2	25.20	39.50
Plan-3	28.50	41.80
Plan-4	40.50	60.90

Step 1: Create the table

CREATE TABLE #Rate (
Rate_Id int,
Rate_Desc varchar(100),
R01 decimal(17,2),
R02 decimal(17,2),
R03 decimal(17,2),
R04 decimal(17,2)
);

Step 2: Insert records

INSERT INTO #Rate VALUES (1,'Plan',14.10,25.20,28.50,40.50);
INSERT INTO #Rate VALUES (2,'Plan',21.50,39.50,41.80,60.90);

Step 3: Use cross apply to create additional rows

SELECT * FROM #Rate
cross apply
(
   select 1,R01 union
   select 2,R02 union
   select 3,R03 union
   select 4,R04 
) r (RowNo, Rate)

Output

Rate_Id	Rate_Desc	R01	R02	R03	R04	RowNo	Rate
1	Plan	14.10	25.20	28.50	40.50	1	14.10
1	Plan	14.10	25.20	28.50	40.50	2	25.20
1	Plan	14.10	25.20	28.50	40.50	3	28.50
1	Plan	14.10	25.20	28.50	40.50	4	40.50
2	Plan	21.50	39.50	41.80	60.90	1	21.50
2	Plan	21.50	39.50	41.80	60.90	2	39.50
2	Plan	21.50	39.50	41.80	60.90	3	41.80
2	Plan	21.50	39.50	41.80	60.90	4	60.90

Step 4: Use PIVOT to change rows to columns

WITH CTE_Rates As (
SELECT * FROM #Rate
cross apply
(
   select 1,R01 union
   select 2,R02 union
   select 3,R03 union
   select 4,R04 
) r (RowNo, Rate))
SELECT [Plan], [1] as [Silver], [2] as [Gold]
FROM (
SELECT Rate_Id,CONCAT(Rate_Desc,'-',RowNo) as [Plan], Rate_Desc, RowNo,Rate
FROM CTE_Rates) r
PIVOT(
 Max(Rate)
  for Rate_Id IN ([1],[2])
) p
;

Final table result from Step 4

Plan	Silver	Gold
Plan-1	14.10	21.50
Plan-2	25.20	39.50
Plan-3	28.50	41.80
Plan-4	40.50	60.90

In this example I have converted Rate_Id 1 and 2 as Silver and Gold. Also R01, R02, R03, R04 as Plan-1, Plan-2, Plan-3, Plan-4.

TSQL – Last Time Table was Updated

Posted on June 16, 2020June 22, 2020 by admin

TSQL – Last Time Table was Updated

If you looking for a script to find out when was the last time a particular table was updated then here it is:

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DATABASENAME')
AND OBJECT_ID=OBJECT_ID('TABLENAME')

SELECT
tbl.name
,ius.last_user_update
,ius.user_updates
,ius.last_user_seek
,ius.last_user_scan
,ius.last_user_lookup
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
FROM
sys.dm_db_index_usage_stats ius INNER JOIN
sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
WHERE ius.database_id = DB_ID()

This normally happens when you don’t have columns such as DateCreated and UpdatedDate in the table.

Live as if you were to die tomorrow. Learn as if you were to live forever.. -Mahatma Gandhi

Measuring Execution Times

The SQL Server Management Studio shows query measurement time in seconds. If we are concern about performance then in-depth measurement is required such as milliseconds.

Using Statistics

set statistics time on

-- your query

set statistics time off

Messages Tab
SQL Server parse and compile time:
CPU time = 67 ms, elapsed time = 67ms

Setting the timing as default for every query
Query -> query options -> advanced -> Execution
check the “set statistics time” checkbox
Check “set statistics IO” checkbox

Using Client Statistics

Ways to turn on the Client Statistics

Menu: Query -> Include client Statistics
Toolbar: Click button Include Statistics
Keyboard: Shift+Alt+S

Properties

Client Execution Time	Time the trial was started.
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements	The number of Insert, Delete or Update statements that were executed in that particular trail.
Rows affected by INSERT, DELETE or UPDATE statements	Number of rows that were affected by Insert, Delete or update statement part of your trial.
Number of SELECT statements	Number of select statement that were executed under that particular trial execution. It includes fetch statements to retrieve rows from cursors.
Rows returned by SELECT statements	Rows selected as part of that trail execution.
Number of transactions	User transactions used in a trail execution.
Network Statistics	How much traffic is moving from client to the server and back.
Number of server roundtrips	Number of times request sent to server and number of time reply received from server in a trail execution.
TDS packets sent from client	Number of TDS packets client has sent to the database server under a trial execution.
TDS packets received from server	The number of TDS packets received by client from database server under a trial execution.
Bytes sent from client	The number of bytes that the client has sent to database server under a trial execution. Includes spaces selected after query as well.
Time Statistics	How much time was spent processing on the client versus how much time was spent waiting for the server in milliseconds.
Client processing time	The cumulative amount of time that the client spent in executing code while the query was executed
Total execution time	The cumulative amount of time (in milliseconds) that the client spent processing while the query was executed, including the time that the client spent waiting for replies from the server as well as the time spent executing code.
Wait time on server replies	The cumulative amount of time (in milliseconds) that the client spent while it waited for the server to reply.

By default Client Statistics shows up to 10 trails. After 10 trails, every trail position get decreased by 1 and trail 0 get removed from list.

Reset Client Statistics
Query Menu -> Reset Client Statistics

Using Execution Time as Variable

DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT @StartTime=GETDATE()

--Query goes here

SELECT @EndTime=GETDATE()
SELECT DATEDIFF(ms, @StartTime, @EndTime) AS [Duration in millisecs]

Temporary Tables

Posted on February 16, 2018February 21, 2018 by admin

Temporary Tables/Variables
Temporary tables are created in tempdb database. They function like regular tables where you can perform Data Modification Language (DML) queries. If you create temp table inside stored procedure it gets destroyed once the stored procedure is completed. Scope of the temporary table is important, visible only to the particular user. Temporary tables begin with a pound symbol ‘#’ or ‘@’. Temporary tables can be indexed and DDL constraints can be applied.

There are multiple types of temporary tables:
1. Local temporary tables
2. Global temporary tables
3. Table Variable
4. Tempdb permanent tables
5. Global table variable

1. Local Temporary table
– Local temporary tables are only available in the current executing scope or the current connection to an instance of SQL Server
– Deleted after disconnects from SQL Server instance
– Cannot be shared between multiple users

CREATE TABLE #tmp

2. Global Temporary table
– Available in other sessions and any connection after created
– Deleted when all users that are referencing the table disconnected from the instance of SQL Server
– Can be shared between multiple users
– Once global temporary tables are created it is saved in the tempdb database

CREATE TABLE ##tmp

3. Table Variable
– Only visible to the connection that creates it, stored in RAM and delete after the batch or stored procedure is completed
– Table variable is prefixed with an ‘@’ sign
– Cannot have non-clustered indexes
– No constraints , default or statistics values for table variables

DECLARE @tmp TABLE

4. Tempdb tables
– Are visible to everyone and are deleted when the server is restarted

USE tempdb CREATE TABLE tmp

5. Global table variable

DECLARE @@tmp TABLE

Using sp_executesql
-sp_executesql runs under a different scope, so if you execute and add data to temp table you will not be able to access the temp table

EXECUTE sp_executesql N'SELECT name INTO #test'

You will not be able to run the select statement into #test temp table like:

SELECT * FROM #test

Analysis:
– When you breakup a complex query to multiple statements, it is better to use temp table for optimization reasons.
– Temporary tables are preferred over table variables interms of indexing and statistics and lifespan.
– For table variable entire block of code needs to be run whereas for temporary tables you can run temporary table anytime after it is created in the current session

CREATE TABLE #Temp
(
 id int NOT NULL,
 Name varchar (10)
)

INSERT INTO #Temp (id, Name)
VALUES (1,'Test')

SELECT * FROM #temp

You can run this statements seperately and #temp table will run

DECLARE @Temp TABLE
(
 id int NOT NULL,
 Name varchar (10)                                   
)

INSERT INTO @Temp (id, Name)
VALUES (1,'Test')

SELECT * FROM @temp

You will have to run the entire code together to get the results. The table variable lifespan is only for the duraton of the transaction that it runs.

Table variable is useful when you want to return table result from the user-defined function.
eg

CREATE FUNCTION claimsbyCode (@code int)
RETURNS
   @claimsbyCode TABLE (
    [id] [INT],
    [name] [varchar(20)]
   )
....

Working with Temp tables
Two methods to add data to temp table:
1. INSERT INTO temp table
2. SELECT INTO temp table

INSERT INTO temp table
– table needs to be created first and then it allows data to be inserted.
– will reuse data pages which are created in cache for DML operations
– will truncate the table when temp table is dropped


INSERT INTO #temptable
SELECT * FROM physicaltable

SELECT INTO temp table
– create the new table for you based on your select statement and inserts the resulting row from the query.
– creates new pages for table creation and physically remove them when the table is dropped.

SELECT * 
INTO #temptable
FROM physicaltablename
WHERE statement

In terms of performance, SELECT…INTO is much faster when compared with INSERT INTO

Happy days….

MS SQL SERVER – Triggers

Posted on February 8, 2018 by admin

MS SQL SERVER – Triggers

Today I was working on Triggers for one of my project so thought to share some information about triggers.

What are Triggers?
Trigger is a database object that is attached to a table and gets fired upon action. The actions are INSERT, UPDATE or DELETE.

How to perform an INSERT/UPDATE Trigger
In my case I had two separate tables: claims and claim status history. So when the claim status changes during the claim process then I wanted to store the history process. When a new claim is created there is a INSERT trigger to add a record to claim history table.

CREATE TRIGGER trg_xxx_update
ON [xxx].[xTable]
AFTER INSERT, UPDATE
AS
INSERT INTO [xxx].StatusHistory
(
  [Id],
  [StatusId],
  [CreatedDateTime],
  [CreatedBy],
  [UpdatedDateTime],
  [UpdatedBy]
)
SELECT
  Id,
  StatusId,
  UpdatedDateTime as CreatedDateTime,
  UpdatedBy as CreatedBy,
  UpdatedDateTime,
  UpdatedBy
FROM
  Inserted

Action Definitions
1. INSERT – perform action after inserting a record to the table
2. UPDATE – perform action after an update is made to the table
3. DELETE – perform action when delete a record from the table

SQL SERVER supports 2 kinds of Triggers
– Data manipulation events (DML triggers) – INSERT/UPDATE/DELETE
– Data definition events (DDL triggers) such as CREATE TABLE

There are two kinds of DML triggers:
1. AFTER
– the trigger fires after the event it is associated with finishes and can only be defined on permanent talbles.
2. INSTEAD OF
– the trigger fires instead of the event it is associated with and can be defined on permanent tables and views

How trigger works
Trigger executes only once no matter how many rows it may affect and also the schema of the trigger must be same of the table or view.
Triggers make use of two special tables called inserted and deleted.
The inserted table contains the data referenced in an INSERT before it is actually comitted to the database.
The deleted table contains the data in the underlying table referenced in a DELETE before it is actually removed form the database
When using UPDATE both tables are used as new data referenced is contained in the inserted and the data that is updated is contained in deleted.

Nested After Triggers
Triggers can be nested that is trigger on TableA updates Table B then TableB trigger updates TableC and so on can continue to a maximum of 32 executions.
Nested triggers are on be default on SQL SERVER but you have the option to disable it.
To check the setting:

EXEC sp_configure 'nested triggers';

To stop all nested triggers:

EXEC sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

RECONFIGURE is important to make the setting take place.

Stop trigger for a database:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Restrict Trigger Nesting
This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.

IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN

Printing Trigger levels, ids

Print 'testing TRIGGER_NESTLEVEL '
+ CAST(TRIGGER_NESTLEVEL() AS VARCHAR(3))
;
print object_id( '[xxx].[trg_xxx_update]' );
print db_id();

INSTEAD OF Triggers
INSTEAD OF Triggers are common with views, the reason is that when you want to UPDATE a view only the base table can be updated at a time. Views may contain aggregations or functions which prevent update therefore An INSTEAD OF trigger can take that UPDATE statement against the view and instead of executing it, replace it with two or more UPDATE statements against the base tables of the view.

How to prevent trigger from recursing
1. Disable trigger recursion
2. Use a trigger INSTEAD OF UPDATE, INSERT
3. Control the trigger by preventing using IF UPDATE

ALTER TRIGGER [dbo].[tblXXX] 
   ON  [dbo].[tblXXX]
   FOR INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON

    IF UPDATE(Name) BEGIN
        UPDATE tblXXX 
        SET Name = Name + CAST((b.Id) AS VARCHAR(10))
        FROM tblXXX a
            INNER JOIN INSERTED b on a.Id = b.Id
    END
END

Side Effect
ROLLBACK TRAN – read more on this

Using ROW_NUMBER() with MS SQL SERVER

Posted on October 24, 2017October 24, 2017 by admin

The introduction of ROW_NUMBER() function with SQL SERVER 2005 solves the problem of calculating row numbers. This is one of the exciting feature and eliminates the problem of creating additional temp tables to achieve the result.

ROW_NUMBER function generates unique incrementing integers from 1 and on. They are only allowed in the SELECT and ORDER BY clauses of a query and not allowed in the query WHERE clause.

Syntax:

ROW_NUMBER ( )
 OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

Basics:

OVER – You define a set of rows for the function to work with per each underlying row or when using empty parentheses, the OVER clause represents the entire underlying query’s result set
PARTITION BY – The argument to partition the query result set by the column name, the row number will reset based on the value changing in the columns supplied. It is like the using the GROUP BY clause
ORDER BY – orders the rows in each partition by the column name

Coding Example

Simple Table

AnimalID	Name	Type
100	Camel	Mammal
101	Turkey	Bird
102	Cat	Mammal
103	Deer	Mammal
104	Pigeon	Bird
105	Duck	Bird

Query:
SELECT AnimalID, Name, Type, ROW_NUMBER() OVER (Order By Name) as RowID
FROM Animal

Result:

AnimalID	Name	Type	RowID
100	Camel	Mammal	1
102	Cat	Mammal	2
103	Deer	Mammal	3
105	Duck	Bird	4
104	Pigeon	Bird	5
101	Turkey	Bird	6

Query: 
SELECT AnimalID, Name, Type, ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Name) as RowID 
FROM Animal
ORDER BY Type, Name

Result:

AnimalID	Name	Type	RowID
105	Duck	Bird	1
104	Pigeon	Bird	2
101	Turkey	Bird	3
100	Camel	Mammal	1
102	Cat	Mammal	2
103	Deer	Mammal	3

Difference between ROW_NUMBER(), RANK(), DENSE_RANK()

ROW_NUMBER(): This one generates a new row number for every row, regardless of duplicates within a partition.
RANK(): This one generates a new row number for every distinct row, leaving gaps between groups of duplicates within a partition.
DENSE_RANK(): This one generates a new row number for every distinct row, leaving no gaps between groups of duplicates within a partition.

Coding Example

WITH T(StyleID, ID)
     AS (SELECT 1,1 UNION ALL
         SELECT 1,1 UNION ALL
         SELECT 1,1 UNION ALL
         SELECT 1,2)
SELECT *,
       RANK() OVER(PARTITION BY StyleID ORDER BY ID)       AS 'RANK',
       ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) AS 'ROW_NUMBER',
       DENSE_RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS 'DENSE_RANK'
FROM   T

StyleID	ID	RANK	ROW_NUMBER	DENSE_RANK
1	1	1	1	1
1	1	1	2	1
1	1	1	3	1
1	2	4	4	2

Filtering using the ROW_NUMBER()

As mentioned earlier in the post that you cannot have ROW_NUMBER() in the WHERE clause of your sql query but if you want to filter the query using the ROW_NUMBER() there is a workaround to create a nested query. Such as:

Select from ( Select row_number() over (partition by x order by x) SrNo,* from x )A where A.SrNo=1

Issues with ROW_NUMBER()

While it is exciting to use ROW_NUMBER() function but recently in one of my query it hurt my performance almost by 3 fold. ROW_NUMBER() are faster when using with simple queries with large number of rows. In my experience I was joining several tables with INNER JOIN and LEFT JOINS it cost me too much on performance. To solve this issue, I had to declare a temporary table and insert all the records and used temp tables to query.

Performance Result

Description	CPU Time	Elapsed Time	Rows
Using ROW_NUMBER()	32141 ms	32608 ms	837
Using Temp Table	1169 ms	1314 ms	837

Happy Quering!!!

I blog about technology, spirituality and lifestyle

Category: MS SQL SERVER

TSQL – Using Pivot Table

Example

TSQL – Last Time Table was Updated

TSQL – Last Time Table was Updated

Measuring Execution Times

Measuring Execution Times

Using Statistics

Using Client Statistics

Using Execution Time as Variable

Temporary Tables

MS SQL SERVER – Triggers

Using ROW_NUMBER() with MS SQL SERVER