How To Keep DataTable's Primary Index In Sync With Database?

May 12, 2008

I'm working with a legacy database whose structure I cannot change.  I'm building a web-based editor for one of the DB's tables.  This table has a Primary Key called "Master_Idx" that is an Identity (autonumber) field.
To start, I query the DB and populate a data table which is cached.  This is used to "feed" the web form.  Any additions, changes, and deletions are reflected in this cached table.
The user then has the flexibility to press Save at any time, which ensures that the database is modified accordingly.
Here's my question:
When new records are added, I assign them a Master_Idx value, the first of which was calculated to be 1 + the Max(Master_Idx) value when the editing session started.  But when I insert these records back into the database it's possible that another user might have also been doing similar editing (of different records from the same table), so there's no guarantee that the Master_Idx values in the data table will be identical to those in the DB.  When I insert a record from the cached table back into the DB, what technique can I use to check what value was assigned to the Master_Idx?  If it's not the same as in the cached table then I need to update it locally in a few places.

I hope all of this makes sense but if it doesn't then let me know and I'll explain it in another way.

Robert W.
 

View 2 Replies


ADVERTISEMENT

Replication :: Non-Clustered Index Not Sync To Subscriber

Aug 7, 2015

Can I enable the Non - Clustered Index option set to 'TRUE' at publisher does it affect anything?

Does the Non clustered Indexes are automatically transfer to the subscriber database?

Does it affects database performance when I enable this option!

View 4 Replies View Related

Filling A DataTable From SqlQuery : If SqlQuery Returns Null Values Problem Ocurrs With DataTable

Sep 3, 2007

Filling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable.
Is it possible using DataTable with some null values in it?
Thanks

View 2 Replies View Related

Updating SQL Database - Using DataTable

Jan 8, 2008

I am quite new to ASP.net 2.0. I have had plenty of experience using ADO.net in standard windows applications.
In my app I am opening a connection to an SQL database and I am creating a DataTable without a DataSet:
Shared m_cnADONetConnection As New System.Data.SqlClient.SqlConnection
Shared m_daDataAdapter As System.Data.SqlClient.SqlDataAdapter
Shared m_rowPosition As Integer
Shared m_dtContacts As New System.Data.DataTable
I am initializing everything and filling my DataTable when the Page first Loads if it isnt a postback.Protected Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Form1.Load
If Not Page.IsPostBack Then
m_rowPosition = 0
m_cnADONetConnection.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:First ASP DatabaseApp_DataMyFirstDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
m_cnADONetConnection.Open()m_daDataAdapter = New System.Data.SqlClient.SqlDataAdapter("Select * From Books", m_cnADONetConnection)m_cbRefillCommand = New System.Data.SqlClient.SqlCommand
m_daDataAdapter.Fill(m_dtContacts)
Me.ShowCurrentRecord()
End IfEnd Sub
The Me.ShowCurrentRecord Sub assigns the values of the current record(row) in the DataTable via (m_rowPosition) to TextBox controls on the form:
I also have record navigation buttons on my form: << < > >> Moving me from record to record (row to row) by incrementing or decrementing m_rowPosition
All is good! I am able navigate the DataTable and the textboxes change their text properties accordingly from record to record.
The only other control on my form is a button which I'm coding its click event to save changes that I made to the current row (record) by changing the values in the textboxes then clicking save.
 Here is my code:Protected Sub ButtonSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonSave.Click
If m_dtContacts.Rows.Count <> 0 Then
m_dtContacts.Rows(m_rowPosition).BeginEdit()m_dtContacts.Rows(m_rowPosition)("Title") = TBTitle.Text
m_dtContacts.Rows(m_rowPosition)("Author") = TBAuthor.Textm_dtContacts.Rows(m_rowPosition)("YearPublished") = TBYearPublished.Text
m_dtContacts.Rows(m_rowPosition)("Price") = TBPrice.Textm_dtContacts.Rows(m_rowPosition)("LastReadOn") = TBLastReadOn.Textm_dtContacts.Rows(m_rowPosition)("PageCount") = TBPageCount.Text
m_dtContacts.Rows(m_rowPosition).AcceptChanges()
m_dtContacts.Rows(m_rowPosition).EndEdit()
m_dtContacts.AcceptChanges()
m_daDataAdapter.Update(m_dtContacts)
End Sub
After I click save I can navigate through my records and back to the one I just changed and updated and all is well. The changes were made in the table.
However, when I close the page and exit out of Visual Web Developer and reopen the database: THE CHANGES WERENT UPDATED!!!
 This worked all the time in VB2005.net when developing a standard windows app.
Can I use the same approach I was using in my code above or am I missing something. 
 
I have read and searched all over and what I'm thinking is that my UpdateCommand, InsertCommand, DeleteCommand, SelectCommand are empty.
Do I have to do it this way?
 
 
 
 
 
 
 

View 1 Replies View Related

Updating DataTable In DataBase Using SqlDataAdapter

Sep 4, 2007

Hi GuysI am facing a problem while updating a DataTable to database using sqldataadapter.In my application I am fetching a dataTable from Database and filling values into textboxes on the UI.User has given facility to change or add new texboxes (new row) on the fly .(Textboxes on the UI are like in a row(tr) having two textboxes in each row.)I am again then converting a new (empty) datatable from scrap and filling its rows with the value of textboxes on submit button event.The datatable which I have created has the same schema as the database table.Now what I want here is that changed value should be reflected to the already existed rows in database and only new rows should be  inserted.I am using a SQLDataAdapter having two sqlcommands , one for update with update procedure name and parameter mapping and another for insert with parameter mapping.But SqlDataAdapter is inserting fresh new rows all the time in the database table not updating the older one.Please help me in the matterThanks & RegardsVishal Sharma 

View 2 Replies View Related

DataTable And Autoincremental Database Fields

Sep 16, 2007

Hi,
I got a problem regarding autoincremental Database Fields and DataTables. Using the technique to map the DataBase to an *.xsd file and trying to insert a Row into the table always results in this kind of error:Cannot insert explicit value for identity column in table 'Cars' when IDENTITY_INSERT is set to OFF.
This only happens when I set CarIDs properties AutoIncrement = true, AutoIncrementSeed = -1, AutoIncrementStep = -1. Having AutoIncrement = false, results in this error:Column 'CarID' does not allow nulls.
Do I missunderstand something, or do I need to change some parts? I would be glad if someone could help me and figure out what I am doing wrong.Thanks a lot have a nice weekend.Regards Johannes 

View 1 Replies View Related

Not Able To Drop Database After Opening Datatable

May 3, 2005

Hi everybody,
I am using VB Webforms as my front end and MSDE 2000 as my back end.  I am not able to drop a database after executing the following vb code on that database:
        Dim vConnection As New SqlConnection(vConnectionString)        Dim vAdapter As New SqlDataAdapter("SELECT party_type_code, party_type_name, party_type_imported" & _                                         " FROM Party_Type" & _                                         " ORDER BY party_type_name", vConnection)        Dim vDataTable As New DataTable()        vAdapter.Fill(vDataTable)        vAdapter.Dispose()        vConnection.Dispose()
On trying to drop the database using the SQL Command "DROP DATABASE PPCABCD2005", I get the error message:Cannot drop the database 'PPCABCD2005' because it is currently in use ...However, if I don't execute the above code, I am able to drop it.  Why is it so?  What is the solution to this problem?

View 2 Replies View Related

Bulk Insert A DataTable Into SQL DataBase

Nov 18, 2005

Hi All!!                I need to export a whole datatable to the database, through bulk insert or any other method..Can any body help me in that??Yes, col's in the database table and datatable are same!!Thx,Regards,Nilee.. 

View 4 Replies View Related

Index With Primary Key

Jun 1, 2008

Hello there,I got a problem when I'm trying to Index a table with PrimaryKeyMy code so far:1    'Create my table2    nonqueryCommand.CommandText = "CREATE TABLE UpdateHistory (id integer IDENTITY(1,1) NOT NULL, version varchar(50) NOT NULL)"3    Console.WriteLine(nonqueryCommand.CommandText)4    Session("Tables") = Session("Tables") + "Number of Rows Affected with table UpdateHistory is: " + nonqueryCommand.ExecuteNonQuery().ToString + "<br />"5    6    'Set column id to Primary Key7    nonqueryCommand.CommandText = "CREATE INDEX idxid ON UpdateHistory (id) With PRIMARY"8    Console.WriteLine(nonqueryCommand.CommandText)9    Session("Tables") = Session("Tables") + "PrimaryKEY - Number of Rows Affected with table UpdateHistory is: " + nonqueryCommand.ExecuteNonQuery().ToString + "<br />"
I do get this error:System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'PRIMARY'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at updates_100_2.Page_Init(Object sender, EventArgs e) in http://server//admin/NewSystem/1.0.0/2.aspx.vb:line 163Note: Line 163 is 9 here..
Anyone who can see what I'm doing wrong?

View 1 Replies View Related

Primary Key Index?

May 17, 1999

Running SQL 6.5, using ActiveX controls(ADO and Datagrid),NT 4.0. All SP's
are current.

I lose the ability to get distinct values with a client-side cursor
after I set up a primary key. Select distinct works perfect before the
primary key creates an index. Any ideas?

View 1 Replies View Related

Index And Primary Key

Dec 9, 2004

By defining a numeric field in table as primary key, will the table be indexed on that particular field?

View 14 Replies View Related

Primary Key Index

Apr 1, 2004

Hi,

I have a table in my SQL 2000 database called utContact, this has a primary called ContactID (int, idenitity), by default this should have a clustered index on it, but when I go to view the indexes on the table it shows the primary key index as non clustered.

How can this be?

SQL server also will not let me change the index to clustered, but I need to as this is causing a lot of table scans in query execution plans.

Any help or advice on this matter would be greatly appreciated.

Cheers

View 7 Replies View Related

How Long Need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?

Dec 21, 2005

I am try to start with SQL BROKER service,

When I lunch from sql Management studio the following query, this don't finish never.

ALTER DATATABLE dbname SET ENABLE_BROKER

 

Where I am mistaking ?

 

View 11 Replies View Related

Change Primary Index Value

Oct 28, 2006

Is there any way to change the value of a primary key value?

View 10 Replies View Related

Primary Key With Cluster Index

Oct 9, 2007

Hi All,


One of my client having 1 million(nearly) records in a table.
I defined the table as below
1) Created table with one col(we can name it as "ID") having IDENTITY
2) Using "alter table", I created CLUSTERED PRIMARY KEY Constraint on Same field (ID)
3) The Primary key having 2 ref with another 2 tables


Now the issue is when we create or define a primary key (With Clustered Option) automatically cluster Index will be created on defined table

As such table having huge data whenever any updation or insertion against that particular table taking huge amount of time, because the cluster Index trying re-paging whole data. Because of re-paging each and every time "Transaction Log also growing in huge" (database is in full recovery mode and client wants in same mode only)
Data partitioning not posible because whole data related and current live data


I tried following options with vain

1) To Clear transaction log I suggested to take regular log backup's
2) I tried to drop cluster index and tried to implement non clustered index
Drop and re-create index is take taking huge amount of time
Even in this process I have to Re_Index remaining Index's also



Pls give me any other solution or suggestion in this regard

with Thanks & Regards
Bhaskara

View 8 Replies View Related

Help Me Please Error Index - Primary Key

Apr 9, 2008

'PXQ_2' table
- Unable to delete index 'PK_PXQ'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]I/O error (bad page ID) detected during read at offset 0x0000000102e000 in file 'D:MSSQL2000MSSQLxxx.mdf'.

I can't drop Primary Key. How to fix this problem?

Hardware(HDD) no error. I test oke?

Please help me.





-= MIB =-

View 7 Replies View Related

Primary Key Without Clustered Index

Jan 28, 2008

Dear All,
i've read one article that with some option, we can avoid creating clustered index on the primary key column. is ti possible?

how can we create a primary key without allowing sql server to create automaticaly a clustered index?

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 1 Replies View Related

Primary Key With Non-clustered Index

Dec 11, 2007

Dear all,


I want to keep certain archive data in certain tables. One such table is currently about 190 GB in size. It has a primary key with clustered index and three non-clustered indexes. The type of queries fired are strictly selects (daily) and inserts (only monthly).

Question: Is it advisable to have a non-clustered index on the primary key column?.....I am finding that the insert performance is getting hurt due to presence of clustered index on such a large table (190 GB).

Let me know your views.


Regards,

Chetan

View 3 Replies View Related

Rda Dropping Primary Key Index

May 2, 2007

I am using RDA to download a tables

I use TrackingOffWithIndexes



the issue is that when I download a small set of data the primary key index is there



when I download a larger set. the index gets dropped (no error) just slowness when querying the table



the max database size is set to 1024mb and the temp database size is set to 1024mb

the actual db size (with the missing index) is 262 mb... lot's of room left!



when I try to create the index manually after the download in sql server management studio I get this error:



Not enough storage is available to complete this operation

I am using 2GB sd cards and nothing else is on the card.



any ideas?



Regards,



eric [at] westgen com

View 14 Replies View Related

Index Statistics And A Primary Key

Sep 5, 2007

Hi

I have a question regarding updating statistics for a primary key.

Background: An update statistics with fullscan is sometimes taking 30 minutes - the table is 80 million rows, with only 4 columns. The table is truncated, and then 80 million rows inserted all in one go.

Now why the update stats is taking that long is another question (I have no idea - any thoughts?), but my question is; Since you can't disable the "not automatically recompute statistics" option for a primary key, and you would think it would be imperitive for the stats to be kept up to date for a PK for inserts.... does this mean the stats would be kept up to date? and an update stat with fullscan isn't required?

Hope someone can help

Thanks
James

View 1 Replies View Related

Creating Primary INDEX Problem

May 30, 2000

I created table and also I define the primary key its okay

but when I generate the SQL script for that table its not
creating the primary key

CREATE TABLE [dbo].[table1] (
[emp_id] [int] NOT NULL ,
[emp_name] [char] (25) NULL ,
[emp_address] [nvarchar] (50) NULL
) ON [PRIMARY]
GO


