October 2003 Blog Posts

  • SQL and the search for Prime and Perfect numbers

    I got an usual request today in reference to writing SQL statements to find Prime & Perfect Numbers. After a bit of clarification, (Primes & Perfect numbers less than 1 million) I convinced them not to write an alogrithm in TSQL and that ideally you would have a table of Prime & Perfect numbers and just JOIN to those tables when needed. "How do we fill the Prime table?", was the obvious next question. This is surprisingly simple thanks to the Internet... Prime Site This sight has a list of Prime numbers... A simply parse and a BCP and we have a 100,000...

  • Random Number Generation and Functions…

    Calling non-deterministic functions is not allowed inside a UDF, which seems to preclude any sort of “random” number generation. Fortunately a UDF can be “tricked” into thinking it is deterministic by using views. Below is a simple RandomNumber generator based on the GUID data type. It simply reverses the GUID and takes the RIGHT @MaxDigits characters and ensures they are actual numbers. --this view simply returns a new GUID CREATE VIEW vNEWID AS SELECT NEWID() AS NEWID GO --A Function that uses part of the GUID for a random number CREATE FUNCTION RandomNumber ( @MaxDigits INT = 4 ) RETURNS INT AS BEGIN DECLARE @TextID VARCHAR(36) SELECT @TextID = RIGHT(CAST(NEWID AS VARCHAR(36)),@MaxDigits) FROM vNewID WHILE PATINDEX('%[^0-9]%', @TextID) > 0...

  • Copying DataTables

    The project I am on sources data from both Mainframe (DB2)and SQL Server. The data is delivered to the front end via web service methods as an XML stream. In most situations all the data "joining" is already done in the middle tier, but for a particular use case, the front end must manage 2 different XML streams (one from DB2 and the other from SQL) First thought was to use the XML classes in .NET but another easier option was to use DataSets. Each stream is loaded into a data set via the LoadXML method. The data schemas are too...

  • The Game of Life

    At the start of this year I took 2 months off to enjoy my family and farm. During a particularly rainy period (precious precious rain) I decided to sharpen up my SQL skills by building games into SQL. First of the bat was Poker, then BlackJack. 2 fairly easy games to implement in SQL, with the only trick being randomisation of the cards. Next on the list was the classic Game Of Life. Although not really a game, it does make for a great exercise. If you don't know what it is have a search on google... So here we go... A Users...

  • Smart Cards, Big Brother and why I don't care.

    The shop I am working at has just rolled out Smart Cards. Each PC has a Smart Card reader connected via a USB port. These Cards allow building and network access. Turn on the PC and wait for the login box. Jam the Card into the reader, type your PIN and away you go. No more password changes! Pull the Card out and the work station is locked. Jam it back in and it unlocks and you can start coding away.... There has been plenty of talk about how "they" will be tracking "time on" but quite frankly I don't...

  • First post

    Hello all and big thankyou to Graz et al for this handy site. Although probably a bit off topic for a SQL log, but I'll be focusing on .NET as I am in the middle (read Deep End) of a large implementation of it. The real goal of the project is to kill off the mainframes. Slowly but surely more and more SQL Server boxes are being implemented to ease the burden and reliance of our mainframes. I didn't make this decision but I will be following orders. We currently stand at 16 physically servers separated into 4 clusters (4 boxes...