SQL
123 views | +0 today
Follow
Your new post is loading...
Your new post is loading...
Scooped by Alan Burstein
Scoop.it!

How to Make Scalar UDFs Run Faster (SQL Spackle) - SQLServerCentral

How to Make Scalar UDFs Run Faster (SQL Spackle) - SQLServerCentral | SQL | Scoop.it
"A Different Type of Scalar UDFI actually owe this wonderful bit of "Spackle" of knowledge to Paul White.  As my boss says, it's a "Spec-hack-ular" idea.

I've forgotten how long ago it was but I was solving a problem on one of the threads on the forums here at SQLServerCentral.com. Because I was necessarily returning a Scalar Value, I used a Scalar UDF in the classic sense. Paul White was one of the people who jumped in on the "Scalar UDFs are Slow" bandwagon but his post was different than all of the others. He suggested using an "iTVF" (inline Table Valued Function) to return the single value instead of using a classic Scalar UDF."

Alan Burstein's insight:

This article has helped me a ton!

more...
No comment yet.
Scooped by Alan Burstein
Scoop.it!

Creative Solutions by Using a Number Table

Creative Solutions by Using a Number Table | SQL | Scoop.it

Every database should have a number table. A number table is just that; a table that contains a single column whose value is a unique number. The table contains a series of rows where the numbers start at 0, or 1, and increments by 1 to some define limit such as 1,000 or 10,000 depending how you plan to use the table. The range of numbers that you will need in your number table depends on what problem you are trying to solve.

more...
No comment yet.
Scooped by Alan Burstein
Scoop.it!

SQL Server Central: Stairway to Data – a SQL Server tutorial

SQL Server Central: Stairway to Data – a SQL Server tutorial | SQL | Scoop.it

IT projects can hit problems that turn out to be due to an insufficient understanding of the basic data and data-types, rather than the database design. It is a sorely neglected topic that might seem to be trivial, but certainly isn't. The DBA, with a broad perspective on corporate data can do a great deal to help application developers to avoid the common mistakes that so often happen, and Joe Celko's Stairway gives the busy IT professional a crash course to understanding the nature of the data being processed.

more...
No comment yet.
Scooped by Alan Burstein
Scoop.it!

SQL Server Interval Queries | Database Development content from SQL Server Pro

SQL Server Interval Queries | Database Development content from SQL Server Pro | SQL | Scoop.it
Interval-related queries tend to perform very inefficiently in SQL Server. Itzik presents an ingenious solution based on the Relational Interval Tree (RI-tree) model.
more...
No comment yet.
Scooped by Alan Burstein
Scoop.it!

Generating Non-uniform Random Numbers with SQL - SQLServerCentral

Generating Non-uniform Random Numbers with SQL - SQLServerCentral | SQL | Scoop.it
Free Microsoft SQL Server articles, news, forums, scripts and FAQs.
more...
No comment yet.
Scooped by Alan Burstein
Scoop.it!

The "Numbers" or "Tally" Table: What it is and how it replaces a loop. - SQLServerCentral

The "Numbers" or "Tally" Table: What it is and how it replaces a loop. - SQLServerCentral | SQL | Scoop.it

SQL Server Central: Free Microsoft SQL Server articles, news, forums, scripts and FAQs.

Alan Burstein's insight:

This was the paragraph that caused the light to go on for me.


"Let me repeat, we do the iterations using existing rows. This can be defined as "For each row from 1 to 10, display the value of the row". The BIG difference is that we only use a single SELECT and we don't actually have to count as we go. We just limit how many rows we use and what the values of the rows are. It produces the same result as the loop does (count of 1 to 10), but it does it using a single SELECT. In other words, it produces a single result set with the entire answer. Nothing RBAR about that... that's set based programming."

more...
No comment yet.
Scooped by Alan Burstein
Scoop.it!

Do You Know sys.dm_exec_text_query_plan? - SQLServerCentral

Do You Know sys.dm_exec_text_query_plan? - SQLServerCentral | SQL | Scoop.it

"One of the most fundamental DMF’s a SQL Server DBA should know is sys.dm_exec_query_plan.

This Dynamic Management Function allows us to see the Execution Plan for queries that are either currently running or have been cached."

more...
No comment yet.
Scooped by Alan Burstein
Scoop.it!

Top 10 steps to optimize data access in SQL Server: Part I (use indexing) - CodeProject

Top 10 steps to optimize data access in SQL Server: Part I (use indexing) - CodeProject | SQL | Scoop.it
As part of a series of articles on several data access optimization steps in SQL Server, this article focuses on using indexing to optimize data access.; Author: Al-Farooque Shubho; Updated: 28 Apr 2009; Section: Database; Chapter: Database; Updated:...
more...
No comment yet.
Rescooped by Alan Burstein from XQuery & T-Sql
Scoop.it!

CTEs with Multiple Recursive Members, Part 2 - SQL Server Pro

CTEs with Multiple Recursive Members, Part 2 - SQL Server Pro | SQL | Scoop.it
SQL Server Pro
CTEs with Multiple Recursive Members, Part 2
SQL Server Pro
When people write recursive common table expressions (CTEs) in T-SQL, they mostly use a single recursive member (the inner query with the reference to the CTE name).

Via Tony Pierascenzi
more...
No comment yet.
Scooped by Alan Burstein
Scoop.it!

Exploring Recursive CTEs by Example - SQLServerCentral

Exploring Recursive CTEs by Example - SQLServerCentral | SQL | Scoop.it
Free Microsoft SQL Server articles, news, forums, scripts and FAQs.
more...
No comment yet.
Scooped by Alan Burstein
Scoop.it!

T-SQL Puzzle | T-SQL content from SQL Server Pro

T-SQL Puzzle | T-SQL content from SQL Server Pro | SQL | Scoop.it
T-SQL puzzle to solve based on the British game show Blockbusters. Read one solution and write your own T-SQL solution to sharpen your T-SQL skills.
Alan Burstein's insight:

I think I can use this to solve the Longest Common Subsequences problem...

more...
No comment yet.