May 8, 2012 at 9:41 am
I found an article by Atif Shehzad which explained schema binding and indexed views very clearly. See it here:
http://www.mssqltips.com/sqlservertip/1610/sql-server-schema-binding-and-indexed-views/
From the article:
Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view. "Cannot schema bind view 'dbo.vw_sampleView' because name 'SAMPLETABLE' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."
This error is only generated in case of schema bound views. In the case of ordinary views you will not get this error.
I figured this was the reason it would fail.
(edit)
Alas, it is using the two part name on the referenced table. Got the point anyway 😉
May 8, 2012 at 9:58 am
May 8, 2012 at 10:06 am
I saw a CREATE UNIQUE CLUSTERED INDEX on a Nullable column (ID2), and said no based on that.
May 8, 2012 at 11:45 am
bitbucket-25253 (5/8/2012)
Hugo Kornelis (5/8/2012)
dogramone (5/8/2012)
Wow, did a google search and there was no mention in any of the four posts I checked about the count_big so I got it wrong.You would have found it if you checked Books Online: http://msdn.microsoft.com/en-us/library/ms191432.aspx. We all like to bash BOL when we find something missing or a documentation error, but in reality I still think SQL Server is one of the best documented programs on the market.
EDIT: Forgot to add: Nice question, but the code was a bit hard to read. May I suggest that the next time someone uses a screenshot for the question instead of just providing the code, he or she first increases the font size?
Ran and ran again to check using IE 7 Windows XP on a 17 inch monitor, zoom set to 100%, my vision with glasses on, checked just last week at 20/20 and I have NO problem reading the question what so ever.
Any suggestions so that I can satisfy those who have stated that it was / is difficult to read ?
I notice two issues which make it hard to read on my monitor. To see them you can increase your zoom to ~400%, and they will become apparent.
First is that there are (usually yellowish) pixels in the white area around the text. This is a common artifact of JPEG compression. To minimize it, either increase the quality level or decrease the compression level of whatever software you use to create the JPEG.
Second is red/purple colors to some of the black text (note on the left brackets in particular.) This is commonly caused by technology like Microsoft's ClearType -- it uses the position of the cells in an LCD (R-G-B) to provide subpixel antialiasing. But if it's tuned to a particular monitor, it will look bad on other monitors. The only solution I know for this is to disable ClearType before taking a screenshot.
One other option to mitigate both of these is to change your SSMS settings to use a bold font. That will thicken the vertical lines, making the text that much easier to distinguish for those of us who set our monitor resolutions beyond our eyes' resolutions. 😀
May 8, 2012 at 12:07 pm
GREAT question, Ron. Thanks!
May 8, 2012 at 4:16 pm
sknox (5/8/2012)
bitbucket-25253 (5/8/2012)
Hugo Kornelis (5/8/2012)
dogramone (5/8/2012)
Wow, did a google search and there was no mention in any of the four posts I checked about the count_big so I got it wrong.You would have found it if you checked Books Online: http://msdn.microsoft.com/en-us/library/ms191432.aspx. We all like to bash BOL when we find something missing or a documentation error, but in reality I still think SQL Server is one of the best documented programs on the market.
EDIT: Forgot to add: Nice question, but the code was a bit hard to read. May I suggest that the next time someone uses a screenshot for the question instead of just providing the code, he or she first increases the font size?
Ran and ran again to check using IE 7 Windows XP on a 17 inch monitor, zoom set to 100%, my vision with glasses on, checked just last week at 20/20 and I have NO problem reading the question what so ever.
Any suggestions so that I can satisfy those who have stated that it was / is difficult to read ?
I notice two issues which make it hard to read on my monitor. To see them you can increase your zoom to ~400%, and they will become apparent.
First is that there are (usually yellowish) pixels in the white area around the text. This is a common artifact of JPEG compression. To minimize it, either increase the quality level or decrease the compression level of whatever software you use to create the JPEG.
Second is red/purple colors to some of the black text (note on the left brackets in particular.) This is commonly caused by technology like Microsoft's ClearType -- it uses the position of the cells in an LCD (R-G-B) to provide subpixel antialiasing. But if it's tuned to a particular monitor, it will look bad on other monitors. The only solution I know for this is to disable ClearType before taking a screenshot.
One other option to mitigate both of these is to change your SSMS settings to use a bold font. That will thicken the vertical lines, making the text that much easier to distinguish for those of us who set our monitor resolutions beyond our eyes' resolutions. 😀
Thanks, will do for future submittals, unfortunately as of this point in time I have an additional 8 QOD scheduled...
May 8, 2012 at 4:42 pm
bitbucket-25253 (5/8/2012)
. . . unfortunately as of this point in time I have an additional 8 QOD scheduled...
Man, this must be some kind of so far not classified dependency or co-dependency. In either case you should seek professional help. 😀
Keep them comin', Ron, no kidding. I am looking forward to them.
May 8, 2012 at 6:40 pm
bitbucket-25253 (5/8/2012)
...unfortunately as of this point in time I have an additional 8 QOD scheduled...
FTFY 😀
May 8, 2012 at 9:19 pm
Oh Man how did I got this wrong I read this reference article not so long ago !! (a month may be) Time to refresh my knowledge....
Great question Ron. Keep it up... and yeah keep those 8 questions coming...
May 9, 2012 at 2:01 am
I failed the response only by a misplaced options. :angry:
But, IMO, the reason would be the fact that ID2 must be set as NOT NULL to enable the creation of the index.
May 9, 2012 at 2:12 am
jalvarocrespo (5/9/2012)
But, IMO, the reason would be the fact that ID2 must be set as NOT NULL to enable the creation of the index.
Nope. You can create an index on a nullable column.
Here's a repro; the code below executes with no errors.
CREATE TABLE dbo.Test
(Nullable int,
Other varchar(20));
go
CREATE UNIQUE CLUSTERED INDEX test_ix ON dbo.Test(Nullable);
go
DROP TABLE dbo.Test;
go
May 9, 2012 at 2:18 am
Hugo Kornelis (5/9/2012)
jalvarocrespo (5/9/2012)
But, IMO, the reason would be the fact that ID2 must be set as NOT NULL to enable the creation of the index.Nope. You can create an index on a nullable column.
Here's a repro; the code below executes with no errors.
CREATE TABLE dbo.Test(Nullable int,
Other varchar(20));
go
CREATE UNIQUE CLUSTERED INDEX test_ix ON dbo.Test(Nullable);
go
DROP TABLE dbo.Test;
go
From BOL,
UNIQUE
Creates a unique index on a table or view. A unique index is one in which no two rows are permitted to have the same index key value. A clustered index on a view must be unique.
The Database Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an error message. Duplicate values must be removed before a unique index can be created on the column or columns. Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created.
May 9, 2012 at 2:38 am
jalvarocrespo (5/9/2012)
From BOL,UNIQUE
Creates a unique index on a table or view. A unique index is one in which no two rows are permitted to have the same index key value. A clustered index on a view must be unique.
The Database Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an error message. Duplicate values must be removed before a unique index can be created on the column or columns. Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created.
I can see how that text in Books Online is confusing.
What they (probably) mean is that you should not create a unique index on a nullable column if you want to have more than at most one row with a NULL value in that column. They probably put in that quote to clarify this behaviour, because the ANSI standard prescribes (for UNIQUE constraints - indexes are not considered in the standard) that NULL values are not considered when enforcing a UNIQUE constraint, so that even with a UNIQUE constraint you can still have multiple NULL values. SQL Server deviates from that standard.
In the case of this question, it's not an issue. The GROUP BY ensures that only a single row will have NULL in the view. If you try the code and change COUNT to COUNT_BIG, you'll see that the index is created without problems.
May 9, 2012 at 9:03 am
Nice question, Ron.
Unlike some others, I don't have any problem with the legibility of the image, although the JPEG quality is pretty abysmal (much nasty clutter) and the effects of sub-pixel anti-aliasing are not pleasant. But I do have a problem with the layout of the SQL (including the SQL embedded in the comment). Things like putting a column's type on the line after its name (instead of keeping them together on a ine) in a create table or view statement, putting the table and column (or column list) on the line after ON (instead of keeping them together) in an index definition make SQL much harder to read and understand. So does tabbing right back to the LHS at every line-break so that layout gives no clue at all as to structure. Normally confronted with something like this I'd stick it into notepad and format it by hand (I still haven't fount a formatter that I like), but of course when you present an image that's impossible so I have to try to understand the thing despite an abysmally bad layout. I don't like that at all.
Tom
May 14, 2012 at 12:21 am
Great question. Looking forward next 8 QotD scheduled... 🙂
Thanks
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply