enriquezreyjoseph (9/27/2013)

---

SET @SqlQuery = ' SELECT * FROM TestMyView ' +

CASE

...

That is my recommendation.

I find the resulting code to be a bit easier to understand.

enriquezreyjoseph (9/27/2013)

---

Dwain,,,,

My sexID will never contain embedded quotes...because with this table

*Bio

bioIB

fname

mname

lname

sexID

statusID

*sex

sexID

sex(male or female only)

*status

statusID

status(single,married or divorced only)

sexID and statusID from bioID belongs to different table...

so my View table would...

I'd bet money though that ChrisM's first solution, bizarre as it seems, is probably the fastest one suggested.

I had to dissect it myself before I figured out what he was...

Perhaps a rather incomplete example will help:

SET @SqlQuery = ' SELECT * FROM TestMyView WHERE 1 = 1 ' +

...

Continuing curiosity regarding this problem drove me to see how the proposed solutions scale, so I built a test harness.

CREATE TABLE #T (

Rate int NOT NULL,

Shift int NOT NULL,

PRIMARY KEY...

sqlnes (9/26/2013)

---

Hi, Guess what, it did count the uncommitted records...

I've done a test,

Step 1: run "select count(*) from table1;" -- return 3

Step 2: begin a transaction to insert 3 records...

I love extended properties. Very comprehensive introductory article!

The only think I find annoying about them (unless I missed something) is that you can't update them. Need to delete...

I do not recommend that...

If I am reading this article (http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx) correctly, this solution should also be pretty fast:

WITH Target AS

(

SELECT Rate, Shift

FROM @T

...

And I just noted that while your question is different, the answers to your earlier post:

http://www.sqlservercentral.com/Forums/Topic1498669-1292-1.aspx

will cross over with the answers here.

The short answer is yes, however I have several issues with what you're trying to do.

1. [Issue #1] In the statement below, you're setting the right part of the quality...

I am a bit curious how hunchback and my solutions would scale against a divide and conquer approach.

SELECT Shift

INTO #T

FROM @T

WHERE Rate = @Rate;

DECLARE @Rows INT = @@ROWCOUNT;

SELECT...

Most likely, the declaration for...

I like the SQL# library too.

Note that this article was based on a "call for an article" by this site's editor, which constrained how the input data was to appear....