Questions tagged [uniqueidentifier]
The uniqueidentifier tag has no usage guidance.
66 questions
0
votes
2
answers
41
views
id increases even if the line has not been created
I was doing a simple crud on nestjs and using postgres, then i noticed something strange.
I made an entity with a unique string column
@Entity()
export class Model {
@PrimaryGeneratedColumn()
id: ...
0
votes
0
answers
40
views
Is this an abuse of UniqueIdentifiers?
I'm designing a new Sql Server database. Every major table uses a UniqueIdentifier as a primary key. I'm adding in a new data object called "Notebook" which is a universal object that can ...
- 101
0
votes
0
answers
333
views
How to create a list of increasing unique identifiers?
I'm creating a list of Connections objects (names of machines with IP address and IP port), but I'm having an issue with the Id field.
Normally this is the way I would work:
INSERT INTO [CONNECTIONS] (...
- 377
0
votes
0
answers
82
views
Generate unique ids in BigQuery data import
I am using Zapier to import woocommerce orders into BigQuery and build a datawarehouse with paid orders from multiple sites. However, multiple sites generate an ID per site, not a unique id globally ...
0
votes
1
answer
78
views
Prevent creation of duplicate rows in database dynamically
I am new to databases so please forgive my mistakes. Here's the scenario.
I use the web for my data source.
Sometimes, like, for a keyword, what my crawler do is crawl same keyword repeatedly (not the ...
0
votes
1
answer
95
views
Do I need to implement id column to table if i know that every added row will be unique? [duplicate]
I want to create database which allow me to store IT job offers from various websites. While creating ER diagram I started wondering if I really need an id column in one particular table. Look at this ...
- 65
0
votes
1
answer
100
views
Must tuples of a relation and their referents be in a one-to-one relationship?
Figures 3.a and 3.b, section 1.4, page 381 of Edgar Codd’s 1970 landmark paper ‘A Relational Model of Data for Large Shared Data Banks’ presents a set of relation schemas (unnormalized in figure 3.a ...
- 71
0
votes
2
answers
615
views
Person unique identification
I am working on a sports database.
I have table for persons(players), teams, leagues etc.
The problem is, how do I identify a person uniqely? I was thinking that a new person could be added by either ...
- 111
1
vote
2
answers
52
views
Large Identifier Uniqueness Across Table
We use NEWSEQUENTIALID to generate uniqueidentifier as table ID.
Issue is raised after server reset as the seed of it is modified and sequentiality breaks.
Is there another suggested solution to ...
- 113
0
votes
1
answer
626
views
How to update a UID throughout the database?
Our database makes use of uniqueidentifier and we have the need to update that value everywhere it's found in our system.
Sample minimal structure:
CREATE TABLE Users
(
id uniqueidentifier not ...
- 173
3
votes
2
answers
1k
views
Hash IDs (such as base64 YouTube video Ids): store as int/bigint, or as string, and why?
I want to generate "unguessable" IDs, rather than /record/1, /record/2, /record/3 etc.
YouTube's style of using base 64 allows for 16.7 million IDs to be stored in just 4 characters (64 * 64 * 64 * ...
- 265
-2
votes
1
answer
46
views
If all associated attributes of an intended primary key are identical, is it still a true primary key?
I'll start with an example. If I have a person table with intended surrogate primary key Id:
+----+------+------------+-------------+
| Id | name | DoB | SSN |
+----+------+-----------...
0
votes
1
answer
182
views
If PK is Non-Clustered index, how to identify clustered index candidate?
I'm designing a database for insurance company. I want to make Primary key as GUID-uniqueidentifier. I want to create Index for each table in database for performance optimization.
I did research ...
- 21
4
votes
1
answer
209
views
Why does querying for records that violate a foreign key return incorrect results?
While doing some pre-checking on a table before adding a new Foreign Key, we were querying to see how many current rows would violate the new key. This is a fairly active database with near constant ...
- 143
1
vote
0
answers
93
views
Incorrect Estimates (CE Model 130) on uniqueidentifier columns
We want to change our application from compatibility level 100 to 130. As part of the tests, we came across a curious problem regarding estimates on uniqueidentifier columns.
The select is very ...
- 11
-2
votes
1
answer
51
views
Is there a mechanism in SQL that automatically generates a unique alphanumeric identifier for each row?
I need a unique identifier for objects in tables other than the autoincremented id and it would be helpful if it is generated by SQL after insert to take the load off of the web server
- 27
0
votes
0
answers
137
views
Is there any global variable for PostgreSQL replication has configured or not
Is there any unique identifier or any global variable which can tell us if replication is configured in current PostgreSQL server. Other than this query
select pg_is_in_recovery();
The above ...
- 1
0
votes
2
answers
811
views
What is the difference between a foreign key and a primary foreign key?
I understand that you use foreign keys to relate rows from one table to the other and if this foreign key also becomes a primary key of that table then it is a primary foreign key.
But, for example, ...
- 135
0
votes
1
answer
2k
views
What is a good unique identifier for customer and payment details for this tourism company?
Given a tourism company, Air T&T, has customers. When a customer checks in to an AirTnT facility (i.e. house-share), AirTnT records the customer's first and last names, address, date of birth, ...
- 135
2
votes
3
answers
2k
views
Is "SET IDENTITY_INSERT" required to insert arbitrary GUID into [uniqueidentifier] column?
SQL Server 2016 and 2017, Standard Edition
I need to insert raw data (from another table) into a table that has two uniqueidentifier columns.
Does this require that I use SET IDENTITY_INSERT Carrier ...
0
votes
0
answers
272
views
How to uniquely identify a row in a table where there are no unique columns in Ingres DB?
I am trying to find a way to uniquely identify a row in a table where there aren't any unique columns. I have seen that it is advised not to use tid as a unique row identifier so I was wondering what ...
4
votes
2
answers
3k
views
Clustered vs Non Clustered Indexes for a Primary Key
I have a large table with around 19 million records in it.
The primary key is a uniqueidentifier column called ID and there is a nonclustered index on this column. An application generates these ...
- 157
25
votes
2
answers
26k
views
What is the data type of the ‘ctid’ system column in Postgres?
The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns.
That page mentions that oid values “are 32-bit quantities”. And that page says the same about ...
- 11k
0
votes
2
answers
3k
views
Make a non-ordinal numeric ID in PostgreSQL
If I declare a column to be serial in PostgreSql, it will auto-increment in a ordinal sequence. The end result may not be contigious due to rollbacks and whatnot, but it's more or less ordinal.
I ...
- 1,271
4
votes
3
answers
3k
views
Should I use Index or Unique Key for a non PK Uniqueidentifier?
Using Sql Server 2014, I have several tables where the PK field is an int, but I also have a UniqueIdentifier field that I need to query by. This field will always be unique.
I am adding indexes for ...
- 203
4
votes
3
answers
4k
views
Performance of using a non-clustered index as primary key
Background
I am designing a database for a record management system. It's early stages as I am considering what to use as a primary key: INT or UNIQUEIDENTIFIER.
The reason for considering ...
- 252
2
votes
2
answers
2k
views
Trigger to get next prefixed ID: Is there a better way?
I have a projects table:
+------------+-----+
| PROJECT_ID | ... |
+------------+-----+
| LC000001 | |
| LC000002 | |
| LC000003 | |
| LC000004 | |
| LC000005 | |
+------...
- 1,497
0
votes
2
answers
11k
views
alter primary id field to uniqueidentifier GUID as default in sql server
I have tables with id(primary key - int) with identity specification (1,1) .
I want to change this column to uniqueidentifier with default ( newid() ) .
What I've tried is
ALTER TABLE myTable ALTER ...
- 283
6
votes
1
answer
264
views
Incorrect uniqueidentifier works without a Primary Key but fails with one
So we were recently running some logic tests on an insert into a table with a uniqueidentifier as a primary key. Here's my test query;
IF OBJECT_ID('tempdb..#DatabaseTable') IS NOT NULL DROP TABLE #...
- 598
1
vote
2
answers
850
views
Deleting records in relation with other tables
I need suggestions on deleting records in relation with other tables
For example i have a client in the clients table and this client has bills, payments, wire transfers etc... so this client id is ...
- 27
9
votes
2
answers
21k
views
How Uniqueidentifier in SQL Server is always a unique value globally?
As per microsoft documentation on UniqueIdentifier, This value is always a unique globally beacuse it's based on network clock and CPU clock time and on the other hand the same documentation says
...
- 229
2
votes
4
answers
2k
views
Query on char with selector with N prefix is very slow
I have a table with a CHAR(36) "id" column, which is a clustered primary key. It has multiple other columns, including an "imdbId" column, which is an INT.
When I update using
UPDATE Movies SET ...
- 123
1
vote
3
answers
1k
views
Is GUID(uniqueidentifier) not suitable for a clustered index? [closed]
When i use uniqueidentifier datatype for primary key column then it will automatically create clustered index...
So, will it slow down my queries or not ?
I am explaining some description of my ...
- 373
1
vote
1
answer
674
views
MySQL unique static ID
I need to get an unique static ID that is not changeable by the users, and if the user copies the database to another server this static ID is different.
Background
My company wants to sell our app ...
- 113
2
votes
2
answers
415
views
Why do SQL Server uniqueidentifier values look different when converted to Db2?
I am currently trying to convert a SQL Server database to DB2. I am using the Data Conversion Workbench (DCW) for IBM Data Studio. The issue I have is that DCW can not seem to correctly extract data ...
- 31
11
votes
3
answers
27k
views
Return the uniqueidentifier generated by a default on insert
Goal
Retrieve the latest guid value in real time after you have inserted the value in the table
Problem
Don't know how to do it
Info
The code should only specify new values for address and zipcode
...
- 331
19
votes
2
answers
6k
views
SQL Server UniqueIdentifier / GUID internal representation
A colleague of mine sent me an interesting question, that I can't entirely explain.
He ran some code (included below) and got somewhat unexpected results from it.
Essentially, when converting a ...
- 1,523
5
votes
2
answers
1k
views
Howto Identify Clustered Index Candidates
I find myself in a situation where I have inherited the db and people complain about database performance. I identified about a dozend of tables not having a clustered index or a not carefully planned ...
- 4,807
28
votes
3
answers
44k
views
Should I use UUID as well as ID
I've been using UUIDs in my systems for a while now for a variety of reasons ranging from logging to delayed correlation. The formats I used changed as I became less naive from:
VARCHAR(255)
VARCHAR(...
- 391
57
votes
3
answers
53k
views
What is the optimal data type for an MD5 field?
We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute).
There is a table names that serves as a sort of central registry. Each row has a ...
- 765
8
votes
5
answers
7k
views
How to uniquely identify SQL instance
I need to find a way how to identify SQL Instance no matter what changes were done to SQL Server and\or Windows Server where it is running, because we're collecting remotely information about ...
- 277
2
votes
1
answer
11k
views
adding autoincrement to id already part of foreign key
I would like to add the autoincrement property for all my table's columns named id in a schema. However, most of them are part of a foreign key constraint. Is there any other way to do it without ...
- 123
4
votes
2
answers
1k
views
Uniquifier on Non-Unique Clustered Index vs Unique Clustered Index
Lets say for example that I have a table structured this way:
ID1 int
ID2 int
ID3 int
DTM datetime
I'm only able to create a unique clustered index on the table if I include ID1, ID2, and ID3 (a ...
- 145
4
votes
3
answers
7k
views
Should I use data type SERIAL for table id columns?
I found out that there is a mysql datatype called SERIAL, it appears to be designed for use as an id column. However some of my peers don't think that the real data type BIGINT implied by SERIAL is ...
- 143
3
votes
2
answers
2k
views
What and How is the ID antipattern?
What is the ID antipattern and why / under which circumstances is it considered an antipattern?
The id antipattern is having a unique ID column in each table. The ID
is generated for each new ...
- 2,420
1
vote
0
answers
118
views
Combining fields to make a unique record or keeping them separated
I've been asked to implement what has before, been a bunch of CSV files, into a MySQL database. I'm at a point in the design where all the schemas, tables and fields have been mocked-up; however I'm ...
- 111
6
votes
2
answers
2k
views
SQL Server: Create New GUID Values for Existing Index
I have an existing table with about 6 million rows. The relevant columns are:
ID int not null PK
Key uniqueidentifier not null
Read/write on this table by Key lookup is probably something like 100 ...
- 161
3
votes
3
answers
8k
views
Adding non-nullable NEWSEQUENTIALID() column to existing table
In SQL Server 2008, I am adding a non-nullable column to an existing table, and want to make the default NEWSEQUENTIALID().
ALTER TABLE MyTable
ADD MyColumn UNIQUEIDENTIFIER NULL
CONSTRAINT ...
- 93
0
votes
1
answer
628
views
Unsure of Rank / Dense_Rank - Mulitple Columns - change rank when unique combination changes
I need to get the unique combinations of 2 fields, with a RankID for each unique combination
In this example I need to create new IDs for each Customer/Value combination.
Source table:
CustID ...
- 1,395
0
votes
1
answer
200
views
unique ID for multi tabel? (MySQL) [closed]
I have a table 'article' and each of these articles belongs to one of three different topics (topic_a, b and c). I also have a table for each of these topics with various fields. How can I connect ...
- 103