New ID Column Design

Jul 31, 2013

I need to periodically send a set of data to a vendor. They need to have a column what is an unique identifyer but they cannot handle 16 digit GUI.So I have this my_old_person table with person_GUI as uniqueidentifier. It got 200K rows in it. I am thinking creating a staging utility table:

Create table my_new_person
(person_GUI varchar(255) not null,<--this will contains the same GUI from the old
Person_ID nvarchar(20))<--send tis to vendor

At the beginning of my nightly ETL, I will run this to get the new data:

Insert into my_new_person
select a.person_GUI, xyz
from my_old_person a join my_new_person b
on a.person_GUI=b.person_GUI
where not exists (Select person_GUI from my_old_person)

How do I implement this person_ID to make it unique and populate correctly in above xyz?

View 6 Replies


ADVERTISEMENT

DB Design :: Create Two Column With Math Function In One Column?

Oct 5, 2015

How Can I Create Two Column with Math Function In one Column  Like Below. 

Create Table Tbl_V_Voucher_Details
(
Id Int IDentity (1,1) Primary Key, 
Catid Int Foreign Key References Tbl_V_Voucher_Info(Id),
ItemId Int Foreign Key References Tbl_V_Item(Id),
Discription varchar
(100),
Qty Int,
Price Float,
Qty * Price As Total 
)

View 5 Replies View Related

DB Design- NULL Column!!

Oct 28, 2005

Hello FriendsSuppose I'm designing one database table- ABC master and it has fields like field1, field2........field5. From these fields, Field1 is PK and 2 & 3 and mandatory. So while creating this table, is it necessary to specify the field 2 & 3 as NotNull in Null column.If I specify them as Null then does it effect aby thing. As i know that before saving the data to the database the programming logic checks whether the mandatory data entered or not?Reply back..Thanks

View 3 Replies View Related

DB Design - Alphabetical Column Names?

Dec 13, 2004