PS: I want to use emp_id as primary key but its not defined in the sql script

Thankx a lot

View 3 Replies View Related

Sysindexes - How To Distinguish Primary Key Index

Apr 22, 2003

indid =1 works, is this the correct way

View 3 Replies View Related

Unique Index On X Columns But Not Primary Key

Feb 23, 2006

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 5 Replies View Related

Create Table + Index + Primary

Dec 17, 2006

for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]


CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]

and that one

CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]


ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE


thank you

View 6 Replies View Related

SQL Server Takes Index Instead Of Primary Key

Aug 10, 2007

Hallo,

Following scenario:


Table1 with column: Year char(4), Quarter char(1), ID decimal(10,0), Hits int
PK_table1 on Year,Quarter,Hits

If i do a "Select Year From table1 group by Year", the executionplan looks like

select year from dbo.table1 group by year110NULLNULL1NULL4NULLNULLNULL86,21644NULLNULLSELECT0NULL
|--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))121Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL406E-061186,21644[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01
|--Parallelism(Gather Streams, ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))132ParallelismGather StreamsORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL800,028547491186,21643[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))143Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL803,6248431186,18788[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Clustered Index Scan(OBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD)154Clustered Index ScanClustered Index ScanOBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD[Testdb].[dbo].[Table1].[YEAR]1,449936E+0774,588317,9747291182,56304[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11


Now, with another index IX_Hits on hits and the same sql query, sql server now takes IX_Hits instead of PK_table1. And, it takes more time. Any idea why?


select Year from dbo.table1 group by Year110NULLNULL1NULL4NULLNULLNULL85,54985NULLNULLSELECT0NULL
|--Sort(DISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))121SortDistinct SortDISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL40,011261260,0001374511185,54985[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01
|--Parallelism(Gather Streams)132ParallelismGather StreamsNULLNULL800,028507491185,53845[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Hash Match(Partial Aggregate, HASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR]))143Hash MatchPartial AggregateHASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR])NULL8049,63581185,50995[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Index Scan(OBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits]))154Index ScanIndex ScanOBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits])[Testdb].[dbo].[Table1].[YEAR]1,449936E+0727,899427,9747291135,87415[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11



Thank You!!

View 6 Replies View Related

Is Index Created On Primary Or Secondary Key?

Apr 15, 2015

Is index created on only primary key or it can be created on secondary key ?

View 2 Replies View Related

Clustered Index On Composite Primary Key

Jul 23, 2005

Is that possible on SQL Server 2000 and onwards?

View 1 Replies View Related

Creating A Primary Key As A Non Clustered Index

Jul 18, 2007

Hi,



I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO


CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO



The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?


Thanks

Nitin

View 3 Replies View Related

Question On Primary Key, Unique Index

Oct 8, 2007



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 5 Replies View Related

Dropping Clustered Index Associated With Primary Key.

Aug 24, 2007

Hi all,


I have a huge table with million of rows, which has ONE Clustered index associated with the PRIMARY KEY, and there are some NON_Clustered indexes.


So,now i decided that, i dont need any more indexes ( not even one) on that table, but i need to maintain primary key on that table.


(a) So, how can i accomplish this (i.e.) having primay key but not having indexes on the table.


Thanks.

View 6 Replies View Related

Transact SQL :: Difference Between Index And Primary Key

Aug 10, 2015

What is the difference between the Index and the Primary Key?

View 14 Replies View Related

How To Return Primary Unique Index Key On Insert

May 27, 2002

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 6 Replies View Related

SQL 2012 :: How To Set Primary Key And Index On Live Production Databases

Mar 20, 2014

just i see a database and a table 'tbl_OutBox_MT' where there is now primary key and have index (non unique, non cluster). and it store almost 3000000 data per everyday. and wipe out data from their and archive all data to other location and broadcast this table 'tbl_OutBox_MT' by mobile operator everyday from morning to evening. but when it perform broadcast it to mobile operator it takes huge time. because this table gather data from different sources (tables) by using complex query and INSER INTO statement and insert into this table.

I need to perform first, my observation is there is no primary key. when i run any complex query into this table it takes huge time and sometimes shows transaction deadlock error.

CREATE TABLE [dbo].[tbl_OutBox_MT](
[TRAN_ID] [varchar](36) NOT NULL,
[OUT_MSG_ID] [int] IDENTITY(1,1) NOT NULL,
[OUT_MSG_ID_TELCO] AS (CONVERT([bigint],((((CONVERT([varchar](4),datepart(year,[PROCESS_TIME]),(0))+case len(CONVERT([varchar](2),datepart(month,[PROCESS_TIME]),(0))) when (1) then '0' else '' end)

[code]....

View 1 Replies View Related







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