Little SQL Server Tricks: Change Between NULL and NOT NULL Constraints
Changing between NULL and NOT NULL constraints for columns in SQL Server is simple. To turn a NOT NULL column into one that allows NULL, you can use this command:
Data Storage
Little SQL Server Tricks: The WITH (NOLOCK) Hint
Consistency is an important part of data storage. That is why READ COMMITTED is the default transaction isolation level in SQL Server. This level prevents you from retrieving values that modified inside a transaction but not committed. However, what is great for consistency is a pain for debugging.
Import CSV Files into SQL Server
Did you ever had a CSV file and needed a simple way to import it into SQL Server? If so, try the BULK INSERT command. As long as you do not have any NULL values, you will not find a simpler way.
Sql Server: Allowing Null in a Unique Constraint
Unique constraints help you to ensure the consistency of your data. As their name suggests, they let you insert a value only once in a column. This includes the special value NULL, what in most cases will not be a problem.
Shrinking the SSISDB
The SQL Server Integration Services (short SSIS) are a great tool to move data around. Not only are they fast, they have many useful parts to make data migration a simple task. The same is true for their production use. Cleaning up their own database is a feature that most applications completely ignore. All is set-up in a way that SSIS can run for a long time without user intervention.
Little SQL Server Tricks: Move a Table into another Schema
Not every design decision for your database will pass the test of time. Better names pop up or sometimes you just created a table in the wrong place. You could throw everything away and start from scratch, but a much better approach is just to fix what is wrong. If you want to move a table to another schema, you can use this SQL Server command:
Little SQL Server Tricks: Empty Table and Reset Auto Increment Id
From time to time I get my development database in a state where I need to get rid of all rows in a table. SQL Server offers two ways to do that: DELETE and TRUNCATE. When you need to remove all rows use TRUNCATE, it is the faster option:
Little SQL Server Tricks: Aggregate Functions on Columns
Aggregate functions (like Min(), Max(), Sum(), etc.) are often used to calculate values in a SELECT statement. Instead of loading all the data and calculate the values for yourself, you let the database give you the answer. That is a lot faster and requires less typing. Aggregate functions are mostly used on rows. But did you know that SQL Server lets you use them on columns as well? This post shows how it is done what could be a simple solution for some tricky questions.
Running DbUp Inside a Transaction
DbUp is a great tool to manage database migration scripts as I explained in the past. For more than two years we used it in multiple projects and never needed more than the setup example from the documentation. However, in the case of an error this snipped isn’t the best configuration and needs manual work to clean-up your database. Let’s look on the underlying problem and how one can fix it.
Paged Results in SQL Server
A common requirement for applications is to display a list – that can range from products to search results. While showing all entries works for small lists, it is not that useful for lists with hundreds of entries. In this case you should use paging and only show 10 or 20 entries at a time. The user sees the first part of entries and then can switch to the next page of the list to get more entries. This approach is faster, reduces the workload on your systems and the user isn’t drowning in data. Let’s look how you can implement paging in applications that use SQL Server.