October 9, 2020 at 7:02 am
Dear Experts
Please help me to convert below oracle unique index to Sql Server.
CREATE UNIQUE INDEX UK_IDX_TLOG ON TLOG_TB (CASE TRAN_TYPE WHEN '200' THEN TRAN_AUTH_ID END );
Please help
October 9, 2020 at 12:31 pm
Was thinking about adding a computed column to the table and adding an index on it. Still a problem if you have multiple TRAN_TYPE per TRAN_AUTH_ID as the index might resolve to NULL which must also be unique.
CREATE TABLE DBO.TLOG_TB
(
TRAN_AUTH_ID int null,
TRAN_TYPE varchar(30) null
)
;
INSERT INTO DBO.TLOG_TB
(
TRAN_AUTH_ID,
TRAN_TYPE
)
SELECT 1,'200'
UNION ALL SELECT 2,'200'
UNION ALL SELECT NULL,NULL;
ALTER TABLE DBO.TLOG_TB
ADD DoubleCheck AS( CASE WHEN TRAN_TYPE='200' THEN TRAN_AUTH_ID END)
CREATE UNIQUE INDEX UK_IDX_TLOG ON DBO.TLOG_TB (DoubleCheck);
SELECT *
FROM DBO.TLOG_TB;
INSERT INTO DBO.TLOG_TB
(
TRAN_AUTH_ID,
TRAN_TYPE
)
SELECT 1,'200';
INSERT INTO DBO.TLOG_TB
(
TRAN_AUTH_ID,
TRAN_TYPE
)
SELECT 1,'100'; --Fails Doublecheck = NULL and NULL is already in the table
October 9, 2020 at 12:58 pm
Please don't cross post the same question across multiple forums. It makes it hard to keep a focused conversation and answer the question efficiently. Please, any additional posts on this topic should go over to here. Thanks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply