Understanding Lead and Lag Functions in SQL Server 2012

 Lead :

          functions access data from a subsequent row (lead) 

 Lag   :

      functions access data from a previous row (Lag )

   Go through following script.

Create table #sk_lead_leg(

[id] [int] IDENTITY(1,1) , 

[Department] [nchar](100) NOT NULL,

[Code] [int] NOT NULL

)

go

insert into #sk_lead_leg(Department ,Code )

values 

('X',100),

('Y',200),

('Z',300)

 go

 select * from #sk_lead_leg order by id asc

 go

Go

SELECT id,Department,Code,

LEAD(Code,1) OVER (ORDER BY Code ) LeadValue,

LAG(Code,1) OVER (ORDER BY Code ) LagValue

FROM #sk_lead_leg

Go

  We can see the output.

Lead :  Last value is null  because it  is started from second value.

Lag : First Value is null because it is started before first value.

 

Saving Chinese ,Hindi ,Marthi or any string in SQL Server?

  Please go through script for inserting character other than english.I will explain with queries.
  go
 create table #sk_Import(Name nvarchar(max))
 go
 insert into #sk_Import values ('मेरा नाम श्रीकांत है')
 go
 select Name  from #sk_Import
 go
            Above query will create table,It will insert value and finally it will show output.
But it will show  '? '  and value which we have inserted.

Solution :
   1. Delete all data .        
delete from #sk_Import
 go
 insert into #sk_Import values (N'मेरा नाम श्रीकांत है')
 go
 select Name  from #sk_Import

  Now data is inserted and it will show correct output .Means it will show us value (Not ' ? ')

Note : We have used N as prefixed.Where  'N' stands for representing unicode characters.

What is Pagination in SQL Server 2012?

There are instances when you want to display large result sets to the end user. The best way to display large result set is to split them i.e.  apply pagination. So developers had their own hacky ways of achieving pagination using “top”, “row_number” etc. But from SQL Server 2012 onwards we can do pagination by using “OFFSET” and “FETCH’ commands.

For instance let’s says we have the following customer table which has 12 records. We would like to split the records in to 6 and 6. 

So doing pagination is a two-step process: -

• First mark the start of the row by using “OFFSET” command.
• Second specify how many rows you want to fetch by using “FETCH” command.

You can see in the below code snippet we have used “OFFSET” to mark the start of row from “0”position. A very important note order by clause is compulsory for “OFFSET” command.

 Collapse | Copy Code

select * from
tblcustomer order by customercode
offset 0 rows – start from zero

In the below code snippet we have specified we want to fetch “6” rows from the start “0”position specified in the “OFFSET”.

 Collapse | Copy Code

fetch next 6 rows only

Now if you run the above SQL you should see 6 rows.

To fetch the next 6 rows just change your “OFFSET” position. You can see in the below code snippet I have modified the offset to 6. That means the row start position will from “6”.

 Collapse | Copy Code

select * from
tblcustomer order by customercode
offset 6 rows



fetch next 6 rows only

The above code snippet displays the next “6” records , below is how the output looks.

Number of open connections in SQL server

Normally, when you’re coding on a website or a system you’re probably using connection to an SQL server database.

When you are the coder or you are a consultant coming to a company to check the code or the server or anything else for that matter, you sometimes need to check the number of connections that are open right now.

This can be done to check the quality of the code regarding connections. If the code does not close the connections, eventually the server will close the pool and the website will no longer work.
I have seen people solving that by upping the number of allowed open connections. That of course is not the solution (not the best solution).

OK, so if you are like me and you are running 1-9 servers that are dedicated to you, each running dozens or hundreds of DB’s, this is an absolutely great method to catch a glimpse on whats going on with your connections.

Run this code on your server and see what happens:

 1: SELECT DB_NAME(dbid) as 'DbNAme', COUNT(dbid) as 'Connections' from master.dbo.sysprocesses with (nolock)

 2: WHERE dbid > 0

 3: GROUP BY dbid

This code will display a list of all the databases on your server with the open connections on each of them.

  If you want details you can run  exec sp_who2 'Active' to get more details.

What is Recursive CTE ?

                      Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression).
  Consider following query

WITH Emp_CTE AS
(
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate FROM HumanResources.Employee WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title,e.BirthDate FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *FROM Emp_CTE
GO

                           In the above example Emp_CTE is a Common Expression Table, the base record for the CTE is derived by the first sql query before UNION ALL. The result of the query gives you the EmployeeID which don’t have ManagerID.
                           Second query after UNION ALL is executed repeatedly to get results and it will continue until it returns no rows. For above e.g. Result will have EmployeeIDs which have ManagerID (ie, EmployeeID of the first result).  This is obtained by joining CTE result with Employee table on columns EmployeeID of CTE with ManagerID of table Employee.
                      This process is recursive and will continue till there is no ManagerID who doesn’t have EmployeeID

RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE in Sql Server

      Rank means: ‘Placing things by merit/grades/preference and assigning a number to it‘

The T-SQL ranking functions ROW_NUMBER(), RANK() and DENSE_RANK() were introduced for ranking of rows in SQL Server 2005.

There syntax is as following:

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

note : only [<partition_by_clause>] is Optional, and everything else is mandatory.

So what is partition ranking ? 

Suppose we want to rank employees in a company according to their salaries. Then a simple rank function will suffice. But what if i want this ranking to be done not in the whole company but in their department to find whose salary is greater than other employees in a Accounts Department. So here, the ‘Partition By‘ comes into picture, where the result set is divided into parts and then ranking is performed.

Example :

Create a simple table:

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE Customer_Orders   (   OrderId INT IDENTITY(1,1) NOT NULL,   CustomerId INT,   Total_Amt decimal (10,2)   )

Insert values into this table :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

INSERT Customer_Orders (CustomerId, Total_Amt) SELECT 1, 100 UNION SELECT 1, 2000 UNION SELECT 1, 560 UNION SELECT 1, 1000 UNION SELECT 2, 1000 UNION SELECT 2, 2500 UNION SELECT 2, 500 UNION SELECT 2, 1500 UNION SELECT 3, 1500

Here i am using the following query to get the data for different ranking functions depending on the value of Total_Amt:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SELECT *,   ROW_NUMBER() OVER (ORDER BY Total_Amt DESC) AS RN,   ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY Total_Amt DESC) AS RN_P,   RANK() OVER (ORDER BY Total_Amt DESC) AS R,   RANK() OVER (PARTITION BY CustomerId ORDER BY Total_Amt DESC) AS R_P,   DENSE_RANK() OVER (ORDER BY Total_Amt DESC) AS DR,   DENSE_RANK() OVER (PARTITION BY CustomerId ORDER BY Total_Amt DESC) AS DR_P   FROM Customer_Orders   ORDER BY Total_Amt DESC

