Articles tagged T-SQL Programming

01 August 2020
01 August 2020

Temporary Tables in SQL Server

Temporary tables are used by every DB developer, but they're not likely to be too adventurous with their use, or exploit all their advantages. They can improve your code's performance and maintainability, but can be the source of grief to both developer and DBA if things go wrong and a process grinds away inexorably slowly. We asked Phil for advice, thinking that it would be a simple explanation.… Read more
08 December 2018
08 December 2018

Graph Edge Constraints and a Crystal Ball

0
2
When I read the list of new features in SQL Server 2019 I became very proud of my crystal ball powers. In July 2017 I published an article about Graph Database feature in SQL Server 2017. In this article, besides showing the improvements and benefits I also highlighted one problem: the lack of graph edge … Read more
08 August 2018
08 August 2018

Spoofing Data Convincingly: Credit Cards

0
13
I haven’t seen a SQL Server table with real unencrypted credit card numbers for several years, and I don’t know of any good reasons to have them stored that way. However, I’ve needed them in the past for testing a web application that had to take credit card details. Generating credit cards in a way … Read more
30 April 2018
30 April 2018

Operator Precedence versus the Confusing Constraint Translation

0
2
The other day, a person I work with was creating some fairly complex CHECK constraints to protect data in a new table that was being created (a practice I am 100% for: https://www.red-gate.com/simple-talk/blogs/eliminating-annoying-space-characters-via-check-constraints/. The multiple constraints were combined into just one, with several conditions condensed into a single Boolean expression, using several ANDs, ORs, and … Read more
07 March 2016
07 March 2016

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
08 February 2016
08 February 2016

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
15 December 2015
15 December 2015

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
04 December 2015
04 December 2015

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
04 November 2015
04 November 2015

Using SQL Prompt to Accelerate the Top 10 Most Common Database Scripts

There may be some people who enjoy repetitive typing, but Grant Fritchey doesn't. He's always preferred SQL Prompt. The standard snippets suit developers fine but aren't so DBA-oriented, so he set about asking the SQLServerCentral community what they typed in the most, and set about producing a set of DBA snippets with the results. … Read more
15 October 2015
15 October 2015

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
24 September 2015
24 September 2015

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
09 September 2015
09 September 2015

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
09 September 2015
09 September 2015

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