Reducing Storage Space By Moving Rarely Required Columns To Separate Table While Sharing Primary Key Constraint

Aug 17, 2006

Dear all,

In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.

During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.

However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.

Hence, I had to create a quite awkward design to enforce the constraints:

1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.

Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.

Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.

Thanks in advance for any advice.

View 6 Replies


ADVERTISEMENT

Transact SQL :: Manage Max Table Storage Space In Case Of Excess Data (size In GB)

Apr 23, 2015

I am using sql server 2008 r2 on my end. I have created a database named testDB. I have a lot of tables with some log tables in this. some tables have contain lack of records in log table.

So my purpose is that I want to fix the table size of those tables(log tables) and want to move records in other database table placed on another location. So my database has no problem.

is there any way to make such above steps which I want for my database?

Is there already built any such functionality in sql server?

View 2 Replies View Related

Help Required :- Table Space Management In Sql Server 2000

Mar 27, 2007

Hi ,



if we have two file group in a particular sql server 2000 database (c and d drive), and in that database suppose one particular table (location c drive) is growing very fast, i want to move it to D: drive file group. so how we can do it.



Thanks

Shiva

View 1 Replies View Related

Table Columns Storage

Jul 20, 2005

Hi ,This is related to datawarehouse , data mining . We are told that datamining tools such as spss or sas , need a large table which has lotsof columns inside . Based on our project , out final table which willbe used in data mining , has lots of computed values . The finalnumber of columns of the table is 7800!(lots of calculated values)First of all , i dont see the reason of this requirement of the datamining tools. I will be appreciated if someone can clarify this: Whydoes a data mining tool need such a large table?The main issue is if i create table with 7800 columns inside ( in factoracle only allows to create table with 1000 columns) , i believe itcant be queried.My basic calculation shows that the average row size of this tablewill be 160kb. , considering my db_block_size of 16kb. this means 10blocks for 1 row. (The table will have more than 10.000.000 rows) Nomatter how fast my disk subsystem is , i think the queries againstthis table will fail. So what can i do? May be I need a differenttype of storage technique for instance column based storage ( i heardthat sysbase has this feature , dont know the details / purpose..)How can i solve such a problem? The database server really does notmatter , it can be oracle,sqlserver , sybase,informix , etc... I willbe appreciated if someone can help me abut the issue.Kind Regards,hope

View 1 Replies View Related

Reducing Log Space

Dec 4, 2000

Hi all!

The transaction log of the databse i am using has grown up to 7GB...(previously the setting was unrestricted file growth...now changed to restrict file growth to 7 GB approx.)
now this 7GB space is not needed....i would like to reduce the size to around 2 GB...how can i achieve this?i observer that on the properties i can only increase the size and not decrease it...also i am using transactional replication..this server is the publisher to four subscribers..

View 1 Replies View Related

How To Set A Primary Key Constraint In A View Table Using SQL Server 2005

Aug 15, 2006

Hi All,
I have created a table using VIEWS in SQL server 2005, now i want to be ablle to edit it in a datagrid but i cannot do so as i there is no primary key!
now does anybody know how to set a primary key constraint so i can set one of the fields as a primary key to identify the row?
many thanks 

View 3 Replies View Related

SQL Server 2008 :: Separate Drives For MDF / LDF On Same Storage?

Apr 14, 2015

I'm aware that it's best practice to separate mdf and ldf files onto separate drives.

However, I see a lot of servers where the underlying disk array is the same for drives on the server.

Is there still any performance benefit to separating mdf and ldf files in this situation?

For example, a single virtual server running SQL Server, with multiple drives attached.All of the drives are connected to a shared storage via iSCSI.There drives C:, D:, E: etc are all actually sharing the same underlying disks.

Obviously, there are some benefits from an administration perspective whereby individual drives can be reconfigured without affecting the others.

View 6 Replies View Related

Add 2 Separate Columns From Separate Tables Using Trigger

Feb 15, 2012

I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...

View 14 Replies View Related

How Much Storage Space Does Nvarchar() Eats? Always Max?

Oct 5, 2006

Hello, everybody,

I am in need of storing long pieces of text in database, length between 2000 and 4000 characters (nvarchar limit).

If I have a column in a table:

my_text nvarchar(2000)

does this mean that in database the size used will always be 2000*2=4000 bytes at least (+ rounding), independent on what the real length of the text stored is?

Anoter alternative - storing texts in files is expected to become unmanageable very soon, so I would like to keep to the db.

Or, would it be reasonable to have such a table:

my_text_1 nvarchar(500),
my_text_2 nvarchar(500),
my_text_3 nvarchar(500),
my_text_4 nvarchar(500)

Or declaration already means the space for a column is reserved even if it is nullable?

My DBs are MS SQL Server 2000/2005.

View 2 Replies View Related

Calculate The Disk Storage Space

Sep 19, 2007

HI All

how to calculate the disk storage space of the database from the front using asp.net

Can anyone give suggestion

Thanks

View 6 Replies View Related

How To Get Primary Key (Columns) Of A Table?

Jan 5, 2006

I want to get the Primary Key Columns in Arrays by sending a tablename. I am using SQL Server 2000 and I want to make a find utility in VB.net whichwill work for all the forms; I have tables with one Primary key and some tables with composite Primary keys. I used to do this in VB 6 by making a function which fills the Primary Keys inList Box (I require to fill in list box), now I need to get in array. Can some one tell me the migration of the following VB 6 Code? This was written for the MS Access, I need same for SQL Server, I cannot find Table Def and Index Object in VB.net 2003. Public Sub GetFieldsFromDatabase (ldbDatabase As Database, lsTableName AsString)     Dim lttabDef As TableDef  Dim liCounter As Integer  Dim liLoop As Integer  Dim idxLoop As Index  Dim fldLoop As Field
  With ldbDatabase    For Each lttabDef In .TableDefs      If lttabDef.Name = lsTableName Then        liCounter = lttabDef.Fields.Count        For liLoop = 0 To liCounter - 1          cboFieldLists.List(liLoop) = lttabDef.Fields(liLoop).Name        Next liLoop        For Each idxLoop In lttabDef.Indexes          With idxLoop            lblIndexName = .Name            If .Primary Then              liCounter = 0              For Each fldLoop In .Fields               cboPrimaryKeys.List(liCounter) = fldLoop.Name                liCounter = liCounter + 1              Next fldLoop            End If          End With        Next        cboFieldLists.ListIndex = 0        If cboPrimaryKeys.ListCount > 0 Then          cboPrimaryKeys.ListIndex = 0        End If        Exit For      End If     Next End WithEnd Sub

View 2 Replies View Related

Hiding Columns -White Space At End Of The Table*****Extremely Urgent

Mar 27, 2008

Hello everyone,
In reports ,My customer requirement is to display column based on selected criteria in UI .
The columns which are not selected by him will hide.
for that we kept an expression in Visibility --> Hide



Code Snippet
= NOT Parameters!Parameters.Value.ToString().Contains("Name")




then coming to HTML Report
It is working fine,but white space coming at end of the Table.
can't we supress the white space?
The white space width is exactly the width of the column which is hidden.
My designing in layout is wrong?
Else is that Problem with the SSRS?
Experts Please let me Know!!!!
Give me Solution!!!
Customer is strictly focusing on that requirement.

***Note: white Space is Some what Acceptable.But My Reports are very big like 45 columns around.When he selects 10 out of 45 then you can assume how much space is coming????????****

View 4 Replies View Related

Integration Services :: Send Mail - Not Enough Storage Space

Sep 14, 2015

I have a complex SSIS package which processes excel files, does some validation, fires off some SSRS reports which produce excel files, and finally sends out emails to groups of people with the appropriate file attached.It's been working fine, until recently. Now, when it reaches the send mail task, it raises an error:

Error: An error occured with the following error message: "Could not load file or assembly 'System.web, Versio 4.0.0.0, culture=neautral, publickeytoken= b0f5f7f11d50a3a' or one of its dependancies. Not enough storage space is available to process this command. (Exception from HRESULT: 0x80070008)".
Further down the report comes 
Microsoft.SqlServer.Dts.Tasks.SendMailRask.SendMailTask, Microsoft.SqlServer.SendMailTask, Version=11.0.0.0, culture=neutral, PublicKeyToken=89845dcd8080cc91

This process completes normally when run in Visual Studio. It fails when run from SQL Server Agent.SQL Server Agent is currently running under my user credentials.

View 14 Replies View Related

Moving Database Instance To Larger Storage Area?

Sep 16, 2015

I have a SQL server with multiple instances on it and would like to move one of them to a drive with more storage.  

I have SQL 2010 on a server with 2 partitions.

The database is located on the C: drive (original build) but the drive isn't partitioned to handle a db of the size that this one will grow to. I would like to move the full DB instance to another partition.

View 6 Replies View Related

Separate Foreign Keys With Shared ID Space

Jul 20, 2005

Let's say I have a type hierarchy: (just an example)the general entity customer:CREATE TABLE customer(customer_id int, customer_name varchar(250),customer_type int)three specific customer subtypes: 1 - business, 2 - home, 3 -universityrepresented by the following three tables (being subtypes, they shareID space for customer_id)CREATE TABLE business_customer(customer_id int, business_sector int,.... )CREATE TABLE home_customer(customer_id int, household_income_bracketint, ...)CREATE TABLE university_customer(customer_id int, number_students int,....)Is it possible to make a foreing key constraint that says:the customer_id in busineness_customer table must reference only thosecustomer_id in the customer table where customer_type == 1?the customer_id in home_customer table must reference only thosecustomer_id in the customer table where customer_type == 2?Thanks!- Robert

View 31 Replies View Related

Ultra Newbie - Primary Key And STORAGE

Aug 14, 2006

I'm trying to learn SQL with SAMS Teach Yourself SQL in 24 hours and using SQL 2000 Server but because the book is a generic SQL code I'm finding some of the examples don't work for me. Firstly is there a STORAGE command in MS SQL as when I tried to create my table and appended:

STORAGE
(INITIAL 20M
NEXT 1M);


All I got was an error and couldn't figure how to get round it.

Secondly, I created my table via:

CREATE TABLE TBL_EMPLOYEE
(EMP_IDCHAR(9)NOT NULL,
EMP_NAMEVARCHAR(40)NOT NULL,
EMP_ST_ADDRVARCHAR(20)NOT NULL,
EMP_CITYVARCHAR(15)NOT NULL,
EMP_STCHAR(2)NOT NULL,
EMP_ZIPINTEGERNOT NULL,
EMP_PHONEINTEGERNULL,
EMP_PAGERINTEGERNULL);


but can't figure out how to now add a Primary Key. I added a line to my QA window:

ALTER TABLE TBL_EMPLOYEE ADD CONSTRAINT EMP_ID PRIMARY KEY;


And when I parse it, it says it complete's successfully but then when I try and execute it errors.

Can you see what I'm doing wrong / should I give up now?

Thanks

View 7 Replies View Related

Columns In Primary Table And Foreign Key Table

Feb 12, 2007

mahesh writes "HI,

I am new to sql server.

can anybody help me

I have a table named tblqualificationmaster.

can i know the foreignkeys and the table related to this

tblqualificationmaster having foeign keys using stored procedure."

View 1 Replies View Related

Space Required For DB Restoration

Sep 22, 2000

Hi Folks:

This seems like a pretty straightforward question, but since I've been trying to find this in BOL and not having any luck, I thought I'd ask some folks more experienced than I.

How much room do I need on a SQL box to restore a 6.45 gig database? (it's
going from a huge machine to a backup tape, then from the backup tape to a
much smaller machine for testing an app, and we're trying to find out what we need to do to make it all work out.)

I'm assuming that I would need significantly more than the 6 gigs of the db for the work of actually doing the restore, but can't find out how much more...

Any ideas would be most appreciated.

Many thanks in advance,

Tom

View 1 Replies View Related

Space Required To Store Indexes

Dec 18, 2007

hi all
i am under impression that indexes also stored seperately from data and need extra space . when we check in EM Table Info, size over there is just data size or sum of data and indexes. if it is just for data then is there is any space used to store indexes and that space is counted in space used by data base or else where.............. please clear my confusion.............. i am quite new in administartion of SQL Server

View 2 Replies View Related

Disk Space Required To Initialize Subscriber

Apr 30, 2008

Quick question- how much disk space is required on the subscriber to initialize a subscription (transactional replication) – does it have to copy the snapshot files (e.g. the bcp files etc) from the distributor, and then initialize the subscriber from this, or does it do this over the network?

Does it differ depending on whether it is push or pull?

Say I have a 10 GB snapshot on the distributor with a push subscription to the subscriber. Do I need 20 GB free on the subscriber? E.g. 10 GB to receive the snapshot files + 10 GB for the subscriber DB? Or just 10 GB for the subscriber DB which is initialized from the snapshot files over the network?

Thanks in advance for any help with this.

View 2 Replies View Related

Transact SQL :: How Much Space Is Required By Index Rebuild Command For Each Database

Sep 24, 2015

I'm upgrading to SQL 2012 from 2008R2, while doing so i will be rebuilding all the indexes on all the database. In my previous environment while doing so, i got space related error in primary filegroup for insufficient space in the primary filegroup. Is there any rule of thumb about how much space is required by index rebuild command for each database, or is there a safe threshold for free space in the database?

View 9 Replies View Related

Required Free Disk Space For SQL Server Backup Operations

Feb 4, 2008

What is the percentage of FREE disk space that is needed for a backup? I have backups that are failing with no disk space errors. But there is enough disk. Does SQL Server need a percentage of free space all the time?

Thanks

geri

View 11 Replies View Related

Storage Of Null Columns

May 23, 2007

Hi,

This question comes without the wish for any philosofical debates concerning NULL



Normally SQL Server 2005 uses 13 bytes to store a NUMERIC(22,7) value. Does it use the full 13 bytes when the column contains NULL as well? And what about DATETIME and (BIG)INT? Most of the info on storage of NULL values is about varchar/char/nvarchar/varchar...



Thnx, Jeroen.

View 3 Replies View Related

Separate Columns

Nov 9, 2005

Hi to all of you,

I am new to SQL I have a problem that I can’t solve.
I have a column with Surname and name (SMITH, James) in one table with data I just need to separate in two columns in one Surname in the other one Name I know how to unite two columns using substrings but not to separate in two columns.

Help is highly appreciated

View 4 Replies View Related

Violation Of PRIMARY KEY Constraint

Mar 23, 2005

Hi,
I am trying to insert into a table. I got the above error message then I deleted all the data in the table. But I am still getting this error.

Whats wrong?

View 2 Replies View Related

Primary Key Constraint Error

Oct 30, 2001

Hello,

I am getting the following message when I am trying to insert into my table. Request is the primary keys and the Bus_Req_Id and Test_Case_Id are both foreign keys. The data that is being inserted into these fields are not unique.

This is the exact error message:

Violation of PRIMARY KEY constraint 'PK_REQUEST_BUS_REQ_TST'. Cannot insert duplicate key in object 'REQUEST_BUS_REQ_TST'.
The statement has been terminated.

I am trying to insert 700 - 900 rows based on certain criteria. If I delete the keys the inserts will work.

Any help would be most greatful.
Thank you in advance

Anne

View 1 Replies View Related

Violation Of PRIMARY KEY Constraint

Sep 21, 2005

I know what this error means is that you can not insert duplicate primary keys in the table but the thing is I am checking the rows if they do not exist then insert otherwise don't do any thing these are the lines I am writing in my strored procedure can someone please let me know what I am doing wrong here.

If not exists
(Select * From GGP WHERE
FFECTIVE_DATE =@v_EFFECTIVE_DATE AND
ASSET_ID= @v_ASSET_ID AND
ASSET_TYPE = @v_ASSET_TYPE AND
Value = @v_Value AND
hour = @v_Hour)

INSERT INTO GGP
(ASSET_ID,ASSET_TYPE,Hour,Value,EFFECTIVE_DATE) values(@v_ASSET_ID,@v_ASSET_TYPE,@v_Hour,@v_Value, @v_EFFECTIVE_DATE)



The exact error is
Violation of PRIMARY KEY constraint 'PK_SP_GGP'. Cannot insert duplicate key in object

Thanks in advance.

View 7 Replies View Related

Violation Of PRIMARY KEY Constraint

Apr 10, 2006

When I try to insert data into a table (let's just call it MyTable for this post), I suddenly get the following error.

Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'MyTable'.

My table does have a primary key field named 'id', which is an auto-incrementing BIGINT. The last record I successfully inserted received 14 in the id field, so I'm assuming the database is trying to assign 15 to the next. Unfortunately, there is already a record with an id of 15; the next available id is 21.

Is there a way to avoid these primary key collisions?

View 5 Replies View Related

Composite Primary Key Constraint

Mar 21, 2007

Hi All,

I have a table that has 3 columns, two of them make a composite primary key. The table is populated with data. What I need to do is to add a third column to a composite primary key. I have tried to do that with the following command:

alter table databasesize
add constraint pk_dbsize primary key (dbid)

But I get the error message:

Table 'databasesize' already has a primary key defined on it.

How can I do this?

View 2 Replies View Related

PRIMARY KEY Constraint Problem

Feb 12, 2004

I have an odd problem on something that used to work fine.
I have an SP that inserts a record into a table (Contract) with two keyed fields.

The keys are as follows:

ContractID and SeqID (Sequence)
These two keys make the records unique.

Ex:
ContractID SeqID
12345 1
12345 2
12345 3
etc....

Several weeks of using this procedure have been fine. Suddenly I started getting this error:

Violation of PRIMARY KEY constraint 'PK_contract'. Cannot insert duplicate key in object 'Contract'.
The statement has been terminated.

I verified that the values do not violate the constraints. In fact, I can type the exact information into the table directly without a problem.

Has anybody experienced this before?

Any help would be apprciated!


Here is the code in the SP;

CREATE PROCEDURE bcipNewContractSeq @ContractID Char(10 )AS

DECLARE @MaxSeqID int
DECLARE @NewSeqID int

SELECT @MaxSeqID = Max(SeqID) from Contract_Live..Contract WHERE ContractID = @ContractID

SET @NewSeqID = @MaxSeqID + 1

--Copy Contract info for new seq with new seqid- record has default start and end dates
INSERT INTO [Contract_Live].[dbo].[Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @ContractID, @NewSeqID, 'In Process', ContractName
FROM [Contract_Live].[dbo].[Contract]
WHERE [Contract_Live].[dbo].[Contract] .ContractID = @ContractID

GO

View 11 Replies View Related

Violation Of Primary Key Constraint

Mar 3, 2006

I'm trying to insert records into a table and getting the below error and not sure how to resolve it:

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_propertyItem'. Cannot insert duplicate key in object 'propertyItem'.
The statement has been terminated.

View 2 Replies View Related

VIOLATION OF PRIMARY KEY CONSTRAINT

Feb 15, 2008

Violation of PRIMARY KEY constraint 'PK_tbl_others. Cannot insert duplicate key in object 'tbl_others.
The statement has been terminated.-- What does it mean i cant load the data to tbl_others

View 2 Replies View Related

Column Name In A Primary Key Constraint

Jul 20, 2005

Hi allWould there be a easy way to find the column name(s) which constitutea Primary constraint for a table through navigating the systemcatalogs.I found that the PK Constraint object in syscontraints is showing thecolid = 0.TIANorman

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved