Removing Primary Key
Nov 27, 2005Hi How do i remove the primary key for this table
create table table2
(col1 int,
col2 int,
primary key(col2))
Thanks
Vic
ADVERTISEMENT
Removing Duplicates To Create A Primary Key
Jan 3, 2008Hi, I'm in the midst of an Access 2003 to SQL server 2000 upsizing project and have come across a table on Sql Server that has a field that looks like it's supposed to be the PK but it contains duplicates. What I'd like to do is to have a cursor start at the first value and increment the next value by 1. Could someone explain how I'd go about this?
Many thanks,
Peter
Convert Composite Primary Key Into Simple Primary Key
Jan 11, 2007Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.
may i convert Composite Primary key into simple primary key in thr table like this.
Thanks,
Uma"
Adding Primary Key To A Table Which Has Already A Primary Key
Aug 28, 2002Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
Auto Incremented Integer Primary Keys Vs Varchar Primary Keys
Aug 13, 2007Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
SQL Server 2008 :: Change Primary Key Non-clustered To Primary Key Clustered
Feb 4, 2015We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
View 2 Replies View Related4 Key Primary Key Vs 1 Key 'artificial' Primary Key
Jan 28, 2004Hi all
I have the following table
CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)
I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id
In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified
I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.
What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?
Thanks Matt
Removing *
Aug 18, 2005Hi! I am trying to remove an "*" I used to transfer a file from Excel to SQL. I put the "*" in the NULL values so that I would not get an error. Now that I have the file in SQL, I need to remove the :*" from some columns in my table. How can I select all of those in a column and delete them without deleting each individual one. Any ideas?
Thanks for your help.
Scott
Removing Old Bak
Jan 15, 2008Started with maintenance cleanup task set up bak and directory did not delete the files.
Then found this http://blog.solidsoft.com/blogs/richards_infrastructure_blog/archive/2007/07/12/369.aspx
Copy script
use master
declare @dt datetime
select @dt=getdate()-2
EXECUTE master.dbo.xp_delete_file 0,N'D:BackupAcctDB',N'BAK',@dt
Get message
Msg 22049, Level 16, State 1, Line 0
xp_delete_file() returned error 2, 'The system cannot find the file specified.'
Anyone else get problem with removing BAK files
Removing Padding From Var
Feb 7, 2008I have a search box to look up using a number as an identifier. Currently my query is where x = @enteredNumber, but some place where a user might get the number it is padded with zeros. We can have them just see the number and retype it.
EG: 000000123 would be entered as 123.
But, I would like to let them copy and paste, but then strip the zeros (left padding only).
EG:
@num = stripPadding(@enteredNumber)
IE:
@num = stripPadding(000000123)
@num = 123
Removing A Default...
Sep 4, 2002Hi,
I'm using SQL Server 7.0 SP3. I added a column to a table like so:
ALTER TABLE T704_RELATIONSHIP_INDEX_CLIENT_REPORT_STORE
ADD Universe_Member_Set_cd INT NOT NULL DEFAULT 1
What is the syntax for dropping the 'DEFAULT' from this column once it has been created? I've looked in BOL but can't seem to get the syntax correct.
Thanks in advance,
Darrin
Removing The Duplicates
Aug 2, 2000I need to remove the duplicates from a table. If a record exits 4 times in a
table I need to delete 3 records and retain only one occurance of that.
I need a query to do this.
Can anybody help.
-Rajesh
Removing BuiltinAdministrators
Mar 20, 2002Hi all,
I am using SQL 2k SP2 on Win 2K Advance server Cluster. My problem is how to remove builtinAdministrators login. Earlier when I tried it was doing failovers from one node to another and not stopping. I was not able to do anything. Any help is appriciated
thanks,
Minesh.
Removing DB Suspect
Aug 31, 2000I wonder if one can give a hint on remove a DB suspect,
Mind you I have used SP_dbremove still did not remove the DB
This is on SQL 7.0 box
Any idea welcomed
Saad
Removing A Filegroup
Apr 25, 2000Hello,
A few days ago one our database's default filegroup filled up. To solve the problem one of my colleagues created a new filegroup. This didn't solve the problem as no objects were placed on this filegroup and the initial one is still full. The idea was to expand the original filegroup over a new physical disk, not to create a new filegroup on that disk. Unfortunately, this is what happened.
To solve this I want to delete the new filegroup, but when I issue the command 'alter database PvgOmcsOds remove filegroup ternary' I get the message 'Server: Msg 5042, Level 16, State 7, Line 1 The filegroup 'ternary' cannot be removed because it is not empty.' Yet there're no objects placed on this filegroup.
How can I get rid of this filegroup?
Stef
Removing Transaction Log From SQL 7 DB
Apr 7, 1999I am trying to remove a transaction log that has grown too large for its databse and server. I have truncated the log and have run dbcc shrinkfile with the EmptyFile Option, but when I try to use alter database remove file and am I told that the object is not empty and cannot be removed. The server is in dbo use only.
What am I missing?
Removing Filegroup
Mar 8, 2001Hi all,
I am trying to remove one of the filegroups in the development database to shrink its size down. I started with emptying and removing its files and ran:
dbcc shrinkfile
(pubs_data_1,emptyfile)
go
and it ran fine, with 'DBCC execution completed' message
but when I tried :
alter database pubs
remove file pubs_data_1
go
I got 'pubs_data_1 cannot be removed because it is not empty'.
I even tried to go to each of the tables and delete from them first which didn't work, then drop all tables in that filegroup (I knew which tables are located on that filegroup), that didn't work either.
Any ideas? Is there a way to check what else sits on that filegroup?
Thanks
Removing Replication
May 8, 2003Hi,
We had transactional replication set up by a consultant with remote distributer, push subscription to the publisher (main production database). However, the subscriber machine had fatal crash and is unrecoverable. Now, replication jobs have been failing on distributor and we immediately need to disable and remove replication as it has started causing problems with transaction log backups on production database.
How can I completely remove this whole replication from publisher and distributor?
Please help.
Thanks,
Shaili
Removing SQL 6.5 Software
May 28, 1999Dear Concerned DBAs,
I am wanting to pick someones brain and find out if there is anything I can do to successfully remove and reinstall SQL Server Version 6.5? I have already removed the software from the SQL Setup icon in ProgramsMSSQL Server 6.5 and went and removed the setup.exe and mssql files associated with the software from the server. Now when I attempt to reinstall the software back onto the server after a hard reboot, I do not get the option once the SQL Server Version 6.5 disk is placed into the CD-Rom to install SQL Server. The only options I get are upgrade, server configurations, etc.
Can anyone or everyone give me a hand as to where to take this dilemma other than rebuilding the server from the ground up?
Anticipatorily Yours,
Daimon Russell
Removing Certain Characters
Aug 18, 2006Hello all,
I have a table named users. It consists of user names, user ids, etc... The problem is that whoever designed the ASP code before me allowed people to enter info in any format they want. This poses a problem because now the name can have 1, 2, or sometimes 3 spaces in between the last and first names. And sometimes the middle initial is used with a period following it. This creates problems when I am trying to execute a Select statement since it won't match if there are an unknown number of spaces in the string and throws an error when a period is used.
Is there a SQL query I can execute to change all the user_names into a format such as the following:
LastName, FirstName MiddleInitial
Like I said, it is already almost the same, just has too many spaces and some have periods after the middle initial
Removing Duplicates
Jan 27, 2005I have a members table and have added an extra few thoushand members to it. Now I need to remove the duplicates.
It doesnt matter which duplicate i remove as long as there are unique email addresses.
so here is the format of the table:
id
email
firstname
lastname
datebirth
if i do a:
SELECT COUNT(DISTINCT Email) AS Expr1
FROM Customer
it returns 21345
and
SELECT Count(Email)
FROM Customer
returns 28987
I can get the unique email addresses into another table by going:
SELECT DISTINCT emailaddress INTO DistinctCustomer
FROM Customer
but this will only return unique email addresses. How do i select distinct email address and all other fields into a new table? or just remove duplicates where email address appears more then once?
Thanks in advance
Tom the SQL beginner
Removing A Paratiotion
Jun 24, 2008i want to build a paratiotin which the key of the paratition will be a datre in this structure :
20080101235959
20080102235959
20080103235959
20080104235959
20080105235959
and so on.....
and i want that when there is a paration that it's "date" is more then 30 days to delete it (and that its data will be deleted too)
how do i do that?
thnaks in advance
peleg
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
Removing Last Two Char
Apr 25, 2007Afternoon all
What is the quickest way to remove the last two char from a colunm and leaving the rest of the colunm intact.
cheers
Removing Data
Jul 23, 2005I have a table that I need to delete some data from and put the deleteddata into a different table.How do I script the following.If Field1 in Table1 is null, remove that row from Table1 and put it ina new table called Table2Regards,Ciarán
View 5 Replies View RelatedRemoving Duplicates
Jul 20, 2005HiI have inherited a web app with the following table structure, and need toproduce a table without any duplicates. Email seems like the best uniqueidentifier - so only one of each e-mail address should be in the table.Following http://www.sqlteam.com/item.asp?ItemID=3331 I have been able toget a duplicate count working:select Email, count(*) as UserCountfrom dbo.Membersgroup by Emailhaving count(*) > 1order by UserCount descBut the methods for create a new table without duplicates fail. My code forthe 2nd method is:sp_rename 'Members', 'temp_Members'select distinct *into Membersfrom temp_MembersTable....CREATE TABLE [dbo].[Members] ([MemberID] [int] IDENTITY (1, 1) NOT NULL ,[Username] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[Password] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[Email] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Title] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Address1] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,[Address2] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,[City] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,[Country] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,[Profession] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Publication] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,[DateAdded] [smalldatetime] NOT NULL ,[SendMail] [smallint] NOT NULL) ON [PRIMARY]GOThanks B.
View 2 Replies View RelatedRemoving Zeros
Sep 24, 2007Hi,
I have a column cost with sample record as '00003433' . how can i remove all zeros in left side alone?.
Out put : 3433.
Thanks
Raj
Removing A Server Name
Mar 31, 2007SQL
Upgrading to VS05 Pro SP1/SqlExpress (from VS03 Pro/SQL MSDE)
Question 1) €“ is there a way to tell which version of the SQL Express was installed? I think it is €œSQL Server 2005 Express Edition With Advance Services€?, but don€™t know how to confirm that.
Question 2) - SQL Server Management Studio Express, Registered Servers, it shows :
Database Engine
sqlexpress (note the lower case)
If I right click and choose €œPreviously Registered Servers€?, it shows a message that I will be displaying SQL Server 2000 (i.e. MSDE) server names, and it shows
SQL Server Group
SQLEXPRESS (note the upper case)
CSOLUTION
I wish to completely remove CSOLUTION (in MSDE you would uninstall it from the Control Panel), but right clicking and choosing the delete option does nothing (it does not delete it). Deleting SQL Server Group also has no affect either. How do I remove CSOLUTION ?
Question 3) - In the VS05 program, the SQL connection string includes €œData Source=.SQLEXPRESS€¦.€? (NOTE UPPER CASE) Why do I have a lower case and an upper case listed above? Starting/stopping one sqlexpress/ SQLEXPRESS does the same to the other. Don€™t I wan€™t to complete delete anything under SQL Server Group since it represents SQL Server 2000 (i.e. MSDE) server names? SQL Server 2000 and CSOLUTION have been uninstalled from this machine.
Thanks!
bob
Removing BUILTINAdministrators
May 1, 2007We need to remove the BUILTINAdministrators from reporting services. I.T. personnel do not want to be able to see restricted reports. We have two department users that have been added as Content Managers and we are running reporting services under a created domain user account that we created and not a system service. I have removed BUILTINAdministrators from the Home folder and when I check, it comes right back. Do I need to deny access to BUILTINAdministrator on SQL Server 2005 itself? I would apprecate any suggestions.
Removing A Certificate
Apr 28, 2008
Hi i'm having issues removing this certificate
select * from sys.certificates
I see
cert_xp_cmdshell_enabler 257 1 NA NO_PRIVATE_KEY 1 Enable xp_cmdshell c5 30 7d 58 ba 8e 96 a6 48 48 df ca 87 bc 91 44 0x010600000000000901000000BEC4CB23E7994CFF1E71403F906BB1EFFDDEAF9A S-1-9-1-600556734-4283210215-1061187870-4021382032-2595217149 Enable xp_cmdshell 2009-04-28 10:31:31.000 2007-04-28 10:31:31.000 0xBEC4CB23E7994CFF1E71403F906BB1EFFDDEAF9A NULL
DROP CERTIFICATE cert_xp_cmdshell_enabler
he certificate cannot be dropped because one or more entities are either signed or encrypted using it.
Is there any way I can drop this as I would like to recreated it and add a whole host of sps that I need to use the xp_cmdshell.
Thanks in advance.
Removing Databases
Jun 5, 2006Using SQL Server Management Studio, I see 4 entries, master, model, msdb and temdb, under Databases -> System Databases. Which of these can I safely remove, if any?
Thank you
Removing Row In Excel
Feb 25, 2008Hi gurus,
I got a pre-formated excel sheet as destination. The header is formatted with color.
The requirement is to populate the excel sheet with different data type (name(string), no (int), sal(int), age(int)) from second row onwards. The source data is a SQL table and I am using SSIS to export data to the above excel destination.
Problem:
Scenario 1: Using SSIS data flow, populate the excel sheet. In this case, the format of the first row is used by all the new inserted records. So, I am not able to format as per the data type requirement.
Scenario 2: To populate data with required data types, I need to format the second row with the specified data types and then export data into the excel in the SSIS data flow. In this case, there is a blank row in the second row position as shown in the picture:
(see the picture on url)
http://picasaweb.google.co.uk/h.zulfi/SsisExcel/photo#5171040908106882370
To meet the requirement, I need to remove the blank row in the excel destination. It should look like below:
http://picasaweb.google.co.uk/h.zulfi/SsisExcel/photo#5171040912401849682
Is there any other method to achieve the export of data meeting the specified requirement?
If no, then is there any method to remove the blank row from excel apart from using com objects to remove the blank row and shift cells up right.
Any thought and help in this regard would be much appreciated.
Cheers,
Zulfi
Removing Duplicates
May 19, 2008
Using a sort transform to remove duplicates takes a very long time to execute.Is there a better way to remove duplicate information.
I have alot of duplicates within the data.
I would like to know a better way than using sort transform
Please let me know
How To Removing Document Map
Jan 30, 2007I add document map to my report but I forgot
how can I remove it
thanks!