SQL Server 2014 :: Get Next Primary Key Without Identity

Jul 31, 2014

I have a table to store int primary keys. Where the value nextID is the value to use.I need to get the value and update it (nextID + 1) before the next person gets it.I thought using tran would work but it doesn't stop a select.How do I get a value (lock the table for the update), update the table (preventing any selects until done) and release the table.

BEGIN TRAN
UPDATE TableIds
SET NextId = NextId + 1
WHERE TableName = 'Users'

WAITFOR DELAY '00:00:20'

SELECT NextId
FROM TableIds
WHERE TableName = 'Users'

COMMIT TRAN

I really want to do this in the reverse order but it doesn't work. But if I do this and have another query to just do a select:

SELECT NextId
FROM TableIds
WHERE TableName = 'Users'

This query never returns. Doesn't the COMMIT TRAN release the lock on the table?

View 9 Replies


ADVERTISEMENT

Identity Fields As Primary Key In Server 7.0

May 13, 1999

Most of the tables in my database are implemented with IDENTITY columns as the primary key.
When an INSERT from my application is attempted, sometimes I get an error returned stating that insert
cannot be done because of duplicate key value. If I try the INSERT again, sometimes it works(??). Of course,
DBCC CHECKIDENT resets the identity value if trying the INSERT again doesn't work. Then sometime a little later, the problem happens again.
Is there anything I can do other than placing into my application code the execution of dbcc checkident
anytime I want to do an insert to prevent the error? By the way, DBCC CHECKDB revealed no problems.

HELP!

View 3 Replies View Related

SQL Server 2012 :: Primary Key And Identity Columns

May 23, 2015

Can a Primary Key column also be a Identity column? The reason I am asking this question is because I have created a table and each time I insert data into the Address Table I am also inserting the AddressID, how do I get the Primary Key (AddressID column) to self generate ID values.

View 9 Replies View Related

SQL Server 2014 :: Composite PK / Surrogate And Sequence Identity

Jun 21, 2015

So, I have some questions about best practice in SQL Server.

1.) I have PK like this (company TINYINT, store TINYINT, action TINYINT, invoice INT, sn SMALLINT). I know JOINS will work faster with surrogate key but I have only couple of JOINS on that table. I use members of PK in WHERE clause mainly, alone and combined for reporting purpose. Is it always better to have surrogate key because they don't have any meaning and context of data laying in current PK.

2.) In my PK from above I have two candidates for using Sequence object. Invoice start with 1 for every (company,store,action) combination. Sn start with 1 for every (company,store,action,invoice) combination. I would like to know can I implement Sequence object here knowing that Sequence don't support PARTITION BY in OVER clause. From what I red it cannot be done via Sequence but I have to ask.Here is data sample for this PK

company store action invoice sn
----------- ----------- ----------- ----------- -----------
1 1 1 2017 1
1 1 1 2018 1
1 1 1 2019 1
1 1 1 2019 2
1 1 1 2019 3
1 1 2 1 1
1 1 2 2 1
1 1 2 2 2
1 1 2 2 3
1 1 2 3 1
1 1 2 3 2
1 1 2 3 3
1 1 2 3 4
1 1 2 3 5

View 7 Replies View Related

SQL Server Admin 2014 :: How To Add Datafile To Mirrored Database In Primary Server

Aug 5, 2015

I need some clarification about adding file in to mirrored dataabse in primary server without downtime and breaking the mirror server.

In our environment we are using monutdisks in both the servers. in primary for ex we have F drive for data files under mount disk 3 in mirror server also we have same drive but in mount drive2.

As per my knowledge if it is same drives we can add the ndf files in the primary that will reflect on mirror. but in current situation i am confusing about mount points with different names.

View 3 Replies View Related

How To Set The Identity Specification For A Primary Key Using A Script, Instead Of SQL Server Management Studio?

May 7, 2008

If I right click on a primary key in SQL Server management studion, I can set the identity specification for a primary keyId so that it is indexible. I do this by setting the properties for "Is Identity", "Identity Increment", and "Identity Seed". This is wonderful, but I have been asked to perform this using a script instead of the SQL server GUI/Sql Server management studio. Is this possible? Does anyone know how to do this? If so, can you show me how?
Ralph

View 1 Replies View Related

SQL Server 2014 :: Insert Dataset Into Two Tables And Grabbing Identity From One For Other

Jan 2, 2015

