Today I share a tip for ne SQL developers that will help with performance. As much as is reasonable, if you build your queries, procedures views and functions to access tables in a consistent order, it can have a big impact on reducing contention when multiple queries are execute. If query A needs a table held by Query B, but […]

Here’s an old tip for SQL newbies. Avoid using “SELECT *“. There are lots of performance problems, and potential inaccuracies that may occur by using this syntax. I don’t mean you should NEVER use SELECT *. When you are doing discovery, don’t know what the table definition is, or forget a column name, don’t hesitate to use this syntax. The […]

There was a lot of valuable feedback from yesterday’s editorial, “Generate Audit Triggers Using SQL Script“, demonstrating a script that creates audit trigger code. Interesting that most of the comments were really about the auditing technique rather than the code generation. So, let me add a few thoughts: The script example provided is not the most efficient code. It doesn’t […]

Here is an example of what I was trying to describe as generating code from the metadata stored in SQL Server. I once had a customer needing to audit all changes in their database. We developed a two phase implementation. First we created a generic audit queue table defined below. We created an audit trigger on any table to be […]

Here is an example of what I was trying to describe as generating code from the metadata stored in SQL Server. I once had a customer needing to audit all changes in their database. We developed a two phase implementation. First we created a generic audit queue table defined below. We created an audit trigger on any table to be […]

One of the coolest things I have found for automating code generation is the use of the INFORMATION_SCHEMA views found in many data engines. They can be extremely useful when you have to write repetitive code based on your database schema. For instance, using Entity Framework, you can generate your tables using database first or code first techniques. Frankly, it […]

One of the coolest things I have found for automating code generation is the use of the INFORMATION_SCHEMA views found in many data engines. They can be extremely useful when you have to write repetitive code based on your database schema. For instance, using Entity Framework, you can generate your tables using database first or code first techniques. Frankly, it […]

Every once in a while I like to review the different system tables for those new to SQL Server. With the rise of code first in Entity Framework, more developers are getting involved with SQL Server databases without some of the basic understanding of tools available to them. First you have the Master database. The master database is the catalog […]

Every once in a while I like to review the different system tables for those new to SQL Server. With the rise of code first in Entity Framework, more developers are getting involved with SQL Server databases without some of the basic understanding of tools available to them. First you have the Master database. The master database is the catalog […]

SQL Injection has a number of different techniques, some of which are available based on the way you have configured your SQL Server engine, database security, and database access by your application. Let’s start with the SQL Service. The best way to thwart SQL Injection is to limit the permissions of the SQL Server service. This is done by creating […]