Convert UTC Time to specific Time-Zone on SQL

Convert UTC Time to specific Time-Zone on SQL

CREATE FUNCTION [dbo].[ConvertDatewithZoneOffset]
(
 @DateTimeStamp DATETIME,
 @TimeZoneOffset VARCHAR(20)
)
RETURNS DATETIME
AS
BEGIN
 RETURN CONVERT(DATETIME, 
               SWITCHOFFSET(CONVERT(DATETIMEOFFSET, 
                                    DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), @DateTimeStamp)),@TimeZoneOffset)) 
END

Read More

Find dependency of a particular object with all procedures/Objects of a database

There are various methods to finds the dependent objects , there is a system defined procedures sp_depends but it does not give perfect result always as it give results on the basis of creation order, more accurate ways are below

If we need to find paymentdetail table dependency on procedures/objects ,then  query

will be

FIND Dependency 

SELECT DISTINCT so.name

FROM sys.syscomments sc

INNER JOIN sys.sysobjects so ON sc.id=so.id

WHERE sc.TEXT LIKE '%paymentdetail%'

OR

SELECT referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT');







FIND Text in Procedures

select name  from sys.procedures

where OBJECT_DEFINITION(OBJECT_ID) like '%paymentdetail%'

Read More

Different types of SQL Keys : Key Constraints

Basically only Primary keys and foreign keys are two types of constraints that can be used to

enforce data integrity in SQL Server tables. These are important database objects. Others are the Database Concepts, these are also used to create relationship among different database tables or views.

Types of SQL Keys

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views.

Super Key

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.Example :Primary key, Unique key, Alternate key are subset of Super Keys.

Candidate Key(Surrogate Key)

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Primary Key

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

Alternate key

A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.


Composite/Compound Key

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

Unique Key

Unique key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values. 

Foreign Key

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. 

Check also:

How to optimize a Stored Procedure - SQL Server

Read More

Recover SQL Server Database from Suspect Mode

If your Database goes to Suspected Mode you can try below queries to retrieve the database in normal mode.

Reason for database to go into suspect mode:

1.    Data files or log files are corrupt.

2.    Database server was shut down improperly

3.    Lack of Disk Space

4.    SQL cannot complete a rollback or roll forward operation.

1. Open SQL Server Management Studio 
2. Open new query window and execute some queries:
   
   
   • use master
   • Alter Database TestDataBase Set Emergency
   • ALTER DATABASE TestDataBase  SET SINGLE_USER
   • DBCC CHECKDB (TestDataBase , REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
   • ALTER DATABASE TestDataBase SET MULTI_USER
   • ALTER DATABASE TestDataBase SET ONLINE
   • SELECT STATE_DESC FROM SYS.DATABASES WHERE NAME='TestDataBase'
     
3. If all queries executed successfully, then database is recovered from suspected mode (some data may be loss after completion of this process. Ask the store to do PI after this process.).

Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly.

Read More

Detach, Attach and Restore the Database with Transact-SQL

Detach, Attach and Restore The Database with Transact-SQL using a test Database. Here i'm using 'MyDataBase' as Test DB.

Prerequisites

• The database must first be detached. Attempting to attach a database that has not been detached will return an error.
• When you attach a database, all data files (MDF and LDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.
• When you attach a database, if MDF and LDF files are located in different directories and one of the paths includes \\?\GlobalRoot, the operation will fail.

Detach The Database

USE master
EXEC sp_detach_db 'MyDataBase', 'true';

Attach The Database

USE master
CREATE DATABASE MyDataBase
ON 
(FILENAME = 'D:\DataBase\MyDataBase.mdf'), -- Location where to store Data File
(FILENAME = 'D:\DataBase\MyDataBase_log.ldf') -- Location where to store Log File
FOR ATTACH;

Restore The DataBase (.Bak File)

USE master
RESTORE DATABASE MyDataBase
FROM DISK = 'D:\DATABASE\MyDataBase'  -- Location where Bak file resides
WITH 
MOVE 'MyDataBase(ThisFashion)_Data' TO 'D:\DataBase\MyDataBase.mdf'  -- Location where to store Data File
MOVE 'MyDataBase(ThisFashion)_Log' TO 'D:\DataBase\MyDataBase_log.ldf'  -- Location where to store Log File

Read More

How to use Cursor in SQL Server

A cursor is a set of rows together with a pointer that identifies a current row.

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable. it's like record-set in ASP and Visual Basic Declaring a Cursor Before using cursor, you first must declare the cursor, i.e. define its scrolling behavior and the query used to build the result set on which the cursor operates. To declare cursor, you can use a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions. DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF column_name [,...n]]}] where cursor_name - the name of the server side cursor, must contain from 1 to 128 characters. INSENSITIVE - specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor. SCROLL - specifies that cursor can fetch data in all directions, not only sequentially until the end of the result set. If this argument is not specified, FETCH NEXT is the only fetch option supported. select_statement - the standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords. READ ONLY - specifies that cursor cannot be updated. UPDATE [OF column_name [,...n]] - specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.

Opening a Cursor

Once a cursor has been declared, you must open it to fetch data from it. To open a cursor, you can use the following syntax:

OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}

where

GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be opened; otherwise, the global cursor will be opened.

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - the name of a cursor variable that references a cursor.

After a cursor is opening, you can determine the number of rows that were found by the cursor. To get this number, you can use @@CURSOR_ROWS scalar function.
Fetching a Cursor
Once a cursor has been opened, you can fetch from it row by row and make multiple operations on the currently active row in the cursor. To fetch from a cursor, you can use the following syntax:

FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar} ] FROM ] { { [GLOBAL] cursor_name } | @cursor_variable_name} [INTO @variable_name[,...n] ]

where

NEXT - the default cursor fetch option. FETCH NEXT returns the next row after the current row.

PRIOR - returns the prior row before the current row.

FIRST - returns the first row in the cursor.

LAST - returns the last row in the cursor.

ABSOLUTE {n | @nvar} - returns the nth row in the cursor. If a positive number was specified, the rows are counted from the top of the data set; if 0 was specified, no rows are returned; if a negative number was specified, the number of rows will be counted from the bottom of the data set.

RELATIVE {n | @nvar} - returns the nth row in the cursor relative to the current row. If a positive number was specified, returns the nth row beyond the current row; if a negative number was specified, returns the nth row prior the current row; if 0 was specified, returns the current row.

GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be fetched; otherwise, the global cursor will be fetched.

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - the name of a cursor variable that references a cursor.

INTO @variable_name[,...n] - allows data returned from the cursor to be held in temporary variables. The type of variables must match the type of columns in the cursor select list or support implicit conversion. The number of variables must match the number of columns in the cursor select list.

Closing a Cursor
When you have finished working with a cursor, you can close it to release any resources and locks that SQL Server may have used while the cursor was open.
To close a cursor, you can use the following syntax:

CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name }

where

GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be closed; otherwise, the global cursor will be closed.

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - the name of a cursor variable that references a cursor.

Note. If you have closed a cursor, but have not deallocated it, you can open it again when needed.

Deallocating a Cursor
When you have finished working with a cursor and want to completely release SQL Server resources that were used by a cursor, you can deallocate a cursor.
To deallocate a cursor, you can use the following syntax:

DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name}

where

GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be deallocated; otherwise, the global cursor will be deallocated.

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - the name of a cursor variable that references a cursor.

Note. Deallocating a cursor completely removes all cursor references. So, after a cursor is deallocated, it no longer can be opened.

Example

DECLARE @ColExpir datetime
DECLARE @ColFallprotec datetime
DECLARE @ColWorkid int
--------------------------------------------------------
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT Table_Training_Detalis.DateExpires,Table_Training_Detalis.Worker_ID
FROM   Table_Courses 
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColExpir,@ColWorkid
WHILE @@FETCH_STATUS = 0
BEGIN
update Table_Workers set WHIMIS= @ColExpir where Worker_ID=@ColWorkid
 
FETCH NEXT FROM @MyCursor
INTO @ColExpir,@ColWorkid
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

Read More

How to optimize a Stored Procedure - SQL Server

We will go over how to optimize Stored Procedure with making simple changes in the code.

• Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

• Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like

SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method

• Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

• Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')

• Use the sp_executesql stored procedure instead of the EXECUTE statement.
  The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

• Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
• Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
• Try to Avoid Inner Query (sub Query) - do not use inner queries in your stored procedure it slows down your sp.
• Use Table Indexes - Tables should have proper indexes and should be compiled time to time as indexes may be weird off after some time due to huge data insertion or deletion.
• Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

Read More

Differences between Stored Procedures and Functions

DIFFERENCES

• Procedure can return zero or n values whereas function can return one value which is
  
  mandatory.
• Procedures can have input/output parameters for it whereas functions can have only input parameters.
• Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
• Functions can be called from procedure whereas procedures cannot be called from function.
• Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
• We can go for transaction management in procedure whereas we can't go in function.
• Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

• UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

In depth

Stored Procedure

A Stored Procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

User-defined Function

A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

User defined functions have three main categories:

1. Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries.
2. Inline function - can contain a single SELECT statement.
3. Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.

Read More

Insert Multiple Records with One Query and Get Count on the basis of GroupBY +SQL

SQL SERVER 2005

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

DECLARE @ProductDtls  TABLE(id INT, Department VARCHAR(30),Product VARCHAR(100))

--INSERT MULTIPLE RECORDS WITH ONE QUERY

INSERT INTO @ProductDtls (ID, Department, Product)

SELECT 1,'Electronics', 'Cameras & Video' UNION ALL

SELECT 2,'Electronics','Cameras & Video' UNION ALL

SELECT 3,'Electronics','Cell Phones & Accessories' UNION ALL

SELECT 4,'Electronics','Cell Phones & Accessories' UNION ALL

SELECT 5,'Electronics','Cell Phones & Accessories' UNION ALL

SELECT 6,'Electronics','Computers, Tablets & Laptops' UNION ALL

SELECT 7,'Electronics','Computers, Tablets & Laptops' UNION ALL

SELECT 8,'Fashion','Clothing & Accessories' UNION ALL

SELECT 9,'Fashion','Clothing & Accessories' UNION ALL

SELECT 10,'Fashion','Shoes' UNION ALL       

SELECT 11,'Fashion','Shoes' UNION ALL

SELECT 10,'Collectibles & Art','Antiques' UNION ALL

SELECT 11,'Collectibles & Art','Antiques' UNION ALL

SELECT 10,'Collectibles & Art','Antiques' UNION ALL

SELECT 11,'Collectibles & Art','Art' UNION ALL

SELECT 12,'Collectibles & Art','Art'

--SELECT ALL RECORDS

SELECT * FROM @ProductDtls

--GET COUNT ON THE BASIS OF GROUP RECORDS

SELECT Department, COUNT(T.Product) 'ProductCount' FROM

(SELECT DISTINCT Department, Product FROM @ProductDtls) T

GROUP BY Department

Read More