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. However, sometimes we need to exclude NULL from the uniqueness requirement. For a long …
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 …
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 …
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:
|
1 |
TRUNCATE TABLE Book |
When this command completes the …
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 …
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 …
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. …
A common task for developers is to store application specific configuration. There are many ways you can take to get that functionality. If you use .Net / C# you can use the simple, built-in way to work with configuration files. Let’s look how this works. Create an app.config or web.config Right click on the …
SQL Server has a great feature called FILESTREAM. It allows applications to store unstructured data (like images or PDF files) on the file system while keeping the transactional capabilities of the database. It’s a straight forward task to activate this feature while you install SQL Server. But it gets tricky when you have a pre-installed …
Backup and restore are simple tasks in SQL Server. The only problem is the user mapping that may no longer work when you restore the database to a different server. If you try to remap the user, you may end up with an error like this one: To fix the user and login mapping you …