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 Backup to Map Drive

MSSQL Backup to Map Drive:

if you want to backup a SQL Server database to a mapped drive use the following script
1) First it will create Map Drive in Local PC
2) Backup the Database.
3) Delete the Map Drive, so that next time the same script can use again.

EXEC master..xp_cmdshell 'net use z: "\\192.168.1.93\share" password /user:user1'
GO

BACKUP DATABASE DB1
to disk='z:\db1.bak' with init
GO

EXEC master..xp_cmdshell 'net use z: /delete'
GO

MSSQL Group by and Sum

Here are some example for MSQL SUM function and GROUP BY:


USE AdventureWorks;
GO
SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
WHERE Color IS NOT NULL
AND ListPrice != 0.00
AND Name LIKE 'Mountain%'
GROUP BY Color
ORDER BY Color;
GO


Alternate way.

USE AdventureWorks ;
GO
SELECT Color, ListPrice, StandardCost
FROM Production.Product
WHERE Color IS NOT NULL
AND ListPrice != 0.00
AND Name LIKE 'Mountain%'
ORDER BY Color
COMPUTE SUM(ListPrice), SUM(StandardCost) BY Color;
GO

Other Example:

USE AdventureWorks;
GO
SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
GROUP BY Color
ORDER BY Color;
GO


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.