This month's discount Comprehensive Database Performance Health Check · Testimonials
SQLAuthority with Pinal Dave
Home
  • Consulting
    • Health Check
  • Free Videos
  • All Articles
    • AI
    • SQL Performance
    • SQL Tips and Tricks
    • Interview Questions and Answers
    • SQL Puzzle
    • SQL Video
    • SQLAuthority News
    • Personal
  • Books
  • Consulting
A harmonica exposes separate chambers from one end, with a small vermilion accent on its casing

PARSENAME for Dotted Values: Build Numbers and Object Names

July 29, 2012
Pinal Dave
SQL Tips and Tricks
SQL Function, SQL Instance, SQL Order By, SQL String

Why does revision 12 sort before 7? Because the parts are text. Here is how PARSENAME fixes it, and where it trips you up.

Read More
A pastry crimper seals a pie edge through its fixed toothed shape beside unused decorative cutters

Dynamic ORDER BY Without Injection: Whitelisting Sort Columns

May 9, 2012
Pinal Dave
SQL Tips and Tricks
Dynamic SQL, SQL Order By, SQL Server Security, SQL Variable

Parameters cannot name a column, so grids end up gluing text into SQL. Here is the approved-list pattern that keeps the sort safe.

Read More
Marbles tipped from a neat row into a bowl, landing mixed

Why a Clustered Index Does Not Guarantee Order Without ORDER BY

March 19, 2012
Pinal Dave
SQL Tips and Tricks
Clustered Index, Execution Plan, SQL Order By, SQL Server

Explain order without ORDER BY through parallel scans, NOLOCK allocation scans, alternate indexes, shared scans, and the explicit sort fix.

Read More
An egg slicer lowers parallel wires through one egg beside individually cut egg slices

Replacing a Running Total Cursor With a Window Function

March 16, 2012
Pinal Dave
SQL Tips and Tricks
SQL Cursor, SQL Function, SQL Order By, Temp Table

That loop adding to a variable can usually become one window function. Here is the proof that the balances match, and the tie trap to watch for.

Read More
A small wooden podium on a plain floor with red ribbons laid across the top step.

Ranking Functions Quiz: ROW_NUMBER, RANK or DENSE_RANK?

January 9, 2012
Pinal Dave
SQL Puzzle
Ranking Functions, SQL Cursor, SQL Function, SQL Order By

This Ranking Functions Quiz uses two tied scores to ask what number the next student gets from ROW_NUMBER, RANK and DENSE_RANK. The answer is 3, 3 and 2. A tested script proves it and replaces a cursor loop with one query.

Read More
A kitchen sieve with a red rim over a bowl of green lentils, a blank paper tag lying loose beside it.

WHERE Clause Quiz: Why Can’t It See My Column Alias?

January 5, 2012
Pinal Dave
SQL Puzzle
CTE, SQL Column, SQL Error Messages, SQL Order By

This WHERE Clause Quiz asks what happens when a query filters on a column alias. The answer is error 207, because WHERE runs before SELECT. A tested script shows three working fixes and a trap where an alias hides a real column.

Read More
Three ranking functions on the same eight scores: RANK gives 1, 2, 2, 4, 5, 5, 5, 8, DENSE_RANK gives 1, 2, 2, 3, 4, 4, 4, 5, and ROW_NUMBER gives 1 to 8, with the tied rows marked in red

RANK( ), DENSE_RANK( ), and ROW_NUMBER( ): How Each Handles Ties

August 12, 2011
Pinal Dave
SQL Tips and Tricks
Ranking Functions, SQL Order By, SQL Scripts, SQL Server

RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) return the same numbers until your data has a tie. This post ranks eight teas with all three functions and shows how each one handles tied scores. It also shows how to list the top three distinct scores and why ROW_NUMBER needs a tiebreaker.

Read More
Previous 1 2 3 4 5 6 7 Next
  • Books
  • Testimonials
  • Privacy Policy

© 2006 – 2026 All rights reserved. pinal @ SQLAuthority.com