Maintainable T-SQL and patterns that avoid correctness and performance problems.
106 associated posts102 primary posts
Aggregations and grouping can be a pain in the rear for the novice SQL developer. Way back in 2003, the ANSI/ISO standards people figured this out and added windows and ranking functions to the standard. In 2005, Microsoft added a few of these functions (ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()) as well as the OVER() clause…
People love to search. Google has us addicted to fast, easy search functions. Users expect every application to have a built-in blazing-fast search functionality. To pull that off, developers build search queries that let users enter a string, and we ask SQL Server to find matches. For example, say our users need to find some…
The ever-insightful Buck Woody (Blog - @BuckWoody) wrote a post telling you to use database schemas. He writes: "But I still see a lot of applications, developed by shops I know as well as vendors, that don’t make use of a Schema. Everything is piled under dbo.... But if you’ll use them properly you can…
R2 makes it easier for DBAs to move databases around from server to server in much the same way virtualization admins move guest OS's around between physical hosts. In my last blog post about SQL Server 2008 R2, I explained that databases are becoming more like virtual servers, and today I'll talk about what that…
Sounds simple, right? Just grab the max. But what if you want to use a single T-SQL operation to fetch the most recent record and some of its attributes? StorminSpank asked: @BrentO SQL Question. 4 columns, ID, Date, Info1, Info2. ID has multiple entries. Want Latest Date for Distinct ID, but also info1 and 2.…
When joining multiple tables together in a query, use aliases every time on every field. Say we've got two tables, Customers and Salespersons. The Customers table has a PreferredSalespersonID field that identifies who their normal sales rep is, and that lets us quickly grab the right salesperson when a customer calls in. We want to…
