Data Flow Task Problem: tables

Showing posts with label tables. Show all posts

Thursday, March 29, 2012

Data file space used calculations

Hi,

Is there any table I can query from to get the space used by a data or log file? I can get this info from TaskPad but I want to query from tables. The sp_spaceused procedure gets the info for a database or table but not the 'data or log' files.

Thanks for any help.

VinnieYou can do this

select cast(size * 8 as int) as Size from dbo.sysfiles

The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)

HTH|||Originally posted by rhigdon
You can do this

select cast(size * 8 as int) as Size from dbo.sysfiles

The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)

HTH

Thanks a lot but the sysfiles can give me only size whereas I need the space used and space free for a data file /log file. Any other suggestions are appreciated.

Thanks in advance.|||For log file you can do:

dbcc sqlperf('logspace')

I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.

HTH|||Originally posted by rhigdon
For log file you can do:

dbcc sqlperf('logspace')

I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.

HTH

Hi,

That is exactly what I am looking for is 'data file'. I could get for log file in sysperfinfo table. I was unsuccessful to find any info for datafile to calculate space used or space free. How does the Taskpad display these things? They may be doing from table. What is it is the question? If any one can help It will be a great help.

Thanks
Vinnie|||True, you can use SP_SPACEUSED to get the data, index space usage and DBCC SQLPERF(LOGSPACE) for the %age of Tlog used for any database.

Run SP_HELPFILE to get the information for physical files associated to that database.

Refer to Vyas's link (http://vyaskn.tripod.com/track_sql_database_file_growth.htm) for more information.

Tuesday, March 27, 2012

Data Extract Question

Hi, I'm hoping someone has an idea or two on this topic. Basically I have three tables of data say tContact, tQuestion, tAnswer
tContact
----
ContactID
Email
Name
tQuestion
----
QuestionID
Question
tAnswer
----
QuestionID
ContactID
Answer
I need to extract the data for the client and they would like to seethe data with one line per contact, but showing every answer to everyquestion... they would like the data formatted like this:
ContactID, Email, Name, Question1Answer, Question2Answer, Question3Answer, Question4Answer, etc......
Obviously to get the data I cansimply do an outerjoin to get allcontact data then all questions, and answers that exist... but thatwill obviously return tabular data with one row per eachanswer... Does anyone have any ideas on how to do this using justSQL? I can pull the data and write a function that spits it outto text using the Stringbuilder class and some logic, but I'm thinkingthis must be possible in SQL natively... any help would be more thanappreciated. Thanks in advance.
-e

emaxwell wrote:
Does anyone have any ideas on how to do this using justSQL? I can pull the data and write a function that spits it outto text using the Stringbuilder class and some logic, but I'm thinkingthis must be possible in SQL natively

Unfortunately SQL Server 2000 does not include the ability to nativelyperform pivot table/cross-tab queries. You will either need to doit in the presentation layer (see the link inthis post) or you can go through some gyrations in your T-SQL code (see this article:Dynamic Cross-Tabs/Pivot Tables).

Sunday, March 25, 2012

Data Export with ADO

Hi All,

I want to develop a small tool to export data from a database (SQL Server and Oracle). I have to export full tables or part of tables depending on an SQL statement.

My first idea is to use ADO. I open a recordset on the table (adCmdTable) and I call the Save method to save the recordset in a file. But, when I open the recordset, it takes a long time ... I suppose that ADO performs a " select * from table " to retreive all the lines ... It's too long for me because some tables have more then 10 millions of lines !!!

Do you known if it's possible to open a table without any "select" (just open and save) ? Do you kown others solutions ? I can develop with DO or ADO.Net.

Thanks in advance for your help.

Fran?ois.

Using adCmdTable causes the client to wait until the table parameters are collected, including a row count.

You should be using ADO.NET (and Visual Studio.NET).

ADO.NET is a disconnected model, so there is no traffic UNTIL you execute your query, returning only the data requested. Any 'slowness' at the start up will be due to the overhead of establishing the connection, and if you judiciously use connection pooling, that may not be much of an issue.

|||Have you looked into SSIS (SQL server integration Service) shipped as part of SQL 2005? You might not need to roll your own tool after all.|||

I try to use the WriteXml method of a DataSet to export my data. I don't want to user a "select * from table" select statement because the number of rows can be very important (more that 10 millions of lines), so I want to set the CommendType if my SqlCommand objet to TableDirect, but I have an error message : "this is not supported by SlqCLient .Net Framework" ! Is there any solution to biend a dataset on a table (without any SQL statement) and call the WriteXml method ?

Thanks.

Fran?ois.

|||SSIS is nice if you use SQL Server. But, unfortunatly, we have some customers with Oracle ... That's why I try to find a "universal" solution.|||No, 'unfortunately' using SQL Server requires the use of the SQL language. (Go figure...)

Thursday, March 22, 2012

Data Driven Subscriptions Bug with XQuery?

Hello all,

I'm not sure if what I'm encountering is a bug, an intended feature, or user error...

I've got two tables set up to assist with some data driven reports we want to run. There are two tables listed below:

CREATE TABLE [dbo].[MS_REPORT_SUBSCRIPTION](
[ID] [int] NOT NULL,
[REPORT_NAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,[REPORT_DESCRIPTION] [varchar](60) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL)

This table holds an instance of an intended data driven subscription report.

CREATE TABLE [dbo].[MS_REPORT_PARAMETERS](
[ID] [int] NOT NULL,
[REPORT_ID] [int] NOT NULL,
[REPORT_PARAMETERS] [ntext] COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL)

This table will hold the report parameters. The REPORT_PARAMETERS column holds an xml string (formatted the same as in the Reporting Services database. This will allow a single instance to have a variable number of parameters.

This is the query I use to get the information:

SELECT
convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[1]', 'int') SupervisorID,
'\\sxcorp1\temp\dmlenz\is_docs\' + convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[2]', 'varchar(255)') FilePath,
convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[3]', 'varchar(3)') Department
FROM DW_DIMENSION..MS_REPORT_SUBSCRIPTION MRS
JOIN DW_DIMENSION..MS_REPORT_PARAMETERS MRP ON MRS.ID = MRP.REPORT_ID
WHERE MRS.ID = 1

This works great when I run it within Management Studio. The problem happens when I try to use it for my data driven query. I get the error below. I guess it doesn't know how to parse it? I'm not sure why this happens since the database doesn't have a problem with it (or does it - see exception below). This is the error I get in Report Manager:

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help

For more information about this error navigate to the report server on the local server machine, or enable remote errors

I looked in the logs and found the exception below. Does anyone have an idea as to why this is happening? I do see the 'ARITHABORT' part below, but am not sure if this is something that just comes back from the exception or if it is truly set incorrectly. If the latter is the case, then is this something that Management Studio sets correctly? Anyone have any idea what those settings are? I guess I'm trying to understand why Mgmt Studio would work but not Report Manager..

Thanks in advance for any insight you all might have.

Regards,

Dan

End of inner exception stack trace
w3wp!library!a!06/08/2006-10:17:57:: i INFO: Call to GetSystemPermissions
w3wp!library!d!06/08/2006-10:18:04:: i INFO: Call to GetSystemPermissions
w3wp!library!d!06/08/2006-10:18:04:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.CannotPrepareQueryException: The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.CannotPrepareQueryException: The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. > System.Data.SqlClient.SqlException: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery(DataSource dataSource, DataSetDefinition dataSet, ReportParameter[]& parameters, Boolean& changed)
End of inner exception stack trace

This link has some info:

http://msdn2.microsoft.com/en-us/library/ms188783.aspx

I found this bullet point interesting:

The SET option settings must be the same as those required for indexed views and computed column indexes. Specifically, the option ARITHABORT must be set to ON when an XML index is created and when inserting, deleting, or updating values in the xml column. For more information, see SET Options That Affect Results.|||

I've attempted many things up to this point. For some reason setting the options before the data driven query still doesn't get rid of the error message above.

I have no gone as far as to create a table valued function that will allow me to SELECT * FROM fn_function. This parses ok, but when I actually schedule & run the data driven subscription, I get the following error in the subscription status: Error: Cannot read the next data row for the data set . Looks like functions don't work quite right either.

Ryan: That article helped clear up a lot of information. Unfortunately I couldn't seem to get anything working yet. Thanks for the link.

If anyone else has any suggestions, I'd be willing to try anything at this point...

Regards,

Dan

|||

Alright. This is another response to my own question. The good news is, I've figured a way around the limitation.

First I tried this (using the set arithabort on option):

SET ARITHABORT on
SELECT convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[1]', 'int') SupervisorID,
'\\sxcorp1\temp\dmlenz\is_docs\' + convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[2]', 'varchar(255)') FilePath,
convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[3]', 'varchar(3)') Department
FROM DW_DIMENSION..MS_REPORT_SUBSCRIPTION MRS
JOIN DW_DIMENSION..MS_REPORT_PARAMETERS MRP ON MRS.ID = MRP.REPORT_ID
WHERE MRS.ID = 1