Ok I think I will need to use a temp table for this and there is no code to share as of yet. Here is the intent.

I need to insert data into two tables (a header and detail table) the Header Table will give me lets say an order number and this order number needs to be placed on the corresponding detail lines in the detail table.

Now if I were inserting a single invoice with one or more detail lines EASY, just set @@Identity to a variable and do a second insert statement.

What is happening is I will be importing a ton of Invoice headers and inserting those into the header table. The details are already in the database across various tables and and I will do that insert based on a select with some joins. As stated I need to get the invoice number from IDENTITY of the header table for each DETAIL insert.

I am assuming the only way to do this is with a loop... Insert one header, get identity; Insert the detail table and include the IDENTITY variable, and repeat.

View 9 Replies View Related

SQL Server 2014 :: Columns List With And Without Identity Column In A Table?

Feb 24, 2015

I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .

Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)

Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)

View 8 Replies View Related

SQL Server 2014 :: 2 Tables Combined Left Join To Primary Key Of A 3 Third Table

Sep 1, 2014

Looking to improve performance of the following code.

It basically generates future days for each dog. So there is a dog table and a day table with every day.

These 2 table cross join and then fill in missing rows. As time moves i will fill in further future dates but will need the initial insert to be a reasonable query.

All columns are covered by index's but the queries at the end take quite a long time. I would hope for index scan to just point out the missing rows especially on the final query.

How to make the last query as fast as possible.

IF OBJECT_ID('dbo.[AllDates]', 'U') IS NOT NULL
DROP TABLE dbo.[AllDates]
CREATE TABLE dbo.[AllDates] (
[Date] date not null PRIMARY KEY
)
;WITH Dates AS

[Code] .....

View 2 Replies View Related

SQL Server Admin 2014 :: Move Text Data From Primary To New Filegroup?

Oct 15, 2015

I need to modify a table to reside on a new filegroup and also point TEXTIMAGE_ON to that filegroup instead of PRIMARY. Apparently in the past, the only way to achieve this via SQL is to create a new table, copy over data, drop the old table and rename the new table to the original name. I found this solution in the SQL Server 2005 forum.

Is there any other way to alter this table in order to point the TEXTIMAGE_ON to new filegroup using SQL Server 2014? We are on Standard edition. The technique I am using is the drop constraint (with move option) and add constraint (to new filegroup) commands. The data and indexes move, but not the text data (it still is in primary filegroup).

View 0 Replies View Related

SQL Server Admin 2014 :: Exit From Not Synchronizing Primary Database In Availability Group

Jan 27, 2015

I Create an availibility Group with a primary and a secondary. (For test)

Then run Planed Failover.

Switched to secondary. That's ok.

After that I update some tables on secondary (That now is primary)

I Run Again Planed Failover on server 2.

Switched. OK

But primary database Get (Not synchronizing) Status.

And in primary I don't have that updates.

How to sync these databases and exit from Not synchronizing.

View 5 Replies View Related

SQL Server Admin 2014 :: Read Only DNS Listener Failover Automation From Primary To Secondary

Mar 23, 2015

I have a 2 node cluster with 2 standalone 2k14 instances having alwayson setup. As per client requirement we have created a client access point with a cname alias in dns to connect to secondary replica. Now, everytime whenerver the roles switch over one has to manually move this resource from the previous secondary node to the new secondary node. This is tedious, and should not be done manually either, so I am looking for a way to automate it so that as soon as the role switches over, the resource group after some time should also switch over to the current secondary.

Env details
xa (current primary)
y2a (secondary)

client access point (resource group): aglreadtest

View 2 Replies View Related

SQL Server Admin 2014 :: Disconnected From Listener In Availability Group (When Primary Failed)

Jun 27, 2015

I setup an availability Group. (Only 2 servers - Primary And secondary) -- 21 , 22

I also define an listener . IP .. 23

1- In First step I connected To Listener (23) And in a while I inserted A record to a table .

While 1=1
insert into Tbl_T1(f1,f2) Values (1,2)

2- in second, I Stop the primary .

- I expected this while whitout disconnect, continue.

3- The while code stopped whit this message :

Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

4- I execute again the script, And it worked in new primary.

My questions :

1- is the listener disconnected between switched primary and secondary ? OR have we data loss between switching?

2- I did some huge update on Primary that fill the Log fiel space. And in last Update I got this error :

Msg 9002, Level 17, State 2, Line 27

The transaction log for database 'Your_DB' is full due to 'LOG_BACKUP'.

Is this (Fill All space) a reason to switch primary? Or not ?

View 2 Replies View Related

SQL Server Admin 2014 :: Primary Filegroup For System Objects / Secondary For Data

Jul 27, 2015

I have been creating databases in SQL 2008 with a primary filegroup for the system objects and a secondary, marked Default, for the data.

We are preparing a migration to SQL 2014, and the administrator is complaining he won't adopt this structure on the new servers because 'there is no benefit' and 'a backup cannot be restored (!?)'.

View 2 Replies View Related

SQL Server 2014 :: Move Data From Database Taking Into Account Identity Keys

Oct 9, 2013

I'm trying to move specific data from three linked tables on a source database to three tables on a destination database by generating dynamic SQL INSERT scripts but am getting stuck on the last set of INSERT statements. I don't think I can use SSIS because the source autonumber fields may already exist on the destination side but I could be wrong.

For simplicity, Table 1 (T1) has one autonumber key of PK1 as well as other fields (A1, B1, etc.).

Table 2 (T2) has one autonumber key of PK2 and a foreign key (FK1) that links back to PK1 as well as other fields (A2, B2, etc.).

Table 3 (T3) has one autonumber key of PK3 and a foreign key (FK2) that links back to PK2 as well as other fields (A3, B3, etc.).

Like this:
T1: PK1, A1, B1, etc.
T2: PK2, FK1, A2, B2, etc.
T3: PK3, FK2, A3, B3, etc.

So, I'm able to query the source database T1 to generate my dynamic SQL INSERT statements that will be run on the destination side. I'm also able to generate my dynamic SQL statements to insert into T2 but when I get to T3 I currently am stuck figuring out how to insert because the destination side is unable to match it's FK2 to the just inserted PK2. It throws the below error.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

View 3 Replies View Related

SQL Server Admin 2014 :: Get Average Of Two Largest Number Amount Three Column For Particular Identity

May 3, 2015

ID A B C AVG
------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------

I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column....

View 9 Replies View Related

SQL Server Admin 2014 :: Re-balancing Blob And Lob Data In Multiple Files On Primary File Group

May 7, 2014

A little background on what I am trying to achieve first. We are moving to Azure virtual machines and we will have 8 disks on the SQL Server box. I am adding more files to the primary file group and each file will go on its own drive. I am then rebalancing data across these files by rebuilding all of the indexes on the tables which is working fine. No problems so far all is good.

I now have an additional problem. If there is a lob or blob column on the table, rebuilding the clustered index and all the non clustered indexes doesn't rebalance the blob or lob data across the disks such as it does with in row data.

I cannot find any articles on rebalancing lob or blob data because all the articles say to move to a new file group. I do not want a new file group, I just want to use the primary file group where the data already resides, and just redistribute it evenly in the same way that I can in row data which is working fine.

One solution I thought about was to BCP data out of the table, truncate the table and then BCP back into the table which I imagine would have the desired effect of distributing the data evenly over the files.

View 2 Replies View Related

SQL Server Admin 2014 :: Few Record Loss In Table Primary Key Where Same Records Exists In Foreign Key Table?

Jun 21, 2015

Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.

View 3 Replies View Related

Identity And Primary Key

Nov 15, 2004

I confuse about Identity and primary key, what is the different between them. One table can have no primary key. Right? Thanks.

View 14 Replies View Related

Primary Key And Identity

Feb 1, 2006

Can someone tell me the difference between making a column primary key column vs. making it identity column?



thanks

View 1 Replies View Related

Primary Key Vs Identity Column

Jan 22, 2007

What is the different between Primary key and Identity Column? and when should I them?
(Im just a beginner with databases...)

View 1 Replies View Related

URGENT!!! Primary Key As Identity

Mar 17, 2007

I orginally had my project ID specified as an Int and the properties set as identity specification that would automatically fill in the ID field however I have now changed it as the ID needs to be specified by the user but now when i update a project the ID isnt seen as the identity so whatever i do affects other records not just the one i select.
 it is now defined as an nvarchar but i dont know how to set that as the identity so that each record can be edited seperately, can someone please help this is really urgent!!
 Im using visual web developer express with sql server, please please help!

View 3 Replies View Related

Setting Identity, Primary Key

Jan 21, 2008

I want to crete temporary table with this coammnd "CREATE TABLE Temp (ID int, name varchar(50))". I would like to know which command is used for setting primary key and identity on ID field. Thankyou in advance.

