Blog posts on Microsoft SQL Server from SQL Workbooks. A funny thing happened on the way to my filtered nonclustered columnstore demo recentlyIt was taking forever to create my demo index. Brent Ozar Unlimiteds specialized experts focus on your goals, diagnose your tough database pains, and make Microsoft SQL Server faster and more reliable. Version history of the AwardWinning SQL Server Maintenance Solution. SQL Server ships with sane defaults, right Well, sort of. SQL Server ships with pretty sane defaults for a single CPU socket server or for a development work station. SQL Server clustered indexes and nonclustered indexes have best practice rules that are tough to decipher. Get an overview of tradeoffs for proper index use. My demo table had a unique clustered index created on two columns Fake. Birth. Date. Stamp DATETIME20, First. Name. By. Birth. Date. Id BIGINT. I used the following code to create my filtered nonclustered columnstore index nccx CREATE NONCLUSTERED COLUMNSTORE INDEX ncciFirst. Name. By. Birth. Datefiltered. ON dbo. First. Name. Datatypes. Each value manipulated by Oracle Database has a datatype. The datatype of a value associates a fixed set of properties with the value. Oracle SQL Developer is a graphical version of SQLPlus that gives database developers a convenient way to perform basic tasks. You can browse, create, edit, and. SQL Server Views are virtual tables that are used to retrieve a set of data from one or more tables. The views data is not stored in the database, but the real. If you are using SQL Server 2008 Enterprise Edition or SQL Server 2008 R2 or SQL Server 2012 Standard and Enterprise Editions, you can even take benefits of the. Sql Server Standard Edition Indexed Views' title='Sql Server Standard Edition Indexed Views' />By. Birth. Date First. Name. By. Birth. Date. Id, Fake. Birth. Date. Stamp, State. Code, First. Name. Id, Gender, Flag. Flag. 2. WHERE Fake. Birth. Date. Stamp lt 2. GOBut creating the index was super slow. Id limited my dataset size so that populating the table and creating the clustered rowstore index on the table could all be done in less than 2. Im running it again as I write this post, and its up to 1. Some clues memory grants and wait stats, oh my. Looking in sys. dmexecquerymemorygrants, I can see that my filtered mccx index create requested 4. MB of memory grant, and got itYay But its only using under 2. MB of memory grant, and its max used grant is 5. MB. Its using all four of the cores on my VM, but CPU use total is at a whopping 2. Running Adam Machanics free spWho. Is. Active procedure, I can see that I am constantly waiting on columnstorebuildthrottle Click for a larger image. Whats up with columnstorebuildthrottleDenzil Ribeiro has a great post on how columnstore indexes are built. How To Install A Britax Roundabout 55 Recall. He writes that while segments can now be built in parallel, the first segment is built with a single thread to see how much of a memory grant is really needed before things get real. While this first segment is being built, other threads wait on it, and the other threads all give off COLUMNSTOREBUILDTHROTTLE. Am I not getting past the first segment Im a slow writer, so at this point, my test filtered nccx create is up to 2. Reminder populating the whole database and creating the rowstore clustered index took less than 2. At this point in my original test, I had a small realization and a question could I have screwed up the filterA test remove the filter. Guess how long it takes to create the same nccx, but with no filter definition, like this CREATE NONCLUSTERED COLUMNSTORE INDEX ncciFirst. Name. By. Birth. Datefiltered. ON dbo. First. Name. By. Birth. Date First. Name. By. Birth. Date. Id, Fake. Birth. Date. Stamp, State. Code, First. Name. Id, Gender, Flag. Flag. 2. GO3. 0 seconds or so. Not were up to 2. Aha I messed up my filterAt this point in my testing, I realized this was a case of user error. Can you guess what I did wrong Dont worry if you dont, it took me longer to figure this out than Id like to admit and I work with this dataset a lot. I used the wrong datatype. To review, my original index definition wasCREATE NONCLUSTERED COLUMNSTORE INDEX ncciFirst. Name. By. Birth. Datefiltered. ON dbo. First. Name. By. Birth. Date First. Name. By. Birth. Date. Id, Fake. Birth. Date. Stamp, State. Code, First. Name. Id, Gender, Flag. Flag. 2. WHERE Fake. Birth. Date. Stamp lt 2. But the Fake. Birth. Date. Stamp column is DATETIME20. DATETIME27. Comparing a larger data type to a smaller one often equals BIG TROUBLE in SQL Server. I knew this, I just didnt think about it. That first index create might complete someday. Im not sure, the longest Ive let it run is 3. But if I correct my index creation definition to thisCREATE NONCLUSTERED COLUMNSTORE INDEX ncciFirst. Name. By. Birth. Datefiltered. ON dbo. First. Name. By. Birth. Date First. Name. By. Birth. Date. Id, Fake. Birth. Date. Stamp, State. Code, First. Name. Id, Gender, Flag. Flag. 2. WHERE Fake. Birth. Date. Stamp lt CAST2. Sql Server 2008 Enterprise Full Crack'>Sql Server 2008 Enterprise Full Crack. AS DATETIME20. GOIt creates in 4. So, yes, data types really matter Extra credit what about rowstore filtered indexesI just know one of you clever folks is going to ask this eventually, so I went ahead and tested. For a rowstore filtered index with a filter on the DATETIME20 column, if I create the index with an explicitly correct datatype, it creates in 1. If I create the same rowstore filtered index with a sloppy filter that SQL Server implicitly converts, the index creates in 2. Both indexes work as expected for sample test queries. So I saw a performance difference for both rowstore and columnstore index creation, but its much more dramatic with filtered columnstore indexes. Extra extra credit what if I have a different clustered index on the table To recap in this case where I saw the super slow filtered nonclustered columnstore index create, I had what Ill call Pattern 1 A unique clustered index with compound key Fake. Birth. Date. Stamp DATETIME20, First. Name. By. Birth. Date. Id BIGINTNonclustered columnstore index with a predicate who has an implicit conversion on Fake. Birthdate. Stamp to DATETIME27Pattern 1 is recommend in Sunil Agarwals post here, so that SQL Server can combine scans on the filtered NCCX with seeks on the clustered index. I wondered if Id have the same problem if instead I had what Ill call Pattern 2 A clustered PK on First. Name. By. Birth. Date. Id BIGINTNonclustered columnstore index with a predicate who has an implicit conversion on Fake. Birthdate. Stamp to DATETIME27A filtered rowstore nonclustered index to help find the rest of the rows. Pattern 2 is demonstrated in Niko Neugebauers post here. And, in fact, with Pattern 2 I do NOT run into the super slow index create with the implicit conversion on the filter of the NCCX. It takes 4. 0 seconds. Arent edge conditions fun In summaryWe should all especially me get in the habit of explicitly casting literals to the proper data type in any kind of filtered index. Thats why I dont think this is a bug. I messed up in my index definition being unclear about a datatype is my bad, not the SQL Servers. Indexed views in SQL Azure. Indexed views can be used to improve performance of complex queries or aggregations, but there are some differences between SQL server standard edition and SQL server Enterprise edition. In standard edition you need to specify the Query hint WITHNOEXPAND, otherwise the query will use the code of the view to query the underlying tables instead of the indexed view, but thats not the case with Enterprise edition, the indexed view will be used without the hint. But what about SQL Azure Can I create indexed views How does SQL Azure handle the indexed view Well lets find out. Execute the script below and look at the resultdont bother to worry about the lack of ORDER BY, its intentionally to demonstrate the use of indexed view, but in real life you shouldnt depend on the order of the data unless you specify ORDER BY create a tableCREATE TABLE dbo. Azure. Table. 1 c. NOT NULL, c. 2 char1. NULL, c. 3 datetime NULL, CONSTRAINT PKsampletable PRIMARY KEY c. GO insert some dataINSERT INTO dbo. Azure. Table. 1 VALUES 1,C,GETDATE, 2,B,GETDATE, 3,A,GETDATE test the query and look at the order of the data SELECT FROM dbo. Azure. Table. 1 create the viewCREATE VIEW dbo. Azure. Table. 1 WITH SCHEMABINDINGASSELECT c. FROM dbo. Azure. Table. GO create the index on the view on another columnCREATE UNIQUE CLUSTERED INDEX ix. ON dbo. v. Azure. Table. 1 c. 2 PRESS CTRLM and execute the code belowSELECT FROM v. Azure. Table. 1 look at the order of the data. SELECT FROM v. Azure. Table. 1 WITHNOEXPANDlook at the order of the datalook at the execution plansWait a minute The code proves that the indexed view is not used unless you specify the NOEXPAND hint. Does this mean that SQL Azure works like Standard edition Well thats what it looks like, at least in SQL Azure Web edition. If you like to hear more about SQL Azure or SQL server, feel free to contact any of our SQL server consultants and we will tell you more. And dont miss the SQLUG event in Kista the 6th October where I will speak about Azure.