I got asked a question about the OUTPUT clause recently and realized I didn’t remember the syntax. I’ve rarely used this, so I had to look it up and thought this would be a good basic post.
The idea with OUTPUT is that the data from the inserted and deleted tables can be output from the INSERT statement, outside of your triggers. This is the same data, but you can access it in the insert.
The format is
INSERT xxx OUTPUT yyyy INTO @zzz VALUES (or SELECT) mmmm
If I had one thing I could change about OUTPUT, I’d like to be able to output directly into variables for those cases in which I know I’m only going to get one result (or maybe I only care about one arbitrary result in a set).
What makes this interesting is when I am using UNION to join the results. How do you place a final resultset from a UNION, EXCEPT, or INTERSECT into a temporary table using SELECT INTO? Where does the INTO portion of the query go?
This is actually a pretty simple thing to do. The INTO for the SELECT INTO goes into the first query of the set. An example of UNIONing the results from sys.dm_exec_query_stats and sys.dm_exec_query_stats into a temporary table is provided in listing 1.
No subqueries are necessary here.
The new String_Split function is a table function which has two inputs:
String_Split(<main string as input>,<delimiter>)
Usage of it should be within From clause of your query because this is a table function.
I’m curious to see how this compares performance-wise to CLR and tally table split methods.
Let’s say you have a table called
dbo.BugReports, and you need to change it to
dbo.SupportIncidents. This can be quite disruptive if you have references to the original name scattered throughout stored procedures, views, functions, and application code. Modern tools like SSDT can make a refactor relatively straightforward within the database (as long as queries aren’t constructed from user input and/or dynamic SQL), but for distributed applications, it can be a lot more complex.
A synonym can allow you to change the database now, and worry about the application later – even in phases. You just rename the table from the old name to the new name (or use
ALTER TABLE ... SWITCHand then drop the original), and then create a synonym named with the old name that “points to” the new name
I’ve used synonyms once or twice, but they’re pretty low on my list, in part because of network effects: if I create this great set of synonyms but the next guy doesn’t know about them, it makes maintenance that much harder.
So here is the thing. When you change one you change them all. That means if you don’t specify a precision when you can then you get the default. That’s not exactly a common problem though. Usually what you are changing is the precision (or possibly the datatype). What is a common mistake is not specifying the nullability.
When modifying DDL, make sure that you keep it consistent and complete.
Let’s take a closer look at the format file 1 There is a single <RECORD>element with multiple <FIELD> elements that correspond to the fields in the data file. There is also a single <ROW> element with multiple <COLUMN> elements that correspond to table columns. Note the xsi:type attributes that specify the SQL data types for the columns of the returned rowset.
I’ve never had great luck with OPENROWSET reading files and tend to reach for SSIS, but I think part of that is I’d never seen as clear an example as Dave’s.
It has long been a habit that I name my constraints, and even if it wasn’t useful for database comparisons, it just helps me to see the database structure all that much eaiser. The fact that I as I get more experience writing SQL and about SQL, I have grown to habitually format my code a certain way makes it all the more interesting to me that I had never come across this scenario to not name constraints.
Temp tables are special. There’s another reason to have non-named constraints on temp tables inside stored procedures: it allows for temp table reuse, as shown on slide 21 in this Eddie Wuerch slide deck from SQL Saturday 75 (incidentally, the first SQL Saturday I ever attended).
Michelle’s code uses INSERT…EXEC to populate a temporary table with the VLF info, and the addition of this extra column breaks the original script. Glenn’s versions of the scripts handle this issue easily since they are version-specific – in the SQL 2012/2014/2016 versions of the script, the temp table declaration is modified to include the extra RecoveryUnitID column, which allows the rest of the script to function as designed.
My problem is I wanted a version of the script that could be used across versions 2005+, and this presented a problem. At first I tried to add an IF…ELSE block to the start of the script to handle the differing CREATE TABLE statements:
This is a good example of working around a problem rather than simply giving up.
The built-in SQL Server function OPENROWSET() provides a way to access remote data from an OLE DB data source. It can be used with the BULK rowset provider to read data from a file without loading the data into a target table. This post will show the basics to get started with OPENROWSET(), the BULK rowset provider, and text files of fixed-width data fields.
For permanent connections, look into linked servers. But for one-off things, OPENROWSET works fine.
Quick definition. A result set is the output of a query. It could result in a one row, one column output or a 100+ column, million+ row output. Either way that’s a result set. Note: you can have multiple result sets from a single object (stored procedure, function etc) call.
This was introduced in SQL Server 2012 and there are a couple of security-related scenarios in which RESULT SETS is helpful. It also lets you rename columns in stored procedure calls, if you’re into that sort of thing.