Updating multiple columns in sql server 2016 speed dating in marietta georgia
Let’s get a fresh copy of the database and create the statistics USE master GO IF DB_ID('Adventure Works2014') IS NOT NULL BEGIN ALTER DATABASE Adventure Works2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE Adventure Works2014 END RESTORE DATABASE Adventure Works2014 FROM DISK = '.\Adventure Works2014.bak' WITH RECOVERY , MOVE 'Adventure Works2014_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.
I’m sure there are many small differences but I just want to talk about how the estimated number of rows differ from one version to another when we query a single table using two predicates and we have multi-column statistics that cover the combination.
202 2006-04-30 -- Reset to old values UPDATE fyi_links SET id = 101, counts = 999 WHERE url = 'dev.fyicenter.com' (1 row(s) affected) Notice that the "id" in the "counts" new value expression is taking the old value of the "id" column, not the updated value, even the "id" column is updated before the "counts" column.
Now try this on a My SQL server, you will get different result.
These numbers match with the compatibility level of the SQL Server versions they were released, coincidence?
I don’t think so So in SQL Server 2016 we have been given an improved version of the CE, which for the examples of this post, I think would be version 130.
Don’t get me wrong, they’ve always been there, but somehow someone forgot about them and weren’t used.
Since last week my mind has been on other things, I’m intending to write a series of articles and didn’t have much of my brain to get me write a post for this week, so finally I decided to speak about some issues which have been already described, but I believe they are good to be reminded. yeah, statistics So statistics are the base for the cardinality estimator (CE from now on) to figure out how many rows will be returned by an operator during the execution of a query, I described some behaviors already in previous posts, so this time I’ll go a bit more to the specifics.