Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Jonathan Roberts,
This is an improvement to my last script: http://www.sqlservercentral.com/scripts/Tally+Table/155213/
Instead of deleteting rows from a table variable it uses a NOT EXISTS to filter out rows that are not prime, it also has a better filter to eliminate some obvious non-primes when populating the table variable @N.
Initially I tried writing the process as a script using temporary tables instead of table variables; when the script is written like this it runs approximately twice as fast (a demonstration of why temporary tables are better than table variables). But a table vaued function cannot have temporary tables in its code.
Of course, the fastest way to get lists of prime numbers, in SQL Server, is to create a permanant table of primes on your database which can be achieved this using this table valued funtion:
Improve the SQL query performance with temporary variables
While it's not likely that many of you need to find prime numbers using T-SQL, it is an interesting ...
Peter He examines the differences between temporary tables and table variables in SQL Server, showin...
Table variables and Temporary tables are used interchangeably – but they were designed for differen...
This article demonstrates how to manage the development of complex queries by building them in stage...