What’s WHILE?
Unlike Data Definition Language statements for creating views, tables or indexes, query statements for inserting and retrieving data into these structures, the WHILE
keyword is part of the flow control. This means that this keyword can be used in SQL statements to control processes and execute certain queries multiple times.
How can you use WHILE statements?
WHILE
statements are especially useful if you want to execute a task multiple times, i.e. until a certain condition is met. To demonstrate how this works, let’s start by creating a small table that shows how deadly different ninjas of our dojo are:
DROP TABLE IF EXISTS #NinjaDemo CREATE TABLE #NinjaDemo ( NinjaId INT IDENTITY(1,1) ,Deadlyness float NOT NULL )
Now we want to fill this table with the first five ninjas, but since we only use demo records, to protect the identity of our ninjas, we fill the Deadliness
column with random numbers. We can then do this as follows:
INSERT INTO #NinjaDemo (Deadlyness) VALUES (RAND()) ,(RAND()) ,(RAND()) ,(RAND()) ,(RAND())
If we now look at the records, we see five entries with random values for deadlyness. But if we want to do this for a larger dojo and need 200 entries instead of the five ninjas, we could either copy and paste a very long (and confusing) statement or we could use a WHILE
statement to create the records. This would be done as follows:
DECLARE @nRows int = 0; SELECT @nRows = COUNT(*) FROM #NinjaDemo WHILE @nRows < 200 BEGIN INSERT INTO #NinjaDemo (Deadlyness) VALUES (RAND()) SELECT @nRows = COUNT(*) FROM #NinjaDemo END SELECT * FROM #NinjaDemo
Now the table has 200 entries, but if we want 500 or 5000 entries instead, we would only have to change a number in the WHILE
condition in this statement. But to fill large tables with random numbers, a separate language construct would certainly not be worth it. The WHILE
statement can of course do more than that. Suppose we want to rebuild all clustered indexes in our database. Let's first write a query that lists all clustered indexes:
SELECT i.[name] as index_name ,schema_name(t.schema_id) + '.' + t.[name] as table_view FROM sys.objects t INNER JOIN sys.indexes i ON t.object_id = i.object_id WHERE t.[type] = 'U' AND i.[type] = 1
To rebuild all these indexes, we generate dynamic SQL statements that we want to execute - ATTENTION, of course you should try this in the Dojo before you compete in the real competition (or don't execute it on production servers!). We can do this with a WHILE
statement, by always rebuilding an index until there is no index to rebuild. To do this we first create a temporary table with the indexes we want to rebuild:
DROP TABLE IF EXISTS #sqlReorg SELECT 'ALTER INDEX ['+i.[name]+'] ON ['+schema_name(t.schema_id) + '].[' + t.[name]+'] REORGANIZE ;' as Stmt INTO #sqlReorg FROM sys.objects t INNER JOIN sys.indexes i ON t.object_id = i.object_id WHERE t.[type] = 'U' AND i.[type] = 1
Now we fetch an index from this table, generate the corresponding statement and delete the index from the table:
DECLARE @stmt NVARCHAR(500) WHILE (SELECT COUNT(*) FROM #sqlReorg) > 0 BEGIN SELECT top(1) @stmt = [stmt] FROM #sqlReorg DELETE FROM #sqlReorg WHERE [Stmt] = @stmt PRINT 'Running '+@stmt EXEC sp_Executesql @stmt END
This is not best practice, of course, as it does not include error handling, but it shows how the WHILE
statement can be used to execute multiple statements. Important: SQL is always efficient when working with data sets. By definition, WHILE
statements work with single records first, so what you do here is potentially always a slow solution to your problem. Often there are better and more efficient solutions, so before you start with a WHILE
statement, think carefully if this is really the best solution and if you can somehow allow the server to work efficiently with multiple records...