Sunday, July 29, 2012

SQL SERVER- How To Stop and Restart to default Instance of SQL Server Using Command Prompt


It is very easy and useful. Follow the below step to perform it.

Open Command Prompt-

Click on Start -> Run -> Type cmd  ->Ok

See if SQL Server instance is already running then below net start mssqlserver command will return below result.
  Now we will STOP the SQL Server using net stop mssqlserver command

  
Now SQL Server has been Stooped
Now again we will restart the SQL Server default Instance using net start mssqlserver command






Like and Share to SQL Integrity Blog

SQL Server – How To Get Financial Year and How to get First Date Of Current Month


Question: How To Get Current Financial Year?
Use the below script to get the current or any year of financial year.

 DECLARE @6 smalldatetime
 DECLARE @8 varchar(20)
 DECLARE @9 varchar(20)
 SET @8  = CAST(YEAR(GETDATE()) AS int)
 SET @6 = CAST(MONTH(GETDATE())AS int)

 if(@6>3)
 begin
 SET @8=CAST(YEAR(GETDATE()) AS int) +1
 SET @9=CAST(YEAR(GETDATE()) AS int)
 SET @9 =@9 +'-'+ @8 
 SELECT @9 AS Financial_Year
 end
 else if (@6<4)
 begin
 SET @8=CAST(YEAR(GETDATE()) AS int) -1
 SET @9= CAST(YEAR(GETDATE()) AS int)
 SET @9 =@8 +'-'+ @9 
 SELECT @9 AS Financial_Year
 end

Output:

Financial_Year
--------------------
2012-2013


Question: How to get First Date Of Current Month?

DECLARE @1 varchar(20)
DECLARE @2 varchar(20)
DECLARE @3 varchar(20)
DECLARE @4 varchar(20)
DECLARE @5 varchar(20)

 SET @1 =GETDATE()
 SET @2 =STUFF (@1,4,3,'')
 SET @3 =SUBSTRING (@2,1,4)
 SET @4 =SUBSTRING (@2,4,LEN(@2))
 SET @5= @3 + '01'+ @4

SELECT @5 AS First_Date_Of_This_month

Output:

First_Date_Of_This_month
------------------------
Jul 01 2012  9:57PM




Like and Share to SQL Integrity Blog

SQL SERVER – Hint’s on All Date Format Using Date Code



 Most of the time we want to format date in output and most of the SQL New Developer consume time to get the desired formatted date output. Here I am sharing the entire date format in below script.

 SET NOCOUNT ON;

 SELECT CONVERT(varchar(12), GETDATE(), 100)
 SELECT CONVERT(varchar(12), GETDATE(), 101)
 SELECT CONVERT(varchar(12), GETDATE(), 102)
 SELECT CONVERT(varchar(12), GETDATE(), 103)
 SELECT CONVERT(varchar(12), GETDATE(), 104)
 SELECT CONVERT(varchar(12), GETDATE(), 105)
 SELECT CONVERT(varchar(12), GETDATE(), 106)
 SELECT CONVERT(varchar(12), GETDATE(), 107)
 SELECT CONVERT(varchar(12), GETDATE(), 108)
 SELECT CONVERT(varchar(12), GETDATE(), 109)
 SELECT CONVERT(varchar(12), GETDATE(), 110)
 SELECT CONVERT(varchar(12), GETDATE(), 111)
 SELECT CONVERT(varchar(12), GETDATE(), 112)
 SELECT CONVERT(varchar(12), GETDATE(), 113)
 SELECT CONVERT(varchar(12), GETDATE(), 114)


  
Output:

------------
Jul 29 2012
------------
07/29/2012
------------
2012.07.29
------------
29/07/2012
------------
29.07.2012


------------
29-07-2012


------------
29 Jul 2012
------------
Jul 29, 2012
------------
21:37:17
------------
Jul 29 2012
------------
07-29-2012
------------
2012/07/29
------------
20120729
------------
29 Jul 2012
------------
21:37:17:793














