SQL Partitioning script, Function and Scheme

/* 
DROP partition SCHEME PS_MIH
DROP partition FUNCTION PF_MIH
*/
--// Script Table Partition by Week/Month
SET NOCOUNT ON
DECLARE
@TableName VARCHAR(128)
, @PartitionBy VARCHAR(128)
, @BoundaryType VARCHAR(15)
, @TimeInterval VARCHAR(15)
, @StartingTime DATETIME
, @NumberOfPartitions INT

DECLARE
@PartionCounter INT
, @tmpBoundary CHAR(19)
, @SQLCMD VARCHAR(MAX)
, @CREATE_SCHEMA VARCHAR(MAX)
, @GOBANANA BIT

SELECT
@TableName = 'MIH'
, @PartitionBy = 'MIHDateCreated'
, @BoundaryType = 'RIGHT'
, @TimeInterval = 'MONTH'
, @StartingTime = '05/01/2010'
, @NumberOfPartitions = 260 --520 --// 5 years
--// BE SURE TO DO DRY RUNS. One known limitation is VARCHAR(MAX) = 8000 char
--// The create partition Schema script is stored in variable
, @GOBANANA = 0

DECLARE @tmpIntervals TABLE(ID INT IDENTITY(1,1), Boundary CHAR(19))
--// Lets get this party started
SELECT
@tmpBoundary = @StartingTime
, @PartionCounter = 1
, @CREATE_SCHEMA = 'CREATE PARTITION SCHEME [PS_'
+ @TableName + '] AS PARTITION [PF_' + @TableName + '] TO ([PRIMARY]'

IF @NumberOfPartitions > 1000
BEGIN
PRINT 'Too many partitions. Limit, for SQL 2005 is 1000 which equates to (8000 Char)'
RETURN
END

WHILE @PartionCounter < @NumberOfPartitions AND @tmpBoundary IS NOT NULL
BEGIN

SELECT
@tmpBoundary = CONVERT(CHAR(19),CASE
WHEN @TimeInterval = 'WEEK' THEN DATEADD(wk,@PartionCounter, @StartingTime)
WHEN @TimeInterval = 'MONTH' THEN DATEADD(mm,@PartionCounter, @StartingTime)
ELSE NULL END ,126) --// Format 2010-01-01T00:00:00

IF @PartionCounter = 1
BEGIN
--// Magic Time
SELECT @SQLCMD = 'CREATE PARTITION FUNCTION [PF_'
+ @TableName + '](DATETIME) AS RANGE RIGHT FOR VALUES(''' + @tmpBoundary + ''')'

PRINT @SQLCMD
IF @GOBANANA = 0 EXEC (@SQLCMD)

END
ELSE
BEGIN
SELECT @SQLCMD = 'ALTER PARTITION FUNCTION [PF_' + @TableName + ']()'
+ ' SPLIT RANGE (''' + CONVERT(CHAR(19),@tmpBoundary, 126) + ''');'

PRINT @SQLCMD
IF @GOBANANA = 0 EXEC (@SQLCMD)
END

SELECT
@CREATE_SCHEMA = @CREATE_SCHEMA + '
,[PRIMARY]'-- + CASE WHEN @NumberOfPartitions - @PartionCounter = 1 THEN '' ELSE ',' END
, @PartionCounter = @PartionCounter + 1
END

IF @PartionCounter = 0
BEGIN
PRINT 'Something not right.'
RETURN
END

SELECT @CREATE_SCHEMA = @CREATE_SCHEMA + ')'
PRINT @CREATE_SCHEMA
IF @GOBANANA = 0 EXEC (@CREATE_SCHEMA)

/*
At this point you now need to apply the partion

1. Drop any existing Clustered Index
2. Transaction recommended
3. Following is tail end of Partion Wizard Script
ALTER TABLE [dbo].[MIH] DROP CONSTRAINT [PK_MIH]

ALTER TABLE [dbo].[MIH] ADD CONSTRAINT [PK_MIH] PRIMARY KEY NONCLUSTERED
(
[MIHID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_PS_MIH_634250676763125000] ON [dbo].[MIH]
(
[MIHDateCreated]
)WITH (SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF) ON [PS_MIH]([MIHDateCreated])


DROP INDEX [ClusteredIndex_on_PS_MIH_634250676763125000] ON [dbo].[MIH] WITH ( ONLINE = OFF )

*/

More to share

"A Hero is an ordinary individual who finds the strength to persevere and endure in spite of overwhelming obstacles."
— Christopher Reeve

Leadership Quotes

Oprime find

Funny find of the morning: Church Of Optimus Prime

TurnKey Fileserver, Extplorer and SSL


note to self:
/etc/ssl/certs/cert.pem
save original, then replace
with valid .pem which should contain:

-----BEGIN RSA PRIVATE KEY-----
jibberinshh......
-----END RSA PRIVATE KEY-----
-----BEGIN CERTIFICATE-----
jibberinshh......
-----END CERTIFICATE-----

The webmin section can be managed within webmin but this is for extplorer, AFAIK.

Manhunt to figure this out, as not a seasoned linux monkey. Found this, which led me to "grep ':443'/etc/* -r" in attempts to find a reference file. Using CoreFTP, /etc/lighttpd/conf-enabled/ list folders, so i couldn't just edit away:

Really strange, or I just haven't grasped something yet.

Oh well, after I replaced the cert.pem and ran "/etc/init.d/lighttpd force-reload", my certificate changed in extplorer and showed as valid.
YAY!!!.

FYI, I used a wildcard certificate.

How to alter multiple columns with T-SQL(sort of)

--//1. Create a table. I see this a lot :-(.
CREATE TABLE myLameTbl(
ID INT Identity(1,1)
, FirstName VarChar(10)
, LastName VarChar(15)
, FavoriteURL1 VarChar(45)
, FavoriteURL2 VarChar(45)
, FavoriteURL3 VarChar(45)
, FavoriteURL4 VarChar(45)
, FavoriteURL5 VarChar(45)
)

--//2. insert some data
INSERT INTO myLameTbl
SELECT
'Bob'
,'Mihada'
,'http://www.databasejournal.com'
,'http://www.google.com/'
,'http://www.turnkeylinux.org/'
,'http://www.facebook.com/'
,'http://www.bobmihada.com/'

SELECT * FROM myLameTbl

--// 3. Unknowningly insert more than table allows.
--// Coming soon: String or binary data would be truncated.
UPDATE myLameTbl
SET FavoriteURL5 = 'http://www.bobmihada.com/2010/11/how-to-alter-multiple-columns-with-t.html'

--// 4. Curse the developer who created said table.
--// 5. Get on with it. Mitigate risk with this script. Review/Execute the printed command.
--// Alters that increase character size should take seconds.
--// As oppose to the SSMS GUI, design and save, always timesout.
--//---------------------------------------------------
DECLARE @SXML as VARCHAR(MAX)
SELECT @SXML = REPLACE((
SELECT
'ALTER TABLE ' + TABLE_NAME
+ ' ALTER COLUMN ' + COLUMN_NAME
+ ' VARCHAR(255)
GO
'
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME ='myLameTbl'
AND COLUMN_NAME LIKE 'FavoriteURL[1-9]'
FOR XML PATH('')
),'&#x0D;','')
PRINT @SXML
--//---------------------------------------------------
--//---------------------------------------------------

--// 6. review your change and smile.
SELECT
TABLE_NAME
,COLUMN_NAME
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME ='myLameTbl'
AND COLUMN_NAME LIKE 'FavoriteURL[1-9]'

--//7. lets try step #3 again
UPDATE myLameTbl
SET FavoriteURL5 = 'http://www.bobmihada.com/2010/11/how-to-alter-multiple-columns-with-t.html'