.::: Simulation create view with additional ID Unique row_number(),hash HASHBYTES/ md5 & Primary Key SQL Server :::.

  

1. Buat Tabel dengan tanggal_lahir
CREATE TABLE karyawan (
    emp_id INT IDENTITY(1,1) PRIMARY KEY,
    nama VARCHAR(100),
    jabatan VARCHAR(100),
    lokasi VARCHAR(100),
    tanggal_lahir DATE
);

2. Insert Data Sample (dengan tanggal lahir)

Saya isi tanggal lahir contoh yang wajar:

INSERT INTO karyawan (nama, jabatan, lokasi, tanggal_lahir) VALUES
('Andi', 'Staff IT', 'Jakarta', '1990-05-12'),
('Sinta', 'HRD', 'Bandung', '1992-11-03'),
('Budi', 'Finance', 'Surabaya', '1988-07-21'),
('Ratna', 'Manager', 'Jakarta', '1985-03-10');

3. Tes Hasil
SELECT * FROM karyawan;
 

4. using ROW_NUMBER()

IF OBJECT_ID('v_with_id', 'V') IS NOT NULL
    DROP VIEW v_with_id;
GO

CREATE VIEW v_with_id AS
SELECT
    ROW_NUMBER() OVER (ORDER BY nama) AS id,
    nama,
    lokasi,
    jabatan
FROM karyawan;
GO

select * from v_with_id;
 

5. using primary key ID Stabil (Konstan)

IF OBJECT_ID('v_with_id_pk', 'V') IS NOT NULL
    DROP VIEW v_with_id_pk;
GO

CREATE VIEW v_with_id_pk AS
SELECT
    emp_id AS id,
    nama,
    jabatan
FROM karyawan;
GO

select * from v_with_id_pk;
 

6. using MD5/UUID

IF OBJECT_ID('v_with_id_hash', 'V') IS NOT NULL
    DROP VIEW v_with_id_hash;
GO

CREATE VIEW v_with_id_hash AS
SELECT
    LOWER(CONVERT(VARCHAR(32), HASHBYTES('MD5', nama + '-' + CONVERT(VARCHAR(10), tanggal_lahir, 120)), 2)) AS id,
    nama,
    tanggal_lahir,
    jabatan
FROM karyawan;
GO

select * from v_with_id_hash;