New answers tagged

0

Finally I managed to talk about it with the DBA. He thought the table will have many many columns (because of a similar table) and all will be needed in the query and in that case page splits will be much common and painful. But since the table is in fact small and all the queries will be based only on the token, he agreed that making the token the ...


9

Some alternatives to adding the auto-increment column via the IDENTITY() function as suggested by @Shaneis are: Create the table explicitly using CREATE TABLE instead of using SELECT INTO. I much prefer this method as it gives you complete control over the Table that is being created, such as including the auto-increment column and specifying that it be ...


5

Sounds like you are looking for the IDENTITY() function: Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. USE [MYDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[spSchedula_Scadenzario] as begin drop table MYDB.dbo.tmpTable select -- Create new ...


2

If the Database is on the same Instance, then you don't need to use OPENQUERY. But even if the Database is on another Instance, then you still shouldn't need OPENQUERY (as @AndriyM reminded me of in a comment on this Answer): since the query uses JOINs to system Views instead of using the built-in meta-data functions (e.g. OBJECT_ID), fully qualifying the ...


13

SELECT INTO has two phases (not visible in execution plans). First, it creates a table that matches the metadata of the query used to create it. This happens in a system transaction, so the (empty) created table will continue to exist even if the SELECT INTO is wrapped in a user transaction that is rolled back. At the end of the first phase, we have an ...


8

If this is the only transaction on that table, then yes. However, another transaction could also do an insert at the same time, and be somewhere in between 1-100, unless you use SERIALIZABLE isolation. From IDENTITY (Property) (Transact-SQL) in the product documentation: The identity property on a column does not guarantee the following: ...


0

There is not guarantee if you execute this code in parallel with other transactions. You have the option to use OUTPUT INSERTED.ColumnId to get the current inserted row id: --This is your real table, for this purpouses I used a TABLE variable DECLARE @TheRealTable TABLE( [Id] [int] IDENTITY(1, 1) NOT NULL, [Name] [varchar](50) ...


2

There's no guarantee unless you hold an exclusive lock on the table until all inserts commit or set your isolation level to SERIALIZABLE. That can cause serious performance and scalability problems with your app so don't do it. Also no guarantees if you do bulk insert. If you can provide your functional requirements instead of a very narrowly defined ...


5

It looks like the comments may have answered this for you, but so there's an actual answer: You cannot have two columns in one table that both have an IDENTITY(n,m) specification. The most sensible approach for your requirement is probably: your normal always-incrementing identity column should have the IDENTITY specification use a SEQUENCE (see here for ...


7

Since you are indicating insert performance is the primary concern, I'd take the recommendation of the DBA and make the clustering key the identity column since it is a unique, monotonically ascending number, which is guaranteed to (almost) never cause page-splits on the table. Also, don't store the GUID in an NVARCHAR(100) column, use the data type ...



Top 50 recent answers are included