It didn't work. The next thing I tried is to create a proc that set the appropriate options. The Data Driven Subscription Engine didn't understand the proc. This was no longer an option.

I stated above that I couldn't get functions to work - this isn't completely true. The reason they didn't work for me was because, in the log files, I was getting the same exception as my original post. At this point, I figured it was a lost cause, however, I was able to set an option before the function sql. Not sure why this worked and the query above didn't.

This is the query that ultimately worked follows:

set ARITHABORT ON

SELECT SM.SupervisorID, '\\sxcorp1\temp\dmlenz\is_docs\zService Scorecards\' + SM.FilePath, SM.FName FileName, SM.Department FROM fn_DataDriven_IS_Service_Metrics(1) SM

I don't understand why setting the option before the xml query didn't work, but I suppose, as long as I got it to work...

I hope this will eventually help someone else.

Regards,

Dan

sql

Wednesday, March 21, 2012

Data Diagram Software

Hi SQL Gurus,
I have a database that has 250 tables. I need to a 3rd party software that
will generate data diagram for me. I used data diagram from sql but it is
useless with a huge amount of tables.
I would be nice if that 3rd party software can do other things more than
just generate data diagram.
Your suggestions are always highly regarded.
Vito Corleone
My personal choice is Visio for Enterprise Architects. It will do forward
and reverse database engineering as well as excellent diagramming. It also
is included in several MSDN products so you may already have a license for
it.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Vito Corleone" <VitoCorleone@.discussions.microsoft.com> wrote in message
news:7A80466D-2948-4775-94A8-70EC75C76AB5@.microsoft.com...
> Hi SQL Gurus,
> I have a database that has 250 tables. I need to a 3rd party software
> that
> will generate data diagram for me. I used data diagram from sql but it is
> useless with a huge amount of tables.
> I would be nice if that 3rd party software can do other things more than
> just generate data diagram.
> Your suggestions are always highly regarded.
> Vito Corleone

Monday, March 19, 2012

data design question - record versioning

Wanted to get feedback from everyone. Current project requires the
need on certain key tables to keep versions of records, and to
possibly use those older versions to selectively rollback. The
business needs the ability to selectively rollback everything that
happened in the data load last Wednesday, for example, but keep all
data loads since then. So I'm exploring history/auditing tables,
filled by triggers, but it seems like a huge pain. I also saw an
article by Ben Allfree ( http://www.codeproject.com/useritems/LisRecordVersioning.asp
) on codeproject that was interesting, but was conceptually quite
different, with some detractors in the feedback. Surely this is a
common issue. What are everyone's thoughts?Common issue, no common answer. I'd say it is dependent on your application
(just like we DBA like to put it). The ratio of active and inactive records
plays an important role in here.
You may also consider set up (indexed) view to help address the issue.
Quentin
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
> Wanted to get feedback from everyone. Current project requires the
> need on certain key tables to keep versions of records, and to
> possibly use those older versions to selectively rollback. The
> business needs the ability to selectively rollback everything that
> happened in the data load last Wednesday, for example, but keep all
> data loads since then. So I'm exploring history/auditing tables,
> filled by triggers, but it seems like a huge pain. I also saw an
> article by Ben Allfree (
> http://www.codeproject.com/useritems/LisRecordVersioning.asp
> ) on codeproject that was interesting, but was conceptually quite
> different, with some detractors in the feedback. Surely this is a
> common issue. What are everyone's thoughts?
>|||Hmmmm - that's what I was afraid the answer would be. I was hoping
there was a magical, documented, easy to implement solution.
There will be one and only one active record at any given time. There
may be 1-n history records. But realistically I doubt there would be
less than 15 history records associated with any given record. And
probably < 10% of all records would have any history at all. Majority
would be fresh inserts.
On May 25, 12:10 pm, "Quentin Ran" <remove_this_qr...@.yahoo.com>
wrote:
> Common issue, no common answer. I'd say it is dependent on your application
> (just like we DBA like to put it). The ratio of active and inactive records
> plays an important role in here.
> You may also consider set up (indexed) view to help address the issue.
> Quentin
> "CoreyB" <unc27...@.yahoo.com> wrote in message
> news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
>
> > Wanted to get feedback from everyone. Current project requires the
> > need on certain key tables to keep versions of records, and to
> > possibly use those older versions to selectively rollback. The
> > business needs the ability to selectively rollback everything that
> > happened in the data load last Wednesday, for example, but keep all
> > data loads since then. So I'm exploring history/auditing tables,
> > filled by triggers, but it seems like a huge pain. I also saw an
> > article by Ben Allfree (
> >http://www.codeproject.com/useritems/LisRecordVersioning.asp
> > ) on codeproject that was interesting, but was conceptually quite
> > different, with some detractors in the feedback. Surely this is a
> > common issue. What are everyone's thoughts... Hide quoted text -
> - Show quoted text -|||I'd go with the active/passive flag. Sounds at most the inactive records
would be as many as the active records, if not drastically less. Putting
adequate index (on the columns PK and that states "same group") will almost
eliminate any access contention problems due to this active/passive issue.
<unc27932@.yahoo.com> wrote in message
news:1180110598.547933.90790@.o5g2000hsb.googlegroups.com...
> Hmmmm - that's what I was afraid the answer would be. I was hoping
> there was a magical, documented, easy to implement solution.
> There will be one and only one active record at any given time. There
> may be 1-n history records. But realistically I doubt there would be
> less than 15 history records associated with any given record. And
> probably < 10% of all records would have any history at all. Majority
> would be fresh inserts.
> On May 25, 12:10 pm, "Quentin Ran" <remove_this_qr...@.yahoo.com>
> wrote:
>> Common issue, no common answer. I'd say it is dependent on your
>> application
>> (just like we DBA like to put it). The ratio of active and inactive
>> records
>> plays an important role in here.
>> You may also consider set up (indexed) view to help address the issue.
>> Quentin
>> "CoreyB" <unc27...@.yahoo.com> wrote in message
>> news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
>>
>> > Wanted to get feedback from everyone. Current project requires the
>> > need on certain key tables to keep versions of records, and to
>> > possibly use those older versions to selectively rollback. The
>> > business needs the ability to selectively rollback everything that
>> > happened in the data load last Wednesday, for example, but keep all
>> > data loads since then. So I'm exploring history/auditing tables,
>> > filled by triggers, but it seems like a huge pain. I also saw an
>> > article by Ben Allfree (
>> >http://www.codeproject.com/useritems/LisRecordVersioning.asp
>> > ) on codeproject that was interesting, but was conceptually quite
>> > different, with some detractors in the feedback. Surely this is a
>> > common issue. What are everyone's thoughts... Hide quoted text -
>> - Show quoted text -
>|||Quentin Ran wrote:
> I'd go with the active/passive flag. Sounds at most the inactive records
> would be as many as the active records, if not drastically less. Putting
> adequate index (on the columns PK and that states "same group") will almost
> eliminate any access contention problems due to this active/passive issue.
I would also consider having a separate history table instead of
active/inactive flag.
Since the rollover/rollback operations are expected to happen
significantly less often than active record access/update, keeping the
history in a separate table may make sense.
Y.S.
> <unc27932@.yahoo.com> wrote in message
> news:1180110598.547933.90790@.o5g2000hsb.googlegroups.com...
>> Hmmmm - that's what I was afraid the answer would be. I was hoping
>> there was a magical, documented, easy to implement solution.
>> There will be one and only one active record at any given time. There
>> may be 1-n history records. But realistically I doubt there would be
>> less than 15 history records associated with any given record. And
>> probably < 10% of all records would have any history at all. Majority
>> would be fresh inserts.
>> On May 25, 12:10 pm, "Quentin Ran" <remove_this_qr...@.yahoo.com>
>> wrote:
>> Common issue, no common answer. I'd say it is dependent on your
>> application
>> (just like we DBA like to put it). The ratio of active and inactive
>> records
>> plays an important role in here.
>> You may also consider set up (indexed) view to help address the issue.
>> Quentin
>> "CoreyB" <unc27...@.yahoo.com> wrote in message
>> news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
>>
>> Wanted to get feedback from everyone. Current project requires the
>> need on certain key tables to keep versions of records, and to
>> possibly use those older versions to selectively rollback. The
>> business needs the ability to selectively rollback everything that
>> happened in the data load last Wednesday, for example, but keep all
>> data loads since then. So I'm exploring history/auditing tables,
>> filled by triggers, but it seems like a huge pain. I also saw an
>> article by Ben Allfree (
>> http://www.codeproject.com/useritems/LisRecordVersioning.asp
>> ) on codeproject that was interesting, but was conceptually quite
>> different, with some detractors in the feedback. Surely this is a
>> common issue. What are everyone's thoughts... Hide quoted text -
>> - Show quoted text -
>

