Reading Time: 3 minutes There is an interesting factoid you should know when it comes to working with foreign key constraints in SQL Server. Normally, foreign key constraints are linked to a Primary Key column in a parent table Take a look at the following Books and BookSales tables: CREATE TABLE Books ( BookID Continue Reading
Developing Databases
How to make a column NOT NULL in SQL Server: Explained with Examples
Reading Time: 3 minutes You may find yourself needing the change the definition of a table or column from time-to-time while working as a database professional. One change you may need to make is changing a column from nullable to non nullable. It may have been decided that we actually don’t want NULL to appear Continue Reading
Difference between unique constraints and primary key constraints: Answered with examples
Reading Time: 4 minutes A primary key constraint and a unique constraint are both meant to enforce uniqueness in column(s). Since that’s true, it begs the question: What’s the difference? In this very brief tutorial, we’ll discuss the two main differences between primary key constraints and unique constraints. Difference # 1: Primary key constraints Continue Reading
SQL Server WAITFOR DELAY: Explained
Reading Time: < 1 minute The WAITFOR DELAY system function is the best tool we have in Microsoft SQL Server to introduce a pause in our SQL code. The syntax is extremely simple. We just run the WAITFOR DELAY statement on its own, like this: WAITFOR DELAY ’00:00:10′ You can see how in single quotes, Continue Reading
How to Gather User ID Values in SQL Server: Just use this one helpful function.
Reading Time: < 1 minute There may be times where it might be helpful to gather the ID of the person or application that is interacting with the data or objects in your database. Maybe you are trying to audit changes to your data, or maybe you want to get a better idea of how Continue Reading
SQL Server Functions VS Stored Procedures: What’s the difference?
Reading Time: 6 minutes Two of the most helpful objects you can create in Microsoft SQL Server are user-defined functions and stored procedures. If you are just starting out with SQL Server, you might wonder “What’s the difference?“. In this very brief tutorial, we’ll discuss the difference between SQL Server functions and stored procedures Continue Reading
SQL Server Filtered Indexes: Explained for Beginners
Reading Time: 5 minutes Filtered indexes in SQL Server can dramatically increase the efficiency of queries against data in your database. They are a fantastic optimization tool you should understand and remember during the design and development of tables in your database! In this brief tutorial, we’ll discuss what a filtered index is and Continue Reading
SQL Server Covering Indexes: Explained with Examples
Reading Time: 6 minutes Indexes are extremely important when it comes to writing queries that return data in an efficient way. We not only want to make sure our queries return exactly the information we need, but we also want that information to be returned to us quickly. In this brief tutorial, we’ll discuss the Continue Reading
SQL Server Key Lookup: Explained with Examples
Reading Time: 7 minutes The SQL Server “Key Lookup” operation is something you may see when looking at the execution plan of a query. Maybe you’re trying to optimize the query to run a bit faster and noticing that a lookup operation is taking a LARGE amount of the query processing time. Or maybe Continue Reading
Why can’t I add a foreign key constraint? ANSWERED!
Reading Time: 4 minutes When attempting to create a foreign key constraint on a table, you may receive an error message very similar to the following: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “constraint_name”. The conflict occurred in database “DatabaseName”, table “TableName”, column ‘ColumnName’. This may be happening because you are Continue Reading