|
|
By Adam Bean, on December 8th, 2009
http://www.sqlslayer.com/wp/admin-database/
- Minor bug fixes for ChangeOwner, JobLogging
- File naming enhancements for Trace_Blocking
By Adam Bean, on December 1st, 2009
http://www.sqlslayer.com/wp/admin-database/
- Added support for RedGate backups
- Added additional permissions for StandardGroupPermissions and new roles
- Removed two part object_name call in CHECKDB for support pre-SP2
- Resolved problem where ‘renamed’ column was not being updated when database owner was changed with ChangeOwner
By Adam Bean, on November 24th, 2009
http://www.sqlslayer.com/wp/admin-database/
Sorry for the lack of update lately … There are many new updates to the admin database. I will do my best to try and work on a change history as well as provide a better change history log per release. One thing to note, we are adding new 2008 specific objects, which require an update to the AUU (admin update utility) as currently we do not support 2008 specific objects as up until now all 2005 objects work in 2008. If all goes to plan the utility will be ready soon and we will post it appropriately. Currently the only object that wouldn’t deploy properly is the 2008.dbo.StandardGroupPermissions. We are also hoping that with the next release of the AUU that we will have very detailed instructions on how to use the utility.
We have to go through a testing cycle to official release our first version. I would appreciate any help on this, but for now they will be considered RC’s (release candidates) until we have finalized our first official release.
Be patient, this process will get better. There is just so much work to do.
Thanks all
By Jeff Mlakar, on November 6th, 2009
Sometimes we need to rollup string values into lists of comma separated values. Using “FOR XML PATH” per column that you want to rollup can provide a way to do this.
This example was created based on work in this thread:
http://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx
Let’s say you have a data set like so:
| id |
fruit |
city |
| 101 |
Apple |
Cleveland |
| 101 |
Apple |
Pittsburgh |
| 101 |
Banana |
Pittsburgh |
| 102 |
Grape |
Cleveland |
| 102 |
Melon |
Cleveland |
| 103 |
Melon |
Pittsburgh |
| 103 |
Melon |
Cleveland |
and for each distinct “id”, you want a list of its distinct fruits and cities.
If you put a FOR XML PATH statement for each column you want to rollup like so:
...
----------fruit---------
,STUFF((SELECT DISTINCT ', ' + NULLIF(fruit,'')
FROM tableToCSVRollup t2
WHERE t1.id = t2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,2,'') AS fruit
...
and then GROUP BY id, you’ll have a nice aggregated set. It will even put it in alphabetical order and remove NULL and empty string values for you.
Complete code example:
;WITH tableToCSVRollup (id,fruit,city)
AS (
SELECT 101,'Apple','Cleveland'
UNION ALL SELECT 101,'Apple','Pittsburgh'
UNION ALL SELECT 101,'Banana','Pittsburgh'
UNION ALL SELECT 102,'Grape','Cleveland'
UNION ALL SELECT 102,'Melon','Cleveland'
UNION ALL SELECT 103,'Melon','Pittsburgh'
UNION ALL SELECT 103,'Melon','Cleveland'
)
SELECT
id
----------fruit---------
,STUFF((SELECT DISTINCT ', ' + NULLIF(fruit,'')
FROM tableToCSVRollup t2
WHERE t1.id = t2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,2,'') AS fruit
----------city----------
,STUFF((SELECT DISTINCT ', ' + NULLIF(city,'')
FROM tableToCSVRollup t2
WHERE t1.id = t2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,2,'') AS city
------------------------
FROM tableToCSVRollup t1
GROUP BY id
ORDER BY id
Results:
| id |
fruit |
city |
| 101 |
Apple, Banana |
Cleveland, Pittsburgh |
| 102 |
Grape, Melon |
Cleveland |
| 103 |
Melon |
Cleveland, Pittsburgh |
——————————————
By Cliff Buckley, on October 13th, 2009
Today, as I was working on a project, I found an opportunity to use the For Loop Container. The basic setup, was three tables, A, B, C for simplicity.
Table A is the Header, Table B a sub-header related to table A via an ID, and Table C related to Table B via an id. I wanted to loop through the Id’s in table B one at a time due to the amount of data. I pulled in TableAId, MinTableBId and MaxTableBId into an object variable, Using For Each Loop Container, I wanted to loop through each TableAId and then loop through each TableBId, pulling records from Table C one at a time. I decided to drop a For Loop Container into the For Each Loop Container. Initially, there was no issue, everything looped through. As the iterations increased, my laptop stopped responding. I was able to see the memory was sitting at 500 megs…then 600… eventually topping out around 850 megs (There is only 1 gig of RAM on the laptop I use at work.). By this time, I had to kill the task.
I thought maybe it was a fluke, so I restarted (when in doubt restart right=)). Cleaned out the tables and ran again. Same result.
After ripping out the for loop, and using a different technique, 150 megs of RAM was consumed. I plan on doing some more tests to see if I can find more useful information for everyone, but I wanted to post this as a caution to be careful with a For Loop Container. This is with SQL 2005 SP2.
By Adam Bean, on October 9th, 2009
Just wanted to extend a warm welcome to anyone finding our site after last night’s presentation. It was a great experience for us and we hope that we can continue to grow our community. I realize the site needs a lot of work, so please bear with us as we continue to improve it to make hopefully a truly new experience for all us SQL professionals.
Thanks
By Jeff Mlakar, on October 8th, 2009
The GROUP BY CUBE statement is pretty sweet. Here’s an example.
First, let’s create our table and add data:
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[GroupByCubeExample]')
AND type in (N'U'))
DROP TABLE [dbo].[GroupByCubeExample]
GO
CREATE TABLE [dbo].[GroupByCubeExample](
[Region] [nvarchar](50) NOT NULL,
[Product] [nvarchar](50) NOT NULL,
[Salesman] [nvarchar](50) NOT NULL,
[Amount] [money] NOT NULL
)
GO
INSERT INTO dbo.GroupByCubeExample
(Region,Product,Salesman,Amount)
SELECT 'America','Bike','Bean, Adam',10
UNION ALL SELECT 'America','Bike','Stanford, Matt',20
UNION ALL SELECT 'America','Car','Bean, Adam',10000.0000
UNION ALL SELECT 'America','Car','Stanford, Matt',20000.0000
UNION ALL SELECT 'Canada','Bike','Bean, Adam',10
UNION ALL SELECT 'Canada','Bike','Stanford, Matt',20
UNION ALL SELECT 'Canada','Car','Bean, Adam',10000.0000
UNION ALL SELECT 'Canada','Car','Stanford, Matt',20000.0000
Then, we can SUM using GROUP BY CUBE for some nice automatic rollups.
SELECT
Region
,Product
,Salesman
,SUM(Amount) AS Amount
FROM dbo.GroupByCubeExample
GROUP BY CUBE(
Region
,Product
,Salesman
)
ORDER BY Region,Product,Salesman
Results (NULLs mean ALL):
Region Product Salesman Amount
------------- ---------- ----------------- ----------
NULL NULL NULL 60060.00
NULL NULL Bean, Adam 20020.00
NULL NULL Stanford, Matt 40040.00
NULL Bike NULL 60.00
NULL Bike Bean, Adam 20.00
NULL Bike Stanford, Matt 40.00
NULL Car NULL 60000.00
NULL Car Bean, Adam 20000.00
NULL Car Stanford, Matt 40000.00
America NULL NULL 30030.00
America NULL Bean, Adam 10010.00
America NULL Stanford, Matt 20020.00
America Bike NULL 30.00
America Bike Bean, Adam 10.00
America Bike Stanford, Matt 20.00
America Car NULL 30000.00
America Car Bean, Adam 10000.00
America Car Stanford, Matt 20000.00
Canada NULL NULL 30030.00
Canada NULL Bean, Adam 10010.00
Canada NULL Stanford, Matt 20020.00
Canada Bike NULL 30.00
Canada Bike Bean, Adam 10.00
Canada Bike Stanford, Matt 20.00
Canada Car NULL 30000.00
Canada Car Bean, Adam 10000.00
Canada Car Stanford, Matt 20000.00
(27 row(s) affected)
And if you don’t like the NULLs, you can use a WHEN GROUPING() statement to better format:
SELECT
CASE
WHEN GROUPING(Region) = 1
THEN 'ALL REGIONS'
ELSE Region
END AS Region
,CASE
WHEN GROUPING(Product) = 1
THEN 'ALL PRODUCTS'
ELSE Product
END AS Product
,CASE
WHEN GROUPING(Salesman) = 1
THEN 'ALL SALESMAN'
ELSE Salesman
END AS Salesman
,SUM(Amount) AS Amount
FROM dbo.GroupByCubeExample
GROUP BY CUBE(
Region
,Product
,Salesman
)
ORDER BY Region,Product,Salesman
Results:
Region Product Salesman Amount
-------------- -------------- --------------- ----------
ALL REGIONS ALL PRODUCTS ALL SALESMAN 60060.00
ALL REGIONS ALL PRODUCTS Bean, Adam 20020.00
ALL REGIONS ALL PRODUCTS Stanford, Matt 40040.00
ALL REGIONS Bike ALL SALESMAN 60.00
ALL REGIONS Bike Bean, Adam 20.00
ALL REGIONS Bike Stanford, Matt 40.00
ALL REGIONS Car ALL SALESMAN 60000.00
ALL REGIONS Car Bean, Adam 20000.00
ALL REGIONS Car Stanford, Matt 40000.00
America ALL PRODUCTS ALL SALESMAN 30030.00
America ALL PRODUCTS Bean, Adam 10010.00
America ALL PRODUCTS Stanford, Matt 20020.00
America Bike ALL SALESMAN 30.00
America Bike Bean, Adam 10.00
America Bike Stanford, Matt 20.00
America Car ALL SALESMAN 30000.00
America Car Bean, Adam 10000.00
America Car Stanford, Matt 20000.00
Canada ALL PRODUCTS ALL SALESMAN 30030.00
Canada ALL PRODUCTS Bean, Adam 10010.00
Canada ALL PRODUCTS Stanford, Matt 20020.00
Canada Bike ALL SALESMAN 30.00
Canada Bike Bean, Adam 10.00
Canada Bike Stanford, Matt 20.00
Canada Car ALL SALESMAN 30000.00
Canada Car Bean, Adam 10000.00
Canada Car Stanford, Matt 20000.00
(27 row(s) affected)
By Cliff Buckley, on October 7th, 2009
While there are different views on how to build different dimensions, there seems to be a common view of how, at least the basic information, of date dimensions looks. This can take a little time to build out. To help in building this dimension, I created a simple table valued function that takes in a start date and returns a date dimension table.
/*******************************************************************************************************
** Name: dbo.GenerateDateDimension
** Desc: Takes a start date and returns all dates up to the current day as a Date Dimension table.
** Auth: Cliff Buckley (SQLSlayer.com)
** Parameters: @StartDate - The starting date for the date dimension table.
** Dependancies: None
** Notes:
** Date: 10.07.2009
** Example: SELECT * FROM dbo.GenerateDateDimension('1/1/2009')
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- ---------------------------------------
**
********************************************************************************************************/
CREATE FUNCTION dbo.GenerateDateDimension
(
@StartDate datetime
)
RETURNS @DimDate TABLE(DateId int,YearNumber smallint, QuarterNumber tinyint, MonthText varchar(20)
,MonthNumber tinyint,DayNumberOfMonth tinyint, DayOfWeekText varchar(20)
,DayNumberOfYear smallint,SQLDate datetime)
AS
BEGIN
DECLARE @EndDate datetime
SET @EndDate = CAST(CONVERT(varchar(8),getdate(),112) as datetime)
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO @DimDate(DateId,YearNumber,QuarterNumber,MonthText,MonthNumber,DayNumberOfMonth,DayOfWeekText,DayNumberOfYear,SQLDate)
SELECT CAST(CONVERT(varchar(8),@StartDate,112) AS INT) AS DateId
,DATENAME(year,@StartDate) AS YearNumber
,DATENAME(quarter,@StartDate) AS QuarterNumber
,DATENAME(month,@StartDate) AS MonthText
,MONTH(@StartDate) AS MonthNumber
,DATENAME(day,@StartDate) AS DayOfMonthNumber
,DATENAME(weekday,@StartDate) AS DayOfWeekText
,DATENAME(dayofyear,@StartDate) AS DayNumberOfYear
,@StartDate AS SQLDate
SET @StartDate = DATEADD(d, 1, @StartDate)
END
RETURN;
END
GO
Unfortunately, the formatting is lost, but you get the idea.
By Cliff Buckley, on October 7th, 2009
As we all know, and some of us hate, triggers are not the best thing for performance. Enter SQL 2005+ with the Output clause.
Here is the scenario. You have a table that is transactional. You only care about the most recent record for your system, but need to have historical data available for reporting and/or auditing purposes. The prior developer created a trigger on the primary table that inserts the old information into an audit table on update of the primary table. That’s not a bad solution, but there is an alternative that will work as well. Below is a very simple example of how this may work.
IF OBJECT_ID(’tempdb..#Current’) IS NOT NULL
DROP TABLE #Current
IF OBJECT_ID(’tempdb..#Hist’) IS NOT NULL
DROP TABLE #Hist
CREATE TABLE #Current(ID int IDENTITY(1,1) , Key varchar(20), Val varchar(255))
CREATE TABLE #Hist(HistID int IDENTITY(1,1) ,OriginalID int, Key varchar(20), Val varchar(255))
INSERT INTO #Current(Key,Val) VALUES(’Root Dir’,'X:\Files’)
INSERT INTO #Current(Key,Val) VALUES(’Temp Dir’,'C:\’)
SELECT ID,Key,Val FROM #Current
– now we realise that C:\ is not the correct value for the temp dir setting, we want to update it but track history…
UPDATE #Current
SET Val = ‘C:\Temp\’
OUTPUT DELETED.ID,DELETED.Key,DELETED.Val INTO #Hist(OriginalId,Key,Val)
WHERE Key = ‘Temp Dir’
SELECT ID, Key, Val FROM #Current
SELECT HistId,OriginalId,Key,Val FROM #Hist
IF OBJECT_ID(’tempdb..#Current’) IS NOT NULL
DROP TABLE #Current
IF OBJECT_ID(’tempdb..#Hist’) IS NOT NULL
DROP TABLE #Hist
CREATE TABLE #Current(ID int IDENTITY(1,1) , Key varchar(20), Val varchar(255))
CREATE TABLE #Hist(HistID int IDENTITY(1,1) ,OriginalID int, Key varchar(20), Val varchar(255))
INSERT INTO #Current(Key,Val) VALUES(’Root Dir’,'X:\Files’)
INSERT INTO #Current(Key,Val) VALUES(’Temp Dir’,'C:\’)
SELECT ID,Key,Val FROM #Current
– now we realise that C:\ is not the correct value for the temp dir setting, we want to update it but track history…
UPDATE #Current
SET Val = ‘C:\Temp\’
OUTPUT DELETED.ID,DELETED.Key,DELETED.Val INTO #Hist(OriginalId,Key,Val)
WHERE Key = ‘Temp Dir’
SELECT ID, Key, Val FROM #Current
SELECT HistId,OriginalId,Key,Val FROM #Hist
This is something similar to the CDC functionality in SQL 2008 and should work better on sets of data than a trigger on the table. I hope to provide some benchmarks in a future post.
(NOTE: This is derrived from another post I made several months ago on my personal blog.)
By Adam Bean, on October 7th, 2009
Lots of work to do to create additional forums, but for now it’s a start. For those of you who find us and want to ask questions about the admin database, DBACentral or general administration, this is a great place to do it. Give us time and the forum will evolve, for now though, it’s a good start.
Thanks
|
|
Comments