Data Design Question

Hi All.
Win 2k pro
SQL server 2k (dev ed)
ASP-VBscript
I got 3 tables but I'm not sure if they should be 3 or all merged into
one table. Table 2 & 3 have a 1-1 relationship with table 1 with
cascade deletes & updates.
TABLE 1: Members
MemberID
FirstName
LastName
Gender
DOB
Age (computed Col)
Description
TABLE 2: Members_Homepages
MemberID
HomePageURL
TABLE 3: Member_Email
MemberID
EmailAddress
Reson for the 3 tables is that not every member will have a homepage
and possibly not every member will have an email address.
If putting these 2 fields into table 1 I dont think it will fail any
of the first 3 Normalization rules (1NF, 2NF, 3NF).
I'm simply splitting them off to save on data & rows per page space
(eg varchar(255) each) but it does mean that all my stored procs that
require the info will need to have "Left joins" to get the homepage &
emails for each member.
Which is the best method : all info 1 table or split into 3 (like I
have) and WHY?
Thanks for any tips on DB designing as I'm new to all this.
ALSO. Does anyone know of any good sites on database design.
Al."Harag" <harag@.softhome.net> wrote in message
news:dgeemv8fu7ienago4l41vsmrfbbifjs5ps@.4ax.com...
> Hi All.
> Win 2k pro
> SQL server 2k (dev ed)
> ASP-VBscript
> I got 3 tables but I'm not sure if they should be 3 or all merged into
> one table. Table 2 & 3 have a 1-1 relationship with table 1 with
> cascade deletes & updates.
> TABLE 1: Members
> MemberID
> FirstName
> LastName
> Gender
> DOB
> Age (computed Col)
> Description
> TABLE 2: Members_Homepages
> MemberID
> HomePageURL
> TABLE 3: Member_Email
> MemberID
> EmailAddress
> Reson for the 3 tables is that not every member will have a homepage
> and possibly not every member will have an email address.
This is what nullable columns are for.
They should all be one table.
> I'm simply splitting them off to save on data & rows per page space
> (eg varchar(255) each) but it does mean that all my stored procs that
> require the info will need to have "Left joins" to get the homepage &
> emails for each member.
> Which is the best method : all info 1 table or split into 3 (like I
> have) and WHY?
Much more efficient to put them in one table. Much.
David|||This is a multi-part message in MIME format.
--=_NextPart_000_033E_01C37C51.767992E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Keep in mind the extra overhead when having nullable columns in your =table. See Kalen Delaney's "Inside SQL Server 2000" for details.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in =message news:Omfq4JHfDHA.3700@.TK2MSFTNGP11.phx.gbl...
"Harag" <harag@.softhome.net> wrote in message
news:dgeemv8fu7ienago4l41vsmrfbbifjs5ps@.4ax.com...
> Hi All.
> Win 2k pro
> SQL server 2k (dev ed)
> ASP-VBscript
> I got 3 tables but I'm not sure if they should be 3 or all merged into
> one table. Table 2 & 3 have a 1-1 relationship with table 1 with
> cascade deletes & updates.
> TABLE 1: Members
> MemberID
> FirstName
> LastName
> Gender
> DOB
> Age (computed Col)
> Description
> TABLE 2: Members_Homepages
> MemberID
> HomePageURL
> TABLE 3: Member_Email
> MemberID
> EmailAddress
> Reson for the 3 tables is that not every member will have a homepage
> and possibly not every member will have an email address.
This is what nullable columns are for.
They should all be one table.
> I'm simply splitting them off to save on data & rows per page space
> (eg varchar(255) each) but it does mean that all my stored procs that
> require the info will need to have "Left joins" to get the homepage &
> emails for each member.
> Which is the best method : all info 1 table or split into 3 (like I
> have) and WHY?
Much more efficient to put them in one table. Much.
David
--=_NextPart_000_033E_01C37C51.767992E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Keep in mind the extra overhead when =having nullable columns in your table. See Kalen Delaney's "Inside SQL =Server 2000" for details.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"David Browne" wrote in =message news:Omfq4JHfDHA.3700=@.TK2MSFTNGP11.phx.gbl..."Harag" =wrote in messagenews:dgeemv8fu7i=enago4l41vsmrfbbifjs5ps@.4ax.com...> Hi All.>> Win 2k pro> SQL server 2k (dev =ed)> ASP-VBscript>> I got 3 tables but I'm not sure if they =should be 3 or all merged into> one table. Table 2 & 3 have a 1-1 =relationship with table 1 with> cascade deletes & updates.>> =TABLE 1: Members> MemberID> FirstName> LastName> Gender> DOB> Age (computed Col)> Description>> TABLE 2: Members_Homepages> =MemberID> HomePageURL>> TABLE 3: Member_Email> =MemberID> EmailAddress>> Reson for the 3 tables is that not every =member will have a homepage> and possibly not every member will have an =email address.This is what nullable columns are for.They =should all be one table.>> I'm simply splitting them off to save on =data & rows per page space> (eg varchar(255) each) but it does =mean that all my stored procs that> require the info will need to have ="Left joins" to get the homepage &> emails for each =member.>> Which is the best method : all info 1 table or split into 3 (like I> =have) and WHY?Much more efficient to put them in one table. Much.David

--=_NextPart_000_033E_01C37C51.767992E0--|||Hmm I don't have that book, I got the Sams Teach yourself Transact SQL
in 21 days for starters, Which I cant find anything about the overhead
for nullable columns.
Can you please point me to any websites about things like this?
thanks
Al.
On Tue, 16 Sep 2003 12:53:01 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:
>Keep in mind the extra overhead when having nullable columns in your table. See Kalen Delaney's "Inside SQL Server 2000" for details.|||This is a multi-part message in MIME format.
--=_NextPart_000_03B7_01C37C58.243E3A10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Her website is www.insidesqlserver.com but you really should get the =book. It's the gold standard on internals.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Harag" <harag@.softhome.net> wrote in message =news:6jhemvkncq2ul8figga5s136a8jrkuduid@.4ax.com...
Hmm I don't have that book, I got the Sams Teach yourself Transact SQL
in 21 days for starters, Which I cant find anything about the overhead
for nullable columns.
Can you please point me to any websites about things like this?
thanks Al.
On Tue, 16 Sep 2003 12:53:01 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:
>Keep in mind the extra overhead when having nullable columns in your =table. See Kalen Delaney's "Inside SQL Server 2000" for details.
--=_NextPart_000_03B7_01C37C58.243E3A10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Her website is http://www.insidesqlserver.com">www.insidesqlserver.com but =you really should get the book. It's the gold standard on =internals.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Harag" wrote in message news:6jhemvkncq2=ul8figga5s136a8jrkuduid@.4ax.com...Hmm I don't have that book, I got the Sams Teach yourself Transact SQLin =21 days for starters, Which I cant find anything about the overheadfor =nullable columns.Can you please point me to any websites about things =like this?thanks Al.On Tue, 16 Sep 2003 12:53:01 -0400, ="Tom Moreau"= wrote:>Keep in mind the extra overhead when having nullable =columns in your table. See Kalen Delaney's "Inside SQL Server 2000" for details.

--=_NextPart_000_03B7_01C37C58.243E3A10--|||This is a multi-part message in MIME format.
--=_NextPart_000_03D0_01C37C59.329401C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You could consider leaving the URL as a separate table for two reasons:
1. Eliminate the null from the Members table
2. Allow for multiple home pages per member, though the primary key =would need to be adjusted.
The sample tables you've shown are quite narrow by themselves. If, =however, you had many columns and only some columns were frequently =accessed while the remainder were not, you could spit the table into two =and maintain a 1:1 relation between them. This is known a "vertical =partitioning".
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Harag" <harag@.softhome.net> wrote in message =news:mmhemv0cffgmtn7q2f3s40ot0vkil8eg0q@.4ax.com...
Hi
I might be wrong but isn't the stated size of both the Homepage &
email fields eg (varchar(255) each =3D510) included in the Rows per page
formula ? I think its somthing like 8060 per page so the less pages
the server reads the better ?
The Email field might be better in the first table as most people have
emails these days. but not everyone has a homepage. possibly even 50%
might have a homepage.
Thanks for the imput.
Al.
On Tue, 16 Sep 2003 11:49:33 -0500, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:
>"Harag" <harag@.softhome.net> wrote in message
>news:dgeemv8fu7ienago4l41vsmrfbbifjs5ps@.4ax.com...
>> Hi All.
>> Win 2k pro
>> SQL server 2k (dev ed)
>> ASP-VBscript
>> I got 3 tables but I'm not sure if they should be 3 or all merged =into
>> one table. Table 2 & 3 have a 1-1 relationship with table 1 with
>> cascade deletes & updates.
>> TABLE 1: Members
>> MemberID
>> FirstName
>> LastName
>> Gender
>> DOB
>> Age (computed Col)
>> Description
>> TABLE 2: Members_Homepages
>> MemberID
>> HomePageURL
>> TABLE 3: Member_Email
>> MemberID
>> EmailAddress
>> Reson for the 3 tables is that not every member will have a homepage
>> and possibly not every member will have an email address.
>This is what nullable columns are for.
>They should all be one table.
>> I'm simply splitting them off to save on data & rows per page space
>> (eg varchar(255) each) but it does mean that all my stored procs that
>> require the info will need to have "Left joins" to get the homepage &
>> emails for each member.
>> Which is the best method : all info 1 table or split into 3 (like I
>> have) and WHY?
>Much more efficient to put them in one table. Much.
>David
>
--=_NextPart_000_03D0_01C37C59.329401C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

You could consider leaving the URL as =a separate table for two reasons:
1. Eliminate the =null from the Members table
2. Allow for =multiple home pages per member, though the primary key would need to be =adjusted.
The sample tables you've shown are =quite narrow by themselves. If, however, you had many columns and only some =columns were frequently accessed while the remainder were not, you could spit the =table into two and maintain a 1:1 relation between them. This is known a ="vertical partitioning".
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Harag" wrote in message news:mmhemv0cffg=mtn7q2f3s40ot0vkil8eg0q@.4ax.com...HiI might be wrong but isn't the stated size of both the Homepage =&email fields eg (varchar(255) each =3D510) included in the Rows per =pageformula ? I think its somthing like 8060 per page so the less pagesthe server =reads the better ?The Email field might be better in the first table as =most people haveemails these days. but not everyone has a homepage. =possibly even 50%might have a homepage.Thanks for the imput.Al.On Tue, 16 Sep 2003 11:49:33 -0500, ="David Browne" wrote:>>"Harag" wrote in message>news:dgeemv8fu7ienago4l41vsmrfbbifjs5ps@.4ax.com...>=> Hi All.>> Win 2k pro> SQL server 2k (dev ed)> ASP-VBscript>> I got 3 tables but =I'm not sure if they should be 3 or all merged into> one table. Table =2 & 3 have a 1-1 relationship with table 1 with> cascade deletes =& updates.>> TABLE 1: Members> MemberID> FirstName> LastName> Gender> DOB> Age (computed Col)> Description>> TABLE 2: =Members_Homepages> MemberID> HomePageURL>> TABLE 3: Member_Email> MemberID> EmailAddress>> Reson for the 3 tables is that not =every member will have a homepage> and possibly not every member =will have an email address.>>This is what nullable columns are for.>>They should all be one table.>>> I'm simply splitting them off to =save on data & rows per page space> (eg varchar(255) each) but it =does mean that all my stored procs that> require the info will =need to have "Left joins" to get the homepage &> emails for each member.>> Which is the best method : all info 1 =table or split into 3 (like I> have) and WHY?>>Much more =efficient to put them in one table. Much.>>David>

--=_NextPart_000_03D0_01C37C59.329401C0--|||>"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:OJV0AMHfDHA.2576@.TK2MSFTNGP11.phx.gbl...
>Keep in mind the extra overhead when having nullable columns in your table.
See >Kalen Delaney's "Inside SQL Server 2000" for details.
If memory serves, varchar columns, nullable or not, and nullable char
columns all have about the same storage characteristics. Nominal storage
required for null columns, no in-place updates. That's about it.
That overhead is orders of magnitude less than than the overhead of having
to perform multiple left joins to retrieve the rows.
David|||Based on your narrative, what you have done is logically correct. Since the
relationship between t1 & t2 as well as t1 & t3 are 1 to (0 or 1) they form
the classic super type/sub type relations.
Generally, it is quite common to keep the logical issues related NULL aside
& concentrate of the physical storage aspects for performance improvements.
In some cases, you'd split up the table into multiple tables based on the
how certain columns are being accessing by DMLs. At the implementation
level, this technique is termed vertical partitioning and can result in
significant performance gains, esp. in datawarehouses.
--
- Anith
( Please reply to newsgroups only )|||>> In an ordinary OLTP application, however, this method is horrible. <<
Perhaps, because "an ordinary OLTP application" (I assume you meant OLTP
database ) considers the performance gains more significant than a vital
function called data integrity ?
>> I wouldn't argue that it's not logically correct. It's just terribly
slow and cumbersome to use. <<
Maybe yes, maybe no. But then it is the data designer's decision whether to
sacrifice the obvious integrity benefits over perceived performance gains.
You have to understand the fundamental difference between logical
representation of data as tables (relations) and the physical storage
(clustering, b-tree indexes, hases, heaps etc). With an obvious confusion
between the logical & physical levels of representations, one may have to
make subjective conclusions like "terribly slow" & write up arbitrary
queries to mix up performance aspects (which is an artifact of the physical
storage) with a well designed logical model.
One foundational concept of any DBMS based on the relational model is that
the DBMS should provide sufficient physical data independence using
relations which allow the user to manipulate the data irrespective of how it
physically stores the data. However, in practice, DBMSs which exclusively
implement SQL have foregone certain level of data independence due to
obvious reasons. Thus, in many cases, a data designer's decision to have a
single table or multiple tables for logical representation of a business
segment, rely on performance implications rather than logical correctness.
>> First you have to perform a number of outer joins to see all the
attributes related to a single entity. This alone probably swamps any
performance gains you might see from using narrow fixed-with tables. <<
That is because you make no distinction between the physical and logical
schema in a database. Again, any performance you see is an artifact of the
physical model. The relational model is nothing but logic applied to
databases, and logic has nothing to say about physical implementation. As
mentioned before, most DBMSs loosely implement "relations" as SQL tables
with more or less direct correlation to the physical model ( files,
bits/bytes etc on the disk). This means a SQL database designer may have to
occasionally corrupt his logical model to make sure sufficient performance
gains are realized by different physical tuning mechanisms. One such popular
mechanism is to ignore the dependencies among the attributes and "bundle up"
multiple entity types into single relation ( which is popularly known as
"denormalization" ).
>> Second your update logic has to involve both inserts and updates. <<
As much an assumption it is, why should this be a problem if multiple DMLs
can guarantee logical correctness ?
>> Third it's extremely difficult to enforce check constraints involving
multiple columns. For instance if you have a requirement that "every member
with a homepage must also have an email unless they joined before 1997", ...
<<
Well, do you realize this rule introduced an additional FD, a depedency of
the email on the year and homepage entities? Are you going to ignore this
altogether?
>> If you have a base type / sub type relationship, with more than one sub
type, and each sub type has multiple columns, then you might begin to
approach the "tipping point" for using this approach. <<
It seems like a "tipping point" for you, because you seem to believe in the
ever-popular argument for "denormalization". You are concerned that the more
the number of tables, the more number of joins involved in a typical query
against those tables. So you are concerned this may cause a dampening effect
in performance. A DBMS that exclusively provides insulation of physical
model from the logical schema would have alleviated your concern. A
significant advantage of the relational model is that it allows the
relational DBMS implementers/vendors to do whatever they want at the
physical level to maximize the performance, provided they are not exposed to
the users. However, in practice, as mentioned before, many popular DBMSs
expose the performance aspects of the physical model to the logical level.
This state of affairs occasionally requires the database designer to
tradeoff certain integrity benefits at the logical level for performance
improvements (that are realized at the physical level). But, it does not
mean a logical model is "horrible" since you do not achieve certain
performance benefits with a specific physical implementation.
For exactly the same reasons, I made clear in my response that, the OP's
representation is logically correct. Your statements about performance,
number of queries/joins etc has nothing to do with it at the logical level.
-- Anith|||Wow, many words. Issue strangely misunderstood.
Ok first off, both designs are logically correct and neither is
"denormalized". Also this issue has very little to do with physical vs
logical design. This is an issue purely of logical design. Performance was
brought into this thread earlier with the assertion that "nullable columns
introduce overhead", and a reference to SQL Server's physical storage of
tables. I only mention the terrible performance of this design to counter
the incorrect and (as you point out) irrelevant notion that that one design
had "more overhead" than the other.
The basic question here is: should optional attributes be stored in nullable
columns or in seperate tables. EG
CREATE TABLE CUSTOMER (ID INT PRIMARY KEY,
PHONE VARCHAR(50) NOT NULL,
FAX VARCHAR(25) NULL)
OR
CREATE TABLE CUSTOMER (ID INT,
PHONE VARCHAR(50) NOT NULL)
CREATE TABLE CUSTOMER_FAX (ID INT PRIMARY KEY REFERENCES CUSTOMER(ID),
FAX VARCHAR(25) NOT NULL)
Neiter one is more or less normalized than the other. NAME and FAX are both
functionally dependant on ID and there is no functional dependency between
them.
Here Fax-owning customers can be thought of as a subtype of customers,
making this a trivial case of the base type/sub type problem. The basic
issues involved don't change if we add more structure to the sub type. EG
CREATE TABLE CUSTOMER (ID INT PRIMARY KEY,
PHONE VARCHAR(50) NOT NULL,
DEPARTMENT INT NULL,
SUPERVISOR INT NULL,
MAIL_STOP VARCHAR(25) NULL)
OR
CREATE TABLE CUSTOMER (ID INT,
PHONE VARCHAR(50) NOT NULL)
CREATE TABLE CUSTOMER_INTERNAL (ID INT PRIMARY KEY REFERENCES CUSTOMER(ID),
DEPARTMENT INT NOT NULL,
SUPERVISOR INT NOT NULL,
MAIL_STOP VARCHAR(25) NOT NULL)
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ajR9b.24252$Aq2.22536@.newsread1.news.atl.earthlink.net...
> >> In an ordinary OLTP application, however, this method is horrible. <<
> Perhaps, because "an ordinary OLTP application" (I assume you meant OLTP
> database ) considers the performance gains more significant than a vital
> function called data integrity ?
There is absolutely no loss of data integrety. That's a silly thing to say.
> >> I wouldn't argue that it's not logically correct. It's just terribly
> slow and cumbersome to use. <<
> Maybe yes, maybe no. But then it is the data designer's decision whether
to
> sacrifice the obvious integrity benefits over perceived performance gains.
Obvious to whom?
> You have to understand the fundamental difference between logical
> representation of data as tables (relations) and the physical storage
> (clustering, b-tree indexes, hases, heaps etc). With an obvious confusion
> between the logical & physical levels of representations, one may have to
> make subjective conclusions like "terribly slow" & write up arbitrary
> queries to mix up performance aspects (which is an artifact of the
physical
> storage) with a well designed logical model.
Again, huh? I think the logical model sucks. It's hard to use, it's
inflexible, it needlessly multiplies relations. It just throughly sucks as
a logical data model. The terrible performance is just a bonus.
>
[ snip ]
> >> Second your update logic has to involve both inserts and updates. <<
> As much an assumption it is, why should this be a problem if multiple DMLs
> can guarantee logical correctness ?
The ease of use of a logical model is important. The inability to perform
simple edits to an entity with single update statements is a sure sign that
your logical model sucks. If you can't property edit your data without
stored procedures and triggers, you've got the wrong data modl.
Every attribute functonally dependant on a relation's primary key should be
in the relation. You are free to scatter an entity's attributes among many
several relations. Normalization does not prohibit it, it's still bad
design.
> >> Third it's extremely difficult to enforce check constraints involving
> multiple columns. For instance if you have a requirement that "every
member
> with a homepage must also have an email unless they joined before 1997",
...
> <<
> Well, do you realize this rule introduced an additional FD, a depedency of
> the email on the year and homepage entities? Are you going to ignore this
> altogether?
And how would you suggest implementing this rule? A bunch of procedural
logic.
It's a bad logcal design because forces you to use procedural code to
implement rules that should be enforced decaratively.
> >> If you have a base type / sub type relationship, with more than one sub
> type, and each sub type has multiple columns, then you might begin to
> approach the "tipping point" for using this approach. <<
> It seems like a "tipping point" for you, because you seem to believe in
the
> ever-popular argument for "denormalization".
No. Again this isnt' denormalization, just good logical design.
>You are concerned that the more
> the number of tables, the more number of joins involved in a typical query
> against those tables. So you are concerned this may cause a dampening
effect
> in performance.
> A DBMS that exclusively provides insulation of physical
> model from the logical schema would have alleviated your concern.
Gee, I'll rush out and by a RDBMS from magical fairy land.
>A
> significant advantage of the relational model is that it allows the
> relational DBMS implementers/vendors to do whatever they want at the
> physical level to maximize the performance, provided they are not exposed
to
> the users. However, in practice, as mentioned before, many popular DBMSs
> expose the performance aspects of the physical model to the logical level.
> This state of affairs occasionally requires the database designer to
> tradeoff certain integrity benefits at the logical level for performance
> improvements (that are realized at the physical level). But, it does not
> mean a logical model is "horrible" since you do not achieve certain
> performance benefits with a specific physical implementation.
> For exactly the same reasons, I made clear in my response that, the OP's
> representation is logically correct. Your statements about performance,
> number of queries/joins etc has nothing to do with it at the logical
level.
>
You are correct. As a logical model it's not "horrible", just "poor".
It's the performance problems that really push it over the edge.
Application design is not only about the logical modeling. If you design an
application and completely ignore performance, you belong unemployed.
An relational database is a practical, useful thing. To properly design
applications you need to have a costing model for relational operations, and
a firm grasp on concurrency mechanisms. Neither of these can be wholly
divorced from RDBMS implementations, but you can abstract them
significantly.
Which sentiment, just moments ago, you agreed with:
"In some cases, you'd split up the table into multiple tables based on the
how certain columns are being accessing by DMLs. At the implementation
level, this technique is termed vertical partitioning and can result in
significant performance gains, esp. in datawarehouses."
David|||>> Ok first off, both designs are logically correct... <<
Based on the limited information one can deduce from the DDL, that can be
subjective. Without a well defined and complete business model (entity
types, attributes & relationships) nothing can be said about the correctness
of a logical design.
>> Performance was brought into this thread earlier with the assertion that
"nullable columns introduce overhead", and a reference to SQL Server's
physical storage of tables. <<
I realize that, and that is why I kept reiterating about logically
correctness and deliberately ignored product specific implementations. You
seem to be fixed on superior performance of using a single table, which you
may or may not see depending on how you implement this schema in a physical
model. And I clearly mentioned that, if it performs better it is simply due
to the lack of physical data independence and does not contribute to the
superiority of a logical model.
I referred to "denormalization" in my previous post, because you introduced
new requirements regarding homepages, emails, year etc. This may have
changed the existing predicates and hence you may have to start over by
identifying any additionally introduced FDs. Ignoring it altogether can lead
to an under normalized schema.
>> There is absolutely no loss of data integrety. That's a silly thing to
say. <<
So say you!
>> Obvious to whom? <<
To anyone who understands the inherent benefits of a logical model based on
relational theory.
>> Every attribute functonally dependant on a relation's primary key should
be in the relation. You are free to scatter an entity's attributes among
many several relations. Normalization does not prohibit it, it's still bad
design. <<
"scatter"? Don't you see a difference between scattering and principled
decomposition? Are you saying subtype/super type relationships in the
logical model is a bad design?
For some basics, if entities have all their attributes including the key
attribute in common, then these entities are considered to be of one entity
type. Considering your customer example, if all customers in the reality
have name and phone as attributes, they are entities of type "Customer"
which maps logically into one table. If the entities have no attributes in
common, they are of distinct types. Now, what if the entities have common
and distinct attributes? Due to common attributes, all customers are
entities of type "Customer", and by virtue of distinct attribute (fax, in
this case), the customers who also has a fax are "customer_fax" entities a
subtype of "customer" super type. In other words, sub type/super type
relationship, shows them as distinct entity types; that means arguably a
customer with a fax in the real world is a different entity from a customer
without a fax.
At the logical level, the whole point of this endeavor is to avoid
inapplicable values in the database.
I think, SQL:99 has a similar sub-table/super-table provision in the CREATE
TABLE DDL using UNDER clause which supports this directly & declaratively.
>> And how would you suggest implementing this rule? A bunch of procedural
logic. It's a bad logcal design because forces you to use procedural code to
implement rules that should be enforced decaratively. <<
You assume much! As mentioned before, if additional FDs are introduced by
this rule, I'd try to eliminate them first. Why do you have to kludge it
with a check constraint on nullable columns with hard-coded values?
>> As a logical model it's not "horrible", just "poor". It's the performance
problems that really push it over the edge. <<
Contradicting yourself? You claimed at the beginning of your post "Also this
issue has very little to do with physical vs logical design. This is an
issue purely of logical design."
Please explain, why should the performance problems at the physical model
determine the superiority of a logical model? By definition, logical models
based on relational theory have nothing to do with performance, by virtue of
physical data independence.
>> Application design is not only about the logical modeling. If you design
an application and completely ignore performance, you belong unemployed. <<
Are you talking about applications or databases?
>> An relational database is a practical, useful thing. <<
Nobody disagrees with that.
>> Which sentiment, just moments ago, you agreed with:
"In some cases, you'd split up the table into multiple tables based on the
how certain columns are being accessing by DMLs. At the implementation
level, this technique is termed vertical partitioning and can result in
significant performance gains, esp. in datawarehouses." <<
Mixed up apples & oranges well! Vertical partitioning is a performance
boosting endeavor which has nothing to do with the logical model. It is done
based on a specific implementation (materialization of data in the disk),
access paths & relies on the physical storage mechanisms. And it is carried
out purely for performance gains. It has got nothing to do with a super
type/sub type decomposition done at the logical level.
I realize many SQL products lack of certain levels of Physical data
independence. Being the distinction of logical & physical layers of
representation so blur in most popular SQL products, vertical partitioning
(based on physical criteria mentioned above) of logical tables may increase
the performance of certain queries. However this should not be confused with
a logical design process and as such any realized performance gains should
not be a criteria for determining the superiority of the logical model.
--
- Anith
( Please reply to newsgroups only )|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23JxxvWVfDHA.1200@.TK2MSFTNGP09.phx.gbl...
> >> Ok first off, both designs are logically correct... <<
> Based on the limited information one can deduce from the DDL, that can be
> subjective. Without a well defined and complete business model (entity
> types, attributes & relationships) nothing can be said about the
correctness
> of a logical design.
> >> Performance was brought into this thread earlier with the assertion
that
> "nullable columns introduce overhead", and a reference to SQL Server's
> physical storage of tables. <<
> I realize that, and that is why I kept reiterating about logically
> correctness and deliberately ignored product specific implementations. You
> seem to be fixed on superior performance of using a single table, which
you
> may or may not see depending on how you implement this schema in a
physical
> model. And I clearly mentioned that, if it performs better it is simply
due
> to the lack of physical data independence and does not contribute to the
> superiority of a logical model.
> I referred to "denormalization" in my previous post, because you
introduced
> new requirements regarding homepages, emails, year etc. This may have
> changed the existing predicates and hence you may have to start over by
> identifying any additionally introduced FDs. Ignoring it altogether can
lead
> to an under normalized schema.
> >> There is absolutely no loss of data integrety. That's a silly thing to
> say. <<
> So say you!
So where's the loss of data integrety? Just hand-waving and FUD.
> >> Obvious to whom? <<
> To anyone who understands the inherent benefits of a logical model based
on
> relational theory.
More hand-waving.
> >> Every attribute functonally dependant on a relation's primary key
should
> be in the relation. You are free to scatter an entity's attributes among
> many several relations. Normalization does not prohibit it, it's still
bad
> design. <<
> "scatter"? Don't you see a difference between scattering and principled
> decomposition? Are you saying subtype/super type relationships in the
> logical model is a bad design?
YES!!! that's exactly what I'm saying. Implementing subtype/super type
relationships in standard SQL is very awkward, difficult to use and maintain
and performs poorly.
This is why some people have implemented "object databases" and others have
added object-oriented extensions to SQL.
So while it makes sense logically, in practice it's best to avoid it.
[ snip ]
> I think, SQL:99 has a similar sub-table/super-table provision in the
CREATE
> TABLE DDL using UNDER clause which supports this directly & declaratively.
Which would be fine.
> >> And how would you suggest implementing this rule? A bunch of
procedural
> logic. It's a bad logcal design because forces you to use procedural code
to
> implement rules that should be enforced decaratively. <<
> You assume much! As mentioned before, if additional FDs are introduced by
> this rule, I'd try to eliminate them first. Why do you have to kludge it
> with a check constraint on nullable columns with hard-coded values?
That's a perfectly ordinary rule. In a real OO or inheritence scheme, the
subtype has access to all of the data of the parent type, so enforcing such
a contraint would be no problem. It's just another reason that building OO
inheritence on top of standard SQL doesn't work well.
>
[ snip ]
> Please explain, why should the performance problems at the physical model
> determine the superiority of a logical model?
How would you choose among logical models? Flip a coin?
There's nothing wrong with using nullable columns, plus it's easy and fast.
Using subtype tables is ok, except it's hard and slow.
It's a no-brainer.
David|||>> So where's the loss of data integrety? Just hand-waving and FUD. <<
Data Integrity simply means completeness and accuracy of data, which is the
vital consideration for a database designed based on relational theory.
Since relational representation of data at the logical level involves
non-loss decompositions, it is done based on principled guidelines so that
data corruption can be prevented. One of the such guidelines is the
principle of normalization. It's primary focus is the removal of update
anomalies by reducing redundancy. OLTP databases, being highly transactional
tend to have under-normalized schemas. (I don't want to repeat the reasons
regarding physical data independence in every post).
Ron Fagin has proved that fifth normal form is both necessary and sufficient
for addressing update anomalies using non-loss decomposition by projection.
( you can search citeseer for details on his papers ) Depending on the
business model, an under-normalized schema is prone to such update anomalies
and this causes loss of data integrity. Is that clear enough for you?
>> To anyone who understands the inherent benefits of a logical model based
on relational theory. > More hand-waving. <<
I thought you'd get the clue.
>> How would you choose among logical models? Flip a coin? <<
A logical model is abstract and it speaks about how one represents data.
Superiority of a model is decided by how effectively it adheres & conforms
to the features it purports to support. Logical models based on relational
theory must support the relational principles. For example, considering the
principle of physical data independence, a logical model that exposes and
relies directly on the physical implementation aspects, violates the
fundamental relational rule of Physical Data Independence and thus is
inferior to another model which provides such independence. Similarly
considering the principle of the atomicity of values, a logical model
supporting MV is inferior to another supporting atomic values. So is the
case with key based identification, view support etc. The point is, by
definition, no valid criteria for determining the superiority of a logical
model from another depends on perceived performance gains of a specific
physical implementation.
It is hard to continue this thread unless you can show you understand what a
logical model is and realize why it has to be different from a physical
model. Without that, any further exchange is meaningless.
--
- Anith
( Please reply to newsgroups only )|||>> Yeah, but now apply it to the situation at hand. <<
You seem to suggest somehow I argued for normalization as a sole reason for
sub-type/super type relationship of entities.
Let me retrace: Initially I pointed out the arguments for so-called
"denormalization" since under-normalized schemas are a general feature of
OLTP databases & the reason for this being the preference for performance
gains over integrity. I also mentioned the lack of Physical data
independence in DBMS may force the data designer to corrupt a logical model
for performance. And I pointed out normalization as a necessary process to
avoid the update anomalies which are generally ignored in such databases.
Also in the discourse, I debunked your arguments about physical performance
being the valid criteria to assess a good logical model. Which part is not
clear?
The primary logical benefit of super-type/sub-type relationships is the
elimination of inapplicable attributes in the database. I already mentioned
this in a previous response.
>> You seem to be under the impression that every non-loss decomposition
somehow eliminates update anomalies, increases normalization or somehow
improves a logical model. <<
Not *every* non-loss decomposition, but principled ones do that. That is not
my impression, it is an empirical fact proven by Ron Fagin. And I suggested
you to check on citeseer, in case needed.
>> It's possible to decompose tables to the point where there is only one
non-key column in each table, but that doesn't make it a good idea. <<
If elimination of functional dependencies or inapplicable attributes demands
such a non-loss decomposition in a meaningful way, it is a good idea. The
number of non-key column in each table is not an objective criteria; it
depends on segment of interest that is being modeled. An accurate logical
representation of the business model (segment of interest in the real world)
without causing data corruption is the goal.
>> My main point is that decomposing a table with nullable columns into a
base type / sub type design is bad because it needlessly increases the
number of tables and it increases the complexity of the DML needed to work
with the model. <<
Not necessarily, an entity subtype-super type relationship is represented in
a database by a 0/1:1 (zero-or-one-to-one) referential constraint, a special
case of the general (0/1:M) referential constraint. By your assessment
above, any zero-or-one to many relationship will require complex DML.
Furthermore, increase in the number of tables is not a criteria to assess
the superiority of a logical model.
>>Your argument seems to be that good relational theory requires the
decomposition of tables with nullable columns into a base type / sub type
model. However you have offered no suggestion as to why this might be. Just
hand waving ...<<
No, elimination of inapplicable values in the database sometimes requires
super-type/sub-type relationship among entities. Relational theory prohibits
inapplicable/non-existent/unknown values and warrants elimination of such
values. So a model with super-type/sub-type relationship solution suits the
problem. Which part do you feel like hand waving?
For any additional references on this topic I would direct you to :
A New Database Design Principle - Relational Database Writings 91-94
: Date & McGoveran
Chapter 6 - Practical issues in Database Management
: Fabian Pascal
Appendix E - The Third Manifesto
: Date & Darwen (I am not fully familiar with the RM strong
suggestion on this issue, but it clears up many confusions)
-- Anith|||I was really having trouble figuring out what wrongheadded notion was behind
the requirement to decompose nulls out of a design.
What really had me puzzeled was your instance that not only were nullable
columns wrong, they were "obviously" wrong. Very strange.
Here is the crux of the matter:
> Relational theory prohibits
> inapplicable/non-existent/unknown values and warrants elimination of such
> values. So a model with super-type/sub-type relationship solution suits
the
> problem.
At least it's not hand-waving any more. It's just wrong. But at least it's
a matter of genuine and substantial controversy and I undersand what you're
arguing.
There may be theoretical support for the proposition that null columns
should not be allowed in a relation. And I agree that NULL's don't really
fit into relational theory. Fundamentally they are an implementation kluge.
But use of nullable columns is so ingraned in SQL and the current RDBMS
products, and the elimination of nulls requires such complication of logical
design that I think they are a necessary evil.
You may disagree with my position here, but it's not "obviously" wrong, and
plenty of people would agree with me on this.
Anyway I'm happy to let the matter lie, as I think we've reduced the issue
down to a single fundamental and controversial proposition.
David|||>> What really had me puzzeled was your instance that not only were nullable
columns wrong, they were "obviously" wrong. <<
"obviously" wrong? Please don't put words in my mouth. I said it is the data
designer's decision whether to sacrifice the obvious integrity benefits over
perceived performance gains. Also I said there is obvious confusion between
the logical & physical layers of representation and that SQL products
forfeit Physical Data Independence for obvious reasons. Let us not
misinterpret.
>> At least it's not hand-waving any more. It's just wrong. <<
Let us be honest here. You made bald assertions about a schema with
super-type/sub-type relationship being "terrible", "horribly slow", "poor"
and "sucks" without giving anything to back it up. Then without any details
about the business model or underlying FDs, you wrote up a two -line DDL &
ask me to find if there is any update anomaly. Then you act surprised how a
logical model can be superior to another without performance criteria.
Moreover you used the borrowed UseNet terms like hand waving, FUD etc when
confronted with the basics of integrity and data independence. Other than
the controversy surrounding Nulls is there anything to back up your above
statement "It's just wrong"? I do understand the controversy behind the
usage of Nulls, but it does not make the elimination of Nulls "just wrong".
The point is before lambasting a design with overused adjectives let us
realize some consider certain criteria for data accuracy over others. And I
mentioned about it in my posts as the data designer's prerogative.
>> Anyway I'm happy to let the matter lie, as I think we've reduced the
issue down to a single fundamental and controversial proposition. <<
Me too.
--
- Anith
( Please reply to newsgroups only )

Data Delivery

I've created a number of .html, pivot tables using Access' Data Page
tools. These work great and by pointing my browser to these files,
users throughout my firm can access these pivot tables. I had assumed
that I could simply publish these pivot tables on the web to outside
users who are configured as active directory users. The .htm pivot
table is linked to a query in Access that is built from a SQL database.
We've separately built a system where users can crawl through our file
server from outside of our network. I placed the .htm file in a folder
that the user has access to and the application shows the file.
However, when the user clicks on the file, s/he gets these messages:
Data Access Pages has detected that your IE security settings will not
allow you to access data from a site considered to be insecure.
[
In order to access the data contained within the Data Access Page, you
need to:
1. Start IE
2. Choose Internet Options from the Tools menu
3. Click on the "Security" Tab
4. Click on the "Trusted Sites" icon
5. Click on the "Sites..." button
6. Uncheck the "Require server verification (https) for all sites in
the zone" checkbox
]
Our technology consultant indicated that there's no way for the data to
"bind" to the browser and it's impossible to expose these .htm, pivot
tables to users outside of our network. Does anyone know a way to
accomplish what I'm trying to do? I feel like it should be possible
since Microsoft created that product to build .htm pages?
Ryan
Any suggestions would be very helpfulRyan.Chowdhury@.gmail.com wrote:
> I've created a number of .html, pivot tables using Access' Data Page
> tools. These work great and by pointing my browser to these files,
> users throughout my firm can access these pivot tables. I had assumed
> that I could simply publish these pivot tables on the web to outside
> users who are configured as active directory users. The .htm pivot
> table is linked to a query in Access that is built from a SQL database.
>
> We've separately built a system where users can crawl through our file
> server from outside of our network. I placed the .htm file in a folder
> that the user has access to and the application shows the file.
> However, when the user clicks on the file, s/he gets these messages:
> Data Access Pages has detected that your IE security settings will not
> allow you to access data from a site considered to be insecure.
> [
> In order to access the data contained within the Data Access Page, you
> need to:
> 1. Start IE
> 2. Choose Internet Options from the Tools menu
> 3. Click on the "Security" Tab
> 4. Click on the "Trusted Sites" icon
> 5. Click on the "Sites..." button
> 6. Uncheck the "Require server verification (https) for all sites in
> the zone" checkbox
> ]
> Our technology consultant indicated that there's no way for the data to
> "bind" to the browser and it's impossible to expose these .htm, pivot
> tables to users outside of our network. Does anyone know a way to
> accomplish what I'm trying to do? I feel like it should be possible
> since Microsoft created that product to build .htm pages?
> Ryan
> Any suggestions would be very helpful
>
First off, you might have better luck asking in an Access group instead
of SQL Server. Second, let me say, YIKES!!! You allow outside access
to your file servers?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data Delivery

data deletion

How Is it possible to delete data from table leaving the tables intact?If you mean all the data you can use DELETE by itself or if you don't have
any referential integrity you can use TRUNCATE TABLE. I would advise making
sure you have valid backups first.
Andrew J. Kelly SQL MVP
"albren" <anonymous@.discussions.microsoft.com> wrote in message
news:CC67A65A-1AE4-4B82-9388-9DF0FE58A3CE@.microsoft.com...
> How Is it possible to delete data from table leaving the tables intact?|||Hi,
2 options,
1. Delete with out a condition
eg: DELETE from <tablename> or DELETE <Tablename>
2. Truncate command(This will be much faster bacause it is a non logged
operation)
eg: TRUNCATE table <tablename>
Thanks
Hari
MCDBA
"albren" <anonymous@.discussions.microsoft.com> wrote in message
news:CC67A65A-1AE4-4B82-9388-9DF0FE58A3CE@.microsoft.com...
> How Is it possible to delete data from table leaving the tables intact?

data deletion

How Is it possible to delete data from table leaving the tables intact?If you mean all the data you can use DELETE by itself or if you don't have
any referential integrity you can use TRUNCATE TABLE. I would advise making
sure you have valid backups first.
--
Andrew J. Kelly SQL MVP
"albren" <anonymous@.discussions.microsoft.com> wrote in message
news:CC67A65A-1AE4-4B82-9388-9DF0FE58A3CE@.microsoft.com...
> How Is it possible to delete data from table leaving the tables intact?|||Hi,
2 options,
1. Delete with out a condition
eg: DELETE from <tablename> or DELETE <Tablename>
2. Truncate command(This will be much faster bacause it is a non logged
operation)
eg: TRUNCATE table <tablename>
Thanks
Hari
MCDBA
"albren" <anonymous@.discussions.microsoft.com> wrote in message
news:CC67A65A-1AE4-4B82-9388-9DF0FE58A3CE@.microsoft.com...
> How Is it possible to delete data from table leaving the tables intact?

Sunday, March 11, 2012

Data conversion

can i convert the data from my Sql tables to Foxpro 2.6 , using an SP ?Are you sure you're converting FROM SQL Server TO FoxPro? Why would you want to do this? And 2.6 is a DOS version...Where are you planning to run it?|||If you are converting to foxpro, what format does it want the data in ?

CSV file, Fixed length format, tab delimited, etc ?

You can probably do it with BCP, which would be the easiest approach, right from a cmd session.|||Are you sure you're converting FROM SQL Server TO FoxPro? Why would you want to do this? And 2.6 is a DOS version...Where are you planning to run it?

my client is using fox pro 2.6 to store his data. now he's upgrading the s/w for office automation and has chosen MSSQL2k as the back end. the problem is that my client is sending data (as dbf files) to various agencies which are used for some kind of processing there.

i wud like to know whether this is possible using a Stored procedure

Thursday, March 8, 2012

Data comparison and update

Hello All,

I have two tables T1 and T2 with the same data structure. I need to compare T1 with T2 for all columns and update T2 for deleted, inserted and updated rows. How can I do this?

Are you duplicating the T1 data into T2? If so, why not simply delete all T2 rows and insert all T1 rows into T2 (or drop T2 and then recreate it from T1, including data)?|||

Hello,

Thanks you very much for the reply. T1 is big and is changing constantly and I am trying to find the discrepancy between T1 and T2 and update T2 based on the discrepancies. SO trying to realize synchronization on a single table. Any idea?

|||I haven't used Triggers in a long time, but it may be a good solution to your situation. Set up the Triggers for UPDATE, INSERT and DELETE operations and have it synchronize your T2 table accordingly. Once the Triggers are defined (and tested), you don't even have to worry about it. Be sure that performance isn't hit by doing this, though.|||

Sorry Jim, wish I could help you, but I'm currently bound by some confidentiality agreements that prohibit me from discussing this in much detail. But here are some choices:

Use a trigger to update t1 whenever t2 is updated (If you need them to be synchronized in realtime, including transaction consistancy). But since they are the same structure, there is usually little reason for implementing it this way.

Replication. You can use this to replicate data from server to server, and probably from table to table as well. There are so many options on how this can be done, it'll take you a while to research and test the possibilities.

Diff-gramming. Write queries to automatically insert, update, and delete those rows which differ from table1 to table2.

DATA COMPARE Question??

I have two tables as:
table1
tablename tablecount
a 1
b 2
c 3
d 4
.
.
table2
tablename tablecount
a 1
b 2
c 10
d 11
.
.
.
How do I write a SP to do a COMPARE and give me the table names that are
different along with count differences.
Thanks,
TomdThis can basically be done by joining table1 with table2 and selecting where
tablecount <> tablecount.
select table1.tablename, table1.tablecount - table2.tablecount as difference
from table1
join table2 on table2.tablename = table1.tablename
where table1.tablecount <> table2.tablecount
"tom d" <tomd@.discussions.microsoft.com> wrote in message
news:C2BB644B-1849-45AF-A72A-38A5E105F8F9@.microsoft.com...
>I have two tables as:
> table1
> tablename tablecount
> a 1
> b 2
> c 3
> d 4
> .
> .
> table2
> tablename tablecount
> a 1
> b 2
> c 10
> d 11
> .
> .
> .
> How do I write a SP to do a COMPARE and give me the table names that are
> different along with count differences.
> Thanks,
> Tomd
>|||Tom,
Try:
SELECT A.TABLENAME, A.TABLECOUNT AS 'COUNT OF TABLE1',B.TABLECOUNT AS
'COUNT OF TABLE2'
FROM TABLE1 A JOIN TABLE2 B
ON A.TABLENAME = B.TABLENAME
WHERE A.TABLECOUNT <> B.TABLECOUNT
HTH
Jerry
"tom d" <tomd@.discussions.microsoft.com> wrote in message
news:C2BB644B-1849-45AF-A72A-38A5E105F8F9@.microsoft.com...
>I have two tables as:
> table1
> tablename tablecount
> a 1
> b 2
> c 3
> d 4
> .
> .
> table2
> tablename tablecount
> a 1
> b 2
> c 10
> d 11
> .
> .
> .
> How do I write a SP to do a COMPARE and give me the table names that are
> different along with count differences.
> Thanks,
> Tomd
>

Data Compare

I have and use Red Gate software for comparison. However; I'm at a loss on
this data compare scenario:
I have two tables I want to compare, but the PK-ID field is seeded
differenly between the 2 tables. So when I do a comparison - every row is
considered different. I looked at a couple other tools to see if I could
compare and exclude columns - but havent' found any. I remember once seeing
a T-SQL solution - but I cannot find that either.
One thought I had was that this particular table, the PK-ID field is not
used anywhere else - so maybe I could just reseed the one table to match the
other.
Can anyone offer some suggestions?It is hard to understand your narrative without an example. Can you post
your table structures, sample data & expected results? For details refer to:
www.aspfaq.com/5006
Anith|||I found the perfect solution just now a SP called sp_compare:
http://www.databasejournal.com/scri...cle.php/1579951
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23eTMaogHGHA.3624@.TK2MSFTNGP09.phx.gbl...
> It is hard to understand your narrative without an example. Can you post
> your table structures, sample data & expected results? For details refer
> to: www.aspfaq.com/5006
> --
> Anith
>

Thursday, March 29, 2012

Data file space used calculations

Tuesday, March 27, 2012

Data Extract Question

Sunday, March 25, 2012

Data Export with ADO

Thursday, March 22, 2012

Data Driven Subscriptions Bug with XQuery?

Wednesday, March 21, 2012

Data Diagram Software

Monday, March 19, 2012

data design question - record versioning

data design question - record versioning

data design question - record versioning

Data Design Question

Data Delivery

Data Delivery

data deletion

data deletion

Sunday, March 11, 2012

Data conversion

Thursday, March 8, 2012

Data comparison and update

DATA COMPARE Question??

Data Compare

Data Flow Task Problem

Blog Archive

About Me