The result :

Difference between CROSS APPLY and OUTER APPLY

          SQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression.
       The APPLY operator comes in two variants, the CROSS APPLY and the OUTER APPLY. The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only.  Whereas the OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression.  For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression. So you might now conclude, the CROSS APPLY is semantically equivalent to INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with a implicit join condition of 1=1 whereas OUTER APPLY is semantically equivalent to LEFT OUTER JOIN.

              You might be wondering if the same can be achieved with regular JOIN clause then why and when to use APPLY operator? Though the same can be achieved with normal JOIN, the need of APPLY arises if you have table-valued expression on right part and also in some cases use of APPLY operator boost the performance of your query.

e.g  
   SELECT * FROM Department D
CROSS APPLY
   (
   SELECT * FROM Employee E
   WHERE E.DepartmentID = D.DepartmentID
   ) A 

GO      SELECT * FROM Department D
OUTER APPLY
   (
   SELECT * FROM Employee E
   WHERE E.DepartmentID = D.DepartmentID
   ) A
GO 

What is running total in sql server

                         For any given account, you sum the debits (deposits) and credit (withdrawals) at a given point in time. After each transaction, you want to know the current balance. Listing A creates a simple example of such a table.

Here are sample rows:

1     2006-11-03 02:33:42.340     10000.00

2     2006-11-03 02:34:50.467     -500.00

3     2006-11-03 02:35:04.857     250.00

4     2006-11-03 02:42:19.763     -124.25

Since the date is defaulted, all you need to do is add a few
amounts. The example keeps it simple, assuming only one bank account.

Now you can create the query that contains the current balance. Since you are recording deposits and withdrawals in the same column as negatives and positives, the sum is straightforward. To derive the current balance, you sum all the previous transactions and add this sum to the value of the current transaction.
The following query accomplishes this:
SELECT
transactionid,
transactiondatetime,
amount,
(SELECT SUM(amount)
FROM dbo.bankaccount as D1
WHERE D1.transactiondatetime <= D0.transactiondatetime) AS balance
FROM dbo.bankaccount AS D0 This results in the following result set:
1     2006-11-03 02:33:42.340 10000.00    10000.00
2     2006-11-03 02:34:50.467 -500.00     9500.00
3     2006-11-03 02:35:04.857 250.00      9750.00
4     2006-11-03 02:42:19.763 -124.25     9625.75

As this example demonstrates, running totals are simple to create once you understand the requirements. The example presented assumes that the table contains only one account, but it's easy to deal with many accounts—you would just need to add a column for BankAccountNumber and a WHERE predicate that specifies the account of interest.

There are two advantages to such a query:

• You don't have to store the results. When scoped by an account number or similar foreign key, performance can be lightning fast.
• You end up with a transaction log that can be inspected row-by-row. If a bug turns up, you will be able to isolate the particular transaction that caused it.

What is Indexed (materialized) views?

                                       Even if it sounds almost the same as the regular views, indexed views are completely different context. That type of the views are not only about the abstraction but more about performance. When you create the indexed view, SQL Server “materializes” the data in the view into physical table so instead of doing complex joins, aggregates, etc, it can queries the data from that “materialized” table. Obviously it’s faster and more efficient.

 When to Use :
                           Indexed views have both a benefit and a cost. The cost of an indexed view is on the maintenance of the clustered index (and any non-clustered indexes you may choose to add). One must weigh the cost to maintain the index against the benefit of query optimization provided by the index. When the underlying tables are subject to significant inserts, updates, and deletes, be very careful in selecting the indexes (both table and view) that will provide the greatest coverage across your queries for the lowest cost. Typically, environments that are best suited for indexed views are data warehouses, data marts, OLAP databases, and the like. Transactional environments are less suitable for indexed views. Look for repeating joins utilizing the same columns, joins on large tables, aggregations on large tables, and repeating queries as potential candidates for indexed views. Be careful of creating indexed views where the result set contains more rows than the base tables as this will be counterproductive.

Steps to create Index View :
1.Create View Schema binding option :
     This means that once the indexed view is created, the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped. 
2.Create the unique clustered index on the view making it an indexed view .

                                       Once this index is created, the result set of this view is stored in the database just like any other clustered index. Any query that explicitly uses the view will be able to take advantage of the index on the view. Queries that contain a predicate similar to the view and that fall into the range defined by the view may also reap the optimization rewards of having that index available

                               Even though the query does not use the indexed view, the optimizer has the option of using the clustered index created on the view if it provides better performance than the clustered or non-clustered indexes on the base table. If you want the optimizer to always choose the indexed view over the base tables when optimizing a query containing an index view, you must use the hint NOEXPAND.