A Fine Slice Of SQL Server
Manoj Pandey asks what the difference between COUNT(*) and COUNT(1) is:
So, we can clearly and without any doubt say that both COUNT(*) & COUNT(1) are same and equivalent.
Both of these are different from COUNT(SomeColumnName), though.
Comments closedTom LaRock shows us that we can pause the SQL Server service, as well as what that gets us:
By pausing the SQL Server service before restarting the instance we allow end users to continue their work uninterrupted and we also stop any new connections to the instance. This is a nicer way of telling people to “get out” of the database in order for the server to be rebooted. I wouldn’t leave the server paused for 60 minutes of course, but I would rather use this method than forcibly disconnect users and rollback their transactions.
This is a nice way of bleeding the service dry before taking an instance down for maintenance.
Comments closedCody Konior notes that upgrading from CTP 3.0 to CTP 3.2 can cause SQL Server R Services to break:
If you were using CTP 3.0 and later ran an in-place upgrade to CTP 3.2 this will silently break R Services. Uninstalling and reinstalling the R component will not fix the problem, but it can be fixed. There are a few interrelated issues here so bear with me.
Hopefully you don’t run into this issue, but if you do, at least there’s a fix.
Comments closedGail Shaw investigates natively compiled user-defined functions in SQL Server 2016:
When I saw that, the first question that came to mind is whether natively compiling a scalar function reduces the overhead when calling that function within another query. I’m not talking about data-accessing scalar UDFs, since natively compiled functions can only access in-memory tables, but functions that do simple manipulation of the parameters passed in. String formatting, for example, or date manipulation.
While not as harmful as data-accessing scalar UDFs, there’s still overhead as these are not inline functions, they’re called for each row in the resultset (as a look at the Stored Procedure Completed XE event would show), and the call to the function takes time. Admittedly not a lot of time, but when it’s on each row of a large resultset the total can be noticeable.
Read the whole thing and check out Gail’s method and conclusions.
Comments closedAndy Galbraith helps us figure out who to blame for database growth:
I feel a little dirty writing about the Default Trace in the world of Extended Events, but I also know that many people simply don’t know how to use XEvents, and this can be faster if you already have it in your toolbox. Also it will work back to SQL 2005 where XEvents were new in SQL 2008.
I have modified this several times to improve it – I started with a query from Tibor Karaszi (blog/@TiborKaraszi), modified it with some code from Jason Strate (blog/@StrateSQL), and then modified that myself for what is included and what is filtered. There are links to both Tibor’s and Jason’s source material in the code below.
I usually just blame the BI team for database growth.
Comments closedKendra Little has some great resources to get you started with a SQL Server migration:
Planning to move to new hardware for your SQL Server? Techniques like log shipping and database mirroring can be incredibly useful to make the change fast and painless– but you’ve got to pick the right techniques for your environment ahead of time, and know how to do a few things that aren’t in the GUI.
Some of this stuff could also feed into a disaster recovery plan.
Comments closedTim Peters introduces us to shredding multi-level XML:
The below XML has data nested in different levels that requires the nodes method to join them together. The nodes method accepts a XML string and returns a rowset. That rowset can then be used with CROSS APPLY to effectively link your way down.
nodes (XQuery) as Table(Column)
The tabular format I need requires data from 3 different levels of this XML gob and I need to wade through 5 “tables” to get there.
Shredding XML is something you occasionally need to do.
Comments closedRolf Tesmer digs into the concept of BI:
Hunting the web for the general definition pulls up many one liners – and yes I guess everyone who is anyone will have a way to define it, and that definition is (or should) be based on their own experiences with building, deploying or supporting BI solutions.
If you are looking for a nice short collection of some of those definitions – and a further explanation of why you need BI – then this is a great post (http://www.jamesserra.com/archive/2013/03/why-you-need-business-intelligence/)
Rolf unpacks the definition and gives us some insight into the nature of Business Intelligence.
Comments closedKoen Verbeeck looks at various ways of capturing changed data:
In some very rare cases, you can actually use change data capture or change tracking on the source system. If you get one of those features implemented, you’re golden. But most of the time you’re not, as a lot of administrators don’t like them because of potential performance impact.
Koen lists several options. One additional option is to use triggers to capture changes in a queue table. If you are dealing with SCD-1 changes (in which you do not need a full reckoning of history) or periodic SCD-2 (in which you keep history but are okay with smashing some changes together if they’re within a time period between ETL loads), loading IDs of changed records into a queue table is reasonably efficient and gets around trying to make sure everybody updates the modified date. It has its own drawbacks, though, starting with it using triggers…
Comments closedRichie Lee talks about using the CASE statement to short-circuit a logical expression:
The issue here is that SQL is a declarative language: unlike procedural languages, there is no guarantee on the ordering of the operations, because optimizers. And SQL Server decides to do something other than what we’d expect: it tries to evaluate the value “Apu” as a date. But by using a CASE expression we can force the optimizer to take the input and match it to the expression (in this case, when a value is a date then convert it to a date) before checking if the value is older than 7 days.
This does work most of the time, but there are exceptions, so as always, test your code.
Comments closed