Hi,
Please, What s the differnce between a column that s a primary key and a column that s a "key/index with Isunique=true"?
Thanks a lot.
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
Hello SQL Server Experts,
I'm upgrading an application that uses a VBScript/ASP front-end and MS Access Backend. The application has many points expecting fields to be true or false..
E.g. SELECT * FROM MyTable WHERE Active=True
However, since SQL Server requires 0 or 1 for bit fields, this query keeps failing. SQL Server (2005 Express) thinks True is a column name and is not automatically converting "True" to 1.
It would be so difficult to re-write the application to do this at the VB level, is there a way to make SQL server do this conversion automatically ?
Thanks for any assistance available.
Sunny
Hi,Would like to know the performance differenece between Multi-columnIndex vs Single Column Indexes. Let's say I have a table with col1,col2, col3 along with a primary key column and non-indexed columns.In queries, I will use col1, col2, and col3 together and some timesjust one or two of these three columns. My questions is, should Icreate one index contains col1, col2, and col3, or create 3 seperatedcolumns. I.e. each column has its own index. Any performancedifference?Thanks a lot.
View 1 Replies View RelatedHello everyone, I am new to ERWIN and I need helps from the experts outthere.We are using ERWin 4.1.2771 and have reversed engineered some MS SQLServer 2000 databases.The problem we are having is that we have a FK on a column to a tablewhere the PK of the referencing table is on another column (such as anidentity column). We have a unique index on the column in the PK tableand SQL Server allows you to build a FK reference even though thecolumn is not defined as the PK.Does anyone know how to create this type of FK within Erwin?Thank You
View 2 Replies View Related
Dear All.
We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.
table is >> Order_Dtl
Columns are:-
Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
I am thank u to all who read or reply.
Arshad
Manager Database
Esoulconsultancy.com
(Teradata Master)
10g OCP
Hello, this SQL query gets all columns of a table:
SELECT syscolumns.name AS [Fields in Items Database], syscolumns.type,
syscolumns.length, syscolumns.isnullable FROM sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name =
'IssueTracker_IssueAttachments' ORDER BY syscolumns.colid
I would like to know, is there a way to get a column saying who is the
primary key in the table ? I am reading columns for tables and
processing them, but I would like to know who is the primary key, is
that possible ?
regards
What is the different between Primary key and Identity Column? and when should I them?
(Im just a beginner with databases...)
I checked column from xsd file which created from DataBase.
And before installing my application I will check any column in destination base whether have complete column or not.
And if there is any column which is not wanted column(not same as xsd structure)
I will delete it by creating sql command as follow "ALTER TABLE tableName DROP COLUMN column1, column2, ......"
and Execute it by program initialization.
So
I need delete it by run-time
However some column may be Primary Key with any reason
That's why I can't delete them by simple command
I expect to delete them by specific column instead of specific constraint name which is not sure name.
Please advise me....
Hi everyone,How do I set a primary key on an existing column using SQL statements? I have column persID of type int in table employees. How do I correctly apply the ALTER command to change persID type to INT IDENTITY(1,1) NOT NULL? Thanks in advance
View 5 Replies View RelatedCan anyone explain the Difference between Primary Key and Identity Column in MSSQL Server 6.5. Please give me any example if possible.
Thanks for Help!!!!
Can a column act as both primary key and foreign key, if so what is script.
And can a table have more than one primary keys/foreign keys, if so what is the script.
will there be any issues if i rename a primary key column name,which is already beging referenced as a foreign key by other tables.
View 1 Replies View RelatedI have a table name Dispatch with the following column
ID,Date, Name, Pickup, Going
I have a next table called Move with the following column
ID, MoveName
I link Move table with Dispatch table via relationship.
ID_Pickup, ID_Going
upon running query no data is shown unless i remove one of the column form the query either pickup or going ...
Is there a query that can return teh name of the primary key column of a table ?
View 2 Replies View RelatedHello,I have a table 'customers', with 2 records:SELECT * FROM customers;customerID | customerName------------------+-------------------------myFriend | myFriend's Nametest | testing user(2 rows)but when I'm asking about customerID column, I get the answer:SELECT customerID FROM customers;ERROR: column "customerid" does not existWhat happens? I'm using PostgreSQL 8.1.3
View 4 Replies View RelatedHi 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 RelatedHi
I have a set of excel files that i need to export to sql2005 using ssis. Now the issue is that i have no idea about he data ie it may have duplication in the primary key column. If i export it as it is to sql server, it will cause me problems. Is there any way i can filter out the rows which have duplication in the primary key column?
Umer
hi all
my question is which query shud i use in sql server 2000 to get which column or columns are primary keys of table
i dont want to use any stored procedures only sql query
sp_primary_keys_rowset is one of d stored proc in sql server 2005 but i couldn't understand which query they are using
i only want to use sql query
given the name of any table how can i programmatically get the name of the primary key column?
View 1 Replies View RelatedI 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".
Hello --
Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.
I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.
Any help is appreciated.
Eric
I have a situation in which i have to get the last value stored in the Primary Key for all the tables. Based on this value i have to update another table which stores the Table names and the last Key value for the table. The values in this table are not correct therefore i have to update it now. I was trying to write a cursor for this but the only problem is i can't get to know how to get the column name on which the primary key is defined for all the tables, thru code.
I would appreciate if someone could help me out with this.
hi !
Just started playing around with SQL 2000 and I createda sample table called 'actor' which has 4 columns
1. actorID
(formula= LEFT(NEWID(), 3)+ LEFT([actorFirst], 2) +
LEFT([actorLast], 2) + RIGHT(NEWID(), 3))
2. actorFirst
varchar(20) NOT NULL
3. actorLast
varchar(20) NOT NULL
4. actorName
(formula = [actorFirst] + ' ' + [actorLast]
Now my problem is that I want to set a primary key constraint on actorID but it doesn't let me because it the NULL check mark is automatically checked and I cannot check it off ... and I can't set a primary key on something which is allowed to be NULL....
I don't understand why 'actorName' column which is also calculated doesn't have that default NULL checked and locked ....
What am I doing wrong ? Please help ....
Hello,
I am trying to recreate a primary key that I dropped in a table....
I'm using a statement like
ALTER TABLE temp WITH NOCHECK add CONSTRAINT PK__tempkey PRIMARY KEY CLUSTERED
(
num,
store
)
But apparently the "store" column was created without a "not null" and it let it create the PK during the table creation but now it won't let me add the PK with that nullable column..
Does anyone know how to get it to use that column to create a primary key after the initial table creation?
Here is the error i get
Cannot define PRIMARY KEY constraint on nullable column in table
Thanks a lot
We are developing a batabase which is meant for financial domain,so it will import data from different source system..
and data from our data base will be further passed to other applications.
In contex of our system integration with other data sources ,whether is it a good idea to have a auto integer primary key a or to implement some logic to generate primary key?
Can some one guide us to some pratical data base design case studies?or some best practices.?
Hi,
MSSql2000: According to docs:
"Columns of type bit cannot have indexes on them"
Its impossible to define index on bit column using EM but ‘create index …’ command in QA is working and the index is created.
I understand why not to create index like this but … its valid or invalid operation to create index on BIT column ?
Thanks
i've these following table and data
tblCamera
TransID | BranchID | X | Y | TransDate
---------------------------------------------
1 | 12 | 90 | 76 | 3/12/2007
2 | 9 | 45 | 79 | 3/12/2007
3 | 12 | 69 | 34 | 3/12/2007
4 | 12 | 78 | 56 | 3/12/2007
5 | 9 | 67 | 35 | 3/12/2007
6 | 12 | 78 | 56 | 3/12/2007
*TransID is a primary key
tblBranch
BranchID | BranchName
--------------------------
9 | Kota Raja
10 | Kota Alam
11 | Kota Lama
12 | Kota Tua
13 | Kota Hantu
*BranchID is a primary key
Which column need to put an index? Can anyone show me the best way?
If i have cluster and NC index on same column,does it degrade performance on DML statements ? any advantage on select statements.
Is it good to have both indexes on same column ?
Is there any advantage to doing this:ALTER TABLE testtable ADDCONSTRAINT PK_sysUserPRIMARY KEY NONCLUSTERED (UserID)WITH FILLFACTOR = 100,CONSTRAINT IX_sysUserUNIQUE NONCLUSTERED (UserID)WITH FILLFACTOR = 100GOover just having the primary key? Does having both an index and aprimary key add anything?thankschris
View 6 Replies View RelatedUpdate: Changed original post as I figured it was not the problem I stated.
Hi all,
Heres the problem. I have a table in SQL Server with two fields, PartSubCategory and PartCatID. PartSubCategory is the primary key. When I import an excel sheet with identical columns into this table, the primary key column in the table gets sorted automatically (alphabetically). I don't want this column sorted but to import the rows from excel in the order as they were. Is this some indexing issue? Is there a fix?
Thanks a lot...Bullpit
I want to add new primary key into existing table which already has a primary key. But,I do not want to remove the old primary key, since there are many records and the old primary key also have relationship with other table
When I am using this query:
alter table hem154
add indexNO uniqueidentifier default newid()
alter table hem154
add CONSTRAINT pk_hem154_indexNo PRIMARY KEY (PK_indexNO)
go
Note:
Hem154 ~ Table name
indexNo ~ Column Name
I get this runtime error:
Msg 1779, Level 16, State 0, Line 1
Table 'hem154' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
I have a varchar column in a table, which is an unique key column. Now when I design this table should I use this column as a primary key or should I add one more Integer column as a primary key column for the performance?
One more point is, when ever I do a search on this table I will search based on that unique varchar column values only, so even then if I add new integer column just for Primary key I will not use this column for searching values.
With these information, can some body help me in deciding this?