Pivot table
The following syntax summarizes how to use the PIVOT operator. Sample Query Complex PIVOT Example UNPIVOT Example
Query Type
Difference between row_number(), rank() and dense_rank() window functions in SQL Server
All Three function work as per order by clause The row_number() function always generates a unique ranking even with duplicate records. RANK() behaves like ROW_NUMBER(), except that “equal” rows are ranked the same. Dense_Rank() Function is similar to Rank with the only difference, this will not leave gaps between groups. Other References https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15
SQL Server Functions
Returns the ASCII code value Converts an integer ASCII code A to a character Search character Replace Character PATINDEX (‘%pattern%’ , ‘A’) STUFF (‘A’, S, L, ‘B’) SOUNDEX DIFFERENCE (‘A’, ‘B’) of soundex LEN Returns length of ‘A’, excluding trailing blanks SUBSTRING –Returns L characters of ‘A’ starting at SSUBSTRING(‘HELLOW’,2,1) = ‘E’SUBSTRING(‘HELLOW’,5,10) = ‘OW’SUBSTRING(‘HELLOW’,10,1) = …
How to reset identity value
After deleting record we need to reset identity columns. we can do this by following command if we run same command after truncate table, Identity value start with 0 To check last value of identity using DBCC
How to write merge statement in SQL Server
SQL Server provides the MERGE statement that allows you to perform three actions at the same time. If you use the INSERT, UPDATE, and DELETE statement individually, you have to construct three separate statements to update the data to the target table with the matching rows from the source table. The following shows the syntax …
How to write select with values in SQL server
This is a example of Select statement without from clause with multiple rows. This code will create a data set with two columns named c1 and c2 and two rows.
T-SQL function
What are function available in SQL Server SQL Server supports these analytic functions: CUME_DIST (Transact-SQL) LEAD (Transact-SQL) FIRST_VALUE (Transact-SQL) PERCENTILE_CONT (Transact-SQL) LAG (Transact-SQL) PERCENTILE_DISC (Transact-SQL) LAST_VALUE (Transact-SQL) PERCENT_RANK (Transact-SQL) Analytic functions calculate an aggregate value based on a group of rows. Unlike aggregate functions, however, analytic functions can return multiple rows for each group. Use …
Check if your dynamic T-SQL statement is valid
I create this stored procedure and use in my scripts and codes.