I have been discussing with some coworkers whether or not it makes sense to invest the time to alphabetize the column names in our tables (aside from the PK and possibly FK's that could be listed first). My reasoning for doing so would make it much easier to scan the list of columns in a table that I was not familiar with to see if it contained a particular column (i.e. meeting_id). I was just wondering if this is common at all in our industry for new DB design (I realize why legacy systems would not be ordered in this way). I remember seeing MSFT designed their tables this way when Site Server first came out.

An argument was made that when you add a new column you would insert it into the appropriate location and in order to make this happen, EP needs to create a temp table, move the data and consequently lock the table until the data has been transferred. While this is correct, I do not see this negative as outweighing the positive experience achieved by scanning a list of fields in a table in a more orderly fashion.

Thoughts?

View 14 Replies View Related

Computed Column Design Help Needed

Apr 3, 2008



I have a Member table and I have a MemberView.

I want the view to return the "count" from a complex query related to the Member record and I'm looking for a good way to do that.


I was thinking:
select M.*, (select count(*) from UDF)

but that just smells bad.

Any pointers would be great.

View 3 Replies View Related

DB Design :: How To Split One Column Into Two Columns In Server

Mar 6, 2015

I have a ipaddress column is there where i need to split the column into two columns because of values like below

172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874

I have written the below query but it will throuh some error.

  select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0 
   from IPADDRESS
error:
Msg 537, Level 16, State 2, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

View 16 Replies View Related

DB Design :: Column Store Index And Constraints

Oct 20, 2015

can you have constraints as such [CreateBy] [nvarchar](30) NOT NULL DEFAULT (suser_sname()),on a table that has a column store index in SQL Server 2012,2014, or 2016?

View 3 Replies View Related

Index Design Recommendation - Examine Column Uniqueness

Nov 30, 2005

I am reading "SQL Server Query Performance Tuning Distilled",on page 104 it talks about one of the index design recommendationswhich is to choose the column that has very high selectivity of valuesinstead of a column that has very few selectivity of values.My question is if I have currently indexes on my tables that have1, 2, 3, 4, ... values only on thousands of rows, are these nonclusteredindexes pretty much useless indexes that I should get rid of?And I know that pretty much the number of selectivity values willalways remain very low.Thank you

View 1 Replies View Related

Report Design Question Two Horizontal Column Headers One Below Other

Jan 30, 2007

I have a report requirement, i am new to reports.

I want to have two lines of column headers.( with 7 columns in parent columns header)

and 7 columns in child column header.

The first column header will show a parent record.

And after the parent record i want to show the next child related horizontal column headers and will show all child record related to the above parent record.

its a one to many: 1 parent record and below all child records for that parent record.

can i use subreports controls to show all child records of the parent.

Thank you very much for the information.

 

 

View 1 Replies View Related

DB Design :: Add A New Int Identity Column As Primary Key To Already Existing Table

Sep 3, 2015

We want to add a new int identity column as a primary key to an already existing table that has a primary key on Guid. Here is the DDL:

CREATE TABLE [dbo].[VRes](
[VResID] [uniqueidentifier] NOT NULL,
[Mes] [varchar](max) NOT NULL,
[PID] [uniqueidentifier] NOT NULL,
[Segt] [int] NOT NULL,

[code]....

Also we currently have 3 million rows on this table. Is having an integer column as identity column and primary key better or shd I consider using BigInt?

View 4 Replies View Related

DB Design :: Change Order Of Column In Database Tables

Jul 22, 2015

How to Change Order of Column In Database Tables

View 10 Replies View Related

Transact SQL :: How To Set Case Sensitivity Of A Column In Design Time

Aug 3, 2015

With Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?

N.B.: Using SQL Server 2005.

View 22 Replies View Related

DB Design :: Unable To Create Index After Column Resizing

Oct 5, 2015

We have a vendor created database with 9000+ tables, one of which has about 6 billion rows.  The vendor redesigned the database recently and ever since we've had terrible performance.

What the vendor did was increase any and all varchar columns (tens of thousands of columns) to 256.

Before the upgrade we had no problems creating an index on the 6billion row table, it would take 2 hours.

Now after the upgrade we've let the index creation command run for 5 days and killed it because it was consuming terabytes of logspace.

The previous design had combined column width of 1049 to what is now over 4000. The primary key itself is 1283 characters (SQL limit is 900).

There is no additional data, just wider columns. Why we are unable to create the index?

What is happening inside SQL Server? Does SQL make "room" in memory for the index for the entire width of the potential max row length?

View 3 Replies View Related

DB Design :: Composite Clustered Index Key Based On Column

Nov 24, 2015

I created composite clustered index key based on Gender and Salary column 

The Query executed Successfully and <g class="gr_ gr_135 gr-alert gr_tiny gr_spell undefined ContextualSpelling multiReplace" data-gr-id="135" id="135">i</g>

got composite index key id Gender(-), Salary I <g class="gr_ gr_310 gr-alert gr_gramm undefined Grammar only-ins replaceWithoutSep" data-gr-id="310" id="310">want</g> know why Gender(-) display like this?

And Gender is <g class="gr_ gr_391 gr-alert gr_spell undefined ContextualSpelling ins-del multiReplace" data-gr-id="391" id="391">nvarchar</g> (20) 

View 2 Replies View Related

Design For Loading Data Without Knowlged Of Datatype Or Column Count

Jul 17, 2006

I am loading data from an external source into SQL Server via ASP.NET/C#. The problem is that I do not necessarily know the data types of each column coming in, perhaps until a user tells the application, which might not occur until after the data is loaded. Also, I cannot anticipate the number of columns coming in. What would table design look like?
Would you use a large table with enough columns (e.g. Column1, Column2, etc.) reasonable enough to accomodate all the columns that the source might have (32?), and use nchar as the datatype with the plan to convert/cast when I use the data? Isn't the cast kind of expensive?
Does this make sense? Surely other foplks have run into this....
My thanks!
 
 

View 1 Replies View Related

SQL 2012 :: Store Attributes / Values In XML Column - Table Design

Oct 29, 2014

I'd like to create a table that will store different order items. Several order items make up one single order. Order items can have 0 or more children (max depth will never be deeper than one). Order items can have up to 150 attributes/values. The way I think this should be done is using XML column instead of the EAV type of model. My table structure currently looks like this:

* child_order_item_id (PK)
* parent_order_item_id (FK to child_order_item_id)
* order_id (FK to Order table)
* product_id (FK to Product table)
* price
* attribute_XML

How my attribute_XML should look like or how to validate the xml.

View 2 Replies View Related

DB Design :: How To Avoid Similar Entries In Column List In Server

Sep 16, 2015

I am sharing one sql query and o/p:

select distinct  case 
          when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully'
          when LastExecutionResult = '2013' then 'Machine Does not have Office 2013'
          when LastExecutionResult = '17023' then 'User cancelled installation'
          when LastExecutionResult = '17302' then 'Application failed due to low disk space.'

[Code] .....

The below is the output for the given query,here i want to see only one comment value in my list and the count is also sum of all where comment should be Application will be installed once machine is online(Bold columns o/p)

Comment  Machine Name
Application will be Installed once machine is Online 4
Application will be Installed once machine is Online 12
Application will be Installed once machine is Online 42
Application will be Installed once machine is Online 120
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60

I need o/p like below:in single line

Application will be Installed once machine is Online 178
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60

View 2 Replies View Related

DB Design :: Optimize A Query That Uses A Varchar Column That Is Used In Order By Clause

May 5, 2015

I am querying a tableA with 1.8 million rows, it has id as its primary key and is a clustered index. I need to select all rows where I order by lastname. Its taking me 45 seconds. Is there anything i can do to optimize the query.Will creating a fulltext index on lastname If so, can you give me an example on how to create a full text index on lastname?

[Project1].[Id] AS [Id], 
[Project1].[DirectoryId] AS [DirectoryId], 
[Project1].[SPI] AS [SPI], 
[Project1].[FirstName] AS [FirstName], 
[Project1].[LastName] AS [LastName], 
[Project1].[NPI] AS [NPI], 
[Project1].[AddressLine1] AS [AddressLine1], 
[Project1].[AddressLine2] AS [AddressLine2], 

[code]...

View 5 Replies View Related

DB Design :: More Than One Table Primary Key Adding In Single Foreign Key Column

Nov 6, 2015

I have come up with one scenarios where I have three table like Product, Services and Subscription.  I have to create one table say Bundle where I can have some of the product id , service id and Subscription id ,  i.e. a bundle may contains sum prduct , services and subscription . How I can design these relations ?

View 3 Replies View Related

DB Design :: Create Views From 2 Tables One Of Which Is Lookup Table That Will Give Column Names

Aug 5, 2015

I am trying a create views that would join 2 tables:

Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape

I want to create a view that looks like

ID, Color, Size, Flavor, Shape

View 4 Replies View Related

DB Design :: Table Partitioning Using Reference Table Data Column

Oct 7, 2015

I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster? 

I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)

View 3 Replies View Related

DB Design :: Database Design For Matrix Representation

May 13, 2015

I have a scenario like below

Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1

How to design tables in SQL Server for the above.

View 2 Replies View Related

Database Design/query Design

Feb 13, 2002

Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:

-------
Fixture
-------
fix_id
fix_date
fix_played

----
Team
----
tem_id
tem_name

-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals

It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.

Fixture contains the details about the fixture like date and fixture id and has it been played

Team contains team info like team id, name, associated graphic

TeamFixture is the table which links the fixture to it's home and away team.

TeamFixture exists to prevent a many to many type relationship.

Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!

View 2 Replies View Related

DB Design :: Table Design For Packages

Aug 18, 2015

I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show.  How to design the table.. 

Product name   :  
Nestle milk |
Rainbow milk
packages  :
CTN,OTR, NOs |

CTN, NOs
Price:
50,20,5 |
40,6

(Remarks for your reference):CTN=10nos, OTR=4 nos  
| CTN=8 Nos

View 3 Replies View Related

Integration Services :: Add New Column Based On Existing Column Using Derived Column Task?

Jul 28, 2015

I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H.  I want to create a new column called "status" based on the values of "Code".

Code:

A
B
C
D
E
F
G
H

If A,C,E,G then "status" = "Active" else if  B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".

View 4 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

How To Refer A Column When The Referencing Column Is An Identity Column

Oct 16, 2006

Hi all,

The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..

i cannot implement this constraint, it throws the error when i execute the below Alter query,

ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE

the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.

any workarounds for this ?

View 3 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;







View 18 Replies View Related

Alter Table Add Column - How Do You Add A Column After Say The Second Column

Jul 11, 2007



When you use "Alter Table add Column", it adds the column to the end of the list of fields.



How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?



Create table T1 ( a varchar(20), b varchar(20), c varchar(20))



Alter table add column x varchar(20)

so that the resulting table is

T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)



Can this be done programmatically?













View 33 Replies View Related

Help Me Design My DB

Nov 7, 2003

Hi all.

I'm going to create a big DB that will hold important info
(the usual stuff - Clients, Products, Orders...)

I wonder where should I use the IDENTITY field,
for example - on Orders I will have Order_ID...
(and where does SQL server the numbers of a deleted records)


My fear is that IDENTITY fields will go wrong somehow so I can
loose connections within the tables
(maybe when restoring my DB to some other locations... with DTS... or other issues)


1. When should I use IDENTITY field ?

2. If I do NOT - how can I lock a record when I add a new one

View 17 Replies View Related

DB Design

Jan 13, 2004

Hi,

I have 7 web forms wizard. after the user edit the fields, I need to keep a history log of the changed data only.

I am thinking of the following structure

log_id, user_id, field_id, old_value, new_value, transaction

Where field_id is a number indicates the changed field.
for example 1> First Name, 2> Last Name etc

Where transaction is ADD, DELETE, EDIT

Also i need to keep history of the details
for example an enterprise has 10 branches.
State, employees
NY, 1000
MD, 500

My log table will look like this
log_id, user_id, field_id, old_value, new_value, transaction
1, 1, 1, , Mick ,John , EDIT
2, 1, 3, ,NY , HI , EDIT
3, 1, 3, ,MD , , DELETE
4, 1, 4, ,500 , , DELETE
5, 1, 3, , ,NV , ADD
6, 1, 4, , ,700 , ADD


Is there a better way to log changes?

Thanks

View 1 Replies View Related

Design Q

Jan 16, 2004

I'm going to create a hardware/software inventory program to keep track on all the computers at the office. This program should be able to search the database using queries like "all computers with a cpu faster than 500Mhz".

I want some tips on my database design.

One solution is to create columns for each piece of hardware, i.e. cpu, ram, hdd etc etc. Then just run simple SELECT queries against them. The problem is that a computer may have many HDDs/CDs etc, and also other type of equipment may be entered in the database like switches and routers.

Another solution is to specify the valuetype + value in one table having a relation to another table containing the actual machines/routers/switches. The problem here is that I can't do numeric comparisons this way since "11" is less than "2", ("all computers with a cpu faster than 500Mhz").

Any suggestions?

View 3 Replies View Related

Bad Design?

Feb 10, 2004

Table A
[ID] [SalesmanEmail] [SalesmanName]
Table B
[ID] [QuoteNo] [SalesmanID]
Table C
[ID] [Product] [QuoteNo]


Program A creates the records for Table C. In the process it reads from Table A and Table B.

Program B reads record from Table C. Program B now needs [SalesmanEmail] field.

The suggestion is to add [SalesmanEmail] to Table C. Is this good or bad design?

View 5 Replies View Related







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