BULK INSERT Ignores UNIQUE Index With IGNORE_DUP_KEY Set?
All,
Just want to make sure that I understand what's going on here.
I have a table with IGNORE_DUP_KEY set on a unique, multi-column
index.
What I'm seeing is this:
1) When performing a BULK INSERT, the UNIQUE index is not being
respected and rows which violate the unique index are inserted.
2) When performing a regular INSERT, the UNIQUE index is being
respected and rows which violate the unique index ARE NOT inserted.
Is this expected behavior.
Also, I have some questions, given the index described.
Q1) Will a regular INSERT that attempts to insert duplicate data get
an error back or just a warning?
Q2) How can I set things up so that a BULK INSERT would NOT allow
duplicates to be entered into the table?
Thanks,
Wes Gamble
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Unique Index Conflict On Insert Into
I get this message when doing an insert into : Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.ElementLocalCharacterised' with unique index 'ElementLocalCharacterised_uq'. The statement has been terminated. Now my question is : does this error mean that all inserts are done, despite those where the unique key produced a conflict? Or does it mean that none of the inserts are done (all are rejected because at least one gave a conflict) ? If the second is the case I would like to know if it is possible to execute the insert into in a way that all inserts are done despite those that raise a conflict? Thanks a lot for any suggestion ! Regards, Fabianus my favorit hoster is ASPnix : www.aspnix.com !
View Replies !
View Related
How To Return Primary Unique Index Key On Insert
Hi, I am making a program in Visual Basic .NET with SQL Server 2000. I have a table "MyTable" with a primary key named "Id". The primary key is 'Create Unique' checked and 'Index' selected. When I insert all the fields required, except "Id" of course, I need the new record's "Id" in my VisualBasic program, but I don't know how... I must do one of them, but don't know how either of them: -Create a trigger on insertion that will send to the user that sended the insert command the "Id" of the record just created. or -get the command in Visual Basic that will send the Insert command with a return field ("Id") Thanks in advance, Sebastien Anselmo
View Replies !
View Related
Cannot Insert Duplicate Key Row In Object 'MSmerge_genhistory' With Unique Index 'unc1MSmerge_genhistory'
I have 1 client who keeps running into the following error on the subscriber and merge agents > €œCannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.€? Last time we got this error I ran a reindex on table MSmerge_genhistory on the publisher database, I then successfully generated a new snapshot and the subscribers started to synchronize again. This time around I keep getting the error even after I follow these steps (I also ran all the jobs to clean up replication). The last time I ran into this error I created a job to reindex msmerge_genhistory on a nightly bases in an effort to avoid this problem. Can somebody please provide me with a workaround and also the reason why this error occurs in the first place. Thank you in advanced, Pauly C
View Replies !
View Related
Cannot Insert Duplicate Key Row In Object 'dbo.lastlogin' With Unique Index 'IX_lastlogin'.
Hi. I have been recently redesigning my tables - creating FK relationships from child tables to the PK userid in the Users table. The specifics of what I did and why can be seen here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1968856&SiteID=1 But, now I am getting the below error: Cannot insert duplicate key row in object 'dbo.lastlogin' with unique index 'IX_lastlogin'. The statement has been terminated. Or, for that matter, SavedSearches or any other table where I need to insert the same userid twice. I can see why I would want to avoid duplicates in the Users table. But, for lastlogin, savedsearches, and a few of my other tables, the same user may account for multiple rows. Any suggestions as to where I messed up and how to deal with this? Thanks. DBO.USERS Code Snippet CREATE TABLE [dbo].[users]( [userid] [int] IDENTITY(1,1) NOT NULL, [lastname] [varchar](50) NULL, [firstname] [varchar](50) NULL, [email] [varchar](50) NOT NULL, [alternateemail] [varchar](50) NULL, [password] [varchar](50) NOT NULL, [role] [varchar](10) NOT NULL, [securityquestion] [varchar](50) NOT NULL, [securityanswer] [varchar](50) NOT NULL, [zipcode] [int] NOT NULL, [birthmonth] [tinyint] NOT NULL, [birthday] [tinyint] NOT NULL, [birthyear] [int] NOT NULL, [gender] [varchar](10) NULL, [city] [varchar](50) NULL, [state] [varchar](50) NULL, [country] [varchar](50) NULL, [registerdate] [datetime] NOT NULL, [editdate] [datetime] NULL, [confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)), CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [userid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_email] UNIQUE NONCLUSTERED ( [email] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF /****** Object: Table [dbo].[lastlogin] Script Date: 08/22/2007 14:16:16 ******/ SET ANSI_NULLS ON DBO.SAVEDSEARCHES CREATE TABLE [dbo].[savedsearches]( [savedsearchesid] [int] IDENTITY(1,1) NOT NULL, [searchname] [varchar](50) NOT NULL, [userid] [int] NOT NULL, [date] [datetime] NULL, [isdefault] [bit] NULL, [gender] [char](10) NULL, [startyear] [varchar](50) NULL, [endyear] [varchar](50) NULL, [country] [varchar](50) NULL, [miles] [int] NULL, [pictures] [varchar](50) NULL, [postal] [int] NULL, [sort] [tinyint] NULL, [photostring] [varchar](50) NULL, [orderby] [tinyint] NULL, CONSTRAINT [PK_SavedSearches] PRIMARY KEY CLUSTERED ( [userid] ASC, [searchname] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[savedsearches] WITH NOCHECK ADD CONSTRAINT [FK_savedsearches_users] FOREIGN KEY([userid]) REFERENCES [dbo].[users] ([userid]) GO ALTER TABLE [dbo].[savedsearches] CHECK CONSTRAINT [FK_savedsearches_users] GO SET QUOTED_IDENTIFIER ON GO The following insert statement returned the error in the subject because userid = 32 already exists in the Users table. INSERT INTO lastlogin VALUES (32, CONVERT(VARCHAR(26), GETDATE(), 109), 1, CONVERT(VARCHAR(26), GETDATE(), 109)) DBO.LASTLOGIN Code Snippet CREATE TABLE [dbo].[lastlogin]( [lastloginid] [int] IDENTITY(1,1) NOT NULL, [userid] [int] NOT NULL, [date] [datetime] NOT NULL, [status] [bit] NOT NULL CONSTRAINT [DF_lastlogin_status] DEFAULT ((0)), [activity] [datetime] NOT NULL CONSTRAINT [DF_lastlogin_activity] DEFAULT (getutcdate()), [online] AS (case when [status]=(1) AND datediff(minute,[activity],getutcdate())<(30) then (1) else (0) end), CONSTRAINT [PK_lastlogin] PRIMARY KEY CLUSTERED ( [date] ASC, [userid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[lastlogin] WITH NOCHECK ADD CONSTRAINT [FK_lastlogin_users] FOREIGN KEY([userid]) REFERENCES [dbo].[users] ([userid]) GO ALTER TABLE [dbo].[lastlogin] CHECK CONSTRAINT [FK_lastlogin_users]
View Replies !
View Related
Cannot Insert Duplicate Key Row In Object 'dbo.blabla..' With Unique Index 'Idx_blablabl'. The Statement Has Been Terminated. At
We are developing a project that is expected to hold TB of data and the back end used is SQL Server 2005. I have the following problem I have applied Nonclustered index over a column on a table. Designed a SP for insertion which caters for updation incase the criteria based on the input is met. The logic goes like this Incase there exists a row containing the value of the column that is indexed for uniqueness, there should be updation. If not there should be a new row created. However often there is an error message that is placed above. This happens only on some of the SPs and only on rare occasions. Can any body tell me if there is any problem with the SQL Server 2005 Thanks in advance R Suresh, SQLDBA
View Replies !
View Related
Unique Constraint And Unique Index, What's The Difference?
What's the difference in the effect of the followings: CREATE UNIQUE NONCLUSTERED INDEX and ALTER TABLE dbo.titles ADD CONSTRAINT titleind UNIQUE NONCLUSTERED I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
View Replies !
View Related
Unique Index Vs Unique Constraint
Hi everyone, I need urgent help to resolve this issue... As far as the performance goes which one is better.. Unique Index(col1, col2) OR Unique constraint(col1, col2) ? Unique constraint automatically adds a unique index and unique index takes care of uniqueness then whats the use of unique constraint ? Which one do one use ? thanks sonali
View Replies !
View Related
Bulk Insert - Bulk Load Data Conversion Error
Im having some issues with bulk insert. This is the table: CREATE TABLE [dbo].[tmp_GA_status]( [GA_recno] [int] NOT NULL, [GA_desc] [varchar](40) NULL ) This is the file (unicode): 1|"test1" 2|"test2" 3|"test3" 4|"test4" 5|"test5" 6|"test6" 7|"test7" 8|"test8" and this is the sql: bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta' with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR=' ', DATAFILETYPE='widechar') so yeah, pretty simple. But whatever I do I get this; Msg 4864, Level 16, State 1, Line 1 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GA_desc). So what am I doing wrong ?
View Replies !
View Related
I Don't Suppose BULK UPDATE Exists?... Like BULK INSERT?
I have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way. Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE? Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!
View Replies !
View Related
Pros: How To Bulk Delete And Bulk Insert?
I have a table containing 8 million records. I need to replace 2 million of these records with a scaled down query that goes something like: SELECT 1, ShareholderID, Assets1 FROM MyTable (Yields appx. 200,000 recods) SELECT 2, ShareholderID, Assets2 FROM MyTable (Yields appx. 200,000 recods) . . . SELECT 10, ShareholderID, Assets1 + Assest2 + Assets3 + ... + Assets9 FROM MyTable (Yields appx. 200,000 recods) Updates and cursors just seem to be too slow. So far I have done the following, but was wondering if anyone could think of a better way. SELECT 6 million records that don't need to be deleted into a #TempTable Use statements above to select into same #TempTable DROP and recreate Original Table SELECT 6 + 2 million records INTO original table. This seems rather convoluted. Is there a better approach? Would it be worth while to dump data to a file and use bcp / Bulk Insert Any comments are appreciated, -Marc
View Replies !
View Related
Error: 0xC002F304 At Bulk Insert Task, Bulk Insert Task: An Error Occurred With The Following Error Message: &&"Cannot Fetch A Row
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table: Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).". Task failed: Bulk Insert Task In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages: BULK INSERT TableName FROM 'C:DataDbTableName.bcp' WITH (DATAFILETYPE='widenative'); What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following: DataFileType: DTSBulkInsert_DataFileType_WideNative RowTerminator: {CR}{LF} Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help. Paul
View Replies !
View Related
IGNORE_DUP_KEY Performance Problem
I have a performance problem using ignore_dup_key='on' on SQL Server 2005 (with or without SP2). The scenario is that we are collecting high volume real time data that contains duplicates. Currently the table contains about 60 million rows (including duplicates) and growing, but about 30% of that is duplicate data. It's envisaged that we will eventually be collecting 20 million rows per day. We currently have an index on the table to assist with data retrieval so I removed all the duplicates and extended this index to cover all the rows and made it unique to prevent duplicates. I did some performance tests that revealed there very little performance hit, if any, from doing this. Here's the index definition: CREATE UNIQUE NONCLUSTERED INDEX [test] ON [dbo].[BILLINGREGISTER] ( [METERPOINT_ID] ASC, [DATETIME] ASC, [METERDATATYPE_ID] ASC, [UNITS_ID] ASC, [ALARM_FLAGS] ASC, [VALUE] ASC ) INCLUDE ( [EXPORTED]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] The data is inserted in batches of about 20-60 rows like this: begin tranaction (using TransactionScope) (read committed isolation level) get data from MSMQ access a couple of tables create batch SQL "insert into Table (...) values (... ) insert into Table (...) values (... ) insert into Table (...) values (... ) insert into Table (...) values (... ) etc " execute batch SQL commit transaction On my hardware this test runs at about 17 batches/second with one client thread, and at about 71 batches/second with 10 client threads. For the test I made sure there were no duplicates. So now I set IGNORE_DUP_KEY = ON and retry the tests. Performance drops to 2.5 batches/second for both 1 and 10 client threads! Why is there this such a massive performance hit? Thanks for any help. Phil Lee
View Replies !
View Related
Unique Index
Just found out that creating a unique index does not create a unique constraint, but creating a unique constraint creates unique index. But effectively they do the same thing.
View Replies !
View Related
Unique Index
if this question is inappropriate here, I apologize (it's at least obliquely related). I have been using ssno as a unique key in a datawarehouse I have been working on because all of the component systems have had it. I now have a database to add where ssno is not available. I have first, last address, city, state,zip and dob. Question is, how to construct a unique identifier from those components. If not unique, then at least usable? Again, if this post is wrong here, I apologize Thanks for any input Walter
View Replies !
View Related
Can't Add Bit Column To Unique Index
This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list. Can anyone shed any light on the problem? Thanks.
View Replies !
View Related
Creating A Unique Index
HiI tried the following from the help file...When you create or modify a unique index, you can set an option toignore duplicate keys. If this option is set and you attempt to createduplicate keys by adding or updating data that affects multiple rows(with the INSERT or UPDATE statement), the row that causes theduplicates is not added or, in the case of an update, discarded.For example, if you try to update "Smith" to "Jones" in a table where"Jones" already exists, you end up with one "Jones" and no "Smith" inthe resulting table. The original "Smith" row is lost because anUPDATE statement is actually a DELETE followed by an INSERT. "Smith"was deleted and the attempt to insert an additional "Jones" failed.The whole transaction cannot be rolled back because the purpose ofthis option is to allow a transaction in spite of the presence ofduplicates.But when I did it the original "Smith" row was not lost.I am doing something wrong or is the help file incorrect.Dan
View Replies !
View Related
Unique Constraint Index
When I add a unique key constraint to column in SQL 6.5 why does it alsocreate an index. e.g. In the table subaccounts I added a unique keyconstraint for the column login and SQL creates an index with the nameUQ_SubAccounts_2__19 (UKC).Does this also mean that there is no need to create an index for thiscolumn?thxMansoor
View Replies !
View Related
UNIQUE INDEX If Not NULL
Hello ! for MS SQL 2000 how can i set an unique index on Serial column but only if Serial IS NOT NULL CREATE UNIQUE INDEX [IX_Product] ON [Product]([Serial]) ON [PRIMARY] i can have 100 rows with a NULL Serial thank you
View Replies !
View Related
Unique Constraint/index
I'm trying to weight the pros and cons of unique constraints and unique indexes. I understand that creating a unique constraint also creates an index. If that is the case, why not just use a unique index? Could someone give me an example of when you would want an unique constraint over an unique indexes Thanks in advance
View Replies !
View Related
Better Performance With Unique Index?
I have read that you get better performance with unique indexes rather than non-unique indexes. I have experimented with this in SQL 2000. I have two identical tables (with about 250000 rows each) with a 12-character unique column. In one table I define it as a regular index and in the other I define it as a unique index. No matter what I try I get identical performance, and the query optimizer shows an identical plan. I even tried clauses such as WHERE 1 < (SELECT COUNT(*) FROM TheTable WHERE key_column = OtherTable.key_column) which should obviously return nothing if TheTable.key_column is unique. However the query still ran a long time no matter if the index is unique or not. I have also tried a unique constraint instead of a unique index and got the same (non)results. Can anyone come up with an example where creating a unique index actually makes a performance difference?
View Replies !
View Related
Double Unique Index
Hello. I have a question. I need to make a double unique index on a table. for example: I have 2 columns, ColumnA and ColumnB. ColumnA can have duplicate values, so is ColumnB, but it should be impossible to have duplicate values on both columns. for example: Row 1: ColumnA = 1, ColumnB = 2 Row2: ColumnA = 1, ColumnB = 2 this shouldn't be possible. Row1: ColumnA = 1, ColumnB = 2 Row2: ColumnA = 1, ColumnB = 3 this should be possible is there any way I can do this? thanks in advance
View Replies !
View Related
Unique Index, SQL 2005
Is there a simple way to find properties of an index for a table? Specifically I am looking for the unique property. I currently have code to to do this, however I feel like there must be some easier way. I am using .net 2005 and SQL 2005 coding in c#. Thanks for any help! Code Snippet _conn.Open(); SqlCommand cmd = new SqlCommand("sp_helpindex", _conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@objname", "TABLENAME")); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(results); foreach (DataRow row in results.Tables[0].Rows) { ArrayList names = new ArrayList(); DataTable dtUnique = new DataTable(); SqlCommand cmd2 = this._conn.CreateCommand(); cmd2.CommandText = String.Format("SELECT INDEXPROPERTY(OBJECT_ID('TABLENAME'), 'INDEXNAME', 'IsUnique') AS isUnique"); dtUnique.Load(cmd2.ExecuteReader()); bool unique = Convert.ToBoolean(dtUnique.Rows[0]["isUnique"]); if (unique) names.Add(tableName); } _conn.Close();
View Replies !
View Related
PK Vs Unique Clustered Index
When not using any enforced relationships in a database (which I know we should, but I like most of you, have inherited sub-optimal design), is there any real difference between using a Primary Key (to which no foreign keys are tied) and using a Unique Clustered index? Thanks for your thoughts
View Replies !
View Related
How To Create Unique Secondary Index ?? HELP
I have created the companyid as Primary Key.How to create a unique secondary index on Company Name. To avoid inserting duplicate records in database with the same companyname. I m creatin database in sql server 2005 with asp.net C# 2005. I know one way is write the query if not exists at the time of insert.But,i want to know is there anyother way to make a unique secondary index for the companyname on the company tablethanxs
View Replies !
View Related
Unique Index On X Columns But Not Primary Key
my table : CREATE TABLE [dbo].[users] ( [ID] [int] NOT NULL , [A1] [nvarchar] (100) NULL , [A2] [nvarchar] (100) NULL , [A3] [nvarchar] (100) NULL ) ON [PRIMARY] i must keep ID columns as primary key ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] but now A1+A2 must be unique how can i do it ? thank you
View Replies !
View Related
Replication- Converts PK To Unique Index
Hi, When I replicate from a 7.0 publication to a 2000 subscription though a 2000 distribution, all the primary keys gets converted to unique clustered index in the subscription. Whereas , when I use 7.0 distribution keys stay keys in the subscription. Can you suggest how can I fix the problem? Is it a bug? Thanks in advance. Hassan
View Replies !
View Related
Error While Droping Unique Index
Hi, I get the following error message while droping unique index. Server: Msg 3723, Level 16, State 5, Line 1 An explicit DROP INDEX is not allowed on index 'dbo.ALEG.IX_ALEG'. It is being used for UNIQUE KEY constraint enforcement. Could you please how the index can be droped. Thanks
View Replies !
View Related
How Do You Determine An Index Is Unique From Sysindexes?
I need to write a script which will respond differently if a table's index forces uniqueness. I am using SQL 7.0 and I am using the [sysindexes] table of my database to examine those indexes. I am using the [id] field from the [sysobjects] table for the table I am examining and I am using that [id] field to find like-valued [id] fields in the [sysindexes] table. My problem is that I can't seem to determine, from an examination of the [sysindexes] table, whether, or not, an index is unique. When adding an index, and checking the unique property of that index, the values that I get in the [status] field of [sysindexes] are different from the values which are supposed to show, at least according to the sysindexes (T-SQL) entry in SQL Server Books Online. For instance, I have a table which combines two text fields which comprise the only key on a table. The unique and clustered boxes are checked in the index setup screen. When I look in the [sysindexes] table, the value in the [status] field is 2113554, which is not a value I see in the books online page. According to my books online page, a unique index should have a value of 2 in the [status] field and a clustered index should have a value of 16 in the [status] field. My assumption is that I should see a value of 18 in that [status] field, not 2113554. I looks like the books online entry might be out-of-date because the field that is labeled [reserved1] in my books online page, is labeled [StatVersion] in my actual [sysindexes] table. That [StatVersion] field looks suspiciously like a Status Version field, possibly indicating that the Status field has undergone some sort of version revision? Is anyone familiar with this stuff? Thanks. Ken
View Replies !
View Related
Question On Primary Key, Unique Index
Question: I have a test table like this CREATE TABLE [dbo].[Test]( [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [addr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ( [name] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] But when I tried to input my data like 'abc', '123 abc' 'abc ','123 abc' SQL server won't recognize 'abc' and 'abc ' is a different value if the last character is a space. Is there a way to make it as a different value? I tried to drop the primary and input the data. When I ran a group by the name column, 'abc' show 2 instead of 1. Seems SQL server is trying to ignore the space at the end too. I also noticed unique index have the same problem too. Please help.
View Replies !
View Related
Partition Switch And Non Unique Index
I have a table partitioned by month. There are no primary key. There is another table with the same structure that I use to load data for the current month. I can perform partition switch with no problem. I created non unique index on integer field in both tables. After this change partition switch does not work anymore. Here is an error that I am getting: 'ALTER TABLE SWITCH' statement failed. The table 'dbo.test' is partitioned while index 'IX_test' is not partitioned." Any help?
View Replies !
View Related
Changing Field For Unique Key Index
SQL 2000 Enterprise Manager won’t let me drop a unique key index and move it to another field. I get an error regarding ‘drop index not allowed because of unique key constraint enforcement. Is there anyway to change fields for unique keys in SQL Enterprise Manager? Thanks, Al
View Replies !
View Related
Questions About Bulk Copy Insert Using 'Memory Based Bulk Copy Operations'
Hi~, Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations. - performance : compared with T-SQL's "BULK INSERT ..." and bcp utility - SQL Server's resource usage : when running memory based bulk copy, server resource's influence - server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after? - row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit - any other guide lines
View Replies !
View Related
Can I Insert/Update Large Text Field To Database Without Bulk Insert?
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View Replies !
View Related
Create Unique Index On Multiple Columns
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example: Row 1 - Column 1 = "cool" Row 1 - Column 3 = 91 Row 3 - Column 1 = "cool" Row 3 - Column 3 = 91 I dont care about one column having duplicate values, I want to protect against Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?
View Replies !
View Related
What's The Difference? Unque Constraint And Unique Index, Etc.?
All,What's the difference between a unique contraint and unique?sementically, if you want a column contain unique values, it is acontraint. And an index is for searching/sort. The questions are:1. Does a unique constraint interally use unique index?2. If Yes to #1, I DO NOT need to create an index for search/sortpurpose, right?3. If Yes to #2, What's better?4. Also for Primary Key column, it is actually a special uniquecontraint. Not need to create index on PK column for searching/sorting,correct?5. Also for FK contraint, no need to create an index forsearching/sorting?ThanksJohn
View Replies !
View Related
|
TRACKER
