Let’s say, just theoretically, you wanted to demo a query that takes SQL Server a long time to compile.
And in order to make SQL Server’s job tougher, you wanted to create the maximum number of indexes possible on a table.
You might do something like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT TOP 999 'CREATE INDEX [IX_' + c1.COLUMN_NAME + '_' + c2.COLUMN_NAME + '_' + c3.COLUMN_NAME + ']' + ' ON [' + c1.TABLE_SCHEMA + '].[' + c1.TABLE_NAME + '] ' + '([' + c1.COLUMN_NAME + '], [' + c2.COLUMN_NAME + '], [' + c3.COLUMN_NAME + ']);' FROM INFORMATION_SCHEMA.COLUMNS c1 INNER JOIN INFORMATION_SCHEMA.COLUMNS c2 ON c1.TABLE_SCHEMA = c2.TABLE_SCHEMA AND c1.TABLE_NAME = c2.TABLE_NAME AND c1.COLUMN_NAME <> c2.COLUMN_NAME AND (c2.CHARACTER_MAXIMUM_LENGTH <> -1 OR c2.CHARACTER_MAXIMUM_LENGTH IS NULL) INNER JOIN INFORMATION_SCHEMA.COLUMNS c3 ON c1.TABLE_SCHEMA = c3.TABLE_SCHEMA AND c1.TABLE_NAME = c3.TABLE_NAME AND c3.COLUMN_NAME NOT IN (c1.COLUMN_NAME, c2.COLUMN_NAME) AND (c3.CHARACTER_MAXIMUM_LENGTH <> -1 OR c3.CHARACTER_MAXIMUM_LENGTH IS NULL) WHERE c1.TABLE_SCHEMA = 'dbo' AND c1.TABLE_NAME = 'Users' AND (c1.CHARACTER_MAXIMUM_LENGTH <> -1 OR c1.CHARACTER_MAXIMUM_LENGTH IS NULL) ORDER BY c3.COLUMN_NAME, c2.COLUMN_NAME |
Which would generate output like this:
The CHARACTER_MAXIMUM_LENGTH <> -1 OR IS NULL stuff is because I don’t want to try to index NVARCHAR(MAX) fields, but I do want to index NVARCHAR(50) fields.
The ORDER BY is because I wanted to get a wide variety of leading fields. If I just ordered by c1.COLUMN_NAME, I wouldn’t get all of the first fields covered in the first 999 indexes. (And I’m not even using a large table.)
I could have dumped all these into strings and executed them, but depending on the speed of the system, the demos I’m trying to do, the fields in the indexes, etc, I may not want them all created right away. Sometimes I experiment with just 25-50 indexes before moving on to, uh, 999.
18 Comments. Leave new
So is this to demo why it is not a good idea to create loads of Indexes??
Isaac – go ahead and read the first sentence of the post and you can learn what the post is about.
“That’s an exercise that will be left up to the reader” -Brent
You have proven the need for new job title: Data Mad Scientist
Hahaha, thanks David!
Hmmm, is it just me or this query returns nothing in SQL Server 2014 SP2 EE and Std ?
Did you change the WHERE clause to specify the schema/table name?
Yes Garret, shortly after Tim’s unproductive comment I even posted here the T-SQL code with the last 2 statements commented/disabled. Guess BrentO in his infinite wisdom does not allow comments to contain T-SQL code (code injection concerns maybe).
The only filter is for jr DBA code. Weird, hm.
Reproducing issue again:
SELECT TOP 999 ‘CREATE INDEX [IX_’ + c1.COLUMN_NAME + ‘_’ + c2.COLUMN_NAME + ‘_’ + c3.COLUMN_NAME + ‘]’
+ ‘ ON [‘ + c1.TABLE_SCHEMA + ‘].[‘ + c1.TABLE_NAME + ‘] ‘
+ ‘([‘ + c1.COLUMN_NAME + ‘], [‘ + c2.COLUMN_NAME + ‘], [‘ + c3.COLUMN_NAME + ‘]);’
FROM INFORMATION_SCHEMA.COLUMNS c1
INNER JOIN INFORMATION_SCHEMA.COLUMNS c2 ON c1.TABLE_SCHEMA = c2.TABLE_SCHEMA AND c1.TABLE_NAME = c2.TABLE_NAME
AND c1.COLUMN_NAME c2.COLUMN_NAME
AND (c2.CHARACTER_MAXIMUM_LENGTH -1 OR c2.CHARACTER_MAXIMUM_LENGTH IS NULL)
INNER JOIN INFORMATION_SCHEMA.COLUMNS c3 ON c1.TABLE_SCHEMA = c3.TABLE_SCHEMA AND c1.TABLE_NAME = c3.TABLE_NAME
AND c3.COLUMN_NAME NOT IN (c1.COLUMN_NAME, c2.COLUMN_NAME)
AND (c3.CHARACTER_MAXIMUM_LENGTH -1 OR c3.CHARACTER_MAXIMUM_LENGTH IS NULL)
— WHERE c1.TABLE_SCHEMA = ‘dbo’ AND c1.TABLE_NAME = ‘Users’
AND (c1.CHARACTER_MAXIMUM_LENGTH -1 OR c1.CHARACTER_MAXIMUM_LENGTH IS NULL)
— ORDER BY c3.COLUMN_NAME, c2.COLUMN_NAME
Now, that’s weird.
It works fine for me. Where are you running it? With the schema and table name quoted out it returns 999 rows in every database except for Model, which returns 0. Similarly, if I create a new database that’s totally empty and run it there, it returns nothing. I think context is your issue, Paulo.
Paulo – that’s a common thing out on the web. A lot of user-entered input is sanitized. It’s nothing I do personally, just built into WordPress.
Learn “stuff” indeed! 🙂
I haven’t seen anyone notice the reason behind doing these “things” that Brent is doing (unless they have and just not said anything….)
I was told years ago that if you find someone who never makes mistakes and is perfect in everything they do – they aren’t really trying anything new and aren’t making an effort.
Using that as a basis, one thing that contributes towards being a good DBA is someone who has either:
a) seen the problems – and fixed them, or
b) created the problems (whether it be intentional or in production through laziness) – and fixed them.
Knowing how to fix problems in theory is helpful.
Knowing how to get yourself out of the pile of poop that you put yourself in is invaluable (provided that you learn from the mistakes…. 🙂 )
Brent, you’ve been having me think of all kinds of funny and bad ideas. Like a SQL Server game, where each of the database pages is a little box in the game. Then you have a sniper rifle that you shoot at one of the little boxes. As you shoot it, DBCC WRITEPAGE makes a “hole” somewhere in the page by writing zeroes.
Plus, you could unleash your forkbomb script with one of those old WW I bombers. Oh, the possibilities…
On a more serious note, it might be fun to gamify fixing database issues. Keep up the good work!
Eric, Eric, Eric, shooting holes in data pages using DBCC WRITEPAGE is possibly the worst, most diabolical thing I’ve heard this week.
Now I want to write it.
It started running fining for me too after 3 minutes of my initial post, as soon as I realized I had to comment the last 2 lines (lack of attention of course). All ok with the context and all good now, I just failed to realize the “security mechanism” implemented in the end of T-SQL code 🙂