|
|
August 2009 Blog Posts
Yesterday I updated my site. It was not that appealing.
This is how it looked before
http://209.85.129.132/search?q=cache:CMr9J28OCwkJ:www.developerworkshop.net/+developerworkshop.net&cd=1&hl=en&ct=clnk&gl=uk
and this is how it loooks now
http://www.developerworkshop.net
I think it's a great improvement, even if there still are some things to fix.
//Peso
The answer to your question is "Yes, I am having a slow day today."
;WITH Fibonacci(n, f, f1)
AS (
SELECT CAST(1 AS BIGINT),
CAST(0 AS BIGINT),
CAST(1 AS BIGINT)
UNION ALL
SELECT n + 1,
f + f1,
f
FROM Fibonacci
WHERE n < 93
)
SELECT n,
f AS Number
FROM Fibonacci
Phew! Now it's finally done.
I haven't coded outside SQL Server since 2007 and that was with VB version 6.0.
Well, I felt the need to start code again (at least for SQLCLR), since there are many tasks that will be easier to do with a SQLCLR routine. To start easy, I coded a "least square regression" routine and you can download it from this location
Regression.dll http://regression.developerworkshop.net/dws.Regression.dll
Install.sql http://regression.developerworkshop.net/Install.sql
Sample.sql http://regression.developerworkshop.net/Sample.sql (good linear regression)
Sample2.sql http://regression.developerworkshop.net/Sample2.sql (better linear regression)
Sample3.sql http://regression.developerworkshop.net/Sample3.sql (polynomial regression)
Update:
* I have created a homepage for this SQLCLR function http://regression.developerworkshop.net
For the Sample2 above, the result for c: drive on Server1 look like this
<dws bestfit="linear" r2="0.99" type="least square...
Some days ago I posted a solution for a simple problem on a forum about to delete multiple spaces in one statement (not using loop). My suggestion was
declare @s varchar(100)
set @s = 'xxxx yyyyy zzzzzz'
SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '')
I used CHAR(2) because that is not commonly used in normal texts. I then thought I could use CHAR(0) to be on the "safe" side, and now strange things begun to happen.
Run this on your own risk, as you will see soon.
Select q,
len(q)
from (
SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(0)), CHAR(0) +...
declare @t table (Id int, dt datetime, value int)
set dateformat 'dmy'
insert into @t
select 1, '10/12/2008', 10 union all
select 1, '11/12/2008', 10 union all
select 1, '12/12/2008', 10 union all
select 1, '13/12/2008', 9 union all
select 1, '14/12/2008', 10 union all
select 1, '15/12/2008', 10 union all
select 1, '16/12/2008', 10 union all
select 1, '17/12/2008', 10 union all
select 2, '05/03/2008', 8 union all
select 2, '06/03/2008', 6 union all
select 2, '07/03/2008', 8 union all
select 2, '08/03/2008', 8 union all
select 2, '09/03/2008', 8 union all
select 2, '20/03/2008', 8
SELECT Id,
MIN(dt) AS Startdt,
MAX(dt) AS Enddt,
MIN(value) AS Value
FROM (
SELECT Id,
dt,
value,
ROW_NUMBER() OVER (PARTITION BY Id,...
Yesterday I hade the unfortenate task to change the database collation for a particular database.
Not only the default database collation should be changed, but also all columns!
After some investigating about how to do this, I noticed that check constraints and foreign key constraints must be removed before changing a collation. Also indexes needed to be removed for this operation to succeed.
I did it manually since it was my first time, but I wrote down the steps and today I gathered the notes into a beta-script, just in case I need to do this again in the future
Why a script? Because most...
Today I was involved in an interesting topic about how to check if a text string really is integer or not.
This is what I finally suggested.
CREATE FUNCTION dbo.fnIsINT
(
@Data NVARCHAR(11)
)
RETURNS INT
AS
BEGIN
RETURN CASE
WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL
WHEN SUBSTRING(@Data, 1, 1) NOT LIKE '[-+0-9]' COLLATE LATIN1_GENERAL_BIN THEN NULL
WHEN @Data IN('-', '+') THEN NULL
WHEN CAST(@Data AS BIGINT) NOT BETWEEN -2147483648 AND 2147483647 THEN NULL
ELSE CAST(@Data AS INT)
END
END
And the BIGINT alternative
CREATE FUNCTION dbo.fnIsBIGINT
(
@Data NVARCHAR(20)
)
RETURNS BIGINT
AS
BEGIN
RETURN CASE
WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL
WHEN SUBSTRING(@Data, 1, 1) NOT LIKE...
It has come to my attention that sometimes there are more than 100,000 records for which a "running streak" should be calculated on, so my previous blog post http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx will not do.
But this will work, and work fast!
DECLARE @Sample TABLE
(
Col1 INT,
Col2 INT,
Col3 INT,
Col4 INT,
Col5 INT,
Col6 INT,
Col7 INT,
Col8 DATETIME
)
INSERT @Sample
SELECT 43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:00' UNION ALL
SELECT 43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:05' UNION ALL
SELECT 43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:10' UNION ALL
SELECT 43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:15' UNION ALL
SELECT 43, 12345, 99887, 0, 0, 0,...
I installed SQL Server Enterprise 2008 R2 on my laptop as a new instance. I also have SQL Server Developer 2008 as default instance.
For the default instance, I have a database named Test on a separate partition of my laptop harddrive.
After installing R2, I tried to attach the Test database, and failed, because I didn't detach the database from the default instance.
So now I detached the Test database from the default instance but didn't make it because it was marked as suspect, even if the icon displayed good. So I took the Test database Offline and then detached it (there...
It has been some years since this article was posted http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data and things has evolved since then. So I thought about using XML to solve the case. If the number of records are large, maybe this approach http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Calculating-Running-Streak-over-many-records.aspx will be better for you?
Below, I am using the same sample data as the original.
DECLARE @Sample TABLE
(
GameDate DATETIME,
Result CHAR(1)
)
INSERT @Sample
SELECT '1/1/2000', 'W' UNION ALL
SELECT '1/12/2000', 'L' UNION ALL
SELECT '1/15/2000', 'W' UNION ALL
SELECT '1/17/2000', 'W' UNION ALL
SELECT '1/22/2000', 'W' UNION ALL
SELECT '2/1/2000', 'L' UNION ALL
SELECT '2/5/2000', 'W' UNION ALL
SELECT '2/8/2000', 'L' UNION ALL
SELECT '2/16/2000', 'W' UNION ALL
SELECT '2/19/2000', 'L' UNION ALL
SELECT '2/25/2000', 'L' UNION ALL
SELECT '2/28/2000', 'L' UNION ALL
SELECT '3/15/2000', 'L' UNION ALL
SELECT '3/19/2000', 'W' UNION ALL
SELECT '3/25/2000', 'W'
For this to...
This is a simple query for creating a script for your foreign keys in your database. It may need tweaking for composite keys.
If that's the case, see here how to concatenate http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
;WITH Yak
AS (
SELECT ROW_NUMBER() OVER (ORDER BY o.[schema_id]) AS RowID,
QUOTENAME(o.name) AS CONSTRAINT_NAME,
QUOTENAME(SCHEMA_NAME(po.[schema_id])) AS FOREIGN_TABLE_SCHEMA,
QUOTENAME(po.name) AS FOREIGN_TABLE_NAME,
QUOTENAME(rccu.COLUMN_NAME) AS FOREIGN_COLUMN_NAME,
QUOTENAME(SCHEMA_NAME(ro.[schema_id])) AS PRIMARY_TABLE_SCHEMA,
QUOTENAME(ro.name) AS PRIMARY_TABLE_NAME,
QUOTENAME(rc.name) AS PRIMARY_COLUMN_NAME,
CASE fk.is_disabled
WHEN 0 THEN 'CHECK'
ELSE 'NOCHECK'
END AS [ENABLED]
FROM sys.foreign_keys AS fk
INNER JOIN sys.objects AS o ON o.[object_id] = fk.[object_id]
INNER JOIN sys.objects AS po ON po.[object_id] = fk.parent_object_id
INNER JOIN sys.objects AS ro ON ro.[object_id]...
In my previous blog post about how to manipulate data in XML columns, http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx, I didn't have time to include how to delete elements.
Now I have and here you can see how to delete elements from a XML column. I am using the same sample data for easier understanding.
CREATE TABLE #Sample
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MemberID INT,
MemberData XML
)
INSERT #Sample
SELECT 1, '<meta><customergroup>F</customergroup><mosaic>Young educated man</mosaic></meta>' UNION ALL
SELECT 2, '<meta><age>24</age></meta>'
CREATE PRIMARY XML INDEX IX_PrimaryXML ON #Sample(MemberData)
CREATE XML INDEX IX_Element ON #Sample(MemberData)
USING XML INDEX IX_PrimaryXML FOR PATH
SELECT MemberID,
MemberData,
MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM #Sample
--...
This algorithm requires an existing Prime numbers table. You can easily create one of your own or importing the primes ranging from 2 to 3,037,000,493 from the Internet. If you only is interested in primes with INT range {2..2,147,483,647} you only need the first 4,792 primes {2..46,337}
DECLARE @Number BIGINT
SET @Number = 2020208534430421
SELECT Prime AS Number,
CAST(1 AS TINYINT) AS Items
INTO #Temp
FROM Primes
WHERE Prime <= SQRT(@Number)
AND @Number % Prime = 0
SELECT @Number = @Number / Number
FROM #Temp
WHILE @@ROWCOUNT > 0
UPDATE #Temp
SET Items = Items + 1,
@Number = @Number / Number
WHERE @Number % Number = 0
SELECT Number,
Items
FROM #Temp
UNION ALL
SELECT @Number,
1
WHERE @Number >...
|