When I display my execution plan, a node indicates an Index Seek, the object
listed is the name of the nonclustered indexe used, and the Output list has
[Bmk1002]. Normally in the Output list I see a list of columns when ther
e is
a Bookmark involved, but this is confusing to me cause it is an Index Seek
and the Output list is simply [Bmk1002]. Using "set showplan_all on" sho
ws
the same thing. In this instance do I look at the corresponding Bookmark nod
e
to determine which columns are being sought? Why does it give me a Bookmark
when I am having returned an Index Seek? This is a nasty multi-join query so
I am not including the statement or the tables and indexes. Any possible
explanations would be appreciated.
Message posted via http://www.droptable.comWell, it is because you are trying to get the column/s that do not covered
by index and SQL Server is using nonclusterd index to point to datapage to
return the data
Like
select col1, col30 from table where col1=5
col1 has an index and we will see index seek with bookmark because col30
does not have any index in that case
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:605b4028efd9b@.uwe...
> When I display my execution plan, a node indicates an Index Seek, the
> object
> listed is the name of the nonclustered indexe used, and the Output list
> has
> [Bmk1002]. Normally in the Output list I see a list of columns when th
ere
> is
> a Bookmark involved, but this is confusing to me cause it is an Index Seek
> and the Output list is simply [Bmk1002]. Using "set showplan_all on" s
hows
> the same thing. In this instance do I look at the corresponding Bookmark
> node
> to determine which columns are being sought? Why does it give me a
> Bookmark
> when I am having returned an Index Seek? This is a nasty multi-join query
> so
> I am not including the statement or the tables and indexes. Any possible
> explanations would be appreciated.
> --
> Message posted via http://www.droptable.com
Showing posts with label explain. Show all posts
Showing posts with label explain. Show all posts
Thursday, March 22, 2012
Bookmark, Explain Plan understanding
When I display my execution plan, a node indicates an Index Seek, the object
listed is the name of the nonclustered indexe used, and the Output list has
[Bmk1002]. Normally in the Output list I see a list of columns when there is
a Bookmark involved, but this is confusing to me cause it is an Index Seek
and the Output list is simply [Bmk1002]. Using "set showplan_all on" shows
the same thing. In this instance do I look at the corresponding Bookmark node
to determine which columns are being sought? Why does it give me a Bookmark
when I am having returned an Index Seek? This is a nasty multi-join query so
I am not including the statement or the tables and indexes. Any possible
explanations would be appreciated.
--
Message posted via http://www.sqlmonster.comWell, it is because you are trying to get the column/s that do not covered
by index and SQL Server is using nonclusterd index to point to datapage to
return the data
Like
select col1, col30 from table where col1=5
col1 has an index and we will see index seek with bookmark because col30
does not have any index in that case
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:605b4028efd9b@.uwe...
> When I display my execution plan, a node indicates an Index Seek, the
> object
> listed is the name of the nonclustered indexe used, and the Output list
> has
> [Bmk1002]. Normally in the Output list I see a list of columns when there
> is
> a Bookmark involved, but this is confusing to me cause it is an Index Seek
> and the Output list is simply [Bmk1002]. Using "set showplan_all on" shows
> the same thing. In this instance do I look at the corresponding Bookmark
> node
> to determine which columns are being sought? Why does it give me a
> Bookmark
> when I am having returned an Index Seek? This is a nasty multi-join query
> so
> I am not including the statement or the tables and indexes. Any possible
> explanations would be appreciated.
> --
> Message posted via http://www.sqlmonster.comsql
listed is the name of the nonclustered indexe used, and the Output list has
[Bmk1002]. Normally in the Output list I see a list of columns when there is
a Bookmark involved, but this is confusing to me cause it is an Index Seek
and the Output list is simply [Bmk1002]. Using "set showplan_all on" shows
the same thing. In this instance do I look at the corresponding Bookmark node
to determine which columns are being sought? Why does it give me a Bookmark
when I am having returned an Index Seek? This is a nasty multi-join query so
I am not including the statement or the tables and indexes. Any possible
explanations would be appreciated.
--
Message posted via http://www.sqlmonster.comWell, it is because you are trying to get the column/s that do not covered
by index and SQL Server is using nonclusterd index to point to datapage to
return the data
Like
select col1, col30 from table where col1=5
col1 has an index and we will see index seek with bookmark because col30
does not have any index in that case
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:605b4028efd9b@.uwe...
> When I display my execution plan, a node indicates an Index Seek, the
> object
> listed is the name of the nonclustered indexe used, and the Output list
> has
> [Bmk1002]. Normally in the Output list I see a list of columns when there
> is
> a Bookmark involved, but this is confusing to me cause it is an Index Seek
> and the Output list is simply [Bmk1002]. Using "set showplan_all on" shows
> the same thing. In this instance do I look at the corresponding Bookmark
> node
> to determine which columns are being sought? Why does it give me a
> Bookmark
> when I am having returned an Index Seek? This is a nasty multi-join query
> so
> I am not including the statement or the tables and indexes. Any possible
> explanations would be appreciated.
> --
> Message posted via http://www.sqlmonster.comsql
Saturday, February 25, 2012
Blob Fields
Can anyone explain me what is blob fields?A blob is a binary large object data type that allows the storage of things like a picture or text in SQL Server , or a pulsing ball of protoplasm from outer space that has an insatiable hunger for human flesh. :)|||rhigdon: don't talk bad about my mama! :)|||Hehe:D
Sunday, February 12, 2012
Bizare String Concatenation Issue - Explanation Needed
Hi all, can someone please explain why this is happening:
I execute the following:
DECLARE @.temp VARCHAR(500)
SET @.temp = ''
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM MyTable T
SELECT @.temp
Say [MyTable] looks like this:
|NAME|
--
A
B
C
D
Here are the result from running the above code on two different SQL Server
2000 installations:
SQL Server 2000 #1
[D]
SQL Server 2000 #2
[A][B][C][D]
I want #2. Is there some database option that I'm missing or something? Any
help would be appreciated. Thanks.SELECT @.@.version from each server? Might be different builds.
For me:
DECLARE @.temp VARCHAR(500)
SET @.temp = ''
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM master.dbo.sysdatabases T
SELECT @.temp
select @.@.version
SQL 2000, build 878 (Sp3a plus hotfixes):
[master][tempdb][model][msdb][pubs][Nort
hwind]
SQL 2005, IDW 13:
[master][tempdb][model][msdb][AdventureW
orksDW][AdventureWorks]
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Treadway" <MikeTreadway@.discussions.microsoft.com> wrote in message
news:39B330F1-41D5-4A7F-BFE0-25998D07C492@.microsoft.com...
> Hi all, can someone please explain why this is happening:
> I execute the following:
> DECLARE @.temp VARCHAR(500)
> SET @.temp = ''
> SELECT @.temp = @.temp + '[' + T.NAME + ']'
> FROM MyTable T
> SELECT @.temp
>
> Say [MyTable] looks like this:
> |NAME|
> --
> A
> B
> C
> D
> Here are the result from running the above code on two different SQL
> Server
> 2000 installations:
> SQL Server 2000 #1
> [D]
> SQL Server 2000 #2
> [A][B][C][D]
> I want #2. Is there some database option that I'm missing or something?
> Any
> help would be appreciated. Thanks.|||Thanks Mike. I should have posted an update, but I found this:
http://support.microsoft.com/defaul...b;EN-US;q287515
What I posted here was a simple example of my issue, my actual query
contained a little more SQL and it was on a view instead of a table. Doing
the following resolved my issue:
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM (SELECT * FROM MyTable) T
This caused the query optimizer to sort the results of the inner query
first, then apply the concatenations.
Thanks again for your response Mike.
"Mike Epprecht (SQL MVP)" wrote:
> SELECT @.@.version from each server? Might be different builds.
> For me:
> DECLARE @.temp VARCHAR(500)
> SET @.temp = ''
> SELECT @.temp = @.temp + '[' + T.NAME + ']'
> FROM master.dbo.sysdatabases T
> SELECT @.temp
> select @.@.version
> SQL 2000, build 878 (Sp3a plus hotfixes):
> [master][tempdb][model][msdb][pubs][Nort
hwind]
> SQL 2005, IDW 13:
> [master][tempdb][model][msdb][AdventureW
orksDW][AdventureWorks]
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mike Treadway" <MikeTreadway@.discussions.microsoft.com> wrote in message
> news:39B330F1-41D5-4A7F-BFE0-25998D07C492@.microsoft.com...
>
>
I execute the following:
DECLARE @.temp VARCHAR(500)
SET @.temp = ''
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM MyTable T
SELECT @.temp
Say [MyTable] looks like this:
|NAME|
--
A
B
C
D
Here are the result from running the above code on two different SQL Server
2000 installations:
SQL Server 2000 #1
[D]
SQL Server 2000 #2
[A][B][C][D]
I want #2. Is there some database option that I'm missing or something? Any
help would be appreciated. Thanks.SELECT @.@.version from each server? Might be different builds.
For me:
DECLARE @.temp VARCHAR(500)
SET @.temp = ''
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM master.dbo.sysdatabases T
SELECT @.temp
select @.@.version
SQL 2000, build 878 (Sp3a plus hotfixes):
[master][tempdb][model][msdb][pubs][Nort
hwind]
SQL 2005, IDW 13:
[master][tempdb][model][msdb][AdventureW
orksDW][AdventureWorks]
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Treadway" <MikeTreadway@.discussions.microsoft.com> wrote in message
news:39B330F1-41D5-4A7F-BFE0-25998D07C492@.microsoft.com...
> Hi all, can someone please explain why this is happening:
> I execute the following:
> DECLARE @.temp VARCHAR(500)
> SET @.temp = ''
> SELECT @.temp = @.temp + '[' + T.NAME + ']'
> FROM MyTable T
> SELECT @.temp
>
> Say [MyTable] looks like this:
> |NAME|
> --
> A
> B
> C
> D
> Here are the result from running the above code on two different SQL
> Server
> 2000 installations:
> SQL Server 2000 #1
> [D]
> SQL Server 2000 #2
> [A][B][C][D]
> I want #2. Is there some database option that I'm missing or something?
> Any
> help would be appreciated. Thanks.|||Thanks Mike. I should have posted an update, but I found this:
http://support.microsoft.com/defaul...b;EN-US;q287515
What I posted here was a simple example of my issue, my actual query
contained a little more SQL and it was on a view instead of a table. Doing
the following resolved my issue:
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM (SELECT * FROM MyTable) T
This caused the query optimizer to sort the results of the inner query
first, then apply the concatenations.
Thanks again for your response Mike.
"Mike Epprecht (SQL MVP)" wrote:
> SELECT @.@.version from each server? Might be different builds.
> For me:
> DECLARE @.temp VARCHAR(500)
> SET @.temp = ''
> SELECT @.temp = @.temp + '[' + T.NAME + ']'
> FROM master.dbo.sysdatabases T
> SELECT @.temp
> select @.@.version
> SQL 2000, build 878 (Sp3a plus hotfixes):
> [master][tempdb][model][msdb][pubs][Nort
hwind]
> SQL 2005, IDW 13:
> [master][tempdb][model][msdb][AdventureW
orksDW][AdventureWorks]
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mike Treadway" <MikeTreadway@.discussions.microsoft.com> wrote in message
> news:39B330F1-41D5-4A7F-BFE0-25998D07C492@.microsoft.com...
>
>
Bitwise Operators!
The outcome of the following 2 simple queries
---
PRINT 15 & 75
PRINT 15 | 75
---
are 11 & 79 respectively. Can someone explain how does SQL Server
compute these values? I went through the topic 'Bitwise Operators' in
BOL but couldn't exactly comprehend the explanation & that's why I am
posting my query here.
Thanks,
Arpan"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124150426.712216.65110@.g14g2000cwa.googlegroups.com...
> The outcome of the following 2 simple queries
> ---
> PRINT 15 & 75
> PRINT 15 | 75
> ---
> are 11 & 79 respectively. Can someone explain how does SQL Server
> compute these values? I went through the topic 'Bitwise Operators' in
> BOL but couldn't exactly comprehend the explanation & that's why I am
> posting my query here.
> Thanks,
> Arpan
>
Bitwise OR : If either bit value is 1, then the resulting bit is 1, else 0
Bitwise AND : If either bit value is 0, then the resulting bit is 0, else 1
OR:
1001011 = 75
0001111 = 15
--
1001111 = 79
AND:
1001011 = 75
0001111 = 15
--
0001011 = 11|||15 & 75 =
(in binary)
00001111 &
01001011
--
00001011 = 11 (in decimal)
15 | 75 =
(in binary)
00001111 |
01001011
--
01001111 = 79 (in decimal)
SQL Server, from memory, just calculates these kind of binary ANDs and
ORs using twos complement arithmetic.
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Arpan wrote:
>The outcome of the following 2 simple queries
>---
>PRINT 15 & 75
>PRINT 15 | 75
>---
>are 11 & 79 respectively. Can someone explain how does SQL Server
>compute these values? I went through the topic 'Bitwise Operators' in
>BOL but couldn't exactly comprehend the explanation & that's why I am
>posting my query here.
>Thanks,
>Arpan
>
>|||Thanks, Chris, for your input but to be honest, I couldn't follow how
did you arrive at the values 1001111 & 0001011? What did you do with
the binary values of 75 & 15 to get to 79 and 11 & their respective
binary values? Please explain me this.
Also how does one find the binary value of a decimal number (of course,
other than using the Windows Scientific Calculator)? Is there any
built-in SQL Server function to do so?
Thanks once again & thanks to Mike as well,
Regards,
Arpan|||The binary number system: each bit represents a power of 2.
0 1
1 2
2 4
3 8
4 16
5 32
6 64
7 128
8 256
9 512
...
For fractions, each bit past the binary point:
-1 .5
-2 .25
-3 .125
-4 .0625
-5 .03125
-6 .015625
-7 .0078125
-8 .00390625
-9 .001953125
...
Each binary number is a combination of bits.
75decimal = 1001011
15decimal = 0001111
A bitwise AND performs a binary AND operation using the following table:
A B | A & B
--
0 0 | 0
0 1 | 0
1 0 | 0
1 1 | 1
for each bit in a binary number, thus
75decimal = 1001011
15decimal = 0001111
--
75 & 15 = 0001011 = 11 decimal
Notice that for each bit position which is 1 in the result, both bits in the
same position of each operand is one.
A bitwise OR performs a binary OR operation using the following table:
A B | A | B
--
0 0 | 0
0 1 | 1
1 0 | 1
1 1 | 1
for each bit in a binary number, thus
75decimal = 1001011
15decimal = 0001111
--
75 | 15 = 1001111 = 79 decimal
Notice that for each bit position which is 1 in the result, at least one bit
from the same position of either operand is one.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124153765.707009.29780@.g49g2000cwa.googlegroups.com...
> Thanks, Chris, for your input but to be honest, I couldn't follow how
> did you arrive at the values 1001111 & 0001011? What did you do with
> the binary values of 75 & 15 to get to 79 and 11 & their respective
> binary values? Please explain me this.
> Also how does one find the binary value of a decimal number (of course,
> other than using the Windows Scientific Calculator)? Is there any
> built-in SQL Server function to do so?
> Thanks once again & thanks to Mike as well,
> Regards,
> Arpan
>|||"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124153765.707009.29780@.g49g2000cwa.googlegroups.com...
> Thanks, Chris, for your input but to be honest, I couldn't follow how
> did you arrive at the values 1001111 & 0001011? What did you do with
> the binary values of 75 & 15 to get to 79 and 11 & their respective
> binary values? Please explain me this.
> Also how does one find the binary value of a decimal number (of course,
> other than using the Windows Scientific Calculator)? Is there any
> built-in SQL Server function to do so?
> Thanks once again & thanks to Mike as well,
> Regards,
> Arpan
>
[url]http://www.math.grin.edu/~rebelsky/Courses/152/97F/Readings/student-binary.html[/u
rl]|||1) These are not queries; they are print statements..
2) Good programmers do not do proprietary, non-realtional, low-level
bits and bytes stuff in SQL.
You keep posting requests for kludges. Perhaps you ought to take the
time to learn RDBMS, data modeling, etc. Remember that it takes six
years to become a Union Journey Carpenter in New York State. Do not
expect to learn all of this by posting to Newsgroups -- all you will do
is collect kludges and become a danger to your employers. .|||Binary arithmetic - All right! Now, if someone will start a thread
about zone and digit punches, I will have come full circle.
Actually, this reminds me of my favorite puzzle only a computer g
(like me) could love. I stole it from Issac Asimov. It was in one of
his Black Widowers mysteries.
Given: Halloween = Christmas
Construct a proof.
----
I will shorten it.
Halloween = Christmas
December 25 = October 31
25dec = 31oct
Payson|||>> Given: Halloween = Christmas Construct a proof.
You already gave the hint there with the author. From The Family Man? For
those who are unfamiliar:
It is basically the distinction between base 10 Vs. base 8
December => DEC
October => OCT
Mathematically, DEC(imal) 25 = OCT(al) 31 or more to put it colloquially:
Christmas = Halloween
Anith
---
PRINT 15 & 75
PRINT 15 | 75
---
are 11 & 79 respectively. Can someone explain how does SQL Server
compute these values? I went through the topic 'Bitwise Operators' in
BOL but couldn't exactly comprehend the explanation & that's why I am
posting my query here.
Thanks,
Arpan"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124150426.712216.65110@.g14g2000cwa.googlegroups.com...
> The outcome of the following 2 simple queries
> ---
> PRINT 15 & 75
> PRINT 15 | 75
> ---
> are 11 & 79 respectively. Can someone explain how does SQL Server
> compute these values? I went through the topic 'Bitwise Operators' in
> BOL but couldn't exactly comprehend the explanation & that's why I am
> posting my query here.
> Thanks,
> Arpan
>
Bitwise OR : If either bit value is 1, then the resulting bit is 1, else 0
Bitwise AND : If either bit value is 0, then the resulting bit is 0, else 1
OR:
1001011 = 75
0001111 = 15
--
1001111 = 79
AND:
1001011 = 75
0001111 = 15
--
0001011 = 11|||15 & 75 =
(in binary)
00001111 &
01001011
--
00001011 = 11 (in decimal)
15 | 75 =
(in binary)
00001111 |
01001011
--
01001111 = 79 (in decimal)
SQL Server, from memory, just calculates these kind of binary ANDs and
ORs using twos complement arithmetic.
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Arpan wrote:
>The outcome of the following 2 simple queries
>---
>PRINT 15 & 75
>PRINT 15 | 75
>---
>are 11 & 79 respectively. Can someone explain how does SQL Server
>compute these values? I went through the topic 'Bitwise Operators' in
>BOL but couldn't exactly comprehend the explanation & that's why I am
>posting my query here.
>Thanks,
>Arpan
>
>|||Thanks, Chris, for your input but to be honest, I couldn't follow how
did you arrive at the values 1001111 & 0001011? What did you do with
the binary values of 75 & 15 to get to 79 and 11 & their respective
binary values? Please explain me this.
Also how does one find the binary value of a decimal number (of course,
other than using the Windows Scientific Calculator)? Is there any
built-in SQL Server function to do so?
Thanks once again & thanks to Mike as well,
Regards,
Arpan|||The binary number system: each bit represents a power of 2.
0 1
1 2
2 4
3 8
4 16
5 32
6 64
7 128
8 256
9 512
...
For fractions, each bit past the binary point:
-1 .5
-2 .25
-3 .125
-4 .0625
-5 .03125
-6 .015625
-7 .0078125
-8 .00390625
-9 .001953125
...
Each binary number is a combination of bits.
75decimal = 1001011
15decimal = 0001111
A bitwise AND performs a binary AND operation using the following table:
A B | A & B
--
0 0 | 0
0 1 | 0
1 0 | 0
1 1 | 1
for each bit in a binary number, thus
75decimal = 1001011
15decimal = 0001111
--
75 & 15 = 0001011 = 11 decimal
Notice that for each bit position which is 1 in the result, both bits in the
same position of each operand is one.
A bitwise OR performs a binary OR operation using the following table:
A B | A | B
--
0 0 | 0
0 1 | 1
1 0 | 1
1 1 | 1
for each bit in a binary number, thus
75decimal = 1001011
15decimal = 0001111
--
75 | 15 = 1001111 = 79 decimal
Notice that for each bit position which is 1 in the result, at least one bit
from the same position of either operand is one.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124153765.707009.29780@.g49g2000cwa.googlegroups.com...
> Thanks, Chris, for your input but to be honest, I couldn't follow how
> did you arrive at the values 1001111 & 0001011? What did you do with
> the binary values of 75 & 15 to get to 79 and 11 & their respective
> binary values? Please explain me this.
> Also how does one find the binary value of a decimal number (of course,
> other than using the Windows Scientific Calculator)? Is there any
> built-in SQL Server function to do so?
> Thanks once again & thanks to Mike as well,
> Regards,
> Arpan
>|||"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124153765.707009.29780@.g49g2000cwa.googlegroups.com...
> Thanks, Chris, for your input but to be honest, I couldn't follow how
> did you arrive at the values 1001111 & 0001011? What did you do with
> the binary values of 75 & 15 to get to 79 and 11 & their respective
> binary values? Please explain me this.
> Also how does one find the binary value of a decimal number (of course,
> other than using the Windows Scientific Calculator)? Is there any
> built-in SQL Server function to do so?
> Thanks once again & thanks to Mike as well,
> Regards,
> Arpan
>
[url]http://www.math.grin.edu/~rebelsky/Courses/152/97F/Readings/student-binary.html[/u
rl]|||1) These are not queries; they are print statements..
2) Good programmers do not do proprietary, non-realtional, low-level
bits and bytes stuff in SQL.
You keep posting requests for kludges. Perhaps you ought to take the
time to learn RDBMS, data modeling, etc. Remember that it takes six
years to become a Union Journey Carpenter in New York State. Do not
expect to learn all of this by posting to Newsgroups -- all you will do
is collect kludges and become a danger to your employers. .|||Binary arithmetic - All right! Now, if someone will start a thread
about zone and digit punches, I will have come full circle.
Actually, this reminds me of my favorite puzzle only a computer g
(like me) could love. I stole it from Issac Asimov. It was in one of
his Black Widowers mysteries.
Given: Halloween = Christmas
Construct a proof.
----
I will shorten it.
Halloween = Christmas
December 25 = October 31
25dec = 31oct
Payson|||>> Given: Halloween = Christmas Construct a proof.
You already gave the hint there with the author. From The Family Man? For
those who are unfamiliar:
It is basically the distinction between base 10 Vs. base 8
December => DEC
October => OCT
Mathematically, DEC(imal) 25 = OCT(al) 31 or more to put it colloquially:
Christmas = Halloween
Anith
Friday, February 10, 2012
Bit datatypes physical storage
Can anyone explain to me how a column defined with a "bit null" datatype is physically stored in MSSQL? Is it stored like a "tinyint null" physically? In other words, how many bytes on the row on the page does a "bit null" datatype consume (assuming a non-null value 0, or 1 is the current value).
Is there any good documentation about the physical storage layout for a data page?
Thanks -From SQL BOL: Microsoft SQL Server optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.A Null column does not consume any storage. SQL Server stores Nullable columns only if they exist. That's good unless they change frequently, then it can be bad.
In general, avoid Nulls as much as you possibly can for two reasons: Data Integrity
Reduced Page Splits|||i won't question point 2 because it seems to be another instance of DBA arcana (with all of which i am not familiar), but point 1 cannot go unchallenged
are you suggesting 6th normal form? because i have a real problem with that
and if not, then what are you suggesting? zeros instead of nulls in numeric columns, and empty strings in character columns? how does this improve data integrity? if anything, this decreases data integrity
or were you specifically referring to null bit fields only? because that wasn't clear (and doesn't make a lot of sense either)|||Ok, if I have 2 bit fields which are then stored in the same byte, and the first field is null, there must be a way that MSSQL knows that without thinking that the first bit of that byte which stores the bit fields is the first bit column value. I'm thinking that there is an offset table on the row, or some kind of null "indicator". Which is it? An offset table or an indicator?
If an offset table, then how are bit fields represented in it? If an indicator, then whoa, I'm lost on why I would choose such a datatype.|||Thanks for the challenges, r937. I'm not prone to making sense. My point is if a value is expected, then don't allow Nulls.
How often do you see DDL posted and all columns but the primary key are Nullable (just be happy there's a primary key, right?). I see it too often.
I like the aracana misconception - no secrets here as I'm a recovering pragmatist. However, I quesiton my ability to explain it succinctly and I'm burnt out on searching MSN for links that do the job for me.
But, I'll try for posterity.
Page Splits occur when SQL Server has to reorganize data on a page and Page Splits are one of the most expensive operations that SQL Server has to do. Updating a value from Null to something that Is Not Null may cause a Page Split on a page that is > 50% full (I'll leave the research up to you for determing the conditions under which SQL Server splits a page).
For a heavy OLTP system, it gets expensive. Thus, if you force users to enter values that are needed, you reduce the probability they'll have to go back and add the values later, thus reducing the probability of a Page Split.
Finally, my working mode is enterprise applications. So, I answer questions with the assumption the answer should apply to an enterprise architecture. I'm often amused how my assumptions lead to useless typing, but hey, I'll keep trying.
Which is it? An offset table or an indicator? Offset. Which led Microsoft to disallow Null bits in the beginning. They succumbed to pressure. Your seeking internals knowledge, and Microsoft has been relaxing their rules around providing it, but I'm not sure they've gone into any depth regarding offsets. I'd tell you more but I'd have to shoot you.
Hope this helps. I gotta Split.|||My point is if a value is expected, then don't allow Nulls.well, sure, if one knows what one's doing (although i would change the word "expected" to "required")
but that's a lot different than "avoid Nulls as much as you possibly can"
the latter advice will cause some people to use DEFAULT 0 and DEFAULT '' needlessly, wantonly, and, in most cases, flat out incorrectly
as far as all columns but PK being nullable, i myself like that idea :)
it allows the referential integrity action "ON INSERT CASCADE [to parent]" (yes, i made that up) whereby if you try to insert a child row with a foreign key for a parent that doesn't exist yet, it takes the foreign key value and uses it to insert a row with that value of the primary key in the parent table (so all other parent columns are therefore assigned NULL or get their defaults) before inserting the child row
would save writing a trigger
and don't say you haven't seen an application where this is a valid business rule ;)|||I was gonna leave this alone, but then I'm a pedant.
The world in which I live requires parents to exist for a child to exist. Now, that could just be my world and with the advent of cloning, it may quickly be invalidated and I'll have to move to a different world.
If you do the math on your interesting CASCADE [to parent] you will find that the parent insert should require knowing all the parent's attributes to do the insert. My interpretation of your proposition is that parent attributes are irrelevant. They're relevant in my world.
Where ya gonna put 'em and how ya gonna get them if you just ignored them?
I have not seen a valid business for which this type of an application would rule. :p|||Avoid nulls as much as you can?
Bullcrap! The value NULL provides information that simply cannot be provided by a default value, that being that absence of information. If a value is unknown, then the last thing I would want would be to have a value place their by default.
I'd classify that as bad data in a heartbeat.
Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."
There is nothing wrong with a table schema that allows many columns to be null. Are you going to prevent a user from entering a record with incomplete information just to satisfy the obsesive compulsions of an overly anal-retentive dba?
Ultimately, the business rules drive the design. If they do not require a value, then allow NULL.|||Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."Who's being anal-retentive?
Expecting values implies they are required. Saying as much as you can implies you are competent enough to know when you can't. I'll switch to explicit mode to avoid illogical conclusions.
I didn't say completely avoid Nulls, I said avoid Nulls as much as you can.
I agree - if a value is not required, then allow Null. But, avoid Nulls as much as you can.
I didn't say ignore them. I didn't say they don't mean anything even though their meaning is unknown. I said avoid Nulls as much as you can.
I didn't say mislead others with default values. I said avoid Nulls as much as you can.
If a value is expected, then don't allow Nulls. That is reality.
That's my story and I'm sticking to it.
Avoid Nulls as much as you can.|||Man, I love this. MaxA, - you rule!!!!
But looking back at my own designs, - I can tell thatt the better I understood the business requirements, the more complex but at the same time cleaner the final design looked like. And ironicly I did notice that fewer nulls were present. Offloading NULLable columns to a separate table always leads to more complex queries, but a LEFT OUTER JOIN is a LEFT OUTER JOIN, and if PKs/FKs are properly indexed, - you'll see a better performance as well...But that's just me ;)
EDITED: By better performance I mean that your table will naturally be theoretically wider when nullable columns are present, and at the time of optimizing your SELECT you have 2 choices: avoid selecting nullable columns, or include them into existing indexes to avoid bookmark lookups. But if you offload those columns into a separate table that will only contain records where the column is not null, it'll result in fewer records to go through and all indexes are used.|||Avoid NULLS as much as you can? That is just a tautology. If you are saying not to allow NULLS when a value is required, then I completely agree with you.
How about this statement: "Avoid making a field required as much as you can."
I didn't say completely avoid Non-Nulls, I said avoid Non-Nulls as much as you can.
I agree - if a value is required, then disallow Null. But, avoid Non-Nulls as much as you can.
I didn't say ignore Non-Nulls. I didn't say they should be used even when a value is unknown. I said avoid Non-Nulls as much as you can.
If a value is not required, then allow Non-Nulls. That is reality.
That's my story and I'm sticking to it.
Avoid Non-Nulls as much as you can.|||Oh that's just plagiarism, Mr. Lindman, come up with your own style! ;)|||Plagiarism IS a style. Just look at Hollywood or the Grammy Awards! ;)
"Plagiarism is the sincerest form of flattery".
I forget who originally said that...|||Apparently Charles Caleb Colton:
http://www.quotationspage.com/quote/27484.html
I just don't think he has ever gotten enough credit for his work, do you?
Is there any good documentation about the physical storage layout for a data page?
Thanks -From SQL BOL: Microsoft SQL Server optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.A Null column does not consume any storage. SQL Server stores Nullable columns only if they exist. That's good unless they change frequently, then it can be bad.
In general, avoid Nulls as much as you possibly can for two reasons: Data Integrity
Reduced Page Splits|||i won't question point 2 because it seems to be another instance of DBA arcana (with all of which i am not familiar), but point 1 cannot go unchallenged
are you suggesting 6th normal form? because i have a real problem with that
and if not, then what are you suggesting? zeros instead of nulls in numeric columns, and empty strings in character columns? how does this improve data integrity? if anything, this decreases data integrity
or were you specifically referring to null bit fields only? because that wasn't clear (and doesn't make a lot of sense either)|||Ok, if I have 2 bit fields which are then stored in the same byte, and the first field is null, there must be a way that MSSQL knows that without thinking that the first bit of that byte which stores the bit fields is the first bit column value. I'm thinking that there is an offset table on the row, or some kind of null "indicator". Which is it? An offset table or an indicator?
If an offset table, then how are bit fields represented in it? If an indicator, then whoa, I'm lost on why I would choose such a datatype.|||Thanks for the challenges, r937. I'm not prone to making sense. My point is if a value is expected, then don't allow Nulls.
How often do you see DDL posted and all columns but the primary key are Nullable (just be happy there's a primary key, right?). I see it too often.
I like the aracana misconception - no secrets here as I'm a recovering pragmatist. However, I quesiton my ability to explain it succinctly and I'm burnt out on searching MSN for links that do the job for me.
But, I'll try for posterity.
Page Splits occur when SQL Server has to reorganize data on a page and Page Splits are one of the most expensive operations that SQL Server has to do. Updating a value from Null to something that Is Not Null may cause a Page Split on a page that is > 50% full (I'll leave the research up to you for determing the conditions under which SQL Server splits a page).
For a heavy OLTP system, it gets expensive. Thus, if you force users to enter values that are needed, you reduce the probability they'll have to go back and add the values later, thus reducing the probability of a Page Split.
Finally, my working mode is enterprise applications. So, I answer questions with the assumption the answer should apply to an enterprise architecture. I'm often amused how my assumptions lead to useless typing, but hey, I'll keep trying.
Which is it? An offset table or an indicator? Offset. Which led Microsoft to disallow Null bits in the beginning. They succumbed to pressure. Your seeking internals knowledge, and Microsoft has been relaxing their rules around providing it, but I'm not sure they've gone into any depth regarding offsets. I'd tell you more but I'd have to shoot you.
Hope this helps. I gotta Split.|||My point is if a value is expected, then don't allow Nulls.well, sure, if one knows what one's doing (although i would change the word "expected" to "required")
but that's a lot different than "avoid Nulls as much as you possibly can"
the latter advice will cause some people to use DEFAULT 0 and DEFAULT '' needlessly, wantonly, and, in most cases, flat out incorrectly
as far as all columns but PK being nullable, i myself like that idea :)
it allows the referential integrity action "ON INSERT CASCADE [to parent]" (yes, i made that up) whereby if you try to insert a child row with a foreign key for a parent that doesn't exist yet, it takes the foreign key value and uses it to insert a row with that value of the primary key in the parent table (so all other parent columns are therefore assigned NULL or get their defaults) before inserting the child row
would save writing a trigger
and don't say you haven't seen an application where this is a valid business rule ;)|||I was gonna leave this alone, but then I'm a pedant.
The world in which I live requires parents to exist for a child to exist. Now, that could just be my world and with the advent of cloning, it may quickly be invalidated and I'll have to move to a different world.
If you do the math on your interesting CASCADE [to parent] you will find that the parent insert should require knowing all the parent's attributes to do the insert. My interpretation of your proposition is that parent attributes are irrelevant. They're relevant in my world.
Where ya gonna put 'em and how ya gonna get them if you just ignored them?
I have not seen a valid business for which this type of an application would rule. :p|||Avoid nulls as much as you can?
Bullcrap! The value NULL provides information that simply cannot be provided by a default value, that being that absence of information. If a value is unknown, then the last thing I would want would be to have a value place their by default.
I'd classify that as bad data in a heartbeat.
Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."
There is nothing wrong with a table schema that allows many columns to be null. Are you going to prevent a user from entering a record with incomplete information just to satisfy the obsesive compulsions of an overly anal-retentive dba?
Ultimately, the business rules drive the design. If they do not require a value, then allow NULL.|||Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."Who's being anal-retentive?
Expecting values implies they are required. Saying as much as you can implies you are competent enough to know when you can't. I'll switch to explicit mode to avoid illogical conclusions.
I didn't say completely avoid Nulls, I said avoid Nulls as much as you can.
I agree - if a value is not required, then allow Null. But, avoid Nulls as much as you can.
I didn't say ignore them. I didn't say they don't mean anything even though their meaning is unknown. I said avoid Nulls as much as you can.
I didn't say mislead others with default values. I said avoid Nulls as much as you can.
If a value is expected, then don't allow Nulls. That is reality.
That's my story and I'm sticking to it.
Avoid Nulls as much as you can.|||Man, I love this. MaxA, - you rule!!!!
But looking back at my own designs, - I can tell thatt the better I understood the business requirements, the more complex but at the same time cleaner the final design looked like. And ironicly I did notice that fewer nulls were present. Offloading NULLable columns to a separate table always leads to more complex queries, but a LEFT OUTER JOIN is a LEFT OUTER JOIN, and if PKs/FKs are properly indexed, - you'll see a better performance as well...But that's just me ;)
EDITED: By better performance I mean that your table will naturally be theoretically wider when nullable columns are present, and at the time of optimizing your SELECT you have 2 choices: avoid selecting nullable columns, or include them into existing indexes to avoid bookmark lookups. But if you offload those columns into a separate table that will only contain records where the column is not null, it'll result in fewer records to go through and all indexes are used.|||Avoid NULLS as much as you can? That is just a tautology. If you are saying not to allow NULLS when a value is required, then I completely agree with you.
How about this statement: "Avoid making a field required as much as you can."
I didn't say completely avoid Non-Nulls, I said avoid Non-Nulls as much as you can.
I agree - if a value is required, then disallow Null. But, avoid Non-Nulls as much as you can.
I didn't say ignore Non-Nulls. I didn't say they should be used even when a value is unknown. I said avoid Non-Nulls as much as you can.
If a value is not required, then allow Non-Nulls. That is reality.
That's my story and I'm sticking to it.
Avoid Non-Nulls as much as you can.|||Oh that's just plagiarism, Mr. Lindman, come up with your own style! ;)|||Plagiarism IS a style. Just look at Hollywood or the Grammy Awards! ;)
"Plagiarism is the sincerest form of flattery".
I forget who originally said that...|||Apparently Charles Caleb Colton:
http://www.quotationspage.com/quote/27484.html
I just don't think he has ever gotten enough credit for his work, do you?
Subscribe to:
Posts (Atom)