Scott Guthrie and Satya Nadella are hosting a SQL Server 2016 live event starting at 10 AM Eastern (via Scott Guthrie).
Yeah, it’s a marketing event, but there might be something interesting and exciting to come out of it.
Comments closedCurated SQL Posts
Reza Rad shows us how to create a custom visualization in Power BI:
This is one of the coolest custom visuals I’ve seen so far. The reason is that this custom visual has a customization in it! with this visualization you can define regions in any picture or images, and map data points to the image in your Power BI report. The image can be everything; human body, airplane seat layout, shop floor layout or football field. You choose the image you want, then you define regions. Let’s have a closer look at this visual.
It’s amazing how easy Power BI makes that. Almost easy enough for me to do it…
Comments closedBrent Ozar notes that several common trace flags are getting turned into all-grown-up commands:
SQL Server 2016 does away with these unintuitive trace flags by adding new ALTER DATABASE commands:
ALTER DATABASE SET MIXED_PAGE_ALLOCATION ON (or OFF, which is the new default behavior)
ALTER DATABASE MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES (or AUTOGROW_SINGLE_FILE, which is still the default)
I think trace flags will still be around for quite some time as a troubleshooting mechanism, but I certainly prefer clearer naming (was that trace flag 1117…or 1171…or maybe…).
Comments closedMike Fal wants to automate loading multiple CSV files:
Once the staging table is in place, the load is actually fairly simple. You have lots of options, but the easiest one for my money is to build a BCP call and execute that. BCP is a simple utility that has been around for a while, mostly because it is effective. The build is not difficult:
It’s quick, dirty, and functional.
Comments closedChris Bell shows us how to get the list of aliases set up on a server:
It is also a pain to sit and transcribe the various alias settings to be able to rebuild them all on the next machine.
There is an export list option for the aliases on your server, that’s nice and all, but there isn’t a corresponding import option.
Plus you have to deal with 32 and 64 bit lists.
The very simple script below helps since you can use to get the details of both the 32 and 64bit SQL Server aliases you have setup on your system.
Ready for it? It’s a long convoluted one:
If you use server aliases, you’ll want to check out this script.
Comments closedJames Anderson shows us where to sign up for the SQL Server on Linux preview:
Couple of thoughts on the sign up form:
-
Red Hat is mentioned in the announcment but this form tells us that only Ubuntu or Docker images are currently available. Will it be available to all Linux distributions? What about OS X?
-
As for Docker, does it ever make sense to have RDBMSs in containers? Containers are generally immutable.
Done. I’m also excited about the continued Microsoft-Red Hat collaboration, not just because I live in Red Hat territory.
In the meantime, Denny Cherry and Joey D’Antoni give their thoughts. Here’s one of Joey’s:
My favorite part of SQL Server on Linux? My Bash scripts for automation work again.
Comments closed
The CSS SQL Server Engineers note that TVPs with spatial columns are now much faster than before:
Table Valued Parameters (TVPs) containing spatial columns can be used as input parameter(s) to stored procedures. SQL Server 2016 improves the scalability, using native spatial validation(s), increasing performance by 15 times or more.
This is a pretty out-there edge case in my experience, but maybe there’s a niche.
Comments closedAnders Pedersen discusses one method he used to implement fast text search in SQL Server:
Looking into what was needed, I quickly realized there was a LOT of data, guess 50+ years of news broadcasts will do this. Consider this was in the early 2000s, some innovation was needed from anything I had coded before. Obviously LIKE searches was out of the question, full text search was not available. So what to do?
Basically I decided to break down each broadcast to words into a separate table, the entire application fit in 2 tables: Story and Words.
This is a case in which thinking about the grain of data helps solve an otherwise-intractable problem.
Comments closedThe time to zero out a file and write data is a function of sequential write performance on the drive(s) where the SQL Server data file(s) are located, when IFI is not enabled. When IFI is enabled, creating or growing a data file is so fast that the time is not of significant consequence. The time it takes to create or grow a value varies in seconds between 15K, SSD, flash, and magnetic storage when IFI is enabled. However, if you do not enable IFI, there can be drastic differences in create, grow, and restore times depending on storage.
There’s a huge performance benefit with turning IFI on.
Comments closedNiko Neugebauer shows how to load data into non-updated non-clustered columnstore indexes:
I decided to make a serious step back and write about something that is concerning the current (SQL Server 2014) and the elder version of SQL Server that supports Nonclustered Columnstore Indexes – (SQL Server 2012).
The Nonclustered Columnstore Indexes in SQL Server 2012 & 2014 are non-updatable, meaning that after they are built on the table, you cannot modify the table anymore – you can only read the data from it.
The common solutions for this problem are:
– Using Partitioning
– Disabling Columnstore, modifying the data and Rebuilding the Columnstore Index then (thus activating it)Sounds easy, doesn’t it ?
Well, like with everything in the real life, there are a couple of quite important gotchas here.
The “non-updatable” part is why I ignored non-clustered columnstore indexes. With SQL Server 2016, I’m going to take another look at them. But if you’re living on 2012 or 2014 for a while, this is a good post to give you an idea of how to load those tables.
Comments closed