Questions tagged [uniqueidentifier]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
38 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 ...
2
votes
2answers
31 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 * ...
-2
votes
1answer
34 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
1answer
42 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 ...
4
votes
1answer
81 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 ...
1
vote
0answers
66 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 ...
-2
votes
1answer
32 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
0
votes
0answers
41 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 ...
0
votes
2answers
197 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, ...
0
votes
1answer
609 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, ...
2
votes
3answers
977 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
0answers
54 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
2answers
1k 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 ...
12
votes
1answer
7k 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 ...
0
votes
2answers
1k 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 ...
4
votes
3answers
877 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 ...
3
votes
3answers
2k 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 ...
2
votes
2answers
934 views

Trigger to get next prefixed ID: Is there a better way?

I have a projects table: +------------+-----+ | PROJECT_ID | ... | +------------+-----+ | LC000001 | | | LC000002 | | | LC000003 | | | LC000004 | | | LC000005 | | +------...
0
votes
2answers
6k 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 ...
6
votes
1answer
137 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 #...
1
vote
2answers
60 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 ...
8
votes
2answers
13k 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 ...
2
votes
4answers
1k 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 ...
1
vote
3answers
799 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 ...
1
vote
1answer
511 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 ...
2
votes
2answers
258 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 ...
8
votes
3answers
15k 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 ...
17
votes
2answers
3k 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 ...
5
votes
2answers
967 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 ...
13
votes
3answers
14k 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(...
41
votes
3answers
25k 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 ...
6
votes
5answers
5k 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 ...
2
votes
1answer
9k 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 ...
4
votes
2answers
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 ...
4
votes
2answers
6k 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 ...
3
votes
2answers
1k 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 ...
1
vote
0answers
72 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 ...
6
votes
2answers
1k 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 ...
3
votes
3answers
7k 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 ...
0
votes
1answer
339 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 ...
0
votes
1answer
192 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 ...
14
votes
3answers
14k views

Indexing a PK GUID in SQL Server 2012

My developers have setup their application to use GUID's as PK for pretty much all of their tables and by default SQL Server has setup the clustered index on these PK's. The system is relatively ...
2
votes
2answers
75 views

best approach to keys, if data from separate DBs to be presented in same UI

Suppose we are creating databases containing information about monkeys, and a user interface to present monkey information to users. We have two separate databases. The schemas are identical (and are ...
5
votes
3answers
5k views

Query to find GUID's “not in” null containing subquery returns no results

When I have a query that checks if a column of type uniqueidentifer does not exist in a table that has a null value then I get no results back. If the subquery does not return a null it works fine and ...
15
votes
3answers
7k views

Securely generate a UNIQUEIDENTIFIER in SQL Server

I intend to be using a UNIQUEIDENTIFIER as an access key that users can use to access certain data. The key will act as a password in that sense. I need to generate multiple such identifiers as part ...
14
votes
5answers
6k views

Sequential GUID or bigint for 'huge' database table PK

I know this type of question comes up a lot, but I've yet to read any compelling arguments to help me make this decision. Please bear with me! I have a huge database - it grows by about 10,000,000 ...
0
votes
1answer
54 views

Mysql database connector table or keys

This may be a simple a question but I just wanted to clarify, at work I have a table containing a list of titles and I also have a list of authors in two different tables. What would be best way to ...
9
votes
2answers
18k views

How to use Unique key via combinations of table fields?

Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1 CREATE TABLE contacts ( id int auto_increment primary key, name varchar(20), network_id int, ...
50
votes
7answers
41k views

Why should I create an ID column when I can use others as key fields? [duplicate]

Possible Duplicate: Why use an int as a lookup table's primary key? So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not think ...
8
votes
4answers
387 views

Is this a standard way to design a database?

I recently learned about how relationships are defined in the database at work, and was wondering if this is a standard practice. Let's say we have two processes: Process A, and Process B. Process B ...