More On OPENROWSET

Kevin Feasel

2016-01-29

Syntax

After introducing us to OPENROWSET, Dave Mason is now digging deeper into the topic, looking at data and format files:

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.

Named Constraints

Louis Davidson on naming constraints:

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).

VLFs And Temp Tables

Andy Galbraith was trying to update a script which counts VLFs and ran into problems defining his temp table:

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.

OPENROWSET And BULK Rowsets

Kevin Feasel

2016-01-20

Syntax

Dave Mason looks at the OPENROWSET function:

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.

Result Sets

Kevin Feasel

2016-01-19

Syntax

Kenneth Fisher learns and teaches us about RESULT SETS:

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.

COUNT(*) Versus COUNT(1)

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.

Case Statement Short-Circuiting

Richie 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.

UPDATE FROM Clause Usage

Rob Farley shows us UPDATE FROM:

1. It can let you access data in the columns of those tables, to use in predicates or expressions.

2. It can let you filter the data in the base table, by only allowing rows which match, such as when using an inner join or right outer join.

3. It can cause rows in the base table to be returned multiple times, if multiple rows in the joined table match a single row in the base table.

4. It can introduce NULL rows, if a full or right outer join is being done (or a left outer join with the base table second) and there are rows in the joined table that don’t match any rows in the base table.

This is a useful bit of T-SQL-specific syntax, but it’s a sharper edge than most UPDATE statements.  For a look back in history, Hugo Kornelis wanted to deprecate this syntax with the release of SQL Server 2008 (though MERGE has its own bugs and “Won’t Fix” problems, so in retrospect, perhaps it’s best that we still have UPDATE FROM).

Use Parentheses Wisely

Jen McCown plays around with the AND and OR operators:

Specifically, how is it evaluated when your where clause says “WHERE This AND That OR Something AND that”, without any clarifying parenthesis?

Let’s play around with this. The simplest test scenario is a SELECT 1. If I get a 1 back, that means my WHERE clause evaluated to true, right? Right.

Parentheses should clarify statements.  If I see an “AND” and an “OR” in a WHERE clause, I want to see parentheses, even if you’ve gotten it right.  It’s too easy to misinterpret precedence.

Object Level Auto-Stats

Andy Mallon on auto-statistics:

I’m not going to say whether I think Auto Update Statistics should be on or off. Instead, I’m going to argue that there are definitely scenarios when you want to have this on AND there are scenarios where you want it turned off. Can you really have this both ways? Absolutely.

I’m going to say yes to auto-update unless you know the answer is no for an object.  But it’s nice to know that the fine-grained option is available.

Categories

June 2017
MTWTFSS
« May  
 1234
567891011
12131415161718
19202122232425
2627282930