IDENTITY_INSERT Scope level

Some time we need to insert in the identity field also but what will be the impact if we want to Allow the  identity insert, see the below example:

Create a Test Table :
————————————–

CREATE TABLE sample(
id int IDENTITY(1,1)
,name VARCHAR(100)
,address VARCHAR(100)
,phoneNo VARCHAR(15)
)

In One Session, I am going to Allow the Identity Insert
————————————————————————–

SET IDENTITY_INSERT dbo.sample ON;

INSERT INTO sample(id,name,address,phoneNo)
SELECT 10,'user-2','address-2','014002555-2'


SELECT * FROM sample

In another session i am going to insert normally without ID value:
——————————————————————-
INSERT INTO dbo.sample(name,address,phoneNo)
SELECT 'user','address','014002555'

SELECT * FROM sample

—————————-

So the Conclusion is IDENTITY_INSERT will be Enabled on Session Level only. You can insert with identity value from one session and another session will be working normally with auto identity value.

Advertisements

UPDATE with top clause

If you want update a table with top clause see the below script:

UPDATE top (1) TABLE1
SET status ='ready'
where status ='hold'

At the same time if you want to return the Row IDs of updated table:

UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid
where status ='hold'

—————— result—————-
rowid
1

Same thing if you want to store in variable

DECLARE @UpdatedIds table (id int)
DECLARE @rowId int


UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid INTO @UpdatedIds
where status ='hold'

SELECT @rowId=id FROM @UpdateIds
SELECT @rowId

Short Cut key and formatting

MSSQL Short Cut key for query Editors:

Some easy short cut key for MSSQL Management studio or Query Editor:

CTRL + f1 = sp_help
CTRL + 1 = sp_who
CTRL + 2 = sp_lock

CTRL + R = Show/Hide Result Window
CTRL + E = Execute the SQL statement
CTRL + L = Execute with Execuation plan
CTRL + G = GOTO line number
CTRL + F = find
CTRL + H = find and replace

CTRL + SHIFT + U = Convert to Upper Case
CTRL + SHIFT + L = Convert to Lower Case

If you want to change the style of your query text editor in MSSQL Management studio:

Go to the following menu:

TOOLS  ==> OPTIONS == > ENVIRONMENT == > FONTS AND COLORS

MSSQL Formatting Window

How to parametrize OPENQUERY in MSSQL

How to parametrize OPENQUERY in MSSQL:

MSSQL has good feature of linked server between two different Database in same location or in remote location.
OPENQUERY helps you to execute your query in Linked server (remote) .

In linked server you can directly execute your query without OPENQUERY but the performance will be a issue.

As per my experience OPENQUERY is best way to access the linked servers.

If you want to pass your variable in OPENQUERY than it is so simple. Use the following way to pass your variable in OPENQUERY.


DECLARE @strDate VARCHAR(10)
DECLARE @strSql VARCHAR(8000)
DECLARE @strLinked VARCHAR(8000)

SET @strDate= '2009-01-01'

SET @strSsql= 'SELECT * FROM TABLE1 WHERE TRN_DATE =' + '''' + '''' + @strDate+ '''' + ''''

SET @strLinked= 'SELECT * FROM OPENQUERY(MYLINKEDSERVER,' + '''' + @strSql+ '''' + ')'

--############ Print and see your script
PRINT @strLinked

--############ Final Execution
EXEC(@strLinked)

As per MSDN:

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

How to delete the MSSQL transaction log.

How to delete the MSSQL transaction log.

If your transaction log is big or too much large. it will utilize your space and other issue might be there, so you need to clean up the transaction log time to time. Follow the following steps and clean your LOG size.

1)  first take log backup

-- Backup on Drive
BACKUP LOG TESTDB TO DISK='d:\TEMP.bak' with init


-- Backup and Delete file

BACKUP LOG TESTDB TO DISK='NUL'

 

2) Shrink your Database.


USE TESTDB
GO
DBCC SHRINKFILE ('TESTDB_log', EMPTYFILE);
GO

From GUI mode: Rclick  DB ==> Task ==> Shirinks ==> Database ==> specify size and press OK

3) Now see your log and file size.


SELECT name AS [File Name] , file_id, physical_name AS [Physical Name],
size/128 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS [Available Space In MB]
FROM sys.database_files;

MSDN:
You can reduce the default size of an empty file by using DBCC SHRINKFILE target_size. For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. This applies only to empty files that have never contained data.