Like and Share to SQL Integrity Blog

Saturday, July 21, 2012

SQL SERVER – Hints On Some function’s on Data & Time


The following DateTime Function are mentioning below-
1) GETDATE()

2) SYSDATETIME()

3) SYSDATETIMEOFFSET()

4) TODATETIMEOFFSET()

5) SWITCHOFFSET()

Examples:

-- Get date fucntion
SELECT GETDATE() GetDate_function;

-- get the sysdatetime function
Select SYSDATETIME() SysDateTime_function;

-- get SYSDATETIMEOFFSET function
SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;

-- get TODATETIMEOFFSET fuction with SYSDATETIMEOFFSET function
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-02:00') 'GetCurrentOffSet-2';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+00:00') 'GetCurrentOffSet+0';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+02:00') 'GetCurrentOffSet+2';

--get TODATETIMEOFFSET fuction with SWITCHOFFSET function
SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-02:00') 'GetCurrentOffSet-2';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00') 'GetCurrentOffSet+0';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:00') 'GetCurrentOffSet+2';

Result:




Like and Share to SQL Integrity Blog

Friday, July 20, 2012

SQL SERVER – Hint on Max size in data type and about -1 length of column.


Question: How many Columns have VARCHAR (MAX), NVARCHAR (MAX), VARBINARY (MAX) and XML Data Type using INFORMATION_SCHEMA.COLUMNS & Value Character Maximum Length -1

 

Generally many SQL Users and Experts thinks about how to find out the how many columns have in the table that have MAX data type and most of the user thinks about that the length of any column can be 0  or more than Zero but VARCHAR(MAX), VARBINARY(MAX),NVARCHAR(MAX) and XML data type contain the -1 CHARACTER MAXIMUM LENGTH in SQL Server.

It is very important to know because MAX data type contain the large value but if you think about it is not necessary to be a MAX than you can reduce the length of your data type

To copy and run the following example in SSMS and see the how many columns are containing -1 length in one database?

Example:

Use AdventureWorks
GO

--For Single Database using INFORMATION_SCHEMA.COLUMN

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE CHARACTER_MAXIMUM_LENGTH=-1

--For Single Table using INFORMATION_SCHEMA.COLUMN

SELECT  TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=OBJECT_NAME(OBJECT_ID('Sales.Store'))

AND CHARACTER_MAXIMUM_LENGTH= -1

Result:



Like and Share to SQL Integrity Blog

Thursday, July 19, 2012

SQL SERVER – Hint on Using uniqueidentifier or GUID Data Type


Question: What is GUID?

Question: How to create Uniqueidentifier with Example?

Question: Drawback of Uniqueidentifier Data type?

Question: What is Size of UniqueidentifierData type?

 

The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). Where as int datatype contain the 4- bytes.

The uniqueidentifier data type does not automatically generate new IDs for inserted rows in the way the IDENTITY property does. For example, to obtain new uniqueidentifier values, a table must have a DEFAULT clause specifying the NEWID or NEWSEQUENTIALID function, or INSERT statements must use the NEWID function.

Uniqueidentifier can only 36 character.

Example:

Use AdventureWorks
Go
-- creating table with UniqueIdentifier
CREATE TABLE TestTab
   (MyUniqueColumn   UNIQUEIDENTIFIER  DEFAULT NEWID(),
    Name      VARCHAR(30) )
GO
-- inserting Some records in created TestTab table

INSERT INTO TestTab(Name) VALUES ('Jainendra')

INSERT INTO TestTab VALUES (NEWID(), 'Jai Verma')
GO
-- Selecting row from table

select * from TestTab

Result:

MyUniqueColumn                           Name
------------------------------------     -----------
C0E91EDB-D8F9-4B85-8ED5-E0494BD3AC49     Jainendra
A8C9D113-A1FC-421E-B8F9-FE91B0DA9604     Jai Verma

(2 row(s) affected)

  
--Clear data
Drop table TestTab

 Limitation and Drawback:

1.        The values are long.
2.        It is not good for query optimization
3.        it’s slow the performance.
4.        This makes them difficult for users to type correctly.
5.        More difficult for users to remember.
6.        The values are random and cannot accept any patterns that may make them more meaningful to users.
7.        There is no way to determine the sequence in which uniqueidentifier values were generated.
8.        They are not suited for existing applications that depend on incrementing key values serially.
9.        At 16 bytes, the uniqueidentifier data type is relatively larger than other data types, such as 4-byte integers. This means indexes that are built using uniqueidentifier keys might be relatively slower than indexes using an int key.

·        Uniqueidentifier can only 36 characters  

See the Example and how to work it:

Step 1

   DECLARE @myGUIDid uniqueidentifier = NEWID();

SELECT CONVERT(char(255), @myGUIDid) AS 'MyGUIDValue';

Result:

MyGUIDValue
----------------------------------------------------
436F7702-0B72-490F-B36F-02819F2A361E

(1 row(s) affected)


Step 2

DECLARE @ID nvarchar(max) = N'436F7702-0B72-490F-B36F-02819F2A361EExtraString';

SELECT @ID, CONVERT(uniqueidentifier, @ID) AS MyTruncatedValue;

Wrong value                                                                                                                            
-------------------------------------------------- --------------------
436F7702-0B72-490F-B36F-02819F2A361EExtraString  

MyTruncatedValue
-------------------------------------------------- --------------------

436F7702-0B72-490F-B36F-02819F2A361E

(1 row(s) affected)

Like and Share to SQL Integrity Blog

SQL SERVER – Best Use of EXISTS Keyword (Performance hint)


In below two scripts I have  used the Use of = and IN operator in 1 & 2 Query  and in Third query I have used the Exists Keyword for checking the performance issue and when I seen the Execution Plan and the outcome is that Use the of = operator or IN operator is less better than use of EXISTS Keyword.

Run the below script and See the Execution plan.

USE AdventureWorks
GO

--1) use of IN operator
SELECT * FROM HumanResources.Employee E
WHERE E.ManagerID  in ( SELECT EmpAdd.EmployeeID
FROM HumanResources.EmployeeAddress EmpAdd
WHERE EmpAdd.EmployeeID = E.ManagerID)
GO
--2) use of = operator
SELECT * FROM HumanResources.Employee E
WHERE E.ManagerID  = ( SELECT EmpAdd.EmployeeID
FROM HumanResources.EmployeeAddress EmpAdd
WHERE EmpAdd.EmployeeID = E.ManagerID)
GO
--3) use of exists Keyword
SELECT * FROM HumanResources.Employee E
WHERE EXISTS ( SELECT EmpAdd.EmployeeID
FROM HumanResources.EmployeeAddress EmpAdd
WHERE EmpAdd.EmployeeID = E.ManagerID)
GO

Result: (Snap Shot of Execution Plan)




Like and Share to SQL Integrity Blog

Wednesday, July 18, 2012

SQL SERVER - Clear Buffer Pool or Clear Cache Memory


Note: I will suggest to you don't run below query on the production server because it's slow down your performance.

So using the following query you can Drop or clear the Cache Memory in SQL Server.

DBCC DROPCLEANBUFFERS;


Result:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Like and Share to SQL Integrity Blog

Tuesday, July 17, 2012

SQL SERVER – Script to Rebuild & Organize all Tables in Single Database Without write lots of Query

Just Run the below T-SQL Script and get all auto build query for Reorganize and Rebuild to all table in a single database.

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        PRINT (@command);
        PRINT  (@command);
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO


Result:

ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REORGANIZE
ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REORGANIZE
ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product] REBUILD
ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product] REBUILD


After that select result and Run to Reorganize and Rebiuld all Tables of a single Database.

ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REORGANIZE
ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REORGANIZE
ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product] REBUILD
ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product] REBUILD

Result:

Command(s) completed successfully.

Like and Share to SQL Integrity Blog