November 1, 2005 at 1:16 pm
When you have an int datatype where the value is null, does that take up the same amount of physical space as when the field actually contains an int value.
My gut feeling was always that when the row is created, SQL Server allocated 4bytes no matter what...but now I'm faced with a design question....to either normalize to save null values or to have more fields and cut down on table outer joins.....
If nulls take up less space then I would opt to eliminate the joins for performace reasons.
November 1, 2005 at 2:11 pm
The amount of space used for a fixed length datatype, such as integer or char(?), is the same amount of space whether it is null or not null. See below for a test script that has been run versus SQL Server 7 & 2000.
I believe that SQL Server 6.5 did use different amounts of space depending on whether a row column was null or not null.
use tempdb
go
CREATE TABLE dbo.FooNumberNull
( FooNumberKey int NOT NULL -- 100 integer columns that are null
, c1 int NULL , c2 int NULL , c3 int NULL , c4 int NULL , c5 int NULL , c6 int NULL , c7 int NULL , c8 int NULL , c9 int NULL , c10 int NULL , c11 int NULL , c12 int NULL , c13 int NULL , c14 int NULL , c15 int NULL , c16 int NULL , c17 int NULL , c18 int NULL , c19 int NULL , c20 int NULL , c21 int NULL , c22 int NULL , c23 int NULL , c24 int NULL , c25 int NULL , c26 int NULL , c27 int NULL , c28 int NULL , c29 int NULL , c30 int NULL , c31 int NULL , c32 int NULL , c33 int NULL , c34 int NULL , c35 int NULL , c36 int NULL , c37 int NULL , c38 int NULL , c39 int NULL , c40 int NULL , c41 int NULL , c42 int NULL , c43 int NULL , c44 int NULL , c45 int NULL , c46 int NULL , c47 int NULL , c48 int NULL , c49 int NULL , c50 int NULL , c51 int NULL , c52 int NULL , c53 int NULL , c54 int NULL , c55 int NULL , c56 int NULL , c57 int NULL , c58 int NULL , c59 int NULL , c60 int NULL , c61 int NULL , c62 int NULL , c63 int NULL , c64 int NULL , c65 int NULL , c66 int NULL , c67 int NULL , c68 int NULL , c69 int NULL , c70 int NULL , c71 int NULL , c72 int NULL , c73 int NULL , c74 int NULL , c75 int NULL , c76 int NULL , c77 int NULL , c78 int NULL , c79 int NULL , c80 int NULL , c81 int NULL , c82 int NULL , c83 int NULL , c84 int NULL , c85 int NULL , c86 int NULL , c87 int NULL , c88 int NULL , c89 int NULL , c90 int NULL , c91 int NULL , c92 int NULL , c93 int NULL , c94 int NULL , c95 int NULL , c96 int NULL , c97 int NULL , c98 int NULL , c99 int NULL )
go
CREATE TABLE dbo.FooCharNull
( FooCharKey int NOT NULL -- 100 integer columns that are null
, c1 char(20) null , c2 char(20) null , c3 char(20) null , c4 char(20) null , c5 char(20) null , c6 char(20) null , c7 char(20) null , c8 char(20) null , c9 char(20) null , c10 char(20) null , c11 char(20) null , c12 char(20) null , c13 char(20) null , c14 char(20) null , c15 char(20) null , c16 char(20) null , c17 char(20) null , c18 char(20) null , c19 char(20) null , c20 char(20) null , c21 char(20) null , c22 char(20) null , c23 char(20) null , c24 char(20) null , c25 char(20) null , c26 char(20) null , c27 char(20) null , c28 char(20) null , c29 char(20) null , c30 char(20) null , c31 char(20) null , c32 char(20) null , c33 char(20) null , c34 char(20) null , c35 char(20) null , c36 char(20) null , c37 char(20) null , c38 char(20) null , c39 char(20) null , c40 char(20) null , c41 char(20) null , c42 char(20) null , c43 char(20) null , c44 char(20) null , c45 char(20) null , c46 char(20) null , c47 char(20) null , c48 char(20) null , c49 char(20) null , c50 char(20) null , c51 char(20) null , c52 char(20) null , c53 char(20) null , c54 char(20) null , c55 char(20) null , c56 char(20) null , c57 char(20) null , c58 char(20) null , c59 char(20) null , c60 char(20) null , c61 char(20) null , c62 char(20) null , c63 char(20) null , c64 char(20) null , c65 char(20) null , c66 char(20) null , c67 char(20) null , c68 char(20) null , c69 char(20) null , c70 char(20) null , c71 char(20) null , c72 char(20) null , c73 char(20) null , c74 char(20) null , c75 char(20) null , c76 char(20) null , c77 char(20) null , c78 char(20) null , c79 char(20) null , c80 char(20) null , c81 char(20) null , c82 char(20) null , c83 char(20) null , c84 char(20) null , c85 char(20) null , c86 char(20) null , c87 char(20) null , c88 char(20) null , c89 char(20) null , c90 char(20) null , c91 char(20) null , c92 char(20) null , c93 char(20) null , c94 char(20) null , c95 char(20) null , c96 char(20) null , c97 char(20) null , c98 char(20) null , c99 char(20) null )
go
CREATE TABLE dbo.FooVarCharNull
( FooVarCharKey int NOT NULL -- 100 integer columns that are null
, c1 varchar(20) null , c2 varchar(20) null , c3 varchar(20) null , c4 varchar(20) null , c5 varchar(20) null , c6 varchar(20) null , c7 varchar(20) null , c8 varchar(20) null , c9 varchar(20) null , c10 varchar(20) null , c11 varchar(20) null , c12 varchar(20) null , c13 varchar(20) null , c14 varchar(20) null , c15 varchar(20) null , c16 varchar(20) null , c17 varchar(20) null , c18 varchar(20) null , c19 varchar(20) null , c20 varchar(20) null , c21 varchar(20) null , c22 varchar(20) null , c23 varchar(20) null , c24 varchar(20) null , c25 varchar(20) null , c26 varchar(20) null , c27 varchar(20) null , c28 varchar(20) null , c29 varchar(20) null , c30 varchar(20) null , c31 varchar(20) null , c32 varchar(20) null , c33 varchar(20) null , c34 varchar(20) null , c35 varchar(20) null , c36 varchar(20) null , c37 varchar(20) null , c38 varchar(20) null , c39 varchar(20) null , c40 varchar(20) null , c41 varchar(20) null , c42 varchar(20) null , c43 varchar(20) null , c44 varchar(20) null , c45 varchar(20) null , c46 varchar(20) null , c47 varchar(20) null , c48 varchar(20) null , c49 varchar(20) null , c50 varchar(20) null , c51 varchar(20) null , c52 varchar(20) null , c53 varchar(20) null , c54 varchar(20) null , c55 varchar(20) null , c56 varchar(20) null , c57 varchar(20) null , c58 varchar(20) null , c59 varchar(20) null , c60 varchar(20) null , c61 varchar(20) null , c62 varchar(20) null , c63 varchar(20) null , c64 varchar(20) null , c65 varchar(20) null , c66 varchar(20) null , c67 varchar(20) null , c68 varchar(20) null , c69 varchar(20) null , c70 varchar(20) null , c71 varchar(20) null , c72 varchar(20) null , c73 varchar(20) null , c74 varchar(20) null , c75 varchar(20) null , c76 varchar(20) null , c77 varchar(20) null , c78 varchar(20) null , c79 varchar(20) null , c80 varchar(20) null , c81 varchar(20) null , c82 varchar(20) null , c83 varchar(20) null , c84 varchar(20) null , c85 varchar(20) null , c86 varchar(20) null , c87 varchar(20) null , c88 varchar(20) null , c89 varchar(20) null , c90 varchar(20) null , c91 varchar(20) null , c92 varchar(20) null , c93 varchar(20) null , c94 varchar(20) null , c95 varchar(20) null , c96 varchar(20) null , c97 varchar(20) null , c98 varchar(20) null , c99 varchar(20) null )
go
CREATE TABLE dbo.FooNumberNotNull
( FooNumberKey int NOT null -- 100 integer columns that are not null
, c1 int not null default(1) , c2 int not null default(1) , c3 int not null default(1) , c4 int not null default(1) , c5 int not null default(1) , c6 int not null default(1) , c7 int not null default(1) , c8 int not null default(1) , c9 int not null default(1) , c10 int not null default(1) , c11 int not null default(1) , c12 int not null default(1) , c13 int not null default(1) , c14 int not null default(1) , c15 int not null default(1) , c16 int not null default(1) , c17 int not null default(1) , c18 int not null default(1) , c19 int not null default(1) , c20 int not null default(1) , c21 int not null default(1) , c22 int not null default(1) , c23 int not null default(1) , c24 int not null default(1) , c25 int not null default(1) , c26 int not null default(1) , c27 int not null default(1) , c28 int not null default(1) , c29 int not null default(1) , c30 int not null default(1) , c31 int not null default(1) , c32 int not null default(1) , c33 int not null default(1) , c34 int not null default(1) , c35 int not null default(1) , c36 int not null default(1) , c37 int not null default(1) , c38 int not null default(1) , c39 int not null default(1) , c40 int not null default(1) , c41 int not null default(1) , c42 int not null default(1) , c43 int not null default(1) , c44 int not null default(1) , c45 int not null default(1) , c46 int not null default(1) , c47 int not null default(1) , c48 int not null default(1) , c49 int not null default(1) , c50 int not null default(1) , c51 int not null default(1) , c52 int not null default(1) , c53 int not null default(1) , c54 int not null default(1) , c55 int not null default(1) , c56 int not null default(1) , c57 int not null default(1) , c58 int not null default(1) , c59 int not null default(1) , c60 int not null default(1) , c61 int not null default(1) , c62 int not null default(1) , c63 int not null default(1) , c64 int not null default(1) , c65 int not null default(1) , c66 int not null default(1) , c67 int not null default(1) , c68 int not null default(1) , c69 int not null default(1) , c70 int not null default(1) , c71 int not null default(1) , c72 int not null default(1) , c73 int not null default(1) , c74 int not null default(1) , c75 int not null default(1) , c76 int not null default(1) , c77 int not null default(1) , c78 int not null default(1) , c79 int not null default(1) , c80 int not null default(1) , c81 int not null default(1) , c82 int not null default(1) , c83 int not null default(1) , c84 int not null default(1) , c85 int not null default(1) , c86 int not null default(1) , c87 int not null default(1) , c88 int not null default(1) , c89 int not null default(1) , c90 int not null default(1) , c91 int not null default(1) , c92 int not null default(1) , c93 int not null default(1) , c94 int not null default(1) , c95 int not null default(1) , c96 int not null default(1) , c97 int not null default(1) , c98 int not null default(1) , c99 int not null default(1))
go
CREATE TABLE dbo.FooCharNotNull
( FooCharKey int NOT null -- 100 integer columns that are not null
, c1 char(20) not null default('1') , c2 char(20) not null default('1') , c3 char(20) not null default('1') , c4 char(20) not null default('1') , c5 char(20) not null default('1') , c6 char(20) not null default('1') , c7 char(20) not null default('1') , c8 char(20) not null default('1') , c9 char(20) not null default('1') , c10 char(20) not null default('1') , c11 char(20) not null default('1') , c12 char(20) not null default('1') , c13 char(20) not null default('1') , c14 char(20) not null default('1') , c15 char(20) not null default('1') , c16 char(20) not null default('1') , c17 char(20) not null default('1') , c18 char(20) not null default('1') , c19 char(20) not null default('1') , c20 char(20) not null default('1') , c21 char(20) not null default('1') , c22 char(20) not null default('1') , c23 char(20) not null default('1') , c24 char(20) not null default('1') , c25 char(20) not null default('1') , c26 char(20) not null default('1') , c27 char(20) not null default('1') , c28 char(20) not null default('1') , c29 char(20) not null default('1') , c30 char(20) not null default('1') , c31 char(20) not null default('1') , c32 char(20) not null default('1') , c33 char(20) not null default('1') , c34 char(20) not null default('1') , c35 char(20) not null default('1') , c36 char(20) not null default('1') , c37 char(20) not null default('1') , c38 char(20) not null default('1') , c39 char(20) not null default('1') , c40 char(20) not null default('1') , c41 char(20) not null default('1') , c42 char(20) not null default('1') , c43 char(20) not null default('1') , c44 char(20) not null default('1') , c45 char(20) not null default('1') , c46 char(20) not null default('1') , c47 char(20) not null default('1') , c48 char(20) not null default('1') , c49 char(20) not null default('1') , c50 char(20) not null default('1') , c51 char(20) not null default('1') , c52 char(20) not null default('1') , c53 char(20) not null default('1') , c54 char(20) not null default('1') , c55 char(20) not null default('1') , c56 char(20) not null default('1') , c57 char(20) not null default('1') , c58 char(20) not null default('1') , c59 char(20) not null default('1') , c60 char(20) not null default('1') , c61 char(20) not null default('1') , c62 char(20) not null default('1') , c63 char(20) not null default('1') , c64 char(20) not null default('1') , c65 char(20) not null default('1') , c66 char(20) not null default('1') , c67 char(20) not null default('1') , c68 char(20) not null default('1') , c69 char(20) not null default('1') , c70 char(20) not null default('1') , c71 char(20) not null default('1') , c72 char(20) not null default('1') , c73 char(20) not null default('1') , c74 char(20) not null default('1') , c75 char(20) not null default('1') , c76 char(20) not null default('1') , c77 char(20) not null default('1') , c78 char(20) not null default('1') , c79 char(20) not null default('1') , c80 char(20) not null default('1') , c81 char(20) not null default('1') , c82 char(20) not null default('1') , c83 char(20) not null default('1') , c84 char(20) not null default('1') , c85 char(20) not null default('1') , c86 char(20) not null default('1') , c87 char(20) not null default('1') , c88 char(20) not null default('1') , c89 char(20) not null default('1') , c90 char(20) not null default('1') , c91 char(20) not null default('1') , c92 char(20) not null default('1') , c93 char(20) not null default('1') , c94 char(20) not null default('1') , c95 char(20) not null default('1') , c96 char(20) not null default('1') , c97 char(20) not null default('1') , c98 char(20) not null default('1') , c99 char(20) not null default('1') )
go
CREATE TABLE dbo.FooVarCharNotNull
( FooVarCharKey int NOT null -- 100 integer columns that are not null
, c1 varchar(20) not null default('1') , c2 varchar(20) not null default('1') , c3 varchar(20) not null default('1') , c4 varchar(20) not null default('1') , c5 varchar(20) not null default('1') , c6 varchar(20) not null default('1') , c7 varchar(20) not null default('1') , c8 varchar(20) not null default('1') , c9 varchar(20) not null default('1') , c10 varchar(20) not null default('1') , c11 varchar(20) not null default('1') , c12 varchar(20) not null default('1') , c13 varchar(20) not null default('1') , c14 varchar(20) not null default('1') , c15 varchar(20) not null default('1') , c16 varchar(20) not null default('1') , c17 varchar(20) not null default('1') , c18 varchar(20) not null default('1') , c19 varchar(20) not null default('1') , c20 varchar(20) not null default('1') , c21 varchar(20) not null default('1') , c22 varchar(20) not null default('1') , c23 varchar(20) not null default('1') , c24 varchar(20) not null default('1') , c25 varchar(20) not null default('1') , c26 varchar(20) not null default('1') , c27 varchar(20) not null default('1') , c28 varchar(20) not null default('1') , c29 varchar(20) not null default('1') , c30 varchar(20) not null default('1') , c31 varchar(20) not null default('1') , c32 varchar(20) not null default('1') , c33 varchar(20) not null default('1') , c34 varchar(20) not null default('1') , c35 varchar(20) not null default('1') , c36 varchar(20) not null default('1') , c37 varchar(20) not null default('1') , c38 varchar(20) not null default('1') , c39 varchar(20) not null default('1') , c40 varchar(20) not null default('1') , c41 varchar(20) not null default('1') , c42 varchar(20) not null default('1') , c43 varchar(20) not null default('1') , c44 varchar(20) not null default('1') , c45 varchar(20) not null default('1') , c46 varchar(20) not null default('1') , c47 varchar(20) not null default('1') , c48 varchar(20) not null default('1') , c49 varchar(20) not null default('1') , c50 varchar(20) not null default('1') , c51 varchar(20) not null default('1') , c52 varchar(20) not null default('1') , c53 varchar(20) not null default('1') , c54 varchar(20) not null default('1') , c55 varchar(20) not null default('1') , c56 varchar(20) not null default('1') , c57 varchar(20) not null default('1') , c58 varchar(20) not null default('1') , c59 varchar(20) not null default('1') , c60 varchar(20) not null default('1') , c61 varchar(20) not null default('1') , c62 varchar(20) not null default('1') , c63 varchar(20) not null default('1') , c64 varchar(20) not null default('1') , c65 varchar(20) not null default('1') , c66 varchar(20) not null default('1') , c67 varchar(20) not null default('1') , c68 varchar(20) not null default('1') , c69 varchar(20) not null default('1') , c70 varchar(20) not null default('1') , c71 varchar(20) not null default('1') , c72 varchar(20) not null default('1') , c73 varchar(20) not null default('1') , c74 varchar(20) not null default('1') , c75 varchar(20) not null default('1') , c76 varchar(20) not null default('1') , c77 varchar(20) not null default('1') , c78 varchar(20) not null default('1') , c79 varchar(20) not null default('1') , c80 varchar(20) not null default('1') , c81 varchar(20) not null default('1') , c82 varchar(20) not null default('1') , c83 varchar(20) not null default('1') , c84 varchar(20) not null default('1') , c85 varchar(20) not null default('1') , c86 varchar(20) not null default('1') , c87 varchar(20) not null default('1') , c88 varchar(20) not null default('1') , c89 varchar(20) not null default('1') , c90 varchar(20) not null default('1') , c91 varchar(20) not null default('1') , c92 varchar(20) not null default('1') , c93 varchar(20) not null default('1') , c94 varchar(20) not null default('1') , c95 varchar(20) not null default('1') , c96 varchar(20) not null default('1') , c97 varchar(20) not null default('1') , c98 varchar(20) not null default('1') , c99 varchar(20) not null default('1') )
go
declare @Iinteger
set@I = 0
while @I < 1020
begin
set@I = 1 + @I
insert into FooNumberNull (FooNumberKey) values (@I)
insert into FooCharNull (FooCharKey) values (@I)
insert into FooVarCharNull (FooVarCharKey) values (@I)
insert into FooNumberNotNull (FooNumberKey) values (@I)
insert into FooCharNotNull (FooCharKey) values (@I)
insert into FooVarCharNotNull (FooVarCharKey) values (@I)
end
exec sp_spaceused 'FooNumberNull' , 'True'
exec sp_spaceused 'FooNumberNotNull' , 'True'
exec sp_spaceused 'FooCharNull' , 'True'
exec sp_spaceused 'FooCharNotNull' , 'True'
exec sp_spaceused 'FooVarCharNull' , 'True'
exec sp_spaceused 'FooVarCharNotNull' , 'True'
go
/*
drop table FooNumberNull
drop table FooNumberNotNull
drop table FooCharNull
drop table FooCharNotNull
drop table FooVarCharNull
drop table FooVarCharNotNull
*/
SQL = Scarcely Qualifies as a Language
November 1, 2005 at 2:22 pm
Awesome. Not only did I get a good answer but empirical evidence to back it!
Thank you much!
November 1, 2005 at 2:30 pm
"but now I'm faced with a design question....to either normalize to save null values or to have more fields and cut down on table outer joins"
For flexibility, a view could create some indepence from the one or two table alternatives if all activity was thru the view.
If you start with one table, then the view is nothing more than select from .
If you later determine that you do need to split into two tables, you could then change the view and add an "instead of" triggers on the view to handle inserts, updates, or deletes to the underlying tables.
Interestingly, if you have two tables, but do not select from the view any columns of the outer joined table, the outer joined tableis not accessed. See below for a test case.
alter TABLE dbo.FooNumberNotNull add constraint FooNumberNotNull_p primary key ( FooNumberKey )
go
alter TABLE dbo.FooNumberNull add constraint FooNumberNull_p primary key ( FooNumberKey )
go
create view FooNumber
(FooNumberNotNull_FooNumberKey
, FooNumberNotNull_c1 , FooNumberNotNull_c2 , FooNumberNotNull_c3 , FooNumberNotNull_c4 , FooNumberNotNull_c5 , FooNumberNotNull_c6 , FooNumberNotNull_c7 , FooNumberNotNull_c8 , FooNumberNotNull_c9 , FooNumberNotNull_c10 , FooNumberNotNull_c11 , FooNumberNotNull_c12 , FooNumberNotNull_c13 , FooNumberNotNull_c14 , FooNumberNotNull_c15 , FooNumberNotNull_c16 , FooNumberNotNull_c17 , FooNumberNotNull_c18 , FooNumberNotNull_c19 , FooNumberNotNull_c20 , FooNumberNotNull_c21 , FooNumberNotNull_c22 , FooNumberNotNull_c23 , FooNumberNotNull_c24 , FooNumberNotNull_c25 , FooNumberNotNull_c26 , FooNumberNotNull_c27 , FooNumberNotNull_c28 , FooNumberNotNull_c29 , FooNumberNotNull_c30 , FooNumberNotNull_c31 , FooNumberNotNull_c32 , FooNumberNotNull_c33 , FooNumberNotNull_c34 , FooNumberNotNull_c35 , FooNumberNotNull_c36 , FooNumberNotNull_c37 , FooNumberNotNull_c38 , FooNumberNotNull_c39 , FooNumberNotNull_c40 , FooNumberNotNull_c41 , FooNumberNotNull_c42 , FooNumberNotNull_c43 , FooNumberNotNull_c44 , FooNumberNotNull_c45 , FooNumberNotNull_c46 , FooNumberNotNull_c47 , FooNumberNotNull_c48 , FooNumberNotNull_c49 , FooNumberNotNull_c50 , FooNumberNotNull_c51 , FooNumberNotNull_c52 , FooNumberNotNull_c53 , FooNumberNotNull_c54 , FooNumberNotNull_c55 , FooNumberNotNull_c56 , FooNumberNotNull_c57 , FooNumberNotNull_c58 , FooNumberNotNull_c59 , FooNumberNotNull_c60 , FooNumberNotNull_c61 , FooNumberNotNull_c62 , FooNumberNotNull_c63 , FooNumberNotNull_c64 , FooNumberNotNull_c65 , FooNumberNotNull_c66 , FooNumberNotNull_c67 , FooNumberNotNull_c68 , FooNumberNotNull_c69 , FooNumberNotNull_c70 , FooNumberNotNull_c71 , FooNumberNotNull_c72 , FooNumberNotNull_c73 , FooNumberNotNull_c74 , FooNumberNotNull_c75 , FooNumberNotNull_c76 , FooNumberNotNull_c77 , FooNumberNotNull_c78 , FooNumberNotNull_c79 , FooNumberNotNull_c80 , FooNumberNotNull_c81 , FooNumberNotNull_c82 , FooNumberNotNull_c83 , FooNumberNotNull_c84 , FooNumberNotNull_c85 , FooNumberNotNull_c86 , FooNumberNotNull_c87 , FooNumberNotNull_c88 , FooNumberNotNull_c89 , FooNumberNotNull_c90 , FooNumberNotNull_c91 , FooNumberNotNull_c92 , FooNumberNotNull_c93 , FooNumberNotNull_c94 , FooNumberNotNull_c95 , FooNumberNotNull_c96 , FooNumberNotNull_c97 , FooNumberNotNull_c98 , FooNumberNotNull_c99
, FooNumberNull_c1 , FooNumberNull_c2 , FooNumberNull_c3 , FooNumberNull_c4 , FooNumberNull_c5 , FooNumberNull_c6 , FooNumberNull_c7 , FooNumberNull_c8 , FooNumberNull_c9 , FooNumberNull_c10 , FooNumberNull_c11 , FooNumberNull_c12 , FooNumberNull_c13 , FooNumberNull_c14 , FooNumberNull_c15 , FooNumberNull_c16 , FooNumberNull_c17 , FooNumberNull_c18 , FooNumberNull_c19 , FooNumberNull_c20 , FooNumberNull_c21 , FooNumberNull_c22 , FooNumberNull_c23 , FooNumberNull_c24 , FooNumberNull_c25 , FooNumberNull_c26 , FooNumberNull_c27 , FooNumberNull_c28 , FooNumberNull_c29 , FooNumberNull_c30 , FooNumberNull_c31 , FooNumberNull_c32 , FooNumberNull_c33 , FooNumberNull_c34 , FooNumberNull_c35 , FooNumberNull_c36 , FooNumberNull_c37 , FooNumberNull_c38 , FooNumberNull_c39 , FooNumberNull_c40 , FooNumberNull_c41 , FooNumberNull_c42 , FooNumberNull_c43 , FooNumberNull_c44 , FooNumberNull_c45 , FooNumberNull_c46 , FooNumberNull_c47 , FooNumberNull_c48 , FooNumberNull_c49 , FooNumberNull_c50 , FooNumberNull_c51 , FooNumberNull_c52 , FooNumberNull_c53 , FooNumberNull_c54 , FooNumberNull_c55 , FooNumberNull_c56 , FooNumberNull_c57 , FooNumberNull_c58 , FooNumberNull_c59 , FooNumberNull_c60 , FooNumberNull_c61 , FooNumberNull_c62 , FooNumberNull_c63 , FooNumberNull_c64 , FooNumberNull_c65 , FooNumberNull_c66 , FooNumberNull_c67 , FooNumberNull_c68 , FooNumberNull_c69 , FooNumberNull_c70 , FooNumberNull_c71 , FooNumberNull_c72 , FooNumberNull_c73 , FooNumberNull_c74 , FooNumberNull_c75 , FooNumberNull_c76 , FooNumberNull_c77 , FooNumberNull_c78 , FooNumberNull_c79 , FooNumberNull_c80 , FooNumberNull_c81 , FooNumberNull_c82 , FooNumberNull_c83 , FooNumberNull_c84 , FooNumberNull_c85 , FooNumberNull_c86 , FooNumberNull_c87 , FooNumberNull_c88 , FooNumberNull_c89 , FooNumberNull_c90 , FooNumberNull_c91 , FooNumberNull_c92 , FooNumberNull_c93 , FooNumberNull_c94 , FooNumberNull_c95 , FooNumberNull_c96 , FooNumberNull_c97 , FooNumberNull_c98 , FooNumberNull_c99 )
as
select FooNumberNotNull.FooNumberKey
, FooNumberNotNull.c1 , FooNumberNotNull.c2 , FooNumberNotNull.c3 , FooNumberNotNull.c4 , FooNumberNotNull.c5 , FooNumberNotNull.c6 , FooNumberNotNull.c7 , FooNumberNotNull.c8 , FooNumberNotNull.c9 , FooNumberNotNull.c10 , FooNumberNotNull.c11 , FooNumberNotNull.c12 , FooNumberNotNull.c13 , FooNumberNotNull.c14 , FooNumberNotNull.c15 , FooNumberNotNull.c16 , FooNumberNotNull.c17 , FooNumberNotNull.c18 , FooNumberNotNull.c19 , FooNumberNotNull.c20 , FooNumberNotNull.c21 , FooNumberNotNull.c22 , FooNumberNotNull.c23 , FooNumberNotNull.c24 , FooNumberNotNull.c25 , FooNumberNotNull.c26 , FooNumberNotNull.c27 , FooNumberNotNull.c28 , FooNumberNotNull.c29 , FooNumberNotNull.c30 , FooNumberNotNull.c31 , FooNumberNotNull.c32 , FooNumberNotNull.c33 , FooNumberNotNull.c34 , FooNumberNotNull.c35 , FooNumberNotNull.c36 , FooNumberNotNull.c37 , FooNumberNotNull.c38 , FooNumberNotNull.c39 , FooNumberNotNull.c40 , FooNumberNotNull.c41 , FooNumberNotNull.c42 , FooNumberNotNull.c43 , FooNumberNotNull.c44 , FooNumberNotNull.c45 , FooNumberNotNull.c46 , FooNumberNotNull.c47 , FooNumberNotNull.c48 , FooNumberNotNull.c49 , FooNumberNotNull.c50 , FooNumberNotNull.c51 , FooNumberNotNull.c52 , FooNumberNotNull.c53 , FooNumberNotNull.c54 , FooNumberNotNull.c55 , FooNumberNotNull.c56 , FooNumberNotNull.c57 , FooNumberNotNull.c58 , FooNumberNotNull.c59 , FooNumberNotNull.c60 , FooNumberNotNull.c61 , FooNumberNotNull.c62 , FooNumberNotNull.c63 , FooNumberNotNull.c64 , FooNumberNotNull.c65 , FooNumberNotNull.c66 , FooNumberNotNull.c67 , FooNumberNotNull.c68 , FooNumberNotNull.c69 , FooNumberNotNull.c70 , FooNumberNotNull.c71 , FooNumberNotNull.c72 , FooNumberNotNull.c73 , FooNumberNotNull.c74 , FooNumberNotNull.c75 , FooNumberNotNull.c76 , FooNumberNotNull.c77 , FooNumberNotNull.c78 , FooNumberNotNull.c79 , FooNumberNotNull.c80 , FooNumberNotNull.c81 , FooNumberNotNull.c82 , FooNumberNotNull.c83 , FooNumberNotNull.c84 , FooNumberNotNull.c85 , FooNumberNotNull.c86 , FooNumberNotNull.c87 , FooNumberNotNull.c88 , FooNumberNotNull.c89 , FooNumberNotNull.c90 , FooNumberNotNull.c91 , FooNumberNotNull.c92 , FooNumberNotNull.c93 , FooNumberNotNull.c94 , FooNumberNotNull.c95 , FooNumberNotNull.c96 , FooNumberNotNull.c97 , FooNumberNotNull.c98 , FooNumberNotNull.c99
, FooNumberNull.c1 , FooNumberNull.c2 , FooNumberNull.c3 , FooNumberNull.c4 , FooNumberNull.c5 , FooNumberNull.c6 , FooNumberNull.c7 , FooNumberNull.c8 , FooNumberNull.c9 , FooNumberNull.c10 , FooNumberNull.c11 , FooNumberNull.c12 , FooNumberNull.c13 , FooNumberNull.c14 , FooNumberNull.c15 , FooNumberNull.c16 , FooNumberNull.c17 , FooNumberNull.c18 , FooNumberNull.c19 , FooNumberNull.c20 , FooNumberNull.c21 , FooNumberNull.c22 , FooNumberNull.c23 , FooNumberNull.c24 , FooNumberNull.c25 , FooNumberNull.c26 , FooNumberNull.c27 , FooNumberNull.c28 , FooNumberNull.c29 , FooNumberNull.c30 , FooNumberNull.c31 , FooNumberNull.c32 , FooNumberNull.c33 , FooNumberNull.c34 , FooNumberNull.c35 , FooNumberNull.c36 , FooNumberNull.c37 , FooNumberNull.c38 , FooNumberNull.c39 , FooNumberNull.c40 , FooNumberNull.c41 , FooNumberNull.c42 , FooNumberNull.c43 , FooNumberNull.c44 , FooNumberNull.c45 , FooNumberNull.c46 , FooNumberNull.c47 , FooNumberNull.c48 , FooNumberNull.c49 , FooNumberNull.c50 , FooNumberNull.c51 , FooNumberNull.c52 , FooNumberNull.c53 , FooNumberNull.c54 , FooNumberNull.c55 , FooNumberNull.c56 , FooNumberNull.c57 , FooNumberNull.c58 , FooNumberNull.c59 , FooNumberNull.c60 , FooNumberNull.c61 , FooNumberNull.c62 , FooNumberNull.c63 , FooNumberNull.c64 , FooNumberNull.c65 , FooNumberNull.c66 , FooNumberNull.c67 , FooNumberNull.c68 , FooNumberNull.c69 , FooNumberNull.c70 , FooNumberNull.c71 , FooNumberNull.c72 , FooNumberNull.c73 , FooNumberNull.c74 , FooNumberNull.c75 , FooNumberNull.c76 , FooNumberNull.c77 , FooNumberNull.c78 , FooNumberNull.c79 , FooNumberNull.c80 , FooNumberNull.c81 , FooNumberNull.c82 , FooNumberNull.c83 , FooNumberNull.c84 , FooNumberNull.c85 , FooNumberNull.c86 , FooNumberNull.c87 , FooNumberNull.c88 , FooNumberNull.c89 , FooNumberNull.c90 , FooNumberNull.c91 , FooNumberNull.c92 , FooNumberNull.c93 , FooNumberNull.c94 , FooNumberNull.c95 , FooNumberNull.c96 , FooNumberNull.c97 , FooNumberNull.c98 , FooNumberNull.c99
from FooNumberNotNull
left outer join FooNumberNull
on FooNumberNull.FooNumberKey= FooNumberNotNull.FooNumberKey
select * from FooNumber
select FooNumberNotNull_FooNumberKey
, FooNumberNotNull_c1 , FooNumberNotNull_c2 , FooNumberNotNull_c3 , FooNumberNotNull_c4 , FooNumberNotNull_c5 , FooNumberNotNull_c6 , FooNumberNotNull_c7 , FooNumberNotNull_c8 , FooNumberNotNull_c9 , FooNumberNotNull_c10 , FooNumberNotNull_c11 , FooNumberNotNull_c12 , FooNumberNotNull_c13 , FooNumberNotNull_c14 , FooNumberNotNull_c15 , FooNumberNotNull_c16 , FooNumberNotNull_c17 , FooNumberNotNull_c18 , FooNumberNotNull_c19 , FooNumberNotNull_c20 , FooNumberNotNull_c21 , FooNumberNotNull_c22 , FooNumberNotNull_c23 , FooNumberNotNull_c24 , FooNumberNotNull_c25 , FooNumberNotNull_c26 , FooNumberNotNull_c27 , FooNumberNotNull_c28 , FooNumberNotNull_c29 , FooNumberNotNull_c30 , FooNumberNotNull_c31 , FooNumberNotNull_c32 , FooNumberNotNull_c33 , FooNumberNotNull_c34 , FooNumberNotNull_c35 , FooNumberNotNull_c36 , FooNumberNotNull_c37 , FooNumberNotNull_c38 , FooNumberNotNull_c39 , FooNumberNotNull_c40 , FooNumberNotNull_c41 , FooNumberNotNull_c42 , FooNumberNotNull_c43 , FooNumberNotNull_c44 , FooNumberNotNull_c45 , FooNumberNotNull_c46 , FooNumberNotNull_c47 , FooNumberNotNull_c48 , FooNumberNotNull_c49 , FooNumberNotNull_c50 , FooNumberNotNull_c51 , FooNumberNotNull_c52 , FooNumberNotNull_c53 , FooNumberNotNull_c54 , FooNumberNotNull_c55 , FooNumberNotNull_c56 , FooNumberNotNull_c57 , FooNumberNotNull_c58 , FooNumberNotNull_c59 , FooNumberNotNull_c60 , FooNumberNotNull_c61 , FooNumberNotNull_c62 , FooNumberNotNull_c63 , FooNumberNotNull_c64 , FooNumberNotNull_c65 , FooNumberNotNull_c66 , FooNumberNotNull_c67 , FooNumberNotNull_c68 , FooNumberNotNull_c69 , FooNumberNotNull_c70 , FooNumberNotNull_c71 , FooNumberNotNull_c72 , FooNumberNotNull_c73 , FooNumberNotNull_c74 , FooNumberNotNull_c75 , FooNumberNotNull_c76 , FooNumberNotNull_c77 , FooNumberNotNull_c78 , FooNumberNotNull_c79 , FooNumberNotNull_c80 , FooNumberNotNull_c81 , FooNumberNotNull_c82 , FooNumberNotNull_c83 , FooNumberNotNull_c84 , FooNumberNotNull_c85 , FooNumberNotNull_c86 , FooNumberNotNull_c87 , FooNumberNotNull_c88 , FooNumberNotNull_c89 , FooNumberNotNull_c90 , FooNumberNotNull_c91 , FooNumberNotNull_c92 , FooNumberNotNull_c93 , FooNumberNotNull_c94 , FooNumberNotNull_c95 , FooNumberNotNull_c96 , FooNumberNotNull_c97 , FooNumberNotNull_c98 , FooNumberNotNull_c99
from FooNumber
SQL = Scarcely Qualifies as a Language
November 1, 2005 at 2:42 pm
Thats the catch though...for the 17 (ouch) outer joins that compiling all of this data would entail, at least 13-14 of the columns would always be queried.
This is the problem with pivoting data that is normalized. Ideally, the query would just return the data as is, and let the app. parse out the data.
It's always a tough sell to explain that it's easier (and much more efficient) to add app servers and load balance than overload a db server where you have finite memory and processor...
November 1, 2005 at 3:21 pm
By Pivot, do you mean turning columns into rows such as:
select c1, c2
, MAX(case PivotColumn when 'x' then Value else null end) as X_Value
, MAX(case PivotColumn when 'Y' then Value else null end) as Y_Value
group by c1, c2
You may want to run a benchmark comparing the database server resource utilization of performing the pivot versus the cost of sending more packets with un-pivoted rows.
The pivot is usually uses less resources.
SQL = Scarcely Qualifies as a Language
November 2, 2005 at 6:57 am
Yeah, it is turning rows into columns but not from the same table. There is a master data table and then two sets of other data tables joined by type-keys. So for example:
Table 1
-----------------------
pkey1 datecreated (other)
Table 2
-----------------------
pkey1 typeid1 value
Table 3
-----------------------
pkey1 typeid2 value
Table 1 - Table 2 and Table 1 - Table 3 are one to many. Pivoting this to have the types from Table 2 and 3 causes a massive query....it's here that I run into performance issues. It is a heavily used table with tons of data.
November 2, 2005 at 11:17 am
Do you mean that the other tables look like:
Create table Table2
( pkey1 integer not null
, typeid1 integer not null
, value varchar(255) not null
, constraint Table2_PK primary key (pkey1 ,typeid1 )
)
The values for "typeid1" really means the "column name" of the value ? e.g values are "weight", "height", "price" and so on ?
There have been many posts at sqlservercentral where someone has designed or inherited a schema with such a structure and
then discovered that data integrity, performance and resource utilization (disk space, memory and CPU) are all significant issues.
The recommended solutions are always the same:
1. start over and redesign with a properly normalized table structure.
2. If redesign is not possible, update your resume.
SQL = Scarcely Qualifies as a Language
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply