Best practise regarding Tables?

Hi!

I have 6-7 tables total containing subobjects for different objects like phonenumbers and emails for contacts.
This meaning i have to do some querys on each detailpage. I will use stored proc for fetching subobjects.

My question therefore was: if i could merge subobjects into same tables making me use perhaps 2 querys instead of 4 and thus perhaps doubling the size
of the tables would this have a possibility of giving me any performance difference whatsoever?

As i see pros arefewer querys, and cons are larger tables and i will need another field separating the types of objects in the table.

Anyone have insight to this?

I would be curious to see what you mean by "subobjects" - usually when you ask a question like this it is a good idea to post your table structure. You'll get more specific help that way.

In general, you should strive for proper normalization of your database. This normally means more tables, and the tables are thin, not wide. However, if you have similar sets of data that can be "typed" and placed into the same table, that is normally a good route to take. For example, if you have a business phone numbers and home phone numbers, I would recommend putting these in the same table and adding a field to specify its type, rather than having two separate tables. On the other hand, you shouldn't try to squeeze more disparate types of data into the same table and type them.

|||

By objects i mean like applications, users, groups.
By subobjects i mean phonenumbers, emails, contacts(for example when an application have a number of contacts linked to it) etc

To illustrate i post some of my tables:

First an object table:

CREATE TABLE [sw20aut].[sw_apps] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[kund_id] [int] NOT NULL ,
[namn] [varchar] (80) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[beskrivning] [text] COLLATE Finnish_Swedish_CI_AS NOT NULL DEFAULT ('') ,
[usr_rubrik] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NOT NULL DEFAULT ('Övrig information') ,
[usr_information] [text] COLLATE Finnish_Swedish_CI_AS NOT NULL DEFAULT ('') ,
[kat] [int] NOT NULL DEFAULT('0') ,
[typ] [tinyint] NOT NULL DEFAULT('0') ,
[skapare] [varchar] (15) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[skapad] [datetime] NOT NULL ,
[del] [bit] NOT NULL DEFAULT ('0')
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Here are some subtables:

CREATE TABLE [sw20aut].[sw_sub_email] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[p_typ] [varchar] (3) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[p_id] [int] NOT NULL ,
[epost] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NULL ,
[kommentar] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NOT NULL
) ON [PRIMARY]

CREATE TABLE [sw20aut].[sw_sub_phone] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[p_typ] [varchar] (3) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[p_id] [int] NOT NULL ,
[tel_land] [varchar] (6) COLLATE Finnish_Swedish_CI_AS NULL ,
[tel_rikt] [varchar] (6) COLLATE Finnish_Swedish_CI_AS NULL ,
[tel_nr] [varchar] (30) COLLATE Finnish_Swedish_CI_AS NULL ,
[kommentar] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NOT NULL
) ON [PRIMARY]

CREATE TABLE [sw20aut].[sw_sub_contacts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[p_typ] [varchar] (3) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[p_id] [int] NOT NULL ,
[kon_id] [int] NOT NULL ,
[kon_kid] [int] NOT NULL ,
[kommentar] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NOT NULL
) ON [PRIMARY]

CREATE TABLE [sw20aut].[sw_sub_grupper] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[p_typ] [varchar] (3) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[p_id] [int] NOT NULL ,
[grp_id] [int] NOT NULL ,
[grp_kid] [int] NOT NULL ,
[kommentar] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NOT NULL
) ON [PRIMARY]

p_id = the parents id
p_typ = type of parent, for example app, con or grp

As seen above i could join sub_contacts and sub_groups easily(need another field to set if its a contact or group though) and i could probably join email and phone as well, in worst case using some kind of commaseparated format to separate countrycode, areacode etc. I have a couple of other similar sub tables as well The question is if it could be worth the effort or the larger tables(more rows) and another field to sort by would negate the advantage of fewer querys when listing? Practically it would mean a step from 6-7 querys on worst pages down to 3-4.
I use batched dynamic querys and some stored procs depending on circumstances

|||

The baseline for the table definition is files and association so if what I am seeing is correct you have maybe one or two secondary tables and the relationship is actually quantified by upper and lower bound Cardinality meaning number of expected rows. And I don't think name should be VarChar 80 more like 30-50. Try the links below for functional dependency and free tables you can clone. Hope this helps.

http://www.databaseanswers.org/data_models/

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

|||

The problem regarding sizeing the name columns in different tables are partly because i will have to import data from old tables used by the former application.

These tables are very poorly designed(even to my standards ;) ) and have caused users to add more than just name in the namefields so forth. I will control this better programatically but
nevertheless stripping of existing text in the field would not be popular so my thought weher to implement it and try steering all new information in a better way.
I think that this meant i needed that size when examining the old data to prevent truncation.

Anyway the tables above are examples i have more secondary tables and quite a few primary tables as well.
The basic concept i was thinking about if its better practice to have larger tables wich results in less querys / page (merging the sub/secondary tables as much as practically possible) or using smaller, slicker tables and more inpage querys/procs. For example using 1 table to link both contacts and groups and 1 table for perhaps phone/email and even links as long as one can reuse the fields not resulting in empty fields.

Shall check your links btw

|||

You could clean the data before importing it and I am concerned you are describing tables on the Calculus end where you have main table and the dimenssions on this end tables sizes should be similar. I know you can use validators but you can also use CHECK CONSTRAINT on you columns so people cannot insert whatever they like in name columns.

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

Best practices - currency elements

Hello! I'm a long-time SQLServer developer, but new to XML.

I find myself doing some XML related to EDI messages.

When you have a field containing a dollar amount, what format should you use in the XSD?

We've been using decimal.

But that's just half the question!

When the XML comes in and there is a round dollar amount, we've been getting the data in integer format, a five dollar order just looks like <mytotal>5</mytotal>.

Wouldn't it seem like a best practice to make this <mytotal>5.00</mytotal>?

Thanks.

Josh

Could this be a problem with the specification of the database column? For example:

Code Snippet

declare @.testo table(myTotal decimal , dec_9_2 decimal(9,2))
insert into @.testo select 5, 3
--select myTotal from @.testo

select myTotal
from @.testo
for xml path('')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<myTotal>5</myTotal>
*/

select dec_9_2
from @.testo
for xml path('')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<dec_9_2>3.00</dec_9_2>
*/

In the first query the source column is simply defined as a DECIMAL column and is displayed without any fractional "decimal" portion. When this column is converted to XML it displays only the whole number portion because really, the data consists of whole number only.

In the second query the source column is defined as DECIMAL (9, 2) column. This provides for 7 whole number digits and 2 decimal digits. When this column is converted to XML it displays the desired decimal places.

Can you provide the DDL for your source column?

|||

The XML is prepared by an outside source, in fact it comes from an EDI message.

I'm wondering whether - more like just how - to raise it with them as an improvement they should make.

Thanks.

Josh

|||

What I would wander is first, are ANY of these fields coming in with decimals. If none, I would definitely raise the issue if your are supposed to be getting 2-decimal accuracy. They may have an error that they are not aware of.

Also, the advantage of getting the decimals is that it eliminates doubt -- which is exactly what you are expressing. It is probably a good idea just to ask the question so that the doubt is eliminated. Much better to talk now than miss something.

sql

Best practice for managing multiple servers

Hi, Does anyone know of a whitepaper or web site containing best practice
information for the Administration of multiple SQL servers?.
I am looking at Administrating multiple servers and would like to create a
one point for administration and notification of the failure of jobs.
Many thanks.
Nick
Search on http://www.sql-server-performance.co...ced_search.asp
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.
|||There may be some useful information here:
Operations Guide -SQL Server 2000
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.

Best practice for managing multiple servers

Hi, Does anyone know of a whitepaper or web site containing best practice
information for the Administration of multiple SQL servers?.
I am looking at Administrating multiple servers and would like to create a
one point for administration and notification of the failure of jobs.
Many thanks.Nick
Search on http://www.sql-server-performance.c...nced_search.asp
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.|||There may be some useful information here:
Operations Guide -SQL Server 2000
http://www.microsoft.com/technet/pr...in/sqlops0.mspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.

Best practice for managing multiple servers

Hi, Does anyone know of a whitepaper or web site containing best practice
information for the Administration of multiple SQL servers?.
I am looking at Administrating multiple servers and would like to create a
one point for administration and notification of the failure of jobs.
Many thanks.Nick
Search on http://www.sql-server-performance.com/advanced_search.asp
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.|||There may be some useful information here:
Operations Guide -SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.

Best practice

Hi,
I'm writing an application using a sql server database. In this database
there roughly 3 kinds of tables:
1. tables containing data which is generated during the run of an
application
2. tables containing data entered by our clients
3. tables containing data entered by our compagny.
Twice a year our clients need to receive new data in table group 3. Thereby
the data stored in table group 1 may be removed.
Only the data in group 2, entered by our clients should stay in the
database.
What's the best practice to achieve this goal?
I was thinking in splitting it up in 2 databases, 1 with group 1 tables, and
1 with group 2 and 3 tables. Twice a year we create a backup of this second
database en our clients restore this database. After this the clients need
to run a program to check the integrity.
Is this the way to go?
Thank's,
PerryOn Tue, 11 Apr 2006 15:36:42 +0200, Perry van Kuppeveld wrote:

>Hi,
>I'm writing an application using a sql server database. In this database
>there roughly 3 kinds of tables:
>1. tables containing data which is generated during the run of an
>application
>2. tables containing data entered by our clients
>3. tables containing data entered by our compagny.
>Twice a year our clients need to receive new data in table group 3. Thereby
>the data stored in table group 1 may be removed.
>Only the data in group 2, entered by our clients should stay in the
>database.
>What's the best practice to achieve this goal?
>I was thinking in splitting it up in 2 databases, 1 with group 1 tables, an
d
>1 with group 2 and 3 tables. Twice a year we create a backup of this second
>database en our clients restore this database. After this the clients need
>to run a program to check the integrity.
>Is this the way to go?
Hi Perry,
I'd prefer to keep all data in just one database. That makes it much
easier to maintain integrity (FOREIGN KEY constraints don't work
coorss-database), plus it will probably yield better performance.
For your half-yearly update of the company-supplied tables, I'd
recommend that you distribute a script to your customers. The script
would either be a .SQL script with INSERT, UPDATE and DELETE statements,
or a .CMD file with a series of SQLCMD and BCP statements, plus the
files to be used in the bcp operations.
Hugo Kornelis, SQL Server MVP|||like Hugo said, one database for sure.
staging tables to import/export data. good names for all tables.
stored procedures to load/run the data in and out.
you need to make a backup before and after all the data movements.

Best Method to update table...

Hy everyone.
I've got a little question regarding the speed of an update query...

situation:
I've got different tables containing information wich i want to add to one big table trough a schedule (or as fast as possible).

Bigtable size:
est. 180000 records with 25 fields (most varchar).

Currently I've tried two different methods:
delete all rows in the big table and add the ones from the little tables again. (trough union all query)
-> Speed ~ 15 Seconds

refresh all changed rows (trough timestamp <>) and add new titles (trough union all query)
-> Speed ~ 20 Seconds

Does anybody know a faster solution? The union queries block the table for those 20 Seconds...

Thanks for any reply!RE: situation: I've got different tables containing information which i want to add to one big table trough a schedule (or as fast as possible).
Bigtable size:
est. 180000 records with 25 fields (most varchar).

Currently I've tried two different methods:
delete all rows in the big table and add the ones from the little tables again. (trough union all query)
-> Speed ~ 15 Seconds

refresh all changed rows (trough timestamp <>) and add new titles (trough union all query)
-> Speed ~ 20 Seconds

Q1 Does anybody know a faster solution? The union queries block the table for those 20 Seconds... Thanks for any reply!

A1 Maybe.

As with many things, it depends on the requirements. For example, some possible considerations may include various permutations and combinations of any of the following: (not an exhaustive list)
a using a lower isolation level for the union queries, and conditionally unioning only updated tables
b implementing triggers to update the target as dml is commited at the source tables
c a create, populate, and rename table scheme (dropping the old table)

Best Advice

I have a table containing 65 fields. One of the fields is a varchar(6) field
which is unique and contains a client number . witin the 64 other fields
there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if a
given pin exists
ie check if '123456' exists in PIN1, PIN2 - PIN10
any suggestionsDo all the pin columns have different values? Or is one of them populated
and the rest of them NULL? And is '123456' the client number in this case?
On 3/12/05 3:37 PM, in article
A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
<PeterNewman@.discussions.microsoft.com> wrote:

> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Peter,
If there is no business-related distinction beween what is in PIn1, from
what is in Pin2 - Pin 10, i.e., if it does not matter where ValueA is in Pin
1
and ValueB in Pin2, or the other way around, then what you have is a simple
list of Pins associated with the parent record... (which s a client, I take
it)
If this is true, then you might want to read up on database normalization
concepts somewhere. The *Right* way to store these data items would be in
another table. If you had the data that way, this query, (as well as man
y
others) would be much simpler... not to mention a whole hos of other
issues...
"Peter Newman" wrote:

> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Sorry Aaron, The PIN numbers will be different or Null. There will always b
e
a PIN1 value but from PIN2 - PIN10 may be Null's
'123456' is the pin number i am sing in this case ane you '111111' the
client number
Thanks
"Aaron [SQL Server MVP]" wrote:

> Do all the pin columns have different values? Or is one of them populated
> and the rest of them NULL? And is '123456' the client number in this case
?
>
>
> On 3/12/05 3:37 PM, in article
> A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
> <PeterNewman@.discussions.microsoft.com> wrote:
>
>|||Hi
You may want to try something like:
CREATE FUNCTION dbo.fn_IsNew( @.ClientName VARCHAR(20), @.PIN CHAR(6))
RETURNS INT
AS
BEGIN
IF EXISTS ( SELECT 1 FROM Cushion WHERE ClientName = @.ClientName
AND ( PIN1 = @.PIN OR PIN2 = @.PIN OR PIN3 = @.PIN OR PIN4 = @.PIN OR PIN5 =
@.PIN OR PIN6 = @.PIN OR PIN7 = @.PIN OR PIN8 = @.PIN OR PIN9 = @.PIN OR PIN10 =
@.PIN ) )
RETURN 1
RETURN 0
END
CREATE TABLE Cushion ( ClientName VARCHAR(20) NOT NULL,
PIN char(6) NOT NULL ,
PIN1 char(6),
PIN2 char(6),
PIN3 char(6),
PIN4 char(6),
PIN5 char(6),
PIN6 char(6),
PIN7 char(6),
PIN8 char(6),
PIN9 char(6),
PIN10 char(6),
CONSTRAINT IsNew CHECK ( dbo.fn_IsNew(ClientName ,PIN) = 0 )
)
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'ABC', '123456', '234567', '345678' )
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'DEF', '123456', '123456', '345678' )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'IsNew'. The
conflict occurred in database 'Needlepoint', table 'Cushion'.
The statement has been terminated.
*/
Normalising the structure would make your queries easier, but you may want
to try something like:
CREATE VIEW NormalisedCushion AS
SELECT ClientName, PIN1 AS OldPin FROM Cushion
UNION ALL SELECT ClientName, PIN2 FROM Cushion
UNION ALL SELECT ClientName, PIN3 FROM Cushion
UNION ALL SELECT ClientName, PIN4 FROM Cushion
UNION ALL SELECT ClientName, PIN5 FROM Cushion
UNION ALL SELECT ClientName, PIN6 FROM Cushion
UNION ALL SELECT ClientName, PIN7 FROM Cushion
UNION ALL SELECT ClientName, PIN8 FROM Cushion
UNION ALL SELECT ClientName, PIN9 FROM Cushion
UNION ALL SELECT ClientName, PIN10 FROM Cushion
SELECT * FROM NormalisedCushion
WHERE OldPin = '234567'
John
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com...
>I have a table containing 65 fields. One of the fields is a varchar(6)
>field
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
> a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Okay, can you provide DDL, sample data, and desired results. See
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:0A679D44-51D8-45D5-8737-C4791812C84B@.microsoft.com...
> Sorry Aaron, The PIN numbers will be different or Null. There will always
be
> a PIN1 value but from PIN2 - PIN10 may be Null's
> '123456' is the pin number i am sing in this case ane you '111111' the
> client number
> Thanks
> "Aaron [SQL Server MVP]" wrote:
>
populated
case?
field
fields
check if a