T-SQL Programming

SQL Server Metadata Functions: The Basics

To be able to make full use of the system catalog to find out more about a database, you need to be familiar with the metadata functions. They save a great deal of time and typing when querying the metadata. Once you get the hang of these functions, the system catalog suddenly seems simple to use, as Robert Sheldon demonstrates in this article.… Read more

When AUTO_UPDATE_STATISTICS Doesn’t Happen

When your SQL Server database is set to have its statistics automatically updated, you will probably conclude that, whenever the distribution statistics are out-of-date, they will be updated before the next query is executed against that index or table. Curiously, this isn't always the case. What actually happens is that the statistics only gets updated if needed by the query optimiser to determine an effective query plan.… Read more

SQL GROUP BY Basics

It sometimes pays to go back and look at what you think you already know about SQL. Joe Celko gives a quick revision of the GROUP BY and HAVING clauses in SQL that are the bedrock of any sort of analysis of data, and comes up with some nuggets that may not be entirely obvious… Read more

Declarative SQL: Using References

There are several ingenious ways of using SQL References to enforce integrity declaratively. Declarative Referential Integrity (DRI) is more effective than using procedural code in triggers, procedures or application layers because it uses the SQL paradigm, thereby making optimisation easier and providing clearer expression of the rules underlying the data.… Read more

Declarative SQL: Using UNIQUE Constraints

In SQL, you can express the logic of what you want to accomplish without spelling out the details of how the database should do it. Nowhere is this more powerful than in constraints. SQL is declarative, and Joe Celko demonstrates, in his introduction to Declarative SQL, how you can write portable code that performs well and executes some complex logic, merely by creating unique constraints.… Read more

Top 10 Most Common Database Scripts

What are the scripts that a working DBA uses so often that they become part of the 'muscle memory'? Grant Fritchey asked the question on the forums of SQL Server Central. From the large response, Grant was able to pick out the ten most popular T-SQL commands, scripts, or fragments. It seems as if, despite the range of tasks we do, there are some common tools we use.… Read more

Dependencies and References in SQL Server

It is important for developers and DBAs to be able to determine the interdependencies of any database object. Perhaps you need to work out what process is accessing that view you want to alter, or maybe find out whether that table-type you wish to change is being used. What are all these dependencies? How do you work out which are relevant? Phil Factor explains.… Read more

How Forwarded Records are Read and Processed in a SQL Server Heap

Before you deliberately use a heap in SQL Server rather than a table, it is worth understanding why a heap has such different characteristics, and therefore relative benefits and disadvantages. Forward pointers and the Page Free Space page are designed with performance in mind, but only in certain circumstances. Uwe explains why, and how heaps can be used to advantage.… Read more

How to Confuse the SQL Server Query Optimizer

Gail Shaw examines three common forms of generic SQL that can and will confuse the SQL Server Optimizer to the point that it generates and reuses very inefficient execution plans. Want to 'trick' SQL Server into performing millions of logical reads to return the data, when only are few thousand were really required? Try writing catch-all queries, or using control flow statements to create general-purpose procedures, or switching parameter values within a procedure. Just don't expect the resulting queries and procedures to perform well, or consistently. The ideal solution to the problem of generic T-SQL is not to write it, but failing that Gail demonstrates techniques such as recompiling the query on each execution, using hints, dynamic SQL, or splitting generic procedures into sub-procedures.… Read more

Reusing T-SQL Code

Database programmers are often caught in the paradox of wanting to implement business rules and complex functionality in one place only, but being concerned by the performance hit of having generic functions or procedures that have a wide range of parameters and outputs. Alex Kuznetsov, in an article taken from his book 'Defensive Database Programming with SQL Server', shows how DRY principles can be put in practice with constraints, stored procedures, triggers, UDFs and indexes.… Read more

How to Get SQL Server Dates and Times Horribly Wrong

One of the times that you need things to go right is when you are doing analysis and reporting. This is generally based on time and date. A sure-fire way of getting managers upset is to get the figures horribly wrong by messing up the way that you handle datetime values in SQL Server. In the interests of peace, harmony and a long career in BI, Robert Sheldon outlines some of the worst mistakes you can make when using SQL Server dates.… Read more

Using the T-SQL PERCENTILE Analytic Functions in SQL Server 2000, 2005 and 2008

Percentiles give meaning to measurements by telling you the percentage of the population being measured who get higher or lower values. They are now easier to calculate in SQL, and are useful for reporting; but are the new analytic functions faster and more efficient than the older methods? Dwain Camps demonstrates, and investigates their relative performance performance.… Read more

Optimizing Batch Process in SQL Server

SQL Server batch processes are usually run from SQL Agent in background. They can take significant time and resources, especially if they are ETL tasks. Quite often, the responsibility for creating these tasks belongs entirely to the developer. Dennes demonstrates that DBAs can advise and assist with this type of batch job by bringing their expertise to bear on the problem of reducing their impact on the working system to a minimum.… Read more

Documenting your SQL Server Database

One of the shocks that a developer can get when starting to program in T-SQL is that there is no simple way of generating documentation for routines, structures and interfaces, in the way that Javadocs or Doxygen provides. To embed the documentation in the source is so obvious and easy that it is a wrench to be without this facility. Phil Factor suggests a solution. … Read more

Time Slots – An Essential Extension to Calendar Tables

After answering many forum entries from developers asking for help with dealing with SQL that involved time intervals and ranges, Dwain dreamed of a generalized tool that sets up time slots of various sorts without the need to experiment; that could do the heavy lifting, so that developers could do aggregations and reports based on time intervals without the hard graft. Here is Dwain's dream made reality. … Read more