Monday 29 April 2013

SQl SERVER- Difference Between On Clause And Where Clause



Difference Between On Clause And Where Clause When Used With Left Join in SQL Server:

Employee Table:  
 


 

use Working

 

create table tbl_Employee(EmpId int primary key ,EmpName nvarchar(50),Salary nvarchar(50),DeptID int references tbl_Department(DeptID))

 

 

insert into tbl_Employee values(2001,'Satish','10000','01')

insert into tbl_Employee values(2002,'Sai','20000','02')

insert into tbl_Employee values(2003,'Krishna','30000','03')

insert into tbl_Employee values(2004,'Sheshu','20000','04')

insert into tbl_Employee values(2005,'Pooja','15000','01')

insert into tbl_Employee values(2006,'Neha','80000','02')

insert into tbl_Employee values(2007,'Prasad','90000','03')

insert into tbl_Employee values(2008,'venkat','30000','04')

insert into tbl_Employee values(2009,'Ganga','40000','01')

insert into tbl_Employee values(2010,'Saraiah','70000','04')

insert into tbl_Employee values(2011,'Abhi','90000','04')

insert into tbl_Employee values(2012,'Riya','50000','04')

insert into tbl_Employee values(2013,'Ramya','20000','04')

insert into tbl_Employee values(2014,'Sriya','80000','04')

insert into tbl_Employee values(2015,'Manoj','10000','04')

insert into tbl_Employee values(2016,'NewEmp01','20000',Null)

insert into tbl_Employee values(2017,'NewEmp02','80000',Null)

insert into tbl_Employee values(2018,'NewEmp03','10000',Null)

 

go

Create table tbl_Department(DeptID int primary key,DeptName nvarchar(50))

insert into tbl_Department values(01,'Admin')

insert into tbl_Department values(04,'IT')

insert into tbl_Department values(02,'Networking')

insert into tbl_Department values(03,'HR')

 

Show only the "HR" and "HR & Accounts" departments along with their relevant employees?

 


Query to find Dept ID IS NULL


SQL SERVER – How to Retrieve TOP 1 and BOTTOM 1 Rows Together using T-SQL:

Correct Script Method 1:
USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID IN (
SELECT TOP 1 MIN(SalesOrderDetailID) SalesOrderDetailID
FROM Sales.SalesOrderDetail
UNION ALL
SELECT TOP 1 MAX(SalesOrderDetailID) SalesOrderDetailID
FROM Sales.SalesOrderDetail)
GO


USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID)
OR
SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC)
GO


 

SQL SERVER – 2008– Find Longest Running Query – TSQL :

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO

 

 

 

 

 

 

 

Circular Dependency : Here when we try to delete a record from TABLE A, it throws an error message as Column P & Column R of TABLE B are depending on Column Q of TABLE A.  When we try to delete a record from TABLE B, it again throws an error message as Column P of TABLE A is depending on Column P of TABLE B. In this case we are prevented from deleting the data from either of the tables because of the circular dependency existing between the tables.

Solution: To delete the records from the tables when circular dependency exists between the tables, we need to break the constraints. We cannot delete the constraints straight way as the tables already contain huge data. In this scenario, update the foreign key in one of the tables to null such that it removes dependencies on one of the tables i.e. Update Column P of TABLE A to null. Once it is updated to null, Column P & Column R of TABLE B have no more dependencies and data can be deleted from TABLE B. Data deletion from TABLE B indicates that there exists no data in TABLE B which is dependent on TABLE A. Finally come back to TABLE A and delete the data which is no more required.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Update Statistics:

Updating the statistics of all the tables regularly in your SQL Server Database is an integral part of Database Maintenance. This can be achieved in many ways. We can do this through a script or create a SQL job scheduled to run at a certain time.

To determine whether an index has it's stats updated we can run the following command:

 

DBCC SHOW_STATISTICS (table_name , index_name)

 

To Update the Stats on a table the following command can be run :

 

UPDATE STATISTICS <table name> WITH FULLSCAN

 

The above command will scan all the rows of the table to get the updated statistics.

   

UPDATE STATISTICS <table name> WITH SAMPLE 50 PERCENT

  

The above script can be run for very large tables in case we want to specify a percentage of the total no of rows.

 

 

We should also update statistics for all tables in a database when we are upgrading from any version to a higher SQL Server version. 

 

Note: The statistics update script/job should only be run during the maintenance window and not during peak hours of usage as it could slow down the performance of the application.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How to get the Particular Data Range

 

Let's say I want to fetch the records which have been inserted within last 10 days that means the records which are added within 21/7/2009 to 30/07/2009(30/07/2009 is Totay's Date) then the query will be like this:
SELECT * FROM tbl_name WHERE dateOfCreation >= CURDATE() - INTERVAL 9 DAY;
 
Note: If we want to fetch the data within a particular Date range then we can use the INTERVAL keyword

 

 

User friendly DATETIME Functions in SQL Server 2008

 

 

declare @dt as date

set @dt = GETDATE()

print @dt

 

output: 2013-02-20

 

declare @dt as datetime2(3)

set @dt = GETDATE()

print @dt

 

output: 2013-02-20 10:46:21.490

 

 

 

 

Insert records for columns where IDENTITY is set

 

SET IDENTITY_INSERT [table_name] ON

INSERT INTO …. SELECT …. 

SET IDENTITY_INSERT [table_name] OFF

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here are some useful DB SQL Queries :


TO COUNT NUMBER OF TABLES IN A DB

  SELECT [TABLECOUNT] = COUNT(OBJ.ID) FROM SYSOBJECTS OBJ WHERE XTYPE='U'


TO COUNT NUMBER OF STORED PROCEDURES IN A DB

  SELECT [SPCOUNT] = COUNT(OBJ.ID) FROM SYSOBJECTS OBJ WHERE XTYPE='P'


GET LIST OF TABLES NAMES AND THEIR ROW COUNTS

  SELECT [TABLENAME] = OBJ.NAME, [ROWCOUNT] = MAX(SI.ROWS) FROM SYSOBJECTS OBJ,
  SYSINDEXES SI WHERE OBJ.XTYPE = 'U' AND SI.ID = OBJECT_ID(OBJ.NAME)
  GROUP BY OBJ.NAME ORDER BY [ROWCOUNT] DESC


GET COLUMNS NAMES, DATA TYPES, IS NULLABLE OF A PARTICULAR TABLE

  SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE + CASE ISNULL(CHARACTER_MAXIMUM_LENGTH,'')
  WHEN '' THEN '' ELSE ' (' + CAST (CHARACTER_MAXIMUM_LENGTH AS VARCHAR(6)) + ')'
  END AS [DATA TYPE], CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL'
  END AS [NULL OR NOT] FROM INFORMATION_SCHEMA.COLUMNS WHERE
  TABLE_NAME = 'L_SAMPLE' ORDER BY TABLE_NAME, COLUMN_NAME


TO SEE THE DEFINITION OF A SP

  EXEC SP_HELPTEXT GET_USER_DATA


GET TABLES CREATION AND MODIFIED DATES

  SELECT [TABLENAME] = NAME, CREATE_DATE, MODIFY_DATE FROM SYS.OBJECTS
  WHERE TYPE = 'U' --AND NAME LIKE '%L_%'
  ORDER BY MODIFY_DATE DESC


GET STORED PROCEDURES CREATION AND MODIFIED DATES

  SELECT [SPNAME] = NAME, CREATE_DATE, MODIFY_DATE FROM SYS.OBJECTS
  WHERE TYPE = 'P' --AND NAME LIKE '%GET%'
  ORDER BY MODIFY_DATE DESC

 

 

Getting List  Of  tables With Number Of Records Of Database:

 

CREATE TABLE #counts

(

    table_name varchar(255),

    row_count int

)

 

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT table_name, row_count

FROM #counts

ORDER BY table_name, row_count DESC

 

 

drop table #counts

 

 

 

Use of CASE with SELECT statement

 

Simple Case Expression

 

SELECT
    Name, UserType = CASE TypeID
     WHEN '0' THEN 'Anonymous'
     WHEN '1' THEN 'Registered'
     WHEN '2' THEN 'Admin'
     ELSE NULL
    END
FROM
   User;

 

Searched CASE expression

 

 SELECT
   Name, Marks, 'Division' =
   CASE
    WHEN Marks < 350 THEN 'Fail'
    WHEN Marks >= 350 AND Marks < 450 THEN ' THIRD'
    WHEN Marks >=450 AND Marks < 550 THEN 'SECOND'
    WHEN Marks >=550 AND Marks < 650 THEN 'FIRST'
    ELSE 'Excellent'
   END
 FROM
     Student; 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How to insert more than one record using a single insert statement

 

CREATE TABLE LogicTree_Emp(ID int, NAME nvarchar(50))

go

INSERT INTO LogicTree_Emp(ID, NAME)

            VALUES(1,'userA'),

                   (2,'userB'),

                   (3,'userC'),

                   (4,'userD'),

                   (5,'userE'),

                   (6,'userF'),

                   (7,'userG'),

                   (8,'userH'),

                   (9,'userI'),

                   (10,'userJ')

 

 

 

 

 

SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

February 6, 2008 by pinaldave

This is very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202


 

 

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


 

 

 

 

 

 

 

 

 

SQL SERVER – How to Retrieve TOP 1 and BOTTOM 1 Rows Together using T-SQL:

Correct Script Method 1:
USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID IN (
SELECT TOP 1 MIN(SalesOrderDetailID) SalesOrderDetailID
FROM Sales.SalesOrderDetail
UNION ALL
SELECT TOP 1 MAX(SalesOrderDetailID) SalesOrderDetailID
FROM Sales.SalesOrderDetail)
GO