View 2 Replies View Related

Help Needed With Primary Key And Identity

Jan 8, 2004

I have some code in my ASP.NET page which uses a SQL 2000 Database that was created before creating the ASP Page. The problem I'm having is using an insert statement such as the following example from the DATAGRID example on the Matrix Product. I want the option to create new rows but my Primary Key doesn't allow Nulls and when I hard code a number in the first field of my table for my ID...it's not automatically generated. I've looked through this forum but I'm having some problems understanding what others have done with Identity or GUID's...etc....:

Sub AddNew_Click(Sender As Object, E As EventArgs)

' add a new row to the end of the data, and set editing mode 'on'

CheckIsEditing("")

If Not isEditing = True Then

' set the flag so we know to do an insert at Update time
AddingNew = True

' add new row to the end of the dataset after binding

' first get the data
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds)

' add a new blank row to the end of the data
Dim rowValues As Object() = {"", "", ""}
ds.Tables(0).Rows.Add(rowValues)

' figure out the EditItemIndex, last record on last page
Dim recordCount As Integer = ds.Tables(0).Rows.Count

If recordCount > 1 Then

recordCount -= 1
DataGrid1.CurrentPageIndex = recordCount DataGrid1.PageSize
DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize

End If

' databind
DataGrid1.DataSource = ds
DataGrid1.DataBind()

End If


End Sub

View 2 Replies View Related

Primary Key VS Identity Column

Apr 5, 2000

Can 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!!!!

View 2 Replies View Related

How To Update None Identity Primary Key Value

Aug 29, 2014

I am working on a project to consolidate 3 distinct buildings name code(6 digit character called BUILDING_CODE Primary Key) into one building code. This is an effort to merge/move everyone who is currently coded under the three building codes into one building code. We currently have this column defined on 5 tables as primary key (none identity) in SQL Server 2005.

Out of the five tables one has two store procedure dependencies.

What would be the best practice to update the six digit BULDING_CODE column Primary Key value without causing any issues?

View 1 Replies View Related

Using An Identity Field As Primary Key

Jul 20, 2005

I want to use the Identity field (increment 1,1) as a primary key andhave a unique constraint on my other field which is of type char.I am worried that related data in other tables may lose referntialintegrity if records in the ID table get messed up and need to bere-entered.Can you please advice on best way to do this. I definitely need anumeric id field because it makes the joins and queries so muchfaster.

View 9 Replies View Related

Using Identity To Generate A Primary Key

Jul 20, 2005

We are converting a legacy visual foxpro system to use a SQL back-end.A number of (existing DBF) tables currently have a zero-filled primarykey eg. '000255' which is just an auto-incrementing key - but alwaysstored as a char field with leading zeros.For backward compatibility we are considering retaining this primarykey and using an identity field to auto-generate the next value, thenconvert the new identity value into the new primary key. So ifidentity is 256 then the key field will be assigned '000256'.Now the problem with this is that the primary key must be non-null andunique so must be given a value in the INSERT statement. But, theidentity value isn't available (I presume) until after the INSERTstatement has executed.Is this a "don't go there" kind of problem?ThanksAndrew GrandisonSA Department of HealthAdelaide, South Australia

View 2 Replies View Related

Designating An Identity Column When You Already Have Primary Key

Jan 13, 2007

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

View 7 Replies View Related

What Is Diffecence Indexes, Primary Key And Identity.

Mar 12, 2007

What is diffecence indexes, primary key and identity.

View 1 Replies View Related

How To Insert Primary Keys Without Using Identity

Nov 17, 2003

I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?

thanks for your time,

Andre

View 1 Replies View Related

Trying To Find Non Identity Primary Keys

Jan 26, 2006

This ain't working

SELECT T.TABLE_NAME,C.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
WHERE OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME),
'TableHasIdentity') = 0
AND T.TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(C.COLUMN_NAME),'IsPrimary Key') = 1
ORDER BY T.TABLE_NAME,C.COLUMN_NAME

This is giving me bogus results...

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE OBJECTPROPERTY(OBJECT_ID(COLUMN_NAME),'IsPrimaryKe y') = 1

I have PK's all over the place. What gives? Too many cocktails with lunch?

View 1 Replies View Related

Identity Column Primary Key Vs Custom Key

May 22, 2008

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.?

View 20 Replies View Related







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