What the what?? I literally JUST ran a query exactly like this, but without the join. I haven’t mixed aggregate and non-aggregate columns in the query without a GROUP BY…the only aggregate is in the subquery, and it’s all by its little lonesome!
It’s funny what one little letter can do to you.
This works for tables, stored procedures, views etc, but there are a few things to be careful about. It doesn’t change the code behind code based objects so you need to modify that as well. And of course any time you use sp_rename you’ll get the warning:
As Kenneth notes, this does not change any underlying code, so renaming columns can potentially break code.
So….when did “DatabaseIntegrityCheck – SYSTEM_DATABASES” start? At 1500 – is that 3pm? You may be able hash out that this translates to 12:15am local time…but what if you want to perform datetime-style math on the RunDate/RunTime? Sure you can do multiple leaps to say (RunDate>X and RunDate<=Y) AND (RunTime>A and RunTime<=B), but you then need to explicitly format your X, Y, A, and B in the appropriate integer-style format. Wouldn’t it be easier to just be able to datetime math?
The next part is even worse – quick – how long did the first instance of “ServerA_RESTORE_FROM_PROD_V2” run?
4,131 somethings (seconds, ms, etc), right?
Maybe (maybe!) there was a valid reason for the SQL Agent tables to have such screwy values for date, time, and duration; regardless, this is a sheer pain to deal with today.
SQL Server 2016 RC 3 is the last of our publicly-available release candidates. You can try this in your development and test environments, and it is available for download today.
In SQL Server 2016 RC 3, enhancements consisted primarily of bug fixes. We continue to refine the product for general availability. For the current release notes, see SQL Server 2016 Release Notes.
That “last of our publicly-available release candidates” thing says to me that we’re going to see RTM soon.
Well, there’s obviously a relationship between private bytes and “total server memory”. The peaks and valleys occur at the same time. But there’s a delta between them, and the delta isn’t constant.
The difference between private bytes and “total server memory” is pretty well confined, too – not varying more than 10 mb in this interval.
Read the whole thing.
In my Simple-Talk article Azure SQL Data Warehouse, I introduced you to SQL Data Warehouse and gave you an overview of the architecture and technologies that drive the service and make it all work. In this article, I go a step further and provide details about getting started with SQL Data Warehouse, demonstrating how to add a sample database and then accessing the server and database settings.
If you want to follow along with my examples and try out SQL Data Warehouse for yourself, you must have an active Azure subscription, even if it’s just the free trial. For those who have already used up their free trial, be aware that SQL Data Warehouse is a pay-as-you-go service, even though it’s still in preview, so unless you’re on an unlimited company budget or happen to have accrued MSDN credits, you’ll want to be judicious in how you try out the service. Fortunately, as you’ll see in this article, you can pause the compute resources when not in use, helping to minimize the costs associated with learning about the service.
This article is all about initial installation and configuration.
I had the need to connect from the command line recently, and decided to make a quick post on using SQLCMD, as I had an issue.
SQLCMD is a command line utility that comes with SQL Server. I know many people don’t use command lines, but they are handy at times.
SQLCMD and OSQL aren’t things you typically need to use a lot, but sometimes you won’t have the ability to run Management Studio and it’s good to know that there are built-in alternatives.
This week, I have been looking forward to the time where I got to read through all the contributions to my#TSQL2SDAY invitation: Favorite SQL Server Feature. Very happy to see this many. I have added a short description of each blog post, as well as my own personal key take-away(s).
So, in no particular order, I give you the round-up:
I like T-SQL Tuesday for several reasons; one of the more selfish reasons is that each month, I get to expand my blogroll a little bit further. This was a particularly good one, so check out the entrants.
All tables are partitioned from a database storage engine perspective since SQL Server 2005, although multiple partitions require Enterprise Edition. The implication of this storage architecture is that one can use ALTER TABLE…SWITCH regardless of SQL Server edition to move the entire contents of one table to another without physically moving rows as long as the source and target tables have an identical schema and are physically aligned (indexes and tables on same filegroup). SWITCH performs fast storage meta-data changes so the operation typically takes less than a second regardless of table size. The target table must be empty before SWITCH. After SWITCH, the target table will contain the data and the source table will be empty (similar to TRUNCATE).
Since the identical schema restriction does not include the IDENTITY column property, SWITCH is a handy technique to add or remove IDENTITY from an existing column of a non-empty table. For an empty table, a simple drop and create is easier and more efficient.
This is one of the few really good uses of the SWITCH operator in standard edition, but if ever you do need it, you’ll be glad it’s there.
This code is coming from an ORM, which is parameterizing the filters, but not the (unnecessary & arbitrary) TOP value. The DMVs all viewed these are separate queries, so it was not aggregating the stats. It also wasn’t reusing the plan (thus chewing up even more CPU from frequent compiles). If the TOP had not been there, or it had been passed as a parameter, my initial query of sys.dm_exec_query_stats should have found it.
There are a couple of issues Andy works through, and his advice is good: just because something runs quickly doesn’t mean it can’t (in aggregate) have a negative effect on your server.