Correct Script Method 2:
USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID)
OR
SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC)
GO



 

Working using CTE example with adventureworks is here :
WITH TopBottomRow
AS
(
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
UNION ALL
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
)
SELECT *
FROM TopBottomRow

 

 

 

 

 

 

 

SQL SERVER – Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value:

The following solution is for getting 6th highest salary from Employee table:


SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

Query to find 6th highest record from database table using RANK Function.

USE AdventureWorks
GO
SELECT
t.*
FROM
(
SELECT
e1.*,
row_number() OVER (
ORDER BY e1.Rate DESC) AS _Rank
FROM
HumanResources.EmployeePayHistory AS e1
) AS t
WHERE
t._Rank = 6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL SERVER – 2008 – Introduction to Filtered Index – Improve performance with Filtered Index


September 1, 2008 by pinaldave

Filtered Index is a new feature in SQL SERVER 2008. Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

When we see an Index created with some WHERE clause then that is actually a FILTERED INDEX.

For Example,

If we want to get the Employees whose Title is “Marketing Manager”, for that let’s create an INDEX on EmployeeID  whose Title is “Marketing Manager” and then write the SQL Statement to retrieve Employees who are “Marketing Manager”.

CREATE NONCLUSTERED INDEX NCI_Department
ON HumanResources.Employee(EmployeeID)
WHERE Title= 'Marketing Manager'

Points to remember when creating Filtered Index:

-          They can be created only as Nonclustered Index
-          They can be used on Views only if they are persisted views.
-          They cannot be created on full-text Indexes.

Let us write simple SELECT statement on the table where we created Filtered Index.

SELECT he.EmployeeID,he.LoginID,he.Title
FROM HumanResources.Employee he
WHERE he.Title = 'Marketing Manager'


Now we will see the Execution Plan and compare the performance before and after the Filtered Index was created on Employee table.

As we can see, in first case the index scan in 100% done on Clustered Index taking 24% of total cost of execution. Once the Filtered index was created on Employee table, the Index scan is 50% on Clustered Index and 50% on Nonclustered Index which retrieves the data faster taking 20% of total cost of execution compared to 24% on table with no index.


If we have table with thousands of records and we are only concern with very few rows in our query we should use Filtered Index.

Conclusion:

A filtered Index is an optimized non clustered Index which is one of the great performance improvements in SQL SERVER 2008 reducing the Index storage cost and reduces maintenance cost.

 

 

 

 

 

 

 

 

 

 

 

 

SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2


March 5, 2008 by pinaldave

I have recently received email that I should update SQL SERVER – Simple Example of Cursor with example of AdventureWorks database.

Simple Example of Cursor using AdventureWorks Database is listed here.

USE AdventureWorks
GO
DECLARE @ProductID INT
DECLARE @getProductID CURSOR
SET @getProductID = CURSOR FOR
SELECT ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE @getProductID
DEALLOCATE @getProductID
GO

 

 

 

 

 

 

 

 

 

 

 

 

 

Example 1 : Simple TRY…CATCH without RAISEERROR function


BEGIN TRY
DECLARE @MyInt INT;
-- Following statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
SELECT 'Divide by zero error encountered.' ErrorMessage
END CATCH;
GO

ResultSet:
ErrorMessage
———————————
Divide by zero error encountered.

Example 2 : Simple TRY…CATCH with RAISEERROR function
BEGIN TRY
DECLARE @MyInt INT;
-- Following statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorMessage = ERROR_MESSAGE();
RAISERROR (@ErrorMessage, 16, 1);
END CATCH;
GO

ResultSet:
Msg 50000, Level 16, State 1, Line 9
Divide by zero error encountered.

 

 

 

 

 

 

 

 

 

 

How To Set Page Break in a report Conditionally


Sometimes we need to set page breaks after a particular number of records in a report, which can be achieved by using following steps.
Steps :
  1. In the Tablix region, select the Detail Row. Right click on it.
  2. Click an Add Group ->Parent group
  3. In the Group By expression, add the Expression  =Ceiling(RowNumber(Nothing)/20),click ok .
  4. Go for Row Group ->Group Properties.
  5. Go for Sorting Properties, Remove the Sort Expression.
  6. Go for Page Breaks and check the 'Between Each Instance of Group”, click ok.
  7. If you don't want to show newly added column, select the total column. Click on delete.
  8. Select delete only columns radio button.
                       
  Now save the report and preview. If your dataset returns 100 records, we are able to see 20 records per each page .

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Query to find number Rows, Columns, Byte Size for each table in the current database:

 

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

 

 

 

Select Only Date Part From DateTime – Best Practice

 

Ø  SELECT GETDATE()

 

Ø  SELECT CONVERT(VARCHAR(10),GETDATE(),111)

 

Ø  SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

 

 

 

 

 

 

 

 

 

 

 

 

 

List All Stored Procedure Modified in Last N Days

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7


----Change 7 to any other day value

Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 7


----Change 7 to any other day value.

 
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)