June 2005 - Posts
Here is the link to the presentation on CRL in SQL, with guidance on when and when not to use it.
There is some interesting statements that corroborate my findings about CLR functions and TSQL functions. i.e. CLR functions are likely to be better than TSQL functions (http://www.sqljunkies.com/WebLog/simons/archive/2005/01/17/6468.aspx)
Thought I would start a challenge.
Who can come up with the best way of calculating prime numbers in SQL server. with all the progamability changes in 2005 there must be a really good way of doing it, or is there.
I've had a few stabs using a CTE and some TSQL put not that impressive, the CTE has limitations, the TSQL produced 1 million prime numbers in 7 minutes.
Can you do better
Heres the some code,
set nocount on
declare @prime table (prime int not null primary key)
--insert into @prime values (2)
--insert into @prime values (3)
--insert into @prime values (5)
--insert into @prime values (7)
--insert into @prime values (11)
declare @number int, @pc int
set @number = 13
set @pc = 1
while @pc < 1000000
if not exists (select 1 from @prime where @number % prime = 0 and prime < sqrt(@number) )
insert into @prime select @number
set @pc = @pc +1
set @number = @number
+ case when @number %2 = 1 then 2
when @number %3 = 2 then 2
when @number %5 = 4 then 2
when @number %7 = 6 then 2
when @number %11 = 10 then 2
else 1 end
as( select 13 number
+ case when s.number %2 = 1 then 2
when s.number %3 = 2 then 2
when s.number %5 = 4 then 2
when s.number %7 = 6 then 2
when s.number %11 = 10 then 2
else 1 end
from seq s
where number < 32767
, prime as (
from seq s
where not exists ( select 1 from seq s2 where s2.number < s.number and (s.number) % s2.number = 0)
option (MAXRECURSION 32767)
So we all know that colour coding code really helps readability, however we all at some point want to print some code out to review, stick on the wall etc, only to get black and white from QA.
Now you get full colour print outs. So get that mamouth SP print it out and produce some modern art
2. Cut and paste
This now maintains both the font of the text being cut as well as the colour
So your word documents can contain the same colour coded, formated SQL that you get in Management Studio
3. Brace highlighting
Ever had the mamouth query with conditional outer joins, sub queries, derived tables and got lost on whether you should but the 4th or 5th brace at a certain point. Well now the brackets will turn bold to show you the matched pair. (This does only seem to happen when you are on the right of the brace and you press delete)
if the cursor is located on the left of a brace pressing CTRL+] will move the cursor to the matching brace. CTLR + SHIFT + ] or CTRL + } will highlight everything enclosed in the braces.
As previously talked about all your queries can be displayed in different tabs in SMS. This enables easy navigation between the queries. There is also a button on the left end of the tabs that when clicked lisst all the queries as another way of selecting one
5. Full Screen
Ever wanted more space to right your queries. Whey not try full screen mode SHIFT + ALT + ENTER
This hides, all but the essentials for righting a query.
Most of these are due to the fact that SSMS is based on Visual Studio so most of the nice editor features of VS are present in SSMS.
There are some many little great things that productivity is going to eventually increase but initially probably descrease while you find them all and see if you like them. :)
Watching Live webcast on the use of CLR in SQL 2005.
When to use TSQL or SQLCLR.
Good so far
Have you encountered the problem of losing your colour coding in Query Analyser and other SQL 2000 tools, after you install a SQL 2005 CTP/Beta.
You need to re-register the SQL2000 color coder.
This is SQLLex.dll and is found in the Binn folder which is by default in
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
Its great I am back in glorious technicolor
I love the tabbed format of this however I have one gripe
The information available on the tabs in Management Studio is unreadable. Currently both the filename and the server and database name are truncated one on the right and one on the left. These means you can't see the full name of the file and also you can't see the server.
This is a real nuisance especially if you have many files that start the same i.e. up_table_insert, up_table_update etc.
and secondly if you have the same file open on many servers. To find the server you have to look at the bottom of the screen.
It would be great if either the tab had 2 lines or the details where displayed in the application caption bar is the case when using MDI mode.
Heres asking :)
I have mentioned Monad before at sqlserverfaq.com and how powerful it looks, You can see a demo video of it at Channel 9
http://Channel9.msdn.com and the video is mms://wm.microsoft.com/ms/msnse/0410/23360/JeffreySnover/jeffrey_snover_2004_demonstration_of_monad.wmv
Now Lee has mentioned that you can join a Monad preview by following the instructions on his Blog
This is the future of scripting, RIP vbscript and batch files
Just registered for the PDC 2005.
Probably will be travelling over on the Saturday to enjoy the pre-conference seminars. I am sure my head will start overloading by the middle of the week, SQL 2005, SSIS, SQLCLR, XQuery, WinFS, Report builder, Longhorn, Avalon and more.
So catching up on the world after a little break (honeymoon).
Found Adam fancies his had as a poker ace but using SQL to run the game http://sqljunkies.com/WebLog/amachanic/archive/2005/05/11/14276.aspx.
On reading my latest SQL Mag I wondered whether Itzik's article on transitive closure and shortest paths might be a way of solving the problem http://www.windowsitpro.com/Article/ArticleID/46117/46117.html.
I'll leave it for you to figure out, will let you know how I get on.