1: Always have a Primary Key
When you create a new table, always ensure you have an ID field set as a primary Key. This is Database Design 101 but I have seen many production databases that have a number of tables with no Primary key. From my experience, 99.9% of the time it's best to set the ID column as an auto increment integer. This ensures that as soon a new entry is added to the table SQL server will automatically increment the ID. Also when you are inserting you don’t have to insert a new ID, SQL Server does this for you.
2: SQL Server Projects
How many times have you have so many SQL windows open with random snippets in SSMS that you can barely keep track. Before I knew about SQL Server projects, I had separate instances of SQL server open for different projects where I was working with data to try and keep things organised, plus I used to keep my machine on for as long as I needed these SQL windows open. Thankfully SSMS has the option to create a SQL Server project where you can organise your SQL files and snippets nice and neatly. It works just like a .SLN file in Visual studio. Give it a try you wont look back.
Just this week I had to update a table that was missing a timestamp field. This is a common mistake, which makes it very difficult to provide reporting on when an update or edit took place. For the vast majority of tables, ensure you add a TimeStamp Field of type DateTime.
A good Example of using timestamps is on a users table. On the User table for intermittentBug we have [DateRegistered], [LastSignedIn] and [EmailVerifiedDate] TimeStamp fields. This provides excellent clarity and reporting for user events.
4: Use diagram tools for Database Design, creating FK's and maintaining referential integrity
SQL Server Management Studio (SSMS) offers an excellent visual database design tool. You can access it by right clicking the "Database Diagrams" folder by expanding your DB in object explorer. It allows you to add and edit tables, assign PK's and drag out FK (foreign Key) relationships. It’s a great tool that makes creating a database schema's simple and easy.
5: SQL Table variables
As a Seasoned C# programmer writing SQL seemed easy at first, SELECT * FROM TABLE, wow this is easy! but things start getting complex quick. A technique I use when writing complicated SQL statements that require many joins and Aggregate functions across various tables is to take full advantage of SQL Table Variables. Table variables are an in memory table object that you can specify and populate. Once populated you can then join to another table variable to produce data that would be very difficult to do otherwise. I have a tutorial that goes through the basics here -> link coming soon
6: Tools for comparing/updating data and schemas
The way I work is to have 3 separate environments Dev, Staging and Live. This means I have 3 DB's, DB_Dev, DB_Staging and DB_Live. The problem is when you have completed all your work on your dev environment and you want to copy your data to staging for testing. You could create backups and then restore, but this is fiddly and time consuming. Also when you want to update your live DB restoring a backup is tricky and can result in downtime. Unbelievably, SSMS doesn't have a native comparison tool for data and schema changes. Thankfully Visual studio does. It has two tools, one for data which will copy data from the source DB to the target DB and a schema comparison which will update the target DB with changes to table structure, store procs, views, functions, ect. These tools are very useful so ensure you check them out.
7: Use Stored procedures
A Stored Procedure is essentially SQL code that can be run on demand. They offer 2 main advantages, 1 being the performance offered by executing SQL directly from the DB server and 2 the option to update at any time without any downtime for dependant apps. A prime example, Lets say we have a store proc that is used to return search results which orders the results by view count descending. If I want to switch the order by I can just alter the stored proc and update on the DB server. The application immediately updates, no need for a release or any downtime.
8: SQL Jobs
If you have access to any version of SQL server higher than express, you can use SQL Jobs. SQL Jobs are an inbuilt scheduling system that you can configure to trigger database events (jobs) that run at specific times or be trigged on an event. I have used them extensively to execute maintenance tasks such as backups or run data feeds. You can go right back to basics and simply trigger a stored proc to run at a certain time or at an interval such as every hour.
9: Generate Scripts
SSMS has a powerful feature called generate scripts that allows you to generate the SQL code that makes up your tables, stored procs, views or your entire database and all its data. The latter is especially useful when you need to backup your database or migrate to another DB server. One of the issues SQL Server has is backwards compatibility between versions. So for example I cannot restore a .bak file that was created in SQL Server 2014 to a SQL 2012 Server, even if the 2012 server is a higher version. To overcome this issue you can generate the entire SQL that makes up your database and all its data and simply execute this on the new server.
10: Object Explorer Details Window
Take this scenario, you have 100s of stored procs that have explicit database joins. You need to update them all to point to a test DB. Is the only way to achieve this to right click each one, update the DB namespace and then hit F5 to execute the alteration? Thankfully no, you can use the little known and often forgotten SSMS Object Explorer Details window. Using the Object explorer you can select all your stored procs, right click and select Drop and Create. This creates one big SQL file that contains all your stored procs so you can simply do a find and replace and then execute the lot in one hit.