Wednesday, November 24, 2010

SQL Server Tips (Keyboard Short cuts)



TaskShortcuts
Create a New FileCTRL+N
Toggle full screen modeSHIFT+ALT+ENTER
Toggle Result PaneCTRL+R
Comment Selected TextCTRL+K, CTRL+C
Uncomment the selected textCTRL+K, CTRL + U
Make the selected text upper caseCTRL+SHIFT+U
Make the selected text lower caseCTRL+SHIFT+L
Toggle between query and results paneF6
Display Object ExplorerF8
Output results in text formatCTRL+D
Output results in a gridCTRL+T
Cancel the executing queryALT+BREAK
Display the estimated execution planCTRL+L
Display the Replace dialog boxCTRL+H
Start incremental searchCTRL+I
Display the Find dialog boxCTRL+F
Find the next occurrence of the previous search textF3
Next bookmarkCTRL+K, CTRL+N
Execute QueryF5
Parse QueryCTRL+F5
Set or remove a bookmark at the current lineCTRL+K, CTRL+K
Previous bookmarkCTRL+K, CTRL+P
Clear bookmarksCTRL+K, CTRL+L
Go To a Line NumberCTRL+G
Display Context Menu(What is done through right click)SHIFT+F10
Change Database(It will focus on available datasoursec dropdown , you can type the database name or you can navigate through arrow buttons)CTRL+U

Friday, November 12, 2010

Deleting Duplicate Records In SQL Server using ROW_NUMBER() and PARTITION BY

--Drop table [dbo].[SalesHistory]
CREATE TABLE [dbo].[SalesHistory] (
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL )
GO
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
SELECT 'Computer','1919-03-18 00:00:00.000',1008.00
UNION ALL SELECT 'BigScreen','1927-03-18 00:00:00.000',91.00
UNION ALL SELECT 'PoolTable','1927-04-01 00:00:00.000',139.00
UNION ALL SELECT 'Computer','1919-03-18 00:00:00.000',1008.00
UNION ALL SELECT 'BigScreen','1927-03-25 00:00:00.000',92.00
UNION ALL SELECT 'PoolTable','1927-03-25 00:00:00.000',108.00
UNION ALL SELECT 'Computer','1919-04-01 00:00:00.000',150.00
UNION ALL SELECT 'BigScreen','1927-04-01 00:00:00.000', 123.00
UNION ALL SELECT 'PoolTable','1927-04-01 00:00:00.000', 139.00
UNION ALL SELECT 'Computer','1919-04-08 00:00:00.000', 168.00
select * from SalesHistory


delete from a from
(
SELECT Product, SaleDate, SalePrice,
DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) Ranking
FROM SalesHistory
) a where Ranking>1

---- Same can be done with ROW_Number Also

delete from a from
(
SELECT Product, SaleDate, SalePrice,
ROW_Number() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) Ranking
FROM SalesHistory
) a where Ranking>1

Copying Column Names From The SQL Server Query Result

Many times you need to see the Sql Server query result into excel or you need to paste the query output in a document . default setting will copy only the data rows not the header row. To copy the headings also you can set the following options .
Query > Query Options... >Results > Grid
check option "Include the column header when Copying or saving the results" .
Click OK and you are done.