August 14, 2012 at 3:50 pm
For example I create some fake data about database files and retrieve only the databases with more than one transaction log file.
I was trying to test my T-SQL skills and see what was the best way of doing this. Below is the first two that came to mind. Any help or advise would be appreciated!
CREATE TABLE #stuff (
[databaseid]TINYINT,
[databasename]VARCHAR(50) NOT NULL,
[filename]VARCHAR(50) NOT NULL UNIQUE,
[filetype]VARCHAR(50) NOT NULL
)
INSERT INTO #stuff VALUES (1, 'games', 'game.mdf', 'data')
INSERT INTO #stuff VALUES (1, 'games', 'game.ldf', 'log')
INSERT INTO #stuff VALUES (2, 'movies', 'movies.mdf', 'data')
INSERT INTO #stuff VALUES (2, 'movies', 'movies_1.ldf', 'log')
INSERT INTO #stuff VALUES (2, 'movies', 'movies_2.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n.mdf', 'data')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log1.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log2.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log3.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log4.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log5.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log6.ldf', 'log')
INSERT INTO #stuff VALUES (4, 'music', 'music.mdf', 'data')
INSERT INTO #stuff VALUES (4, 'music', 'music.ndf', 'data')
INSERT INTO #stuff VALUES (4, 'music', 'music.ldf', 'log')
SELECTs.[databaseid]
,COUNT(*) AS qty
INTO#stuffaggregate
FROM#stuff s
WHEREs.[filetype] = 'log'
GROUP BY s.[databaseid]
HAVING COUNT(*) > 1
ORDER BY s.[databaseid]
SELECTs1.[databaseid]
,s1.[databasename]
,s1.[filename]
,s1.[filetype]
FROM#stuff s1
WHEREs1.[databaseid] IN (SELECT s2.[databaseid] FROM #stuffaggregate s2)
ORDER BY s1.[databasename] ASC
SELECTs1.[databaseid]
,s1.[databasename]
,s1.[filetype]
FROM#stuff s1
WHEREs1.[databaseid] IN (SELECT s2.[databaseid] FROM #stuff s2 WHERE s2.[filetype]= 'log' GROUP BY s2.[databaseid] HAVING COUNT(*) > 1)
TRUNCATE TABLE #stuffaggregate
DROP TABLE #stuffaggregate
TRUNCATE TABLE #stuff
DROP TABLE #stuff
August 14, 2012 at 4:04 pm
Here is another query:
CREATE TABLE #stuff (
[databaseid]TINYINT,
[databasename]VARCHAR(50) NOT NULL,
[filename]VARCHAR(50) NOT NULL UNIQUE,
[filetype]VARCHAR(50) NOT NULL
)
INSERT INTO #stuff VALUES (1, 'games', 'game.mdf', 'data')
INSERT INTO #stuff VALUES (1, 'games', 'game.ldf', 'log')
INSERT INTO #stuff VALUES (2, 'movies', 'movies.mdf', 'data')
INSERT INTO #stuff VALUES (2, 'movies', 'movies_1.ldf', 'log')
INSERT INTO #stuff VALUES (2, 'movies', 'movies_2.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n.mdf', 'data')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log1.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log2.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log3.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log4.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log5.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log6.ldf', 'log')
INSERT INTO #stuff VALUES (4, 'music', 'music.mdf', 'data')
INSERT INTO #stuff VALUES (4, 'music', 'music.ndf', 'data')
INSERT INTO #stuff VALUES (4, 'music', 'music.ldf', 'log')
GO
WITH AggData AS (
SELECT
COUNT(*) OVER (PARTITION BY databaseid, filetype) FileCnt,
databaseid,
filetype
FROM
#stuff
)
SELECT
databaseid,
databasename,
filename,
filetype
FROM
#stuff s
WHERE
EXISTS(SELECT 1 FROM AggData ag WHERE ag.databaseid = s.databaseid AND ag.filetype = 'log' AND ag.FileCnt > 1)
;
GO
DROP TABLE #stuff;
GO
August 14, 2012 at 4:10 pm
I personally usually just do them in a single build, like so:
SELECT
s.[databaseid]
,s.[databasename]
,s.[filename]
,s.[filetype]
FROM
#stuff AS s
JOIN
(SELECT DatabaseID FROM #stuff GROUP BY DatabaseID HAVING COUNT(*) > 1) AS drv
ONs.DatabaseID = drv.DatabaseID
That said, it'll depend on how large your dataset is and if you have indexes optimized to DatabaseID or whatever you're linking on for quick-counts. A temp table with alternative indexing can help if the data-set's large enough.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 14, 2012 at 4:13 pm
Beautiful! I have no experience with partition. Does it have a better execution plan too? I could find out myself but I'm on an iPhone right now.
smallmoney (8/14/2012)
For example I create some fake data about database files and retrieve only the databases with more than one transaction log file.I was trying to test my T-SQL skills and see what was the best way of doing this. Below is the first two that came to mind. Any help or advise would be appreciated!
CREATE TABLE #stuff (
[databaseid]TINYINT,
[databasename]VARCHAR(50) NOT NULL,
[filename]VARCHAR(50) NOT NULL UNIQUE,
[filetype]VARCHAR(50) NOT NULL
)
INSERT INTO #stuff VALUES (1, 'games', 'game.mdf', 'data')
INSERT INTO #stuff VALUES (1, 'games', 'game.ldf', 'log')
INSERT INTO #stuff VALUES (2, 'movies', 'movies.mdf', 'data')
INSERT INTO #stuff VALUES (2, 'movies', 'movies_1.ldf', 'log')
INSERT INTO #stuff VALUES (2, 'movies', 'movies_2.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n.mdf', 'data')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log1.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log2.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log3.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log4.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log5.ldf', 'log')
INSERT INTO #stuff VALUES (3, 'pr0n', 'pr0n_log6.ldf', 'log')
INSERT INTO #stuff VALUES (4, 'music', 'music.mdf', 'data')
INSERT INTO #stuff VALUES (4, 'music', 'music.ndf', 'data')
INSERT INTO #stuff VALUES (4, 'music', 'music.ldf', 'log')
SELECTs.[databaseid]
,COUNT(*) AS qty
INTO#stuffaggregate
FROM#stuff s
WHEREs.[filetype] = 'log'
GROUP BY s.[databaseid]
HAVING COUNT(*) > 1
ORDER BY s.[databaseid]
SELECTs1.[databaseid]
,s1.[databasename]
,s1.[filename]
,s1.[filetype]
FROM#stuff s1
WHEREs1.[databaseid] IN (SELECT s2.[databaseid] FROM #stuffaggregate s2)
ORDER BY s1.[databasename] ASC
SELECTs1.[databaseid]
,s1.[databasename]
,s1.[filetype]
FROM#stuff s1
WHEREs1.[databaseid] IN (SELECT s2.[databaseid] FROM #stuff s2 WHERE s2.[filetype]= 'log' GROUP BY s2.[databaseid] HAVING COUNT(*) > 1)
TRUNCATE TABLE #stuffaggregate
DROP TABLE #stuffaggregate
TRUNCATE TABLE #stuff
DROP TABLE #stuff
August 14, 2012 at 4:15 pm
Ah man I forgot we can replace subqueries with joins which is faster than subqueries
Evil Kraig F (8/14/2012)
I personally usually just do them in a single build, like so:
SELECT
s.[databaseid]
,s.[databasename]
,s.[filename]
,s.[filetype]
FROM
#stuff AS s
JOIN
(SELECT DatabaseID FROM #stuff GROUP BY DatabaseID HAVING COUNT(*) > 1) AS drv
ONs.DatabaseID = drv.DatabaseID
That said, it'll depend on how large your dataset is and if you have indexes optimized to DatabaseID or whatever you're linking on for quick-counts. A temp table with alternative indexing can help if the data-set's large enough.
August 14, 2012 at 4:17 pm
Evil Kraig F (8/14/2012)
I personally usually just do them in a single build, like so:
SELECT
s.[databaseid]
,s.[databasename]
,s.[filename]
,s.[filetype]
FROM
#stuff AS s
JOIN
(SELECT DatabaseID FROM #stuff GROUP BY DatabaseID HAVING COUNT(*) > 1) AS drv
ONs.DatabaseID = drv.DatabaseID
That said, it'll depend on how large your dataset is and if you have indexes optimized to DatabaseID or whatever you're linking on for quick-counts. A temp table with alternative indexing can help if the data-set's large enough.
Not quite right. Should only return values where there are more than 1 log files.
August 14, 2012 at 4:21 pm
you're right. i couldn't test this because i'm on a iphone right now but if he added WHERE filetype = 'log' in his subquery on the join then it should be correct results?
JOIN
(SELECT DatabaseID FROM #stuff WHERE filetype = 'log' GROUP BY DatabaseID HAVING COUNT(*) > 1) AS drv
ONs.DatabaseID = drv.DatabaseID
Lynn Pettis (8/14/2012)
Evil Kraig F (8/14/2012)
I personally usually just do them in a single build, like so:
SELECT
s.[databaseid]
,s.[databasename]
,s.[filename]
,s.[filetype]
FROM
#stuff AS s
JOIN
(SELECT DatabaseID FROM #stuff GROUP BY DatabaseID HAVING COUNT(*) > 1) AS drv
ONs.DatabaseID = drv.DatabaseID
That said, it'll depend on how large your dataset is and if you have indexes optimized to DatabaseID or whatever you're linking on for quick-counts. A temp table with alternative indexing can help if the data-set's large enough.
Not quite right. Should only return values where there are more than 1 log files.
August 14, 2012 at 4:23 pm
smallmoney (8/14/2012)
you're right. i couldn't test this because i'm on a iphone right now but if he added WHERE filetype = 'log' in his subquery on the join then it should be correct results?
DERP! :blush:
Yeaaaah, just put your where clause in.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy