Top 5 SQL Server Tips for Beginners and Junior DBAs

Hello guys, If you are using Microsoft SQL Server database and use SQL Server Management Studio in your day to day work and looking for SQL Server tips to improve productivity and work fast and efficiently then you have come to the right place. Earlier, I have shared SQL Server online courses, SQL Server Interview questions and SQL Server Management Studio keyboard shortcuts and today, I am going to share amazing tips to make most of SSMS. These are the tips I learned hard way after working close to 8 years on SQL Server backend. 

Anyway, we all love tips? isn't it? Well, I certainly do, they not only make life easier on work but also gives us a joy of discovery. 

Every time I discover a not-so-trivial tip I feel happy and become more productive and that's why I am always in hunt of useful tips which can help me in my work. 

So, when I started working with Microsoft SQL Server as a Java developer because one of my project had SQL Server backend with lot of stored procedure called using JDBC, it was no different. 

I started from scratch so learning tips was not on card but after spending a couple of months I realized that I can do a lot better by applying some of the general SQL tips I have learned over the years and learning few tips from colleagues and DBAs which helps us with database admin work like permissions, replication, and user management. 

This article is collection of those practical tips. 

Even though I initially thought to share 10 tips as the title 10 tips to take your SQL Server Management Studio skill to next level sounds great but I don't have that many now, though it will definitely have that many and more in future. 

Even though I am good at discovering and learning tips but I am equally bad at documenting them and really good at forgetting them. Sounds similar? well, we all learn important stuff every day but we also forget something equally important. 

I have thought about this article for a long time and even wrote many tips to include in this one but I somehow lost them. They may be berried somewhere in those unpublished drafts or manuscripts which I wrote. 

So, finally I decided to push whatever I remember top of my head now and will update and include more tips later. At least whatever I will put will help someone in their work and save some of their time and may even provide a bit of joy of discovering these tips. 

Though, there is no criteria on this tips they are generally help you in your job. Some of the tips will help you to write better and faster SQL queries while other will make it easier to work in SQL Server Management Studio, my favorite tool to work with SQL Server database. 

Btw, learning tips only help if you have basic knowledge, if you don't know SQL Server at all or a beginner, I suggest you to join some comprehensive courses like Introduction to SQL Server before jumping into these tips. 

If you are in hurry, here are the top 5 SQL Server Management Studio tips every developers and junior Database Administrator should know

5 Practical SQL Server Tips for Programmers and DBAs

Anyway, without wasting anymore of your time, here are some of my practical tips to work better with Microsoft SQL Server and Management Studio:

1. Use Alias for tables in Query

Always use alias while writing SQL query, particularly with SQL Server Management as it not only reduces the length of the query and makes it easier to refer to the data with just one or two letter, but also it helps SQL Editor like SSMS in Code Completion. 

For example, SQL Server Management Studio will show all the columns from table which further helps you to write query.

If you write below query:

select COUNT(*) from Instruments.dbo.OptionsESM o where o.Disabled=0 

when you type o. SSMS will show all the columns. This is super useful when your table has large number of columns

2. Avoid database name on SQL Scripts

Avoid prepending database name in SQL query while writing them in script. This will allow you to run the exact same address into other copies of same database for testing. For example, if you want to run your SQL queries on Accounts table in Production but before that you want to test it the prod copy of database to avoid any syntax errors. 

Most likely you will ask your DBA to copy prod database into UAT server with names like Accounts_PROD_DATE, now if you have used database names like [Accounts].[dbo].[Customer] then you need to change that line all over the place in script, which is both error prone and risky. 

If you have just written [dbo].[Customer] then you can just write click on the database in SQL Server Management studio and copy the whole script and run it, without any change. 

Good Example:

alter table [dbo].[Customer] add CustomertId bigint null

Bad Example

alter table [Accounts].[dbo].[Customer] add CustomerId bigint null

3. Extracting Stored Procedure code

Sometime you need to copy the proc from one database to other database like from UAT to Production. In order to get the SQL to create the stored proc, I used to go to the database in SQL Server Management Studio, then find the proc under "Programmability" node.

Then I look for the proc, which can be very painful if you have hundreds of proc, and then right click on the proc and select "Script Stored Procedure As" - > "Create to" or "Alter to" depending upon whether its a new proc or existing proc. 

This method works but it is too time consuming. 

The better way to get the code for a stored procedure is to use the "sp_helptext" system proc, which will give you the code instantly rather than you look for. Just run it like below:

EXEC sp_helptext usp_AccountByCustomerId_Get

This will give you the proc code like below:

CREATE PROCEDURE [dbo].[usp_AccountByCustomerId_Get] 

This is fast and good and work all the time. But, it always give you CREATE PROCEDURE, hence if you have to run into production and stored procedure already exists then you need to change CREATE to ALTER before running. 

4. Use SELECT to generate dynamic queries

Sometime we need to copy data from one database to another. AFAIK, there is no direct way where you can select the data, right click and export as insert into statement. 

If there is then please let us know. You need to write a script for yourself to do this, for this you need to know all columns and their data types. This can be obtained by using sp_help stored procedure. 

In such cases, SELECT command can be really useful for generating queries with data from a database. For example, if you want to copy some rows from a table in one environment to other like DEV to UAT then you can do that by using SELECT statements. 

For example, suppose you need to copy 100s of new books from your DEV to UAT database for testing? You don't have insert statement written for you and you don't want to write them by hands, in this case you can use SELECT queries like below to generate those insert statement using DEV data:

select 'insert into [MyUATBooksTable] (ISBN, Title, User) values (' 
+ CAST(p.ISBN as VARCHAR) 
+ ', '
+ p.Title 
+ ''', CURRENT_USER)'
from MyDEVBooksTable p where Title like "%SQL%"

This will generate insert statements which you can execute in your UAT or Testing database. 

5. Use BEFORE and AFTER for verifying SQL updates

If you are updating data on your database for a limited number of records like 10 to 15 then you can use "BEFORE" and "AFTER" with SELECT clause to print the list before and after the change. You can use this to verify and compare the data updates. 

Though, I always prefer an automated test to confirm the data integrity, this quickly way is also great when you are updating a limited number of records in UAT or Production environment.

Here is the example where I am updating price of all Josh Bloch book in my Books table to give you 50% discount

select 'BEFORE', Title, Author, Price from Book where Author="Josh Bloch"

update Book set Price=20 where Author="Josh Bloch"

select 'AFTER', Title, Author, Price from Book where Author="Josh Bloch"

That's all about the 5 practical SQL Server tips for developers and DBAs. I knew more tips but I am also really good at forgetting them. Hopefully this article will now encourage me and provide a single place to write whatever SQL Server tip I learn along the way. If you have a SQL Server tip which have helped you in your work, feel free to share with us. 

Other SQL Articles and Tutorials You may like

• How to join three tables in one single SQL query (solution)
• Write a SQL query to find all table names on a database in MySQL (solution)
• 5 Courses to learn Database and SQL Better (courses)
• The real difference between WHERE and HAVING clause in SQL? (answer)
• Top 5 Courses to learn Microsoft SQL Server in-depth (courses)
• How to migrate SQL queries from Oracle to SQL Server? (answer)
• 6 Free Courses to learn SQL from scratch (Free SQL courses)
• Top 5 Websites to learn SQL online for FREE? (resource)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Write a SQL query to copy or backup a table in MySQL (solution)
• How do you find the duplicate rows in a table on a database? (solution)
• 5 Advanced SQL books to level up your SQL skills (books)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• What is the difference between View and Materialized View in Database? (answer)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• Difference between Primary and Candidate key in the table? (answer)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• Difference between Self and Equi Join in SQL? (answer)
• 4 Free Books to learn Microsoft SQL Server database (books)
• Top 5 Websites to learn SQL online for FREE? (websites)
• 5 Free Courses to learn Database and SQL (free courses)
• 5 Free Courses to learn Oracle and SQL Server? (courses)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between the Unique and Primary keys in the table? (answer)
• Top 5 Courses to learn PostgreSQL in-depth (courses)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between Primary and Foreign keys in the table? (answer)

Thanks for reading this article so far. If you like these SQL Server tips then please share with your friends and colleagues. If you have any questions or feedback then please drop a note. If you have SQL Server or SQL Server Management Studio tip, feel free to share with us by dropping a note. 

As I have said before, We all can learn from each other and remember, each reader sharing a tip, we would have 100s of useful practical tips on our sleeves.

P. S. - If you want to learn SQL Server in depth and looking for online courses and tutorials then you can also checkout these best TSQL and SQL Server courses. These are the best online courses you can get to learn SQL Server and tools like SSMS in depth.