I have a  table created with the following T-SQL:  CREATE TABLE [dbo].[TableName](     [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED  (     [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]   I took a look at this table today and noticed the id column was skipping values:     1,   2,   3,   4,   5,   6,   7,   8,   9,   10,   11,   12,   13,   14,   15,   16,   17,   18,   19,   20,   21,   22,   23,   24,   25,   26,   27,   1027,   1028,   1029,   1030,   1031,   1032,   1033,   1034,   1035,   2034,   2035,   3034   I'm not worried about this or anything (my application doesn't care what the primary key is), I was just curious about what might cause this to occur. It does seem there's a sort of pattern, of jumping 1000 every time a "jump" happens.  My application using entity framework to insert these rows, if that makes any difference.
44606
I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script:  CREATE TABLE [dbo].[PONumbers] (   [PONumberPK] [int] IDENTITY(1,1) NOT NULL,   [NewPONo] [bit] NOT NULL,   [DateInserted] [datetime] NOT NULL DEFAULT GETDATE(),   CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED ([PONumberPK] ASC)     );   And a stored procedure created using this script:  CREATE PROCEDURE [dbo].[GetPONumber]  AS BEGIN     SET NOCOUNT ON;      INSERT INTO [dbo].[PONumbers]([NewPONo]) VALUES(1);     SELECT SCOPE_IDENTITY() AS PONumber; END   At the time of creation, this works fine. When the stored procedure runs, it starts at the desired number and increments by 1.  The strange thing is that, if I shut down or hibernate my computer, then the next time the procedure runs, the sequence has advanced by almost 1000.   See results below:    You can see that the number jumped from 8 to 1002!   Why is this happening?  How do I ensure that numbers aren't skipped like that?  All I need is for SQL to generate numbers that are:   a) Guaranteed unique.  b) increment by the desired amount.    I admit I'm not a SQL expert. Do I misunderstand what SCOPE_IDENTITY() does?  Should I be using a different approach? I looked into sequences in SQL 2012+, but Microsoft says that they are not guaranteed to be unique by default.
36370
This is probably a simple answer but I can't find it.  I have a table with a column of integers and I want to ensure that when a row is inserted that the value in this column is greater than zero.  I could do this on the code side but thought it would be best to enforce it on the table.  Thanks!  I was in error with my last comment all is good now.
69888
I have a custom object, with a Text Name (ID) field. When I upsert two records with identical Name/ID, but otherwise different values, in two separate upsert statement, one record results, as expected.  When I create a list, add these records to the list, and then upsert the list, I get a DUPLICATE_VALUE error. What gives? By definition, upsert should not do this, should it? Even worse: it doesn't matter whether a record with the identical Name already exists or not, the upsert will fail in both scenarios.  By the way: by default upsert a does not use the Name/ID field as identifying/unique field, as you might expect. You need to do upsert a Name  Complete code:  List&lt;Flight__c&gt; flights = new List&lt;Flight__c&gt;(); Flight__c f1 = new Flight__c(); f1.Name = 'ABC123'; f1.Flight_Number__c = 'ABC'; flights.add(f1);  Flight__c f2 = new Flight__c(); f2.Name = 'ABC123'; f2.Flight_Number__c = '123'; flights.add(f2);  upsert flights Name;      Error message: Line: 12, Column: 1 System.DmlException: Upsert failed.   First exception on row 0; first error: DUPLICATE_VALUE, Duplicate   external id specified: ABC123: [Name]   Name being the standard identifying field of a custom object. You cannot edit the properties of this field, e.g. making it unique or not unique.
84603
Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed?  Reason I ask is because in MS SQL Server I can create an index on this ID, which as I stated is my primary key.  Edit: an additional question - will it do any harm to additionally index the primary key?
54151
I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no syntax errors as it displays the msg that says "Alter Table [ParentTable] Drop Constraint FK_Name"... for a total of 23 keys, but yet when I refresh the Server or the DB, and Tables, I still see them when I expand the Keys node for each table? so why its not dropping them? Also, do I need to convert it to a permanent table or put the script in a stored procedure in case if I want to run it again for testing purposes? Please advice.  I used the following script from "Swastik Mishra" posted by "Pinal Dave"        SET NOCOUNT ON DECLARE @table TABLE( RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200), PrimaryKeyConstraintName NVARCHAR(200), PrimaryKeyConstraintTableSchema NVARCHAR(200), PrimaryKeyConstraintTableName NVARCHAR(200), PrimaryKeyConstraintColumnName NVARCHAR(200) ) INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema,     ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName) SELECT U.CONSTRAINT_NAME, U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY' UPDATE @table SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA, PrimaryKeyConstraintTableName = TABLE_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintColumnName = COLUMN_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME --SELECT * FROM @table --DROP CONSTRAINT: SELECT ' ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +      ForeignKeyConstraintTableName + '] DROP CONSTRAINT ' + ForeignKeyConstraintName + '  GO’ FROM @table –ADD CONSTRAINT: SELECT ‘ ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +   ForeignKeyConstraintTableName + '] ADD CONSTRAINT ‘ + ForeignKeyConstraintName + ‘ FOREIGN KEY(‘ +   ForeignKeyConstraintColumnName + ‘) REFERENCES [' + PrimaryKeyConstraintTableSchema + ']. [' + PrimaryKeyConstraintTableName + '](‘ + PrimaryKeyConstraintColumnName + ‘)  GO’ FROM @table GO
61514
(Follow up from this post: )    PostgreSQL 9.5.  I can't divulge full details, but table has 22 columns and 5 indexes:   primary key ('pk'), text (btree) another text (btree) a timestamp with time zone (btree) a tsvector (gin) my latest one, a bigint (btree)   (From the previous post you know that I was trying to avoid creating this additional column, and just use an expression index--adding two integer columns together--without success.  The bigint column here probably could have been just 'integer' but I made a mistake creating it; it took about an hour to add the column, populate it, and reindex, so I'm hoping this isn't relevant but mentioning it just in case it is.)  All are btree except the tsvector.  The following queries all take only 12ms and use just an Index Scan:   SELECT pk FROM table ORDER BY pk DESC LIMIT 10 SELECT pk FROM table ORDER BY text_column DESC LIMIT 10 SELECT pk FROM table ORDER BY timestamp_column DESC LIMIT 10   But if I try to use my new bigint index for ORDER BY:  SELECT pk FROM table ORDER BY bigint_column DESC LIMIT 10  ...it takes 2.7s and uses Limit -&gt; Sort -&gt; Seq Scan.  My "cheating" method is the closest I seem to be able to get to making use of the index:  SELECT pk FROM table WHERE bigint_column &gt; 1000000 ORDER BY bigint_column DESC LIMIT 10   This takes 12ms and uses Limit -&gt; Sort -&gt; Bitmap Heap Scan (bigint_column &gt; 1000000) -&gt; Bitmap Index Scan (bigint_column &gt; 1000000).  This is after a VACUUM ANALYZE after adding the index.  I thought it strange that my expression index was not being used in the other question.  Now it's just a plain old column (I haven't even added what would be the necessary triggers to actually go this route.)  Why isn't my newest index being used, when the other three work "just fine"?  (As pointed out in the comments at  , an Index-Only Scan would be even better.  I fail to see why all of these queries wouldn't use at least an Index Scan, let alone an Index-Only Scan, instead of a full Seq Scan.)  The index definition has DESC NULLS LAST (although it's a non-nullable column.)
103055
Please look at this code:  create table #t1(   id int identity (1,1),   val varchar(10) );   insert into #t1 values ('a'); insert into #t1 values ('b'); insert into #t1 values ('c'); insert into #t1 values ('d');   Now, whenever you execute this  select *,      ( select top 1 val from #t1 order by NEWID()) rnd  from #t1 order by 1;   you will get a result with where all rows have the same random value. e.g.  id          val        rnd ----------- ---------- ---------- 1           a          b 2           b          b 3           c          b 4           d          b   I know a way using a cursor to loop throw the rows and get different random values, but that is not performant.   A clever solution to this is   select t1.id, t1.val, t2.val from #t1 t1     join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on  t1.id = t2.lfd    But I simplified the query. The real query looks more like  select *,      ( select top 1 val from t2 where t2.x &lt;&gt; t1.y order by NEWID()) rnd  from t1 order by 1;   and the simple solution doesn't fit. I'm looking for a way to force repeated evaluation of  ( select top 1 val from #t1 order by NEWID()) rnd    without the use of cursors.  Edit: Wanted output:  perhaps 1 call  id          val        rnd ----------- ---------- ---------- 1           a          c 2           b          c 3           c          b 4           d          a   and a second call  id          val        rnd ----------- ---------- ---------- 1           a          a 2           b          d 3           c          d 4           d          b   The value for each row just should be a random value independent from the other rows  Here is the cursor version of the code:  CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));  DECLARE @id INT DECLARE @val VARCHAR(10) DECLARE c CURSOR FOR SELECT id, val FROM #t1 OPEN c FETCH NEXT FROM c INTO @id, @val WHILE @@FETCH_STATUS = 0 BEGIN     INSERT INTO #res     SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd      FETCH NEXT FROM c INTO @id, @val END CLOSE c DEALLOCATE c  SELECT * FROM #res
12741
When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation?  In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default value - which could be very painful, obviously. With the optimization the DB would instantaneously create the new column, store just one copy of the default value which would be returned when no non-default value is found for that column in a suitable index data structure.  For example  such an .
9775
So I'm currently creating some SQL to read through the postgres (9.1) catalogs to build table definitions. However, I am encountering a problem with SERIAL/BIGSERIAL data types.  Example:  CREATE TABLE cruft.temp ( id BIGSERIAL PRIMARY KEY ); SELECT * FROM information_schema.columns WHERE table_schema='cruft' AND table_name='temp'; "db","cruft","temp","id",1,"nextval('cruft.temp_id_seq'::regclass)","NO","bigint",,,64,2,0,,,,,,,,,,,,,"db","pg_catalog","int8",,,,,"1","NO","NO",,,,,,,"NEVER",,"YES"   It gives me database name (db), schema name (cruft), table name (temp), column name (id), default value (nextval( ... )), and data type (bigint and int8 .. NOT bigserial) ... I realize that I could just check to see if the default value was a sequence - but I don't believe that would be 100% accurate since I could manually create a sequence and create a non serial column where the default value was that sequence.  Does anyone have a suggestion for how I might accomplish this? Anything other than checking the default value for a nextval(*_seq)?  Edited for SQL solution added here in case of TL;DR or new users unfamiliar with the pg_catalog:  with sequences as (   select oid, relname as sequencename from pg_class where relkind = 'S' ) select   sch.nspname as schemaname, tab.relname as tablename, col.attname as columnname, col.attnum as columnnumber, seqs.sequencename from pg_attribute col join pg_class tab on col.attrelid = tab.oid join pg_namespace sch on tab.relnamespace = sch.oid left join pg_attrdef def on tab.oid = def.adrelid and col.attnum = def.adnum left join pg_depend deps on def.oid = deps.objid and deps.deptype = 'n' left join sequences seqs on deps.refobjid = seqs.oid where sch.nspname != 'information_schema' and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_   and col.attnum &gt; 0   and seqs.sequencename is not null -- TO ONLY VIEW SERIAL/BIGSERIAL COLUMNS order by sch.nspname, tab.relname, col.attnum;
124048
In order to better understand the SQL Server query processor, I've been thinking about how the ORDER BY clause works, and how SQL Server provides results.  It appears SQL Server will provide results in exactly the same order for any given set of data that remains the same and never, ever changes.  Once you introduce any kind of uncertainty such as developers changing something, the order of results can no longer be expected to be the same.  Simply seeing the results in the same order, each time you press F5 does not guarantee the results you expect.  Try this:  USE TempDB; DROP TABLE TestOrderBy; DROP TABLE TestOrderBy2; CREATE TABLE TestOrderBy (     ID INT NOT NULL CONSTRAINT PK_TestOrderBy PRIMARY KEY CLUSTERED IDENTITY(1,1)     , SomeData varchar(255) ); INSERT INTO TestOrderBy (SomeData) VALUES ('E'); INSERT INTO TestOrderBy (SomeData) VALUES ('D'); INSERT INTO TestOrderBy (SomeData) VALUES ('C'); INSERT INTO TestOrderBy (SomeData) VALUES ('B'); INSERT INTO TestOrderBy (SomeData) VALUES ('A');  CREATE TABLE TestOrderBy2 (     ID INT NOT NULL CONSTRAINT PK_TestOrderBy2 PRIMARY KEY CLUSTERED IDENTITY(1,1)     , SomeData varchar(255) ); INSERT INTO TestOrderBy2 (SomeData) VALUES ('E'); INSERT INTO TestOrderBy2 (SomeData) VALUES ('D'); INSERT INTO TestOrderBy2 (SomeData) VALUES ('C'); INSERT INTO TestOrderBy2 (SomeData) VALUES ('B'); INSERT INTO TestOrderBy2 (SomeData) VALUES ('A');  SELECT SomeData  FROM TestOrderBy UNION ALL SELECT SomeData FROM TestOrderBy2;  CREATE INDEX IX_TestOrderBy_SomeData ON TestOrderBy (SomeData); CREATE INDEX IX_TestOrderBy2_SomeData ON TestOrderBy2 (SomeData);  SELECT SomeData  FROM TestOrderBy UNION ALL SELECT SomeData FROM TestOrderBy2;   The results:    As you see, adding a simple index on the fields selected in the query alters the order of the results.  From here on, I'm adding ORDER BY unless I truly don't care.
12535
I need to know how to return a default row if no rows exist in a table. What would be the best way to do this? I'm only returning a single column from this particular table to get its value.   Edit: This would be SQL Server.
65148
Using OpenSUSE leap 15.1 10.2.29-lp151.2.9.1-x86_64 from vendor OpenSUSE (installed)  CREATE TABLE testtab2(     my_id2 INT AUTO_INCREMENT,     name2 VARCHAR(10),     comment2 VARCHAR(20),     PRIMARY KEY (my_id2) );   testtab.txt:  |Roger |hurray   | |George|today    | |Paul  |yesterday| |John  |last year| |Ringo |tomorrow |   LOAD DATA LOCAL INFILE 'testtab.txt' INTO TABLE testtab FIELDS TERMINATED BY '|';   works fine with linenumbers (my_id) 1-5  INSERT INTO testtab (name,comment) VALUES ('Beatles','band');   this gets linenumber (my_id) 8 - should be 6.  If testtab.txt have 8 records and using INSERT INTO 1 record this gets linenr (my_id) 16. This happens in other tables too - one moved from 199 to 256.  Anyone who can solve the problem?
67071
When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually?  The reason that I am asking this question is that when we use UNIQUE INDEX (col1, col2, col3), it acts as INDEX for the first column only, and we need to create additional INDEXs for other columns. I want to know if that's the case for Composite Primary Key too.
48569
I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string).  I understand that using a nvarchar(50) rather than an int would use way more space if it is linked to a table with many records.  On the other hand, using the lookup value directly would basically save us doing a join. I can imagine this would be a big saving if the join is always required (We're working on a web app so this counts quite a bit).  What are the advantages of using a int primary key (specifically for a lookup table) other than it being "the standard thing to do"?
123651
I have 400 million rows in a Postgres db, and the table has 18 columns:  id serial NOT NULL, a integer, b integer, c integer, d smallint, e timestamp without time zone, f smallint, g timestamp without time zone, h integer, i timestamp without time zone, j integer, k character varying(32), l integer, m smallint, n smallint, o character varying(36), p character varying(100), q character varying(100)   Columns e, k, and n are all NULL, they do not store any values at all and are completely useless at this point. They were part of the original design, but never removed.  Edit - most of the other columns are non-NULL.  Questions:   How can I calculate the impact this has on storage? Is it equal to size of column * # of rows ?  Will dropping these empty columns noticeably improve performance for this table? Would the page cache be able to fit more rows?
18644
I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to change that the primary key starts at 6?  CREATE TABLE animals (  id MEDIUMINT NOT NULL AUTO_INCREMENT,  name CHAR(30) NOT NULL,  PRIMARY KEY (id) ) ENGINE=MyISAM;  INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich');   Result: id  name 1   dog 2   cat 3   penguin 4   lax 5   whale 6   ostrich    DELETE FROM animals WHERE id = 6; INSERT INTO animals (name) VALUES ('x');   Result: id  name 1   dog 2   cat 3   penguin 4   lax 5   whale 7   x    Thanks for advice.
101615
I have a table like the following:  create table my_table (     id   int8 not null,     id_A int8 not null,     id_B int8 not null,     id_C int8 null,     constraint pk_my_table primary key (id),     constraint u_constrainte unique (id_A, id_B, id_C) );   And I want (id_A, id_B, id_C) to be distinct in any situation. So the following two inserts must result in an error:  INSERT INTO my_table VALUES (1, 1, 2, NULL); INSERT INTO my_table VALUES (2, 1, 2, NULL);   But it doesn't behave as expected because according to the documentation, two NULL values are not compared to each other, so both inserts pass without error.  How can I guarantee my unique constraint even if id_C can be NULL in this case? Actually, the real question is: can I guarantee this kind of uniqueness in "pure sql" or do I have to implement it on a higher level (java in my case)?