I want to do a query on a db's system tables to determine what column(s) are
part of the primary key.
Example.
Code:
SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
sysobjects.name='tablename'
Yields all the column names. But I just want to see those that are part of
the primary key. How?
Ryan
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
shuldn't you use information_schema?
.~. Might, Courage, Vision. Sincerity. http://www.linux-sxs.org
/ v \
/( _ )\ (Ubuntu 5.10) Linux 2.6.14.3
^ ^ 22:37:07 up 9 days 2:31 load average: 3.12 1.63 0.80|||Try:
select
*
from
information_schema.constraint_column_usage
where
table_name = 'Order Details'
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:037F8848-C843-4A77-B385-6994E6235CDA@.microsoft.com...
>I want to do a query on a db's system tables to determine what column(s)
>are
> part of the primary key.
> Example.
> Code:
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
> Yields all the column names. But I just want to see those that are part of
> the primary key. How?
> Ryan|||Thank you both of you. Yes, Man-wai Chang, the information_schema looks lik
e
it's a lot easier than joining the system tables like I was doing. Thank
you, both.
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Thursday, March 29, 2012
Wednesday, March 21, 2012
Detecting bad date with YYYYMMDD format
Every night we get a big dump of data from one of our outside financial
companies. In the file, one of the columns is a date, with the
following format:
YYYYMMDD
When we migrate this data into our database, SQL Server will
automaically cast this 8-character "varchar" field to a smalldatetime
field. This means that I can do this:
UPDATE table
SET datefield = tb.datefield
FROM loadedfile tb
However, I recently discovered that they sent us a few bad dates
(20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value."
So, my question is -- how do I go about detecting that the 8-character
string is not a valid date, while doing it on-the-fly inside the UPDATE
statement?Do your import into a seperate table with generic data types. Add a column
to the head of the table that indicates the attempted date of import. Add
another column that will indicate the type of error that you are having
with the column.
Now, do the import to this table and update column1 with getdate and column2
with NULL.
Next, run a routine that will validate all the data and update column2 from
null to something that indicates error.
Finally, do your normal import from this new table only where column2 is
null. Now you have a history of all the issues.
When everything is complete, if there is a count(*) > 0 in this table,
meaning that there were errors, you can email someone or just toss red
flags how ever you do this.
You can also check count(*) to see if > 0 and if true then you might not
want to do any of the import process and throw your flags.
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
new|||You can use the ISDATE() function, which returns 1 if valid and 0 if not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jsevlie@.gmail.com> wrote in message news:1132076310.299318.326260@.g43g2000cwa.googlegroups
.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||You can use a WHERE clause to limit the rows processed, or use CASE within
the statement.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
<jsevlie@.gmail.com> wrote in message
news:1132076310.299318.326260@.g43g2000cwa.googlegroups.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Try this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT ISDATE(@.Date)
It returns "1" (which is obviously not right!)
Howver, do this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT CAST(@.date AS smalldatetime)
And it throws the error I'm getting.
Then, I did this:
declare @.dyear varchar(10)
declare @.dmonth varchar(10)
declare @.dday varchar(10)
declare @.newdate varchar(10)
IF ISDATE(@.Date) = 1 BEGIN
set @.dyear = substring(@.Date, 1, 4)
set @.dmonth = substring(@.Date, 5, 2)
set @.dday = substring(@.Date, 7, 2)
set @.newdate = @.dyear + '-' + @.dmonth + '-' + @.dday
select @.Date, @.newdate, isdate(@.newdate)
END
And it still thinks my string is a valid date. What gives?|||BOL shows a SmallDateTime as:
Date and time data from January 1, 1900, through June 6, 2079, with accuracy
to the minute.
A regular DateTime field will handled dates between the years 1753 and 9999.
So the first option is to store the data as a DateTime instead of a
SmallDateTime, and CAST the field to DateTime:
UPDATE table
SET datefield = CAST(tb.datefield AS DATETIME)
FROM loadedfile tb
If the field in table is SmallDateTime and you cannot change it, then you
will have to throw in some sort of business logic - what happens if the date
is > 2079? If it is to set the date to be entered as 1/1/2079, then you
could do something like this:
UPDATE table
SET datefield = CASE WHEN CAST(db.datefield AS DATETIME) > '1/1/2079'
THEN '1/1/2079'
ELSE CAST(tb.datefield AS SMALLDATETIME)
END
FROM loadedfile tb
This will check to see if the data passed in is too big, and if it is pass
in the date of '1/1/2079'. You could then query the table for anything with
the '1/1/2079' date and know it was something that was wrong - and follow up
to fix it.
Hope this helps some
Rob|||-- check for the smalldatetime limits
update table
set datefield = tb.datefield
from loadedfile tb
where tb.datefield>='19000101' and tb.datefield<='20790606'
-- report on those that are out of smalldatetime range
-- the implicit int conversion should be OK, unless other bad data is
there as well
select <col list>
from loadedfile
where datefield<'19000101' or datefield>'20790606'
you may want to change the datatype of the loadedfile table column to
datetime [not smalldatetime, to avoid errors] instead of varchar, then
you'd be comparing dates to dates.
jsevlie@.gmail.com wrote:
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Very nice, I like the idea about casting to a full datetime string and
then seeing if it passes a threshold. That should work, I'll give it a
try.
Thanks!
companies. In the file, one of the columns is a date, with the
following format:
YYYYMMDD
When we migrate this data into our database, SQL Server will
automaically cast this 8-character "varchar" field to a smalldatetime
field. This means that I can do this:
UPDATE table
SET datefield = tb.datefield
FROM loadedfile tb
However, I recently discovered that they sent us a few bad dates
(20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value."
So, my question is -- how do I go about detecting that the 8-character
string is not a valid date, while doing it on-the-fly inside the UPDATE
statement?Do your import into a seperate table with generic data types. Add a column
to the head of the table that indicates the attempted date of import. Add
another column that will indicate the type of error that you are having
with the column.
Now, do the import to this table and update column1 with getdate and column2
with NULL.
Next, run a routine that will validate all the data and update column2 from
null to something that indicates error.
Finally, do your normal import from this new table only where column2 is
null. Now you have a history of all the issues.
When everything is complete, if there is a count(*) > 0 in this table,
meaning that there were errors, you can email someone or just toss red
flags how ever you do this.
You can also check count(*) to see if > 0 and if true then you might not
want to do any of the import process and throw your flags.
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
new|||You can use the ISDATE() function, which returns 1 if valid and 0 if not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jsevlie@.gmail.com> wrote in message news:1132076310.299318.326260@.g43g2000cwa.googlegroups
.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||You can use a WHERE clause to limit the rows processed, or use CASE within
the statement.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
<jsevlie@.gmail.com> wrote in message
news:1132076310.299318.326260@.g43g2000cwa.googlegroups.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Try this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT ISDATE(@.Date)
It returns "1" (which is obviously not right!)
Howver, do this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT CAST(@.date AS smalldatetime)
And it throws the error I'm getting.
Then, I did this:
declare @.dyear varchar(10)
declare @.dmonth varchar(10)
declare @.dday varchar(10)
declare @.newdate varchar(10)
IF ISDATE(@.Date) = 1 BEGIN
set @.dyear = substring(@.Date, 1, 4)
set @.dmonth = substring(@.Date, 5, 2)
set @.dday = substring(@.Date, 7, 2)
set @.newdate = @.dyear + '-' + @.dmonth + '-' + @.dday
select @.Date, @.newdate, isdate(@.newdate)
END
And it still thinks my string is a valid date. What gives?|||BOL shows a SmallDateTime as:
Date and time data from January 1, 1900, through June 6, 2079, with accuracy
to the minute.
A regular DateTime field will handled dates between the years 1753 and 9999.
So the first option is to store the data as a DateTime instead of a
SmallDateTime, and CAST the field to DateTime:
UPDATE table
SET datefield = CAST(tb.datefield AS DATETIME)
FROM loadedfile tb
If the field in table is SmallDateTime and you cannot change it, then you
will have to throw in some sort of business logic - what happens if the date
is > 2079? If it is to set the date to be entered as 1/1/2079, then you
could do something like this:
UPDATE table
SET datefield = CASE WHEN CAST(db.datefield AS DATETIME) > '1/1/2079'
THEN '1/1/2079'
ELSE CAST(tb.datefield AS SMALLDATETIME)
END
FROM loadedfile tb
This will check to see if the data passed in is too big, and if it is pass
in the date of '1/1/2079'. You could then query the table for anything with
the '1/1/2079' date and know it was something that was wrong - and follow up
to fix it.
Hope this helps some
Rob|||-- check for the smalldatetime limits
update table
set datefield = tb.datefield
from loadedfile tb
where tb.datefield>='19000101' and tb.datefield<='20790606'
-- report on those that are out of smalldatetime range
-- the implicit int conversion should be OK, unless other bad data is
there as well
select <col list>
from loadedfile
where datefield<'19000101' or datefield>'20790606'
you may want to change the datatype of the loadedfile table column to
datetime [not smalldatetime, to avoid errors] instead of varchar, then
you'd be comparing dates to dates.
jsevlie@.gmail.com wrote:
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Very nice, I like the idea about casting to a full datetime string and
then seeing if it passes a threshold. That should work, I'll give it a
try.
Thanks!
Detect all NtUserName
Hi,
I have a problem to find out every NtUserName in my SP that take
information from Sysprocesses. Some NtUserName columns ary empty and I have
much hard work to discover who's that guy connect in my Database.
I think to match another utility throught xp_cmdshell to run an get
NtUserName by hostname parameter, but I didn't find out any DOS utility to
easy display that information.
Please, I hope some help .
Thanks!
Krisnamourt
If that wont tell you the appropiate names
Select loginame from sysprocesses
you dont actually work with integrated security, dont you ?
If you want to get the username even the User connects with Sql Server
authentification that would be a kind of hack, to put the WIndowsUser from
your application in the connectionstring to read that Conenction property
from SQLServer.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Krisnamourt Correia via droptable.com" <forum@.nospam.droptable.com>
schrieb im Newsbeitrag
news:fffd09ac4f6d4a96a909e4f78a47e8d8@.droptable.co m...
> Hi,
> I have a problem to find out every NtUserName in my SP that take
> information from Sysprocesses. Some NtUserName columns ary empty and I
> have
> much hard work to discover who's that guy connect in my Database.
> I think to match another utility throught xp_cmdshell to run an get
> NtUserName by hostname parameter, but I didn't find out any DOS utility to
> easy display that information.
> Please, I hope some help .
> Thanks!
> Krisnamourt
|||Our Client conections has been in BDE conection by Delphi program. This use
one connection login. I have a SP to help some issues to controll locks and
other needs, but in all most that connection, the ntusername come blank and
other with the real ntusername, no different that library are Named Pipe or
TCP/IP. That information are collected in sysprocesses with hostname, login
and ntusername, but the last is a problem to find out.
I want to ever get NtUserName! Is that a bug's BDE of Delphi?
"Jens Sü?meyer" wrote:
> If that won′t tell you the appropiate names
> Select loginame from sysprocesses
> you don′t actually work with integrated security, don′t you ?
> If you want to get the username even the User connects with Sql Server
> authentification that would be a kind of hack, to put the WIndowsUser from
> your application in the connectionstring to read that Conenction property
> from SQLServer.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Krisnamourt Correia via droptable.com" <forum@.nospam.droptable.com>
> schrieb im Newsbeitrag
> news:fffd09ac4f6d4a96a909e4f78a47e8d8@.droptable.co m...
>
>
|||No not at all, Windows username is only given if you come along with
integrated security.
Jens SUessmeyer.
"Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B8F488D8-00EA-4DA3-81FA-B30803AE3377@.microsoft.com...[vbcol=seagreen]
> Our Client conections has been in BDE conection by Delphi program. This
> use
> one connection login. I have a SP to help some issues to controll locks
> and
> other needs, but in all most that connection, the ntusername come blank
> and
> other with the real ntusername, no different that library are Named Pipe
> or
> TCP/IP. That information are collected in sysprocesses with hostname,
> login
> and ntusername, but the last is a problem to find out.
> I want to ever get NtUserName! Is that a bug's BDE of Delphi?
> "Jens Smeyer" wrote:
|||Ok! You mean that I have used inconsistence information...but I Knew that,
because I saw in many times that variable information...so...if in some times
SQL give me...why don't ever?The conecction string is the same for all
users...ok!...but in other hand...I want to use any utility "married " with
XP_CMDSHELL output collection...using temporary table to manipulate that
string...the problem is...what utility?...when i worked wit Windows NT, there
was one resouce kit utility that I passed the host and it answered the
NTUserName...but now my Network is Windows 2000 AD. Do you know some?
"Jens Sü?meyer" wrote:
> No not at all, Windows username is only given if you come along with
> integrated security.
> Jens SUessmeyer.
> "Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:B8F488D8-00EA-4DA3-81FA-B30803AE3377@.microsoft.com...
>
>
|||Look at this thread, that would be the only chance to get the Windows name
according to SQL Server Auth.
http://groups.google.de/group/micros...2257c40e693afe
Any XP_CMDShell wont work because it is executed on the server and the
server has no information about the User presented on the other side.
HTH, Jens Suessmeyer.
"Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A137A4EA-6891-43CC-87CC-FAAC1748C197@.microsoft.com...[vbcol=seagreen]
> Ok! You mean that I have used inconsistence information...but I Knew that,
> because I saw in many times that variable information...so...if in some
> times
> SQL give me...why don't ever?The conecction string is the same for all
> users...ok!...but in other hand...I want to use any utility "married "
> with
> XP_CMDSHELL output collection...using temporary table to manipulate that
> string...the problem is...what utility?...when i worked wit Windows NT,
> there
> was one resouce kit utility that I passed the host and it answered the
> NTUserName...but now my Network is Windows 2000 AD. Do you know some?
> "Jens Smeyer" wrote:
I have a problem to find out every NtUserName in my SP that take
information from Sysprocesses. Some NtUserName columns ary empty and I have
much hard work to discover who's that guy connect in my Database.
I think to match another utility throught xp_cmdshell to run an get
NtUserName by hostname parameter, but I didn't find out any DOS utility to
easy display that information.
Please, I hope some help .
Thanks!
Krisnamourt
If that wont tell you the appropiate names
Select loginame from sysprocesses
you dont actually work with integrated security, dont you ?
If you want to get the username even the User connects with Sql Server
authentification that would be a kind of hack, to put the WIndowsUser from
your application in the connectionstring to read that Conenction property
from SQLServer.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Krisnamourt Correia via droptable.com" <forum@.nospam.droptable.com>
schrieb im Newsbeitrag
news:fffd09ac4f6d4a96a909e4f78a47e8d8@.droptable.co m...
> Hi,
> I have a problem to find out every NtUserName in my SP that take
> information from Sysprocesses. Some NtUserName columns ary empty and I
> have
> much hard work to discover who's that guy connect in my Database.
> I think to match another utility throught xp_cmdshell to run an get
> NtUserName by hostname parameter, but I didn't find out any DOS utility to
> easy display that information.
> Please, I hope some help .
> Thanks!
> Krisnamourt
|||Our Client conections has been in BDE conection by Delphi program. This use
one connection login. I have a SP to help some issues to controll locks and
other needs, but in all most that connection, the ntusername come blank and
other with the real ntusername, no different that library are Named Pipe or
TCP/IP. That information are collected in sysprocesses with hostname, login
and ntusername, but the last is a problem to find out.
I want to ever get NtUserName! Is that a bug's BDE of Delphi?
"Jens Sü?meyer" wrote:
> If that won′t tell you the appropiate names
> Select loginame from sysprocesses
> you don′t actually work with integrated security, don′t you ?
> If you want to get the username even the User connects with Sql Server
> authentification that would be a kind of hack, to put the WIndowsUser from
> your application in the connectionstring to read that Conenction property
> from SQLServer.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Krisnamourt Correia via droptable.com" <forum@.nospam.droptable.com>
> schrieb im Newsbeitrag
> news:fffd09ac4f6d4a96a909e4f78a47e8d8@.droptable.co m...
>
>
|||No not at all, Windows username is only given if you come along with
integrated security.
Jens SUessmeyer.
"Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B8F488D8-00EA-4DA3-81FA-B30803AE3377@.microsoft.com...[vbcol=seagreen]
> Our Client conections has been in BDE conection by Delphi program. This
> use
> one connection login. I have a SP to help some issues to controll locks
> and
> other needs, but in all most that connection, the ntusername come blank
> and
> other with the real ntusername, no different that library are Named Pipe
> or
> TCP/IP. That information are collected in sysprocesses with hostname,
> login
> and ntusername, but the last is a problem to find out.
> I want to ever get NtUserName! Is that a bug's BDE of Delphi?
> "Jens Smeyer" wrote:
|||Ok! You mean that I have used inconsistence information...but I Knew that,
because I saw in many times that variable information...so...if in some times
SQL give me...why don't ever?The conecction string is the same for all
users...ok!...but in other hand...I want to use any utility "married " with
XP_CMDSHELL output collection...using temporary table to manipulate that
string...the problem is...what utility?...when i worked wit Windows NT, there
was one resouce kit utility that I passed the host and it answered the
NTUserName...but now my Network is Windows 2000 AD. Do you know some?
"Jens Sü?meyer" wrote:
> No not at all, Windows username is only given if you come along with
> integrated security.
> Jens SUessmeyer.
> "Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:B8F488D8-00EA-4DA3-81FA-B30803AE3377@.microsoft.com...
>
>
|||Look at this thread, that would be the only chance to get the Windows name
according to SQL Server Auth.
http://groups.google.de/group/micros...2257c40e693afe
Any XP_CMDShell wont work because it is executed on the server and the
server has no information about the User presented on the other side.
HTH, Jens Suessmeyer.
"Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A137A4EA-6891-43CC-87CC-FAAC1748C197@.microsoft.com...[vbcol=seagreen]
> Ok! You mean that I have used inconsistence information...but I Knew that,
> because I saw in many times that variable information...so...if in some
> times
> SQL give me...why don't ever?The conecction string is the same for all
> users...ok!...but in other hand...I want to use any utility "married "
> with
> XP_CMDSHELL output collection...using temporary table to manipulate that
> string...the problem is...what utility?...when i worked wit Windows NT,
> there
> was one resouce kit utility that I passed the host and it answered the
> NTUserName...but now my Network is Windows 2000 AD. Do you know some?
> "Jens Smeyer" wrote:
Detect all NtUserName
Hi,
I have a problem to find out every NtUserName in my SP that take
information from Sysprocesses. Some NtUserName columns ary empty and I have
much hard work to discover who's that guy connect in my Database.
I think to match another utility throught xp_cmdshell to run an get
NtUserName by hostname parameter, but I didn't find out any DOS utility to
easy display that information.
Please, I hope some help .
Thanks!
KrisnamourtIf that wont tell you the appropiate names
Select loginame from sysprocesses
you dont actually work with integrated security, dont you ?
If you want to get the username even the User connects with Sql Server
authentification that would be a kind of hack, to put the WIndowsUser from
your application in the connectionstring to read that Conenction property
from SQLServer.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Krisnamourt Correia via droptable.com" <forum@.nospam.droptable.com>
schrieb im Newsbeitrag
news:fffd09ac4f6d4a96a909e4f78a47e8d8@.SQ
droptable.com...
> Hi,
> I have a problem to find out every NtUserName in my SP that take
> information from Sysprocesses. Some NtUserName columns ary empty and I
> have
> much hard work to discover who's that guy connect in my Database.
> I think to match another utility throught xp_cmdshell to run an get
> NtUserName by hostname parameter, but I didn't find out any DOS utility to
> easy display that information.
> Please, I hope some help .
> Thanks!
> Krisnamourt|||Our Client conections has been in BDE conection by Delphi program. This use
one connection login. I have a SP to help some issues to controll locks and
other needs, but in all most that connection, the ntusername come blank and
other with the real ntusername, no different that library are Named Pipe or
TCP/IP. That information are collected in sysprocesses with hostname, login
and ntusername, but the last is a problem to find out.
I want to ever get NtUserName! Is that a bug's BDE of Delphi?
"Jens Sü?meyer" wrote:
> If that won′t tell you the appropiate names
> Select loginame from sysprocesses
> you don′t actually work with integrated security, don′t you ?
> If you want to get the username even the User connects with Sql Server
> authentification that would be a kind of hack, to put the WIndowsUser from
> your application in the connectionstring to read that Conenction property
> from SQLServer.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Krisnamourt Correia via droptable.com" <forum@.nospam.droptable.com>
> schrieb im Newsbeitrag
> news:fffd09ac4f6d4a96a909e4f78a47e8d8@.SQ
droptable.com...
>
>|||No not at all, Windows username is only given if you come along with
integrated security.
Jens SUessmeyer.
"Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B8F488D8-00EA-4DA3-81FA-B30803AE3377@.microsoft.com...[vbcol=seagreen]
> Our Client conections has been in BDE conection by Delphi program. This
> use
> one connection login. I have a SP to help some issues to controll locks
> and
> other needs, but in all most that connection, the ntusername come blank
> and
> other with the real ntusername, no different that library are Named Pipe
> or
> TCP/IP. That information are collected in sysprocesses with hostname,
> login
> and ntusername, but the last is a problem to find out.
> I want to ever get NtUserName! Is that a bug's BDE of Delphi?
> "Jens Smeyer" wrote:
>|||Ok! You mean that I have used inconsistence information...but I Knew that,
because I saw in many times that variable information...so...if in some time
s
SQL give me...why don't ever?The conecction string is the same for all
users...ok!...but in other hand...I want to use any utility "married " with
XP_CMDSHELL output collection...using temporary table to manipulate that
string...the problem is...what utility?...when i worked wit Windows NT, ther
e
was one resouce kit utility that I passed the host and it answered the
NTUserName...but now my Network is Windows 2000 AD. Do you know some?
"Jens Sü?meyer" wrote:
> No not at all, Windows username is only given if you come along with
> integrated security.
> Jens SUessmeyer.
> "Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitr
ag
> news:B8F488D8-00EA-4DA3-81FA-B30803AE3377@.microsoft.com...
>
>|||Look at this thread, that would be the only chance to get the Windows name
according to SQL Server Auth.
/72250f1fc3f0ef16/912257c40e693afe?q=Jens+S%C3%BC%C3%9Fmeyer+windows&rnum=2&hl=de#
912257c40e693afe" target="_blank">http://groups.google.de/group/micro...12257c40e693afe
Any XP_CMDShell wont work because it is executed on the server and the
server has no information about the User presented on the other side.
HTH, Jens Suessmeyer.
"Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A137A4EA-6891-43CC-87CC-FAAC1748C197@.microsoft.com...[vbcol=seagreen]
> Ok! You mean that I have used inconsistence information...but I Knew that,
> because I saw in many times that variable information...so...if in some
> times
> SQL give me...why don't ever?The conecction string is the same for all
> users...ok!...but in other hand...I want to use any utility "married "
> with
> XP_CMDSHELL output collection...using temporary table to manipulate that
> string...the problem is...what utility?...when i worked wit Windows NT,
> there
> was one resouce kit utility that I passed the host and it answered the
> NTUserName...but now my Network is Windows 2000 AD. Do you know some?
> "Jens Smeyer" wrote:
>sql
I have a problem to find out every NtUserName in my SP that take
information from Sysprocesses. Some NtUserName columns ary empty and I have
much hard work to discover who's that guy connect in my Database.
I think to match another utility throught xp_cmdshell to run an get
NtUserName by hostname parameter, but I didn't find out any DOS utility to
easy display that information.
Please, I hope some help .
Thanks!
KrisnamourtIf that wont tell you the appropiate names
Select loginame from sysprocesses
you dont actually work with integrated security, dont you ?
If you want to get the username even the User connects with Sql Server
authentification that would be a kind of hack, to put the WIndowsUser from
your application in the connectionstring to read that Conenction property
from SQLServer.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Krisnamourt Correia via droptable.com" <forum@.nospam.droptable.com>
schrieb im Newsbeitrag
news:fffd09ac4f6d4a96a909e4f78a47e8d8@.SQ
droptable.com...
> Hi,
> I have a problem to find out every NtUserName in my SP that take
> information from Sysprocesses. Some NtUserName columns ary empty and I
> have
> much hard work to discover who's that guy connect in my Database.
> I think to match another utility throught xp_cmdshell to run an get
> NtUserName by hostname parameter, but I didn't find out any DOS utility to
> easy display that information.
> Please, I hope some help .
> Thanks!
> Krisnamourt|||Our Client conections has been in BDE conection by Delphi program. This use
one connection login. I have a SP to help some issues to controll locks and
other needs, but in all most that connection, the ntusername come blank and
other with the real ntusername, no different that library are Named Pipe or
TCP/IP. That information are collected in sysprocesses with hostname, login
and ntusername, but the last is a problem to find out.
I want to ever get NtUserName! Is that a bug's BDE of Delphi?
"Jens Sü?meyer" wrote:
> If that won′t tell you the appropiate names
> Select loginame from sysprocesses
> you don′t actually work with integrated security, don′t you ?
> If you want to get the username even the User connects with Sql Server
> authentification that would be a kind of hack, to put the WIndowsUser from
> your application in the connectionstring to read that Conenction property
> from SQLServer.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Krisnamourt Correia via droptable.com" <forum@.nospam.droptable.com>
> schrieb im Newsbeitrag
> news:fffd09ac4f6d4a96a909e4f78a47e8d8@.SQ
droptable.com...
>
>|||No not at all, Windows username is only given if you come along with
integrated security.
Jens SUessmeyer.
"Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B8F488D8-00EA-4DA3-81FA-B30803AE3377@.microsoft.com...[vbcol=seagreen]
> Our Client conections has been in BDE conection by Delphi program. This
> use
> one connection login. I have a SP to help some issues to controll locks
> and
> other needs, but in all most that connection, the ntusername come blank
> and
> other with the real ntusername, no different that library are Named Pipe
> or
> TCP/IP. That information are collected in sysprocesses with hostname,
> login
> and ntusername, but the last is a problem to find out.
> I want to ever get NtUserName! Is that a bug's BDE of Delphi?
> "Jens Smeyer" wrote:
>|||Ok! You mean that I have used inconsistence information...but I Knew that,
because I saw in many times that variable information...so...if in some time
s
SQL give me...why don't ever?The conecction string is the same for all
users...ok!...but in other hand...I want to use any utility "married " with
XP_CMDSHELL output collection...using temporary table to manipulate that
string...the problem is...what utility?...when i worked wit Windows NT, ther
e
was one resouce kit utility that I passed the host and it answered the
NTUserName...but now my Network is Windows 2000 AD. Do you know some?
"Jens Sü?meyer" wrote:
> No not at all, Windows username is only given if you come along with
> integrated security.
> Jens SUessmeyer.
> "Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitr
ag
> news:B8F488D8-00EA-4DA3-81FA-B30803AE3377@.microsoft.com...
>
>|||Look at this thread, that would be the only chance to get the Windows name
according to SQL Server Auth.
/72250f1fc3f0ef16/912257c40e693afe?q=Jens+S%C3%BC%C3%9Fmeyer+windows&rnum=2&hl=de#
912257c40e693afe" target="_blank">http://groups.google.de/group/micro...12257c40e693afe
Any XP_CMDShell wont work because it is executed on the server and the
server has no information about the User presented on the other side.
HTH, Jens Suessmeyer.
"Krisnamourt" <Krisnamourt@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A137A4EA-6891-43CC-87CC-FAAC1748C197@.microsoft.com...[vbcol=seagreen]
> Ok! You mean that I have used inconsistence information...but I Knew that,
> because I saw in many times that variable information...so...if in some
> times
> SQL give me...why don't ever?The conecction string is the same for all
> users...ok!...but in other hand...I want to use any utility "married "
> with
> XP_CMDSHELL output collection...using temporary table to manipulate that
> string...the problem is...what utility?...when i worked wit Windows NT,
> there
> was one resouce kit utility that I passed the host and it answered the
> NTUserName...but now my Network is Windows 2000 AD. Do you know some?
> "Jens Smeyer" wrote:
>sql
Wednesday, March 7, 2012
Designing reusable column transformations?
We receive thousands of files every week from various clients and we attempt to clean the columns using the same technique over and over so the data is consistent. The problem is I dont see a way to reuse complex column transformations in different packages. I would hate to have to go change every package if we change the rules for cleaning a column.
So #1: Can you create some kind of script or .net function that cleans a column and reuse it in multiple packages (or even in the same package)?
#2: Is it possible to call functions from the Derived Column expression builder?
Thanks!
1)May be. You always can use a script component with the transformation rules and copy and paste it many times; it that sounds reasonable to you.
2) No you can not.
|||
Craig, I have had do do the same thign.The best solution is not a script component, as the person above mentioned.
Take the hit, build a custom component. Once you get it built, adding a new column is as simple as adding a new column to the transform.
You will want to build a custom pipline component. Both Wrox and O'riely have some decent chapters decribing how to do this. and there is definately info in microsoft.
I have 350 packages, that all chagne time from european time to us time, in the data flow. By writing 1 component, once, I was able to then delegate the rest of the work, since the hard part was encapsulated in a compoent.
good luck
Saturday, February 25, 2012
Designing groups in multiple columns
hello,
> i am designing a grade report and i got a problem, the problem is that me divided the report into two columns, me grouped the records on the basis of semesters. now i want to show the first semester's records in first column and second semester's records in second column, then third semester's records in first column and so on.. but it doesn't hapens so. i want to set the layout of the group but there is not any option to set layout of the group.there is layout option for section but not for group. what me should do ? ? ?
iam desiginging one report for one student. each student have multiple semester and each semester consist of multiple subjects.I hace CR XI, go to details, section expert, layout with multiple columns, you should see a layout tab pop up in the upper right, click on it, you can control the direction the data flows as well as turn on, layout groups with multiple columns.
> i am designing a grade report and i got a problem, the problem is that me divided the report into two columns, me grouped the records on the basis of semesters. now i want to show the first semester's records in first column and second semester's records in second column, then third semester's records in first column and so on.. but it doesn't hapens so. i want to set the layout of the group but there is not any option to set layout of the group.there is layout option for section but not for group. what me should do ? ? ?
iam desiginging one report for one student. each student have multiple semester and each semester consist of multiple subjects.I hace CR XI, go to details, section expert, layout with multiple columns, you should see a layout tab pop up in the upper right, click on it, you can control the direction the data flows as well as turn on, layout groups with multiple columns.
Designating an identity column when you already have primary key
Hello --
Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.
I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.
Any help is appreciated.
Eric
Hello my friend,
Turn Allow Nulls off for these other 2 columns. In addition to this, here is some SQL you can run at any time to check for duplicate values: -
SELECT ErrorTypeID, COUNT(*) AS Counter
FROM TBLERROR
GROUP BY ErrorTypeID
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
Kind regards
Scotty
|||tblErrror is a custom table in my database. Substitute it for your table and the field you want to check|||
Thanks, Scotty.
Is there any way to apply manual constraints so the duplicate value doesn't end up in the table in the first place, or would I instead have to insert new rows through a stored procedure and apply the check/abort in there?
|||Hello again my friend,
Put this index on your table: -
CREATE UNIQUE INDEX MyIndexName ON MyTableName (MyFieldName)
If there are duplicates already, the above statement will fail. If there are not, it will work and if someone, or some code, tries to insert a duplicate, the record will not be inserted/updated and an error will result.
Kind regards
Scotty
|||
If you want to remove an index you have added, do the following: -
DROP INDEX MyTableName.MyIndexName
Kind regards
Scotty
|||
EricLaszlo:
Hello --
Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.
I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.
Any help is appreciated.
Eric
The IDENTITY property can generate auto numbers for your column however it does not guarantee uniqueness because IDENTITY can generate gaps, so Microsoft recommend you use GUID instead. But you cannot use GUID because you have three columns that need to be Unique so that leave only one option Unique constraint because you can define more than one Unique constraint in a table. I have seen table definitions with many constraints so you could run some tests in relation to ADO.NET as needed for your application. Try the link below for details. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms191166.aspx
|||Thank you both very much for your input.
Based on that, I was able to apply the contstraints with the following syntax:
ALTER
TABLE ProfilesADD
CONSTRAINT ncEmailUNIQUE
NONCLUSTERED(Email)Having read a little about clustered indices in my travels, I tried to apply one to my Id column - didn't notice that this column already had a clustered index - not sure if that was from designating is as and Identity column or applying primary key.
In any case, I think I'm good for now...
Design: multiple columns for primary key
Hi,
A very basic question:
Imagine I have a table with multiple fields, some strings, some integers
etc.
None of these fields are unique on their own, but all fields together is
unique per record and thus can be used as the primary key.
However, referring to a record based on all these columns is quite
cumbersome (and not efficient, or does indexing take care of this nearly
entirely compared to a single identity field?), so how is this situation
best solved in practice.
Do I use an ugly IDENTITY column just for 'convenience' or continue to use
all fields, resulting in huge queries that lose legibility, or is there
another way?
LisaLisa Pearlson wrote:
> Do I use an ugly IDENTITY column just for 'convenience' or continue
> to use all fields, resulting in huge queries that lose legibility, or
> is there another way?
I would use "an ugle IDENTITY column" without doubt! Maybe others have
different opinions. It'll make your life a less easier. Otherwise if
you want to refer to that row in a foreign key you need to include all
the columns of the key which isn't really doable.
Kind regards,
Stijn Verrept.|||Lisa Pearlson (no@.spam.plz) writes:
> A very basic question:
> Imagine I have a table with multiple fields, some strings, some integers
> etc.
> None of these fields are unique on their own, but all fields together is
> unique per record and thus can be used as the primary key.
> However, referring to a record based on all these columns is quite
> cumbersome (and not efficient, or does indexing take care of this nearly
> entirely compared to a single identity field?), so how is this situation
> best solved in practice.
> Do I use an ugly IDENTITY column just for 'convenience' or continue to use
> all fields, resulting in huge queries that lose legibility, or is there
> another way?
It doesn't have to be IDENTITY, you can roll your own as well. But judging
from the shallow description you give, it appears that this could be a
solution. But you should add UNIQUE constraint on the other columns as well
to ensure their uniqueness.
I like to point out that adding a surrogate key does not always make things
simpler. I had a table with a four-column key, and then I needed to add a
sub-table with two more keys. Since a six-column key sounded too much, I
added a surrogate key to the main table. Years later I had reason to
write code to maintain these tables. Turned out that the surrogate key
made this a whole lot more complex. So the next time I had revise those
tables, I removed the surrogate key. (I was also able to remove one the
columns in the four-column key, and one of the keys in the sub-table.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Using the terms "field" and "record" will bring down the wrath of Celko!
I use IDENTITY for a number of reasons, and none of them are for
convenience:
(1) An IDENTITY value is immutable.
(2) Cascading updates are not required to maintain integrity.
(3) IDENTITY reduces redundancy.
(4) Joins generally perform better on IDENTITY values.
(5) IDENTITY reduces lock contention.
There are other reasons, and I could expound further on the merits of each
of the above.
It is not unusual in a logical database design to have tables with compound
primary keys; however, when it comes time to implement the design,
surrogates--including IDENTITY--make a lot more sense.
One more thing: if you do use an IDENTITY PRIMARY KEY, be sure to create a
UNIQUE constraint or index on the combination of columns that are together
unique per row. Alternate keys should be enforced by the database with a
UNIQUE constraint.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23hOXsSz9FHA.3928@.TK2MSFTNGP11.phx.gbl...
> Hi,
> A very basic question:
> Imagine I have a table with multiple fields, some strings, some integers
> etc.
> None of these fields are unique on their own, but all fields together is
> unique per record and thus can be used as the primary key.
> However, referring to a record based on all these columns is quite
> cumbersome (and not efficient, or does indexing take care of this nearly
> entirely compared to a single identity field?), so how is this situation
> best solved in practice.
> Do I use an ugly IDENTITY column just for 'convenience' or continue to use
> all fields, resulting in huge queries that lose legibility, or is there
> another way?
> Lisa
>|||I understand 3, 4 and take 5 for granted (some internal DBMS matter I
presume), but could you elaborate a bit on 1 and 2?
1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
identcol=456 ?
2) What does cascading have to do with it? (as I understand cascading, it's
like triggers where change in one record triggers changes in other
tables/records?)
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:OIHDWpz9FHA.3168@.TK2MSFTNGP10.phx.gbl...
> Using the terms "field" and "record" will bring down the wrath of Celko!
> I use IDENTITY for a number of reasons, and none of them are for
> convenience:
> (1) An IDENTITY value is immutable.
> (2) Cascading updates are not required to maintain integrity.
> (3) IDENTITY reduces redundancy.
> (4) Joins generally perform better on IDENTITY values.
> (5) IDENTITY reduces lock contention.
> There are other reasons, and I could expound further on the merits of each
> of the above.
> It is not unusual in a logical database design to have tables with
> compound primary keys; however, when it comes time to implement the
> design, surrogates--including IDENTITY--make a lot more sense.
> One more thing: if you do use an IDENTITY PRIMARY KEY, be sure to create a
> UNIQUE constraint or index on the combination of columns that are together
> unique per row. Alternate keys should be enforced by the database with a
> UNIQUE constraint.
>
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:%23hOXsSz9FHA.3928@.TK2MSFTNGP11.phx.gbl...
>|||Lisa Pearlson (no@.spam.plz) writes:
> I understand 3, 4 and take 5 for granted (some internal DBMS matter I
> presume), but could you elaborate a bit on 1 and 2?
> 1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
> identcol=456 ?
> 2) What does cascading have to do with it? (as I understand cascading,
> it's like triggers where change in one record triggers changes in other
> tables/records?)
Actually, I think of the reasons that Brian listed, only 5 is really
applicable to IDENTITY columns, although 1 has a touch to it: if you
have an IDENTITY property on the key, you know that the value cannot
be updated, not even by mistake.
Points 2-4 applies to surrogate keys in general, no matter if they have
IDENTITY or not.
I guess what Brian means with cascading is that if you use entirely
natural keys these can change. Say that you get the idea to use the
stock symbol as the key for financial instruments. Then the company changes
the name, and gets a new symbol. With the symbol as key, you have to update
all tables where the symbol appears. With a surrogate key, there is no
need to.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Immutable primary keys yield many benefits.
A mutable primary key will make your code less stable. It's possible in a
concurrent environment to read a row with a particular primary key value and
while you're working on it for another transaction or transactions to make
changes so that when you initiate your update, it appears that the row no
longer exists, or even worse, that the row exists even though it's really
another row. Here's a simple example: Assume that you're tracking a part
as it moves from machine to machine on an assembly line. Assume also that
only one part can be manipulated by one machine at one time. The mutable
primary key is this example is {PartNo, Location}. Now, you read the row
with key {'45G', 'PRESS01'} prior to some manipulation. While you're
working on the row, the part is moved from 'PRESS01' to 'FORMER01', so it
now appears to you that the row no longer exists. If another part '45G' is
moved onto 'PRESS01' prior to your update, then it will appear to you that
the row still exists even though it's a different part, and you may
erroneously update the row for the wrong part. To counter this, you must
either lock and hold the row when you read it (not a very attractive
prospect because it will severely reduce concurrency and will preclude the
use of disconnected datasets, message queues, etc.), or write a ton of code
on the client end to detect the change--which may not always be possible or
practical. Note that this problem increases in complexity when there are
related tables, because it's possible for a row to look the same on the
primary key table, but to actually refer to a different row with a different
set of related rows. With an IDENTITY primary key, as each part is placed
in production a new row is added and a new IDENTITY value is generated.
Because that value cannot change, when you go to perform your update, you
can determine not only that the part has moved, but exactly where it is now.
In addition, the problem with related tables cannot occur, because the
related rows refer to a value that cannot change.
Another problem lies with UPDATE triggers that are used for auditing or to
implement transition constraints. SQL Server update triggers have two
pseudotables, deleted and inserted, which contain the old and new values for
each row that was updated. When an update affects more than one row, there
is no supported mechanism to determine which row in the inserted pseudotable
corresponds to each row in the deleted pseudotable. (Oracle has a FOR EACH
ROW trigger, which I've been begging Microsoft to implement.) IDENTITY
solves this problem because since the key cannot change, you can join the
deleted and inseted pseudotables on the IDENTITY column and determine
exactly what happened to each row.
Cascading updates cause more problems than they're worth.
The most common form of avoidable deadlock is caused by multiple
transactions obtaining and holding locks on rows in more than one table in a
different order. To combat this, you must make sure that you obtain locks
on tables in the same order in every procedure, function, trigger, and
batch. Cascading updates throw a wrench into this. There is no way to
determine with any degree of certainty the order in which related rows will
be locked when there is a cascading heirarchy present. This makes it much
more difficult--if not impossible--to determine a locking order that will
eliminate avoidable deadlocks.
Another problem with cascading updates is that whenever a change is made,
the rowversion (timestamp) on each affected row is updated. Do you really
want to indicate that a Sales Order has been changed when only the
salesperson's employee number has been changed? It makes sense to indicate
a change when the Sales Order is assigned to another salesperson, but in
this case the change is cosmetic, not material, and in my opinion should not
occur.
This brings up another problem: triggers on every affected table throughout
the cascading heirarchy also fire. It's much more difficult to determine
whether they fired for simply a cosmetic change, or if there is a material
change that should be validated against business rules.
All of these problems occur because a database that uses natural primary
keys is riddled with redundancy. For this reason among others, I advocate
the use of surrogate keys and in particular, IDENTITY at the physical level.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23nPAi%2309FHA.3884@.TK2MSFTNGP10.phx.gbl...
>I understand 3, 4 and take 5 for granted (some internal DBMS matter I
>presume), but could you elaborate a bit on 1 and 2?
> 1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
> identcol=456 ?
> 2) What does cascading have to do with it? (as I understand cascading,
> it's like triggers where change in one record triggers changes in other
> tables/records?)
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OIHDWpz9FHA.3168@.TK2MSFTNGP10.phx.gbl...
>|||>> Let's get back to the basics of an RDBMS. Rows are not records; fields ar
e not columns; tables are not files.
If (a,b,c) is a key in the data model, then you have to make it a key
in the schema. Well, you want things screwed.
Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
type, not a relational and not verifiable, it is redundant in the face
of a real key.
How do you guarantee that you have the IDENTITY and the real key in
synch? Most "ID-iots" do not bother with a real key (they mimic a
sequential file and pointer chains instead) and get redundant rows when
someone posts the same data multiple times.
Answer: it is impossible and therefore data integrity is impossible. I
am just starting to do some SOX consulting work; I will flunk your
database for this. There was some discussion of this at CA a few w
s
ago.
A good RDBMS will handle the access for you, so that you do not have to
drop down to that level.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
> type, not a relational and not verifiable, it is redundant in the face
> of a real key.
New readers should note that this is Joe Celko's private definition, and
thus nothing to bother about.
I could say that by definition Joe Celko is always wrong, but that would
not be a very strong argument. A much stronger argument is that far too many
of his posts consists of inaccurate standard rants, and where the main
rationale for the post is to insult the person who asked.
> How do you guarantee that you have the IDENTITY and the real key in
> synch?
In Lisa's case, it appears to be simple: she should have a UNIQUE
cosntraints on the real key. However, there are plentyful of cases where
there is no real key, or where any "real key" is far beyond being
practically usable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||So, DB2, Oracle, Sybase and Microsoft SQL Server are not 'good RDBMS'? Seems
the market disagrees.
The whole point about using a surrogate key with the IDENTITY property is so
that you gain better performance, reduce complexity for backups, security
etc... as well as being able to change your natural key without having to
build a horrendous transaction of multiple update statements that will lock
up your tables while its being done - usually people cluster on the primary
key (natural key).
People only get redundant rows if they forget to add constraints to their
table, the surrogate key definition would be,..
create table individual (
id int not null identity constraint sk_individual unique
clustered,
last_name varchar(50) not null,
first_name varchar(50) not null,
dob int not null,
constraint pk_individual primary key nonclustered( last_name,
first_name, dob )
)
Mind you, we both know that the natural key on individual isn't the above,
in fact their isn't an easy one - consider a user group like my own, do you
want me to ask people for the NI (social security) number ? I wouldn't get
many members, I'd probably not even get my registration under the Data
Protection act here in the UK passed either.
Well fud - go out and get some bloody experience on real systems please!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133661996.695550.257580@.o13g2000cwo.googlegroups.com...
> If (a,b,c) is a key in the data model, then you have to make it a key
> in the schema. Well, you want things screwed.
> Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
> type, not a relational and not verifiable, it is redundant in the face
> of a real key.
> How do you guarantee that you have the IDENTITY and the real key in
> synch? Most "ID-iots" do not bother with a real key (they mimic a
> sequential file and pointer chains instead) and get redundant rows when
> someone posts the same data multiple times.
> Answer: it is impossible and therefore data integrity is impossible. I
> am just starting to do some SOX consulting work; I will flunk your
> database for this. There was some discussion of this at CA a few ws
> ago.
> A good RDBMS will handle the access for you, so that you do not have to
> drop down to that level.
>
A very basic question:
Imagine I have a table with multiple fields, some strings, some integers
etc.
None of these fields are unique on their own, but all fields together is
unique per record and thus can be used as the primary key.
However, referring to a record based on all these columns is quite
cumbersome (and not efficient, or does indexing take care of this nearly
entirely compared to a single identity field?), so how is this situation
best solved in practice.
Do I use an ugly IDENTITY column just for 'convenience' or continue to use
all fields, resulting in huge queries that lose legibility, or is there
another way?
LisaLisa Pearlson wrote:
> Do I use an ugly IDENTITY column just for 'convenience' or continue
> to use all fields, resulting in huge queries that lose legibility, or
> is there another way?
I would use "an ugle IDENTITY column" without doubt! Maybe others have
different opinions. It'll make your life a less easier. Otherwise if
you want to refer to that row in a foreign key you need to include all
the columns of the key which isn't really doable.
Kind regards,
Stijn Verrept.|||Lisa Pearlson (no@.spam.plz) writes:
> A very basic question:
> Imagine I have a table with multiple fields, some strings, some integers
> etc.
> None of these fields are unique on their own, but all fields together is
> unique per record and thus can be used as the primary key.
> However, referring to a record based on all these columns is quite
> cumbersome (and not efficient, or does indexing take care of this nearly
> entirely compared to a single identity field?), so how is this situation
> best solved in practice.
> Do I use an ugly IDENTITY column just for 'convenience' or continue to use
> all fields, resulting in huge queries that lose legibility, or is there
> another way?
It doesn't have to be IDENTITY, you can roll your own as well. But judging
from the shallow description you give, it appears that this could be a
solution. But you should add UNIQUE constraint on the other columns as well
to ensure their uniqueness.
I like to point out that adding a surrogate key does not always make things
simpler. I had a table with a four-column key, and then I needed to add a
sub-table with two more keys. Since a six-column key sounded too much, I
added a surrogate key to the main table. Years later I had reason to
write code to maintain these tables. Turned out that the surrogate key
made this a whole lot more complex. So the next time I had revise those
tables, I removed the surrogate key. (I was also able to remove one the
columns in the four-column key, and one of the keys in the sub-table.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Using the terms "field" and "record" will bring down the wrath of Celko!
I use IDENTITY for a number of reasons, and none of them are for
convenience:
(1) An IDENTITY value is immutable.
(2) Cascading updates are not required to maintain integrity.
(3) IDENTITY reduces redundancy.
(4) Joins generally perform better on IDENTITY values.
(5) IDENTITY reduces lock contention.
There are other reasons, and I could expound further on the merits of each
of the above.
It is not unusual in a logical database design to have tables with compound
primary keys; however, when it comes time to implement the design,
surrogates--including IDENTITY--make a lot more sense.
One more thing: if you do use an IDENTITY PRIMARY KEY, be sure to create a
UNIQUE constraint or index on the combination of columns that are together
unique per row. Alternate keys should be enforced by the database with a
UNIQUE constraint.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23hOXsSz9FHA.3928@.TK2MSFTNGP11.phx.gbl...
> Hi,
> A very basic question:
> Imagine I have a table with multiple fields, some strings, some integers
> etc.
> None of these fields are unique on their own, but all fields together is
> unique per record and thus can be used as the primary key.
> However, referring to a record based on all these columns is quite
> cumbersome (and not efficient, or does indexing take care of this nearly
> entirely compared to a single identity field?), so how is this situation
> best solved in practice.
> Do I use an ugly IDENTITY column just for 'convenience' or continue to use
> all fields, resulting in huge queries that lose legibility, or is there
> another way?
> Lisa
>|||I understand 3, 4 and take 5 for granted (some internal DBMS matter I
presume), but could you elaborate a bit on 1 and 2?
1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
identcol=456 ?
2) What does cascading have to do with it? (as I understand cascading, it's
like triggers where change in one record triggers changes in other
tables/records?)
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:OIHDWpz9FHA.3168@.TK2MSFTNGP10.phx.gbl...
> Using the terms "field" and "record" will bring down the wrath of Celko!
> I use IDENTITY for a number of reasons, and none of them are for
> convenience:
> (1) An IDENTITY value is immutable.
> (2) Cascading updates are not required to maintain integrity.
> (3) IDENTITY reduces redundancy.
> (4) Joins generally perform better on IDENTITY values.
> (5) IDENTITY reduces lock contention.
> There are other reasons, and I could expound further on the merits of each
> of the above.
> It is not unusual in a logical database design to have tables with
> compound primary keys; however, when it comes time to implement the
> design, surrogates--including IDENTITY--make a lot more sense.
> One more thing: if you do use an IDENTITY PRIMARY KEY, be sure to create a
> UNIQUE constraint or index on the combination of columns that are together
> unique per row. Alternate keys should be enforced by the database with a
> UNIQUE constraint.
>
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:%23hOXsSz9FHA.3928@.TK2MSFTNGP11.phx.gbl...
>|||Lisa Pearlson (no@.spam.plz) writes:
> I understand 3, 4 and take 5 for granted (some internal DBMS matter I
> presume), but could you elaborate a bit on 1 and 2?
> 1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
> identcol=456 ?
> 2) What does cascading have to do with it? (as I understand cascading,
> it's like triggers where change in one record triggers changes in other
> tables/records?)
Actually, I think of the reasons that Brian listed, only 5 is really
applicable to IDENTITY columns, although 1 has a touch to it: if you
have an IDENTITY property on the key, you know that the value cannot
be updated, not even by mistake.
Points 2-4 applies to surrogate keys in general, no matter if they have
IDENTITY or not.
I guess what Brian means with cascading is that if you use entirely
natural keys these can change. Say that you get the idea to use the
stock symbol as the key for financial instruments. Then the company changes
the name, and gets a new symbol. With the symbol as key, you have to update
all tables where the symbol appears. With a surrogate key, there is no
need to.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Immutable primary keys yield many benefits.
A mutable primary key will make your code less stable. It's possible in a
concurrent environment to read a row with a particular primary key value and
while you're working on it for another transaction or transactions to make
changes so that when you initiate your update, it appears that the row no
longer exists, or even worse, that the row exists even though it's really
another row. Here's a simple example: Assume that you're tracking a part
as it moves from machine to machine on an assembly line. Assume also that
only one part can be manipulated by one machine at one time. The mutable
primary key is this example is {PartNo, Location}. Now, you read the row
with key {'45G', 'PRESS01'} prior to some manipulation. While you're
working on the row, the part is moved from 'PRESS01' to 'FORMER01', so it
now appears to you that the row no longer exists. If another part '45G' is
moved onto 'PRESS01' prior to your update, then it will appear to you that
the row still exists even though it's a different part, and you may
erroneously update the row for the wrong part. To counter this, you must
either lock and hold the row when you read it (not a very attractive
prospect because it will severely reduce concurrency and will preclude the
use of disconnected datasets, message queues, etc.), or write a ton of code
on the client end to detect the change--which may not always be possible or
practical. Note that this problem increases in complexity when there are
related tables, because it's possible for a row to look the same on the
primary key table, but to actually refer to a different row with a different
set of related rows. With an IDENTITY primary key, as each part is placed
in production a new row is added and a new IDENTITY value is generated.
Because that value cannot change, when you go to perform your update, you
can determine not only that the part has moved, but exactly where it is now.
In addition, the problem with related tables cannot occur, because the
related rows refer to a value that cannot change.
Another problem lies with UPDATE triggers that are used for auditing or to
implement transition constraints. SQL Server update triggers have two
pseudotables, deleted and inserted, which contain the old and new values for
each row that was updated. When an update affects more than one row, there
is no supported mechanism to determine which row in the inserted pseudotable
corresponds to each row in the deleted pseudotable. (Oracle has a FOR EACH
ROW trigger, which I've been begging Microsoft to implement.) IDENTITY
solves this problem because since the key cannot change, you can join the
deleted and inseted pseudotables on the IDENTITY column and determine
exactly what happened to each row.
Cascading updates cause more problems than they're worth.
The most common form of avoidable deadlock is caused by multiple
transactions obtaining and holding locks on rows in more than one table in a
different order. To combat this, you must make sure that you obtain locks
on tables in the same order in every procedure, function, trigger, and
batch. Cascading updates throw a wrench into this. There is no way to
determine with any degree of certainty the order in which related rows will
be locked when there is a cascading heirarchy present. This makes it much
more difficult--if not impossible--to determine a locking order that will
eliminate avoidable deadlocks.
Another problem with cascading updates is that whenever a change is made,
the rowversion (timestamp) on each affected row is updated. Do you really
want to indicate that a Sales Order has been changed when only the
salesperson's employee number has been changed? It makes sense to indicate
a change when the Sales Order is assigned to another salesperson, but in
this case the change is cosmetic, not material, and in my opinion should not
occur.
This brings up another problem: triggers on every affected table throughout
the cascading heirarchy also fire. It's much more difficult to determine
whether they fired for simply a cosmetic change, or if there is a material
change that should be validated against business rules.
All of these problems occur because a database that uses natural primary
keys is riddled with redundancy. For this reason among others, I advocate
the use of surrogate keys and in particular, IDENTITY at the physical level.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23nPAi%2309FHA.3884@.TK2MSFTNGP10.phx.gbl...
>I understand 3, 4 and take 5 for granted (some internal DBMS matter I
>presume), but could you elaborate a bit on 1 and 2?
> 1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
> identcol=456 ?
> 2) What does cascading have to do with it? (as I understand cascading,
> it's like triggers where change in one record triggers changes in other
> tables/records?)
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OIHDWpz9FHA.3168@.TK2MSFTNGP10.phx.gbl...
>|||>> Let's get back to the basics of an RDBMS. Rows are not records; fields ar
e not columns; tables are not files.
If (a,b,c) is a key in the data model, then you have to make it a key
in the schema. Well, you want things screwed.
Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
type, not a relational and not verifiable, it is redundant in the face
of a real key.
How do you guarantee that you have the IDENTITY and the real key in
synch? Most "ID-iots" do not bother with a real key (they mimic a
sequential file and pointer chains instead) and get redundant rows when
someone posts the same data multiple times.
Answer: it is impossible and therefore data integrity is impossible. I
am just starting to do some SOX consulting work; I will flunk your
database for this. There was some discussion of this at CA a few w
sago.
A good RDBMS will handle the access for you, so that you do not have to
drop down to that level.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
> type, not a relational and not verifiable, it is redundant in the face
> of a real key.
New readers should note that this is Joe Celko's private definition, and
thus nothing to bother about.
I could say that by definition Joe Celko is always wrong, but that would
not be a very strong argument. A much stronger argument is that far too many
of his posts consists of inaccurate standard rants, and where the main
rationale for the post is to insult the person who asked.
> How do you guarantee that you have the IDENTITY and the real key in
> synch?
In Lisa's case, it appears to be simple: she should have a UNIQUE
cosntraints on the real key. However, there are plentyful of cases where
there is no real key, or where any "real key" is far beyond being
practically usable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||So, DB2, Oracle, Sybase and Microsoft SQL Server are not 'good RDBMS'? Seems
the market disagrees.
The whole point about using a surrogate key with the IDENTITY property is so
that you gain better performance, reduce complexity for backups, security
etc... as well as being able to change your natural key without having to
build a horrendous transaction of multiple update statements that will lock
up your tables while its being done - usually people cluster on the primary
key (natural key).
People only get redundant rows if they forget to add constraints to their
table, the surrogate key definition would be,..
create table individual (
id int not null identity constraint sk_individual unique
clustered,
last_name varchar(50) not null,
first_name varchar(50) not null,
dob int not null,
constraint pk_individual primary key nonclustered( last_name,
first_name, dob )
)
Mind you, we both know that the natural key on individual isn't the above,
in fact their isn't an easy one - consider a user group like my own, do you
want me to ask people for the NI (social security) number ? I wouldn't get
many members, I'd probably not even get my registration under the Data
Protection act here in the UK passed either.
Well fud - go out and get some bloody experience on real systems please!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133661996.695550.257580@.o13g2000cwo.googlegroups.com...
> If (a,b,c) is a key in the data model, then you have to make it a key
> in the schema. Well, you want things screwed.
> Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
> type, not a relational and not verifiable, it is redundant in the face
> of a real key.
> How do you guarantee that you have the IDENTITY and the real key in
> synch? Most "ID-iots" do not bother with a real key (they mimic a
> sequential file and pointer chains instead) and get redundant rows when
> someone posts the same data multiple times.
> Answer: it is impossible and therefore data integrity is impossible. I
> am just starting to do some SOX consulting work; I will flunk your
> database for this. There was some discussion of this at CA a few w
s> ago.
> A good RDBMS will handle the access for you, so that you do not have to
> drop down to that level.
>
Design/load question
Hi. I am redesigning a database to be more normalized. There are 4 columns that appear in many of the tables that I plan to add to a new table. Here is what the database looks like now:
table1
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.
table2
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.
table3
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.
If I add a 4th table with the columns that are the same in each of the tables, I'd have this:
table1
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.
table2
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.
table3
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.
table4
samecolumn1
samecolumn2
samecolumn3
samecolumn4
To link table4 to the other tables, I'd define foreign keys on table4 that reference the primary keys of tables 1-3. What I'd like to avoid is having a composite key on table4 made up of the primary keys of tables 1-3. That doesn't sound correct. How do I do this? Also when it comes time to load data, I know that I need to populate tables 1-3 first, but how do I then uniquely identify the rows in tables 1-3 that must go into table4? Thanks for the help!
Thanks for the response. Here's what you asked for:
table1
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.
table2
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.
table3
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.
If I add a 4th table with the columns that are the same in each of the tables, I'd have this:
table1
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.
table2
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.
table3
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.
table4
samecolumn1
samecolumn2
samecolumn3
samecolumn4
To link table4 to the other tables, I'd define foreign keys on table4 that reference the primary keys of tables 1-3. What I'd like to avoid is having a composite key on table4 made up of the primary keys of tables 1-3. That doesn't sound correct. How do I do this? Also when it comes time to load data, I know that I need to populate tables 1-3 first, but how do I then uniquely identify the rows in tables 1-3 that must go into table4? Thanks for the help!
Byron,
Could you please be more specific on the column names for each table?
Steve
|||Steve,Thanks for the response. Here's what you asked for:
LodgingSummary (table1)
LodgingSummaryID
LoadTransactionCode
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses
CardTransaction (table2)
LoadTransactionCode
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
Period
AcquiringBIN
CardAcceptorID
SupplierName
SupplierCity
SupplierStateProvinceCode
FleetService (table3)
FleetServiceID
LoadTransactionCode
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
PurchaseType
FuelType
FuelUnitMeasureCode
FuelQuantity
FuelUnitCost
FuelGrossAmount
AccountInfo (table4)
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
|||
Byron,
CREATE TABLE AccountInfo
(
AccountNumber (PK)
PostingDate
TransactionReferenceNumber
SequenceNumber
)
CREATE TABLE CardTransaction
(
LoadTransactionCode (PK)
LodgingSummaryID(FK)--allow nulls
FleetServiceID (FK) --allow nulls
Period
AcquiringBIN
CardAcceptorID
SupplierName
SupplierCity
SupplierStateProvinceCode
)
CREATE TABLE LodgingSummary
(
LodgingSummaryID (PK)
AccountNumber
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses
)
CREATE TABLE FleetService
(
FleetServiceID (PK)
PurchaseType
FuelType
FuelUnitMeasureCode
FuelQuantity
FuelUnitCost
FuelGrossAmount
)
CREATE TABLE AccountInfo
(
AccountNumber (PK)
PostingDate
TransactionReferenceNumber
SequenceNumber
)
If you do this, then that person can never use this account again. Better off making a surrogate for this data:
CREATE TABLE AccountInfo
(
AccountInfoId int identity(1,1) primary key, --I don't care about the value of this, it is just a surrogate for:
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
UNIQUE (accountNumber, PostingDate, TransactionReferenceNumber, SequenceNumber)
)
I find that I am a bit wary of the data here. Is AcctNumber and TransactionRefNumber not unique in and of itself? Same with it and SequenceNumber. Make sure that your alt key(s) really define with is actually unique, and not just a blob like this (unless the same tranNumber and sequenceNumber might be repeated for the same account, but just on different days.
I am rarely happy when a date is part of a key that is not representing an Event of some sort (like if I had a table that recorded when I got gas for my car, it would have a date as the key.) I don't know your data, so I am not sure of course :)
|||Thanks for the response, Louis. I added my responses is blue.
"CREATE TABLE AccountInfo
(
AccountNumber (PK)
PostingDate
TransactionReferenceNumber
SequenceNumber
)
If you do this, then that person can never use this account again. Better off making a surrogate for this data:"
This is correct. There will be multiple transactions for the same account number."CREATE TABLE AccountInfo
(
AccountInfoId int identity(1,1) primary key, --I don't care about the value of this, it is just a surrogate for:
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
UNIQUE (accountNumber, PostingDate, TransactionReferenceNumber, SequenceNumber)
)
I find that I am a bit wary of the data here. Is AcctNumber and
TransactionRefNumber not unique in and of itself? Same with it and
SequenceNumber. Make sure that your alt key(s) really define with is
actually unique, and not just a blob like this (unless the same
tranNumber and sequenceNumber might be repeated for the same account,
but just on different days.
I am rarely happy when a date is part of a key that is not
representing an Event of some sort (like if I had a table that recorded
when I got gas for my car, it would have a date as the key.) I don't
know your data, so I am not sure of course :)"
There are many more tables that could have transactions related to travel, lodging, purchases, etc. These are all in different tables according to the data spec I have to live with. TransactionRefNumber should be unique, but there is no guarantee. When you said:"UNIQUE (accountNumber, PostingDate, TransactionReferenceNumber, SequenceNumber)"
What is this? Are you creating a unique constraint here? Thanks.|||One more thing, please. Now when I want to load my values into my newly normalized table (LodgingSummary), how do I select each row individually? LodgingSummary will have the primary key of AccountInfo as a foreign key. Let's look at a real table:
Table: LodgingSummary (the newly normalized table)
LodgingSummaryID (PK)
AccountInfoID (FK) <-- This is the primary key of AccountInfo
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses
Table: AccountInfo (the table I'm "normalizing" to)
AccountInfoID (PK)
AccountNumber <--moved from tbl LodgingSummary to tbl AccountInfo
PostingDate <--moved from tbl LodgingSummary to tbl AccountInfo
TransactionReferenceNumber <--moved from tbl LodgingSummary to tbl AccountInfo
SequenceNumber <--moved from tbl LodgingSummary to tbl AccountInfo
After the load, there will be a 1:1 relationship between the rows in the two tables (primary key to foreign key). My datasource for the load is the original de-normalized table:
Table load_LodgingSummary
LodgingSummaryID (PK)
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses
Here's my current code to load the normalized table:
[CODE]
set identity_insert lodgingsummary on
insert into lodgingsummary
(
LodgingSummaryID,
AccountInfoID,
LoadTransactionCode,
NoShowIndicator,
CheckInDate,
DailyRoomRate,
TotalOtherCharges,
TotalTaxAmount,
TotalFoodBeverageCharges,
TotalPrepaidExpenses
)
select
LodgingSummaryID,
(select ai.accountinfoID AccountInfoID from accountinfo ai,
load_LodgingSummary ll
where ai.accountnumber=ll.accountnumber and ai.postingdate=ll.postingdate
and
ai.TransactionReferenceNumber=ll.TransactionReferenceNumber
and ai.sequencenumber=ll.sequencenumber),
LoadTransactionCode,
NoShowIndicator,
CheckInDate,
DailyRoomRate,
TotalOtherCharges,
TotalTaxAmount,
TotalFoodBeverageCharges,
TotalPrepaidExpenses
from load_lodgingsummary
set identity_insert lodgingsummary off
[/CODE]
The subquery in the INSERT will return more than 1 AccountInfoID. Is there any way to write a statement that will load each row individually or do I need to build a recordset and individually insert each row of the recordset?
Friday, February 24, 2012
Design Table; Columns/Description get with SP??
Hi,
No, functions don't create anything, they can only retrieve data. To
add an extended property from T-SQL, you use sp_addextendedproperty.
I'm using stored procedures to create an online data dictionary for
all of our dbs and I thought it would be very cool if there was a way
that I could somehow pull the columns description entered in the table
design view (lower pane) of EM. I have poked around, but have not
found a way. It is my suspcion that this may be impossible. Does any
smart person out there know?
Also want to pull pks and fks and other constraints, which I think I
can figure out how to do, but if anyone has any pointers, that would
also be appreciated.
Many Thanks,
Georgiavesta (vesta0424@.gmail.com) writes:
Quote:
Originally Posted by
It looks to me like you create your extended properties with this
function. Are there any default functions to get the information that
has been manually entered in the description field?
No, functions don't create anything, they can only retrieve data. To
add an extended property from T-SQL, you use sp_addextendedproperty.
I cannot really give any examples of using either, because I have not
used extended properties myself very much. Or rather not at all.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Design Slow
I'm building a report with dozens of grids and around 8 columns per grid with
3-4 groups each. The report runs fine (slow because of the amount of data
but that's expected). When I am in the report designer editing the report,
simply trying to widen a column, change a label, add a text box or move a
grid is UNBELIEVABLY slow.
Can this be fixed? My guess is Visual Studio is "memorizing" the action
being performed as a type of macro to enable "undos" and based on the volume
of grids, text boxes, etc... it take a long time to do this.How much RAM do you have on your PC?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> I'm building a report with dozens of grids and around 8 columns per grid
> with
> 3-4 groups each. The report runs fine (slow because of the amount of data
> but that's expected). When I am in the report designer editing the
> report,
> simply trying to widen a column, change a label, add a text box or move a
> grid is UNBELIEVABLY slow.
> Can this be fixed? My guess is Visual Studio is "memorizing" the action
> being performed as a type of macro to enable "undos" and based on the
> volume
> of grids, text boxes, etc... it take a long time to do this.|||2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
132mb when working on just this .rdl report.
"Bruce L-C [MVP]" wrote:
> How much RAM do you have on your PC?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> > I'm building a report with dozens of grids and around 8 columns per grid
> > with
> > 3-4 groups each. The report runs fine (slow because of the amount of data
> > but that's expected). When I am in the report designer editing the
> > report,
> > simply trying to widen a column, change a label, add a text box or move a
> > grid is UNBELIEVABLY slow.
> >
> > Can this be fixed? My guess is Visual Studio is "memorizing" the action
> > being performed as a type of macro to enable "undos" and based on the
> > volume
> > of grids, text boxes, etc... it take a long time to do this.
>
>|||My guess is that you are hitting some sort of resource problem (not a ram
problem however).
Dozens of grids is pretty unusual. One thing you might consider is that
instead of having all these grids on one report is to have a report with
multiple subreports. I hide the subreports in listview so the user does not
see it (that is a property of the report you can get to with report
manager). This enables you to test each subreport independently. Then you
drag and drop subreport onto the main report, hook up the parameters and
away you go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
>2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
> 132mb when working on just this .rdl report.
> "Bruce L-C [MVP]" wrote:
>> How much RAM do you have on your PC?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
>> > I'm building a report with dozens of grids and around 8 columns per
>> > grid
>> > with
>> > 3-4 groups each. The report runs fine (slow because of the amount of
>> > data
>> > but that's expected). When I am in the report designer editing the
>> > report,
>> > simply trying to widen a column, change a label, add a text box or move
>> > a
>> > grid is UNBELIEVABLY slow.
>> >
>> > Can this be fixed? My guess is Visual Studio is "memorizing" the
>> > action
>> > being performed as a type of macro to enable "undos" and based on the
>> > volume
>> > of grids, text boxes, etc... it take a long time to do this.
>>|||Ok, I'm expirementing with the sub report route and design seems faster if I
can work out my layout issues. Each subreport will be calling the same 4
store procs, each of which is a MONSTER. So, my question is if I use
subreports, am I hammering the SQL Server more by calling each ofthese 4
procs "repetitively" for each sub report, rather than a single call to each
proc from a single monster report with dozens of grids.
"Bruce L-C [MVP]" wrote:
> My guess is that you are hitting some sort of resource problem (not a ram
> problem however).
> Dozens of grids is pretty unusual. One thing you might consider is that
> instead of having all these grids on one report is to have a report with
> multiple subreports. I hide the subreports in listview so the user does not
> see it (that is a property of the report you can get to with report
> manager). This enables you to test each subreport independently. Then you
> drag and drop subreport onto the main report, hook up the parameters and
> away you go.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
> > 132mb when working on just this .rdl report.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> How much RAM do you have on your PC?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> >> > I'm building a report with dozens of grids and around 8 columns per
> >> > grid
> >> > with
> >> > 3-4 groups each. The report runs fine (slow because of the amount of
> >> > data
> >> > but that's expected). When I am in the report designer editing the
> >> > report,
> >> > simply trying to widen a column, change a label, add a text box or move
> >> > a
> >> > grid is UNBELIEVABLY slow.
> >> >
> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
> >> > action
> >> > being performed as a type of macro to enable "undos" and based on the
> >> > volume
> >> > of grids, text boxes, etc... it take a long time to do this.
> >>
> >>
> >>
>
>|||Hmmm, so your grids are tied to the same dataset(s). I.e. in the monster
report you have only 4 datasets for all the grids?
Well, then the answer is yes you will be calling it once for each subreport.
If it would work for you to have 4 subreports where each subreport has all
the grids for one dataset. Then you main report does nothing other than host
the four subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:1F36FDE2-0CA5-4017-BA68-CF385753A3C0@.microsoft.com...
> Ok, I'm expirementing with the sub report route and design seems faster if
> I
> can work out my layout issues. Each subreport will be calling the same 4
> store procs, each of which is a MONSTER. So, my question is if I use
> subreports, am I hammering the SQL Server more by calling each ofthese 4
> procs "repetitively" for each sub report, rather than a single call to
> each
> proc from a single monster report with dozens of grids.
> "Bruce L-C [MVP]" wrote:
>> My guess is that you are hitting some sort of resource problem (not a ram
>> problem however).
>> Dozens of grids is pretty unusual. One thing you might consider is that
>> instead of having all these grids on one report is to have a report with
>> multiple subreports. I hide the subreports in listview so the user does
>> not
>> see it (that is a property of the report you can get to with report
>> manager). This enables you to test each subreport independently. Then you
>> drag and drop subreport onto the main report, hook up the parameters and
>> away you go.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
>> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using
>> >about
>> > 132mb when working on just this .rdl report.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> How much RAM do you have on your PC?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
>> >> > I'm building a report with dozens of grids and around 8 columns per
>> >> > grid
>> >> > with
>> >> > 3-4 groups each. The report runs fine (slow because of the amount
>> >> > of
>> >> > data
>> >> > but that's expected). When I am in the report designer editing the
>> >> > report,
>> >> > simply trying to widen a column, change a label, add a text box or
>> >> > move
>> >> > a
>> >> > grid is UNBELIEVABLY slow.
>> >> >
>> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
>> >> > action
>> >> > being performed as a type of macro to enable "undos" and based on
>> >> > the
>> >> > volume
>> >> > of grids, text boxes, etc... it take a long time to do this.
>> >>
>> >>
>> >>
>>|||Yes, I did some profiling and unfortunately, based on the complexity of the
underlying stored procs, I don't think I can justify calling them each
multiple times in production just speed design/development. I will try to
achieve something similar to what you are saying by trying to use a List
Boxes for a repeated groups...this may cut down on the number of grids and
help me.
Thanks for all your input.
"Bruce L-C [MVP]" wrote:
> Hmmm, so your grids are tied to the same dataset(s). I.e. in the monster
> report you have only 4 datasets for all the grids?
> Well, then the answer is yes you will be calling it once for each subreport.
> If it would work for you to have 4 subreports where each subreport has all
> the grids for one dataset. Then you main report does nothing other than host
> the four subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:1F36FDE2-0CA5-4017-BA68-CF385753A3C0@.microsoft.com...
> > Ok, I'm expirementing with the sub report route and design seems faster if
> > I
> > can work out my layout issues. Each subreport will be calling the same 4
> > store procs, each of which is a MONSTER. So, my question is if I use
> > subreports, am I hammering the SQL Server more by calling each ofthese 4
> > procs "repetitively" for each sub report, rather than a single call to
> > each
> > proc from a single monster report with dozens of grids.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> My guess is that you are hitting some sort of resource problem (not a ram
> >> problem however).
> >>
> >> Dozens of grids is pretty unusual. One thing you might consider is that
> >> instead of having all these grids on one report is to have a report with
> >> multiple subreports. I hide the subreports in listview so the user does
> >> not
> >> see it (that is a property of the report you can get to with report
> >> manager). This enables you to test each subreport independently. Then you
> >> drag and drop subreport onto the main report, hook up the parameters and
> >> away you go.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
> >> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using
> >> >about
> >> > 132mb when working on just this .rdl report.
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> How much RAM do you have on your PC?
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> >> >> > I'm building a report with dozens of grids and around 8 columns per
> >> >> > grid
> >> >> > with
> >> >> > 3-4 groups each. The report runs fine (slow because of the amount
> >> >> > of
> >> >> > data
> >> >> > but that's expected). When I am in the report designer editing the
> >> >> > report,
> >> >> > simply trying to widen a column, change a label, add a text box or
> >> >> > move
> >> >> > a
> >> >> > grid is UNBELIEVABLY slow.
> >> >> >
> >> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
> >> >> > action
> >> >> > being performed as a type of macro to enable "undos" and based on
> >> >> > the
> >> >> > volume
> >> >> > of grids, text boxes, etc... it take a long time to do this.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
3-4 groups each. The report runs fine (slow because of the amount of data
but that's expected). When I am in the report designer editing the report,
simply trying to widen a column, change a label, add a text box or move a
grid is UNBELIEVABLY slow.
Can this be fixed? My guess is Visual Studio is "memorizing" the action
being performed as a type of macro to enable "undos" and based on the volume
of grids, text boxes, etc... it take a long time to do this.How much RAM do you have on your PC?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> I'm building a report with dozens of grids and around 8 columns per grid
> with
> 3-4 groups each. The report runs fine (slow because of the amount of data
> but that's expected). When I am in the report designer editing the
> report,
> simply trying to widen a column, change a label, add a text box or move a
> grid is UNBELIEVABLY slow.
> Can this be fixed? My guess is Visual Studio is "memorizing" the action
> being performed as a type of macro to enable "undos" and based on the
> volume
> of grids, text boxes, etc... it take a long time to do this.|||2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
132mb when working on just this .rdl report.
"Bruce L-C [MVP]" wrote:
> How much RAM do you have on your PC?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> > I'm building a report with dozens of grids and around 8 columns per grid
> > with
> > 3-4 groups each. The report runs fine (slow because of the amount of data
> > but that's expected). When I am in the report designer editing the
> > report,
> > simply trying to widen a column, change a label, add a text box or move a
> > grid is UNBELIEVABLY slow.
> >
> > Can this be fixed? My guess is Visual Studio is "memorizing" the action
> > being performed as a type of macro to enable "undos" and based on the
> > volume
> > of grids, text boxes, etc... it take a long time to do this.
>
>|||My guess is that you are hitting some sort of resource problem (not a ram
problem however).
Dozens of grids is pretty unusual. One thing you might consider is that
instead of having all these grids on one report is to have a report with
multiple subreports. I hide the subreports in listview so the user does not
see it (that is a property of the report you can get to with report
manager). This enables you to test each subreport independently. Then you
drag and drop subreport onto the main report, hook up the parameters and
away you go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
>2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
> 132mb when working on just this .rdl report.
> "Bruce L-C [MVP]" wrote:
>> How much RAM do you have on your PC?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
>> > I'm building a report with dozens of grids and around 8 columns per
>> > grid
>> > with
>> > 3-4 groups each. The report runs fine (slow because of the amount of
>> > data
>> > but that's expected). When I am in the report designer editing the
>> > report,
>> > simply trying to widen a column, change a label, add a text box or move
>> > a
>> > grid is UNBELIEVABLY slow.
>> >
>> > Can this be fixed? My guess is Visual Studio is "memorizing" the
>> > action
>> > being performed as a type of macro to enable "undos" and based on the
>> > volume
>> > of grids, text boxes, etc... it take a long time to do this.
>>|||Ok, I'm expirementing with the sub report route and design seems faster if I
can work out my layout issues. Each subreport will be calling the same 4
store procs, each of which is a MONSTER. So, my question is if I use
subreports, am I hammering the SQL Server more by calling each ofthese 4
procs "repetitively" for each sub report, rather than a single call to each
proc from a single monster report with dozens of grids.
"Bruce L-C [MVP]" wrote:
> My guess is that you are hitting some sort of resource problem (not a ram
> problem however).
> Dozens of grids is pretty unusual. One thing you might consider is that
> instead of having all these grids on one report is to have a report with
> multiple subreports. I hide the subreports in listview so the user does not
> see it (that is a property of the report you can get to with report
> manager). This enables you to test each subreport independently. Then you
> drag and drop subreport onto the main report, hook up the parameters and
> away you go.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
> > 132mb when working on just this .rdl report.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> How much RAM do you have on your PC?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> >> > I'm building a report with dozens of grids and around 8 columns per
> >> > grid
> >> > with
> >> > 3-4 groups each. The report runs fine (slow because of the amount of
> >> > data
> >> > but that's expected). When I am in the report designer editing the
> >> > report,
> >> > simply trying to widen a column, change a label, add a text box or move
> >> > a
> >> > grid is UNBELIEVABLY slow.
> >> >
> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
> >> > action
> >> > being performed as a type of macro to enable "undos" and based on the
> >> > volume
> >> > of grids, text boxes, etc... it take a long time to do this.
> >>
> >>
> >>
>
>|||Hmmm, so your grids are tied to the same dataset(s). I.e. in the monster
report you have only 4 datasets for all the grids?
Well, then the answer is yes you will be calling it once for each subreport.
If it would work for you to have 4 subreports where each subreport has all
the grids for one dataset. Then you main report does nothing other than host
the four subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:1F36FDE2-0CA5-4017-BA68-CF385753A3C0@.microsoft.com...
> Ok, I'm expirementing with the sub report route and design seems faster if
> I
> can work out my layout issues. Each subreport will be calling the same 4
> store procs, each of which is a MONSTER. So, my question is if I use
> subreports, am I hammering the SQL Server more by calling each ofthese 4
> procs "repetitively" for each sub report, rather than a single call to
> each
> proc from a single monster report with dozens of grids.
> "Bruce L-C [MVP]" wrote:
>> My guess is that you are hitting some sort of resource problem (not a ram
>> problem however).
>> Dozens of grids is pretty unusual. One thing you might consider is that
>> instead of having all these grids on one report is to have a report with
>> multiple subreports. I hide the subreports in listview so the user does
>> not
>> see it (that is a property of the report you can get to with report
>> manager). This enables you to test each subreport independently. Then you
>> drag and drop subreport onto the main report, hook up the parameters and
>> away you go.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
>> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using
>> >about
>> > 132mb when working on just this .rdl report.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> How much RAM do you have on your PC?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
>> >> > I'm building a report with dozens of grids and around 8 columns per
>> >> > grid
>> >> > with
>> >> > 3-4 groups each. The report runs fine (slow because of the amount
>> >> > of
>> >> > data
>> >> > but that's expected). When I am in the report designer editing the
>> >> > report,
>> >> > simply trying to widen a column, change a label, add a text box or
>> >> > move
>> >> > a
>> >> > grid is UNBELIEVABLY slow.
>> >> >
>> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
>> >> > action
>> >> > being performed as a type of macro to enable "undos" and based on
>> >> > the
>> >> > volume
>> >> > of grids, text boxes, etc... it take a long time to do this.
>> >>
>> >>
>> >>
>>|||Yes, I did some profiling and unfortunately, based on the complexity of the
underlying stored procs, I don't think I can justify calling them each
multiple times in production just speed design/development. I will try to
achieve something similar to what you are saying by trying to use a List
Boxes for a repeated groups...this may cut down on the number of grids and
help me.
Thanks for all your input.
"Bruce L-C [MVP]" wrote:
> Hmmm, so your grids are tied to the same dataset(s). I.e. in the monster
> report you have only 4 datasets for all the grids?
> Well, then the answer is yes you will be calling it once for each subreport.
> If it would work for you to have 4 subreports where each subreport has all
> the grids for one dataset. Then you main report does nothing other than host
> the four subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:1F36FDE2-0CA5-4017-BA68-CF385753A3C0@.microsoft.com...
> > Ok, I'm expirementing with the sub report route and design seems faster if
> > I
> > can work out my layout issues. Each subreport will be calling the same 4
> > store procs, each of which is a MONSTER. So, my question is if I use
> > subreports, am I hammering the SQL Server more by calling each ofthese 4
> > procs "repetitively" for each sub report, rather than a single call to
> > each
> > proc from a single monster report with dozens of grids.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> My guess is that you are hitting some sort of resource problem (not a ram
> >> problem however).
> >>
> >> Dozens of grids is pretty unusual. One thing you might consider is that
> >> instead of having all these grids on one report is to have a report with
> >> multiple subreports. I hide the subreports in listview so the user does
> >> not
> >> see it (that is a property of the report you can get to with report
> >> manager). This enables you to test each subreport independently. Then you
> >> drag and drop subreport onto the main report, hook up the parameters and
> >> away you go.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
> >> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using
> >> >about
> >> > 132mb when working on just this .rdl report.
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> How much RAM do you have on your PC?
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> >> >> > I'm building a report with dozens of grids and around 8 columns per
> >> >> > grid
> >> >> > with
> >> >> > 3-4 groups each. The report runs fine (slow because of the amount
> >> >> > of
> >> >> > data
> >> >> > but that's expected). When I am in the report designer editing the
> >> >> > report,
> >> >> > simply trying to widen a column, change a label, add a text box or
> >> >> > move
> >> >> > a
> >> >> > grid is UNBELIEVABLY slow.
> >> >> >
> >> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
> >> >> > action
> >> >> > being performed as a type of macro to enable "undos" and based on
> >> >> > the
> >> >> > volume
> >> >> > of grids, text boxes, etc... it take a long time to do this.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Sunday, February 19, 2012
design question - so many columns....
Got a situation where our main, core data in our main tables consist
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?
On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.
|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.
|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -
|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.
|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
> 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?
On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.
|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.
|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -
|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.
|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
> 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.
design question - so many columns....
Got a situation where our main, core data in our main tables consist
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > Got a situation where our main, core data in our main tables consist
> > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > several "sets" of booleans. Example - health risk factors....high
> > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > say. The user can choose none, all, or any combo in between in these
> > sets of booleans. Well each of those fundamentally is just a bit
> > column, with a zero or one, attached to the record concerning the
> > individual person/event record. Well what if I have 8 or 9 "sets" of
> > these boolean questions? This results in 150-200+ columns in my
> > table. I know sql can handle up to 1024, and these data really belong
> > on this record with this individual person/event.
> > I just wanted to see what others thought from the design perspective.
> > Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
> > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > Got a situation where our main, core data in our main tables consist
> > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > several "sets" of booleans. Example - health risk factors....high
> > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > say. The user can choose none, all, or any combo in between in these
> > > sets of booleans. Well each of those fundamentally is just a bit
> > > column, with a zero or one, attached to the record concerning the
> > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > these boolean questions? This results in 150-200+ columns in my
> > > table. I know sql can handle up to 1024, and these data really belong
> > > on this record with this individual person/event.
> > > I just wanted to see what others thought from the design perspective.
> > > Any suggestions?
> > That's quite easy. Let's say you have one column called 'Risk Factor'
> > that can take 20 possible values. You can store values like
> > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > would prefer this. Another solution is you can have a varchar(20) with
> > values like 10001000 etc where you set a bit for particular risk
> > factor. You have to know the ordinal position for a particular risk
> > and if that bit is set or not.
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>> Got a situation where our main, core data in our main tables consist
>> of 15-20 normal columns (dates, integers, varchar, etc.) and then
>> several "sets" of booleans. Example - health risk factors....high
>> blood pressure, diabetes, depression....up to 20 risk factors, let's
>> say. The user can choose none, all, or any combo in between in these
>> sets of booleans. Well each of those fundamentally is just a bit
>> column, with a zero or one, attached to the record concerning the
>> individual person/event record. Well what if I have 8 or 9 "sets" of
>> these boolean questions? This results in 150-200+ columns in my
>> table. I know sql can handle up to 1024, and these data really belong
>> on this record with this individual person/event.
>> I just wanted to see what others thought from the design perspective.
>> Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers => 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
> > On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> > > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > > Got a situation where our main, core data in our main tables consist
> > > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > > several "sets" of booleans. Example - health risk factors....high
> > > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > > say. The user can choose none, all, or any combo in between in these
> > > > sets of booleans. Well each of those fundamentally is just a bit
> > > > column, with a zero or one, attached to the record concerning the
> > > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > > these boolean questions? This results in 150-200+ columns in my
> > > > table. I know sql can handle up to 1024, and these data really belong
> > > > on this record with this individual person/event.
> > > > I just wanted to see what others thought from the design perspective.
> > > > Any suggestions?
> > > That's quite easy. Let's say you have one column called 'Risk Factor'
> > > that can take 20 possible values. You can store values like
> > > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > > would prefer this. Another solution is you can have a varchar(20) with
> > > values like 10001000 etc where you set a bit for particular risk
> > > factor. You have to know the ordinal position for a particular risk
> > > and if that bit is set or not.
> > and of course if you want to be relational then you have a row for
> > each 'Risk Factor'. So instead of growing the table horizontally you
> > grow them vertically. So if a person has 'Diabetes' and 'High Blood
> > Pressure' there are 2 rows for that person. Then you can group by a
> > particular 'Risk Factor'. For example, find all patients where 'Risk
> > Factor' is 'Diabetes' and sum how much they spent etc on medication
> > etc.- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > Got a situation where our main, core data in our main tables consist
> > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > several "sets" of booleans. Example - health risk factors....high
> > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > say. The user can choose none, all, or any combo in between in these
> > sets of booleans. Well each of those fundamentally is just a bit
> > column, with a zero or one, attached to the record concerning the
> > individual person/event record. Well what if I have 8 or 9 "sets" of
> > these boolean questions? This results in 150-200+ columns in my
> > table. I know sql can handle up to 1024, and these data really belong
> > on this record with this individual person/event.
> > I just wanted to see what others thought from the design perspective.
> > Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
> > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > Got a situation where our main, core data in our main tables consist
> > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > several "sets" of booleans. Example - health risk factors....high
> > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > say. The user can choose none, all, or any combo in between in these
> > > sets of booleans. Well each of those fundamentally is just a bit
> > > column, with a zero or one, attached to the record concerning the
> > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > these boolean questions? This results in 150-200+ columns in my
> > > table. I know sql can handle up to 1024, and these data really belong
> > > on this record with this individual person/event.
> > > I just wanted to see what others thought from the design perspective.
> > > Any suggestions?
> > That's quite easy. Let's say you have one column called 'Risk Factor'
> > that can take 20 possible values. You can store values like
> > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > would prefer this. Another solution is you can have a varchar(20) with
> > values like 10001000 etc where you set a bit for particular risk
> > factor. You have to know the ordinal position for a particular risk
> > and if that bit is set or not.
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>> Got a situation where our main, core data in our main tables consist
>> of 15-20 normal columns (dates, integers, varchar, etc.) and then
>> several "sets" of booleans. Example - health risk factors....high
>> blood pressure, diabetes, depression....up to 20 risk factors, let's
>> say. The user can choose none, all, or any combo in between in these
>> sets of booleans. Well each of those fundamentally is just a bit
>> column, with a zero or one, attached to the record concerning the
>> individual person/event record. Well what if I have 8 or 9 "sets" of
>> these boolean questions? This results in 150-200+ columns in my
>> table. I know sql can handle up to 1024, and these data really belong
>> on this record with this individual person/event.
>> I just wanted to see what others thought from the design perspective.
>> Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers => 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
> > On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> > > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > > Got a situation where our main, core data in our main tables consist
> > > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > > several "sets" of booleans. Example - health risk factors....high
> > > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > > say. The user can choose none, all, or any combo in between in these
> > > > sets of booleans. Well each of those fundamentally is just a bit
> > > > column, with a zero or one, attached to the record concerning the
> > > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > > these boolean questions? This results in 150-200+ columns in my
> > > > table. I know sql can handle up to 1024, and these data really belong
> > > > on this record with this individual person/event.
> > > > I just wanted to see what others thought from the design perspective.
> > > > Any suggestions?
> > > That's quite easy. Let's say you have one column called 'Risk Factor'
> > > that can take 20 possible values. You can store values like
> > > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > > would prefer this. Another solution is you can have a varchar(20) with
> > > values like 10001000 etc where you set a bit for particular risk
> > > factor. You have to know the ordinal position for a particular risk
> > > and if that bit is set or not.
> > and of course if you want to be relational then you have a row for
> > each 'Risk Factor'. So instead of growing the table horizontally you
> > grow them vertically. So if a person has 'Diabetes' and 'High Blood
> > Pressure' there are 2 rows for that person. Then you can group by a
> > particular 'Risk Factor'. For example, find all patients where 'Risk
> > Factor' is 'Diabetes' and sum how much they spent etc on medication
> > etc.- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.
Subscribe to:
Posts (Atom)