Everyone who shares a SQL Sever or other helpful “technical” trick (i.e., hack/tip/shortcut) as a comment to this post by Saturday, Oct. 31, will receive a SQL Solutions Group reusable cotton face mask and pen (pumpkin orange!). Plus, everyone who participates is eligible for our grand prize: A $50 Amazon gift card. We’ll select one random submitter to win!
Here’s a trick from Senior Consultant Scott Klein.
CSM helps school districts and other educational organizations maximize the benefits they are eligible to receive through the E-Rate program.
As businesses increasingly rely on data-driven decision-making, the need for robust SQL database support has never been greater. However, many companies face significant challenges in
This is part three of the blog series on following a structure when performance tuning. From the previous posts (part 1, part 2) we have
This Post Has 13 Comments
One of my favorite tricks is also one of the most simple!
The CTRL+X keyboard shortcut (or “Control X”) is “cut”– but it doesn’t work like cutting does in most other Microsoft applications. In MS Word, for example, CTRL+X is also cut, but it only cuts what you’ve selected. If you haven’t selected anything and just have a flashing cursor, MS Word doesn’t do anything.
SSMS is way cooler. In SSMS, if you have text selected, it works just like MS Word. However, if you DON’T have anything selected, it cuts the entire line! If your cursor is just flashing, not just the contents of the line are removed, but the entire line itself is removed.
This is great for two reasons. Obviously, it’s a quick and easy way to cut– but that wouldn’t be that useful in itself. Rather, the real benefit here is making it easy to rapidly delete lines of code. Whether you have old code you’ve commented out and need to remove, or empty lines you need to clean up, CTRL+X is a very, very quick way to rapidly remove entire lines from your query with minimal effort. I use it a fair bit.
Hope this helps someone!
Thanks for sharing, Zach!
One thing I have used is a little known trick is to use Common Table Expressions (CTEs) to delete duplicate data:
— Delete Duplicate Data – typically used in staging tables without primary key
CREATE TABLE #Patients (
PatientKey int IDENTITY(1,1),
PatientID int NOT NULL,
LastName varchar(40) NOT NULL,
FirstName varchar(40) NULL,
MiddleInit char(1) NULL,
Gender char(1) NULL,
DOB date NULL
);
INSERT INTO #Patients(PatientID, LastName, FirstName, MiddleInit, Gender, DOB)
VALUES (123456, ‘Duck’, ‘Daffy’, ‘D’, ‘M’, ’03/05/1964′),
(234567, ‘Bunny’, ‘Bugs’, ‘B’, ‘M’, ’02/29/1964′),
(345678, ‘Coyote’, ‘Wile’, ‘E’, ‘M’, ’01/15/1966′),
(123456, ‘Duck’, ‘Daffy’, ‘D’, ‘M’, ’03/05/1964′), — Duplicate
(456789, ‘Runner’, ‘Road’, NULL, ‘F’, ’04/23/1974′),
(345678, ‘Coyote’, ‘Whylee’, NULL, NULL, ’01/15/1966′), — Duplicate
(123456, ‘Duck’, ‘Daffy’, ‘D’, ‘M’, ’03/05/1964′), — Duplicate
(567890, ‘Duck’, ‘Daisy’, NULL, ‘F’, ’05/03/1966′),
(564231, ‘Fudd’, ‘Elmer’, NULL, ‘M’, ’03/20/1946′),
(853156, ‘Bird’, ‘Tweety’, NULL, ‘M’, ’10/10/1960′),
(462137, ‘Cat’, ‘Sylvester’, ‘T’, ‘M’, ’12/15/1955′),
(134679, ‘LePue’, ‘Peppy’, NULL, ‘M’, ’11/11/1959′),
(963741, ‘Duck’, ‘Huey’, NULL, ‘M’, ’09/09/1979′),
(963742, ‘Duck’, ‘Louie’, NULL, ‘M’, ’09/09/1979′),
(963743, ‘Duck’, ‘Dewey’, NULL, ‘M’, ’09/09/1979′);
SELECT * FROM #Patients ORDER BY PatientID;
— Show duplicate data
WITH cteDups AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY P.PatientID ORDER BY P.PatientID, P.PatientKey) AS RowNbr
FROM #Patients P
)
SELECT *
FROM cteDups D
WHERE D.RowNbr > 1;
— Delete duplicate data
WITH cteDups AS (
SELECT ROW_NUMBER() OVER(PARTITION BY P.PatientID ORDER BY P.PatientID, P.PatientKey) AS RowNbr
FROM #Patients P
)
DELETE
FROM cteDups
WHERE RowNbr > 1;
As long as you can specify the criteria that determines how to identify duplicates, this trick works beautifully! By how you arrange the ORDER BY in the ROW_NUMBER() function, you can do things like keeping the latest record or other such criteria.
Thanks for sharing, Aaron!
I would like to share the easy and simple tip that helps most of them working with Schemas. The trick is to view the Schema object from TSQL editor without many of the commands flow in.
ALT + F1 ( Schema definition)
Highlight the <> and be on the database where the table lies and press ALT + F1 give your definition identity/index/datatypes/constraints etc.,
For procedures, same trick works as knowing what all parameters name, type defined and order or parameter.
Hope this tip(ALT + F1) help and most of you may already know. help me a ton.
Thanks
AG
Anil, thanks for sharing that tip!
I sometimes want to export my results to excel and traditionally, I just click on the results, copy them, open excel, and paste them in. It seems like a no-brainer by now as I do this alot to share results with auditors and such.
However, with a free SSMS add on from APEXSQL.com called SQLComplete, when you right click on the results in grid form, one of menu options is to export to excel directly. It may not sound like much but after you do it a handful of times with a large number of columns and rows, it makes you wonder why Microsoft didn’t create it. No more parsing and figuring out what the separator should be.
For the record, I don’t work for them. I just like free software to make me more productive.
Tim, does seem strange that MS didn’t create that. Good tip. thanks!
A few SSMS keyboard shortcuts I find useful
CTRL+K+C / CTRL+K+U — comment / uncomment line
CTRL+R to hide/unhide results pane
Shift+Alt+Arrow selection — grab rectangular blocks of text/code for copy/paste
Cheers
We can all use some good keyboard shortcuts. Thanks!
Congratulations to Tim Gensler who was our random winner of the $50 Amazon gift card!
Woo hoo! Congrats Tim!
Congrats Tim!