A Fine Slice Of SQL Server
Dave Mattingly shows an easy way to anglicize values:
If your customer’s name is “José” but you search for “Jose”, you won’t (by default) find him.
Here’s a simple way to take care of that in your SQL database, without changing the data that you have.
If a particularly system only needs to support one language (e.g., English), this can be helpful, at least until somebody throws in Chinese or Hebrew characters. That said, supporting Unicode is the best move when available.
Comments closedRichie Lee has a script to check the last known CHECKDB run date:
One of the most important duties of a DBA is to ensure that CHECKDB is run frequently to ensure that the database is both logically and physically correct. So when inheriting an instance of SQL, it’s usually a good idea to check when the last CHECKDB was last run. And ironically enough, it is actually quite difficult to get this information quickly, especially if you have a lot of databases that you need to check. The obvious way is to run DBCC DBINFO against the specific database. This returns more than just the last time CHECKDB was run, and it is not especially clear which row returned tells us the last CHECKDB (FYI the Field is “dbi_dbccLastKnownGood”.)
It’s a bit of a shame that this information isn’t made available in an easily-queryable DMV.
Comments closedSteve Jones shows how to chain Common Table Expressions:
In this way I can more easily see in the first example I’m joining two tables/views/CTEs together. If I want to know more about the details of one of those items, I can easily look up and see the CTE at the beginning.
However when I want multiple CTEs, how does this work?
The answer is simple but powerful. Once you’ve read up on CTEs, you start to see the power of chaining CTEs. And then you go CTE-mad until you see the performance hit of the monster you’ve created. Not that I’ve ever done that…nope…
Comments closedAaron Bertrand shows us Common Table Expressions:
A CTE is probably best described as a temporary inline view – in spite of its official name, it is not a table, and it is not stored (like a #temp table or @table variable). It operates more like a derived table or subquery, and can only be used for the duration of a single SELECT, UPDATE, INSERT, or DELETE statement (though it can be referenced multiple times within in that statement).
This is a great article on CTEs; give it a read, even if you’re familiar with them.
1 CommentMatt Allington shows how that reducing cardinality helps with reducing data sizes with DAX:
With both of these concepts combined, the file size was reduced from the original 264 MB to 238 MB, a reduction of almost 10%. You can see where the space savings have come from by comparing the before and after column sizes in the 2 tables below. The SalesValueExTax column (65MB) was replaced with the Margin column (44MB) and the CostValue column (63MB) was replaced with the CostPerCase column (50MB).
Check it out, as well as the memory tool.
Comments closedJovan Popovic shows how to use data in GeoJSON format.
First, building data in GeoJSON format from a spatial type:
In geometry object are placed type of the spatial data and coordinates. In “property” object can be placed various custom properties such as address line, town, postcode and other information that describe object. SQL Server stores spatial information as geometry or geography types, and also stores additional properties in standard table columns.
Since GeoJSON is JSON, it can be formatted using new FOR JSON clause in SQL Server.
In this example, we are going to format content of Person.Address table that has spatial column SpatialLocation in GeoJSON format using FOR JSON clause.
Then, converting GeoJSON to Geography types:
New OPENJSON function in SQL Server 2016 enables you to parse and load GeoJSON text into SQL Server spatial types.
In this example, I will load GeoJSON text that contains a set of bike share locations in Washington DC. GeoJSON sample is provided ESRI and it can be found in https://github.com/Esri/geojson-layer-js/blob/master/data/dc-bike-share.json
Check them out.
Comments closedErik Darling looks at operators which result in serial plans:
In the past, there were a number of things that caused entire plans, or sections of plans, to be serial. Scalar UDFs are probably the first one everyone thinks of. They’re bad. Really bad. They’re so bad that if you define a computed column with a scalar UDF, every query that hits the table will run serially even if you don’t select that column. So, like, don’t do that.
What else causes perfectly parallel plan performance plotzing?
Commenting on one of his comments, I can name at least one good reason to use a table variable.
Comments closedTom Roush talks VLFs, changes in DBCC LOGINFO, and Availability Groups:
Turns out SQL 2008R2 (where the original script worked) returns different fields than 2012 and 2014 (where it didn’t).
I figured I didn’t want to find out which version of the script to use every time I needed to run it on a server, so I told the script to figure that out by itself, and then run the appropriate hunk of code (example below)
This is a good explanation of how to back out of a complex situation.
Comments closedTom LaRock shows us how to change the compatibility level of an Azure SQL Database:
You can change the compatibility level of an Azure SQL Database.
It’s true! I know!
OK, so I’m a little excited about this one. See, I’ve been giving this talk on cardinality for the past couple of years now, so this is a hidden gem to me. When I found out this was possible I took out my demo scripts to see if changing the compatibility level would have any effect.
This is interesting, especially given that Management Studio doesn’t give you that option. Know your T-SQL, folks.
Comments closedAdam Machanic has made the SQLQueryStress source code publicly available:
So here’s the official word: The attached source code is hereby released to the world, copyright and royalty free. You may use it, if you like, for whatever you want. Enjoy! If you use it for a public project, I would appreciate a mention in the acknowledgements section, but even that is not required. This source code is yours, warts and all. I was tempted to do some cleanup work, but at this point it’s just not something I’m ever going to touch again. I upgraded the project from Visual Studio 2005 to Visual Studio 2013, confirmed that it builds and seems to work, and that’s that.
Adam may never have used in a production scenario, but I certainly have, and SQLQueryStress is still the best free load simulator. There’s also a GitHub repo thanks to Erik Ejlskov Jensen, so go forth and hack at some C# code.
Comments closed