Mssql database

Mssql Tutorial: sqlservertutorial.net

Step by step scenario

mssql datatype

Data type(Transact-SQL)

mssql connection information

DDL/DCL: Create/Modify table

In order to use IDENTITY, you must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

-- Create table with autoincrement (IDENTITY [ (seed , increment) ])
CREATE TABLE dbo.SHARE (
    SHARE_ID INT IDENTITY(1,1) PRIMARY KEY IDENTITY,
    SHARE_HASH  VARCHAR (64) NOT NULL,
    SHARE_DT INT NOT NULL,
    SHARE_TITLE VARCHAR (100) NOT NULL,
    SHARE_CONTENTS  VARCHAR (4096) NOT NULL
);

CREATE TABLE dbo.TBL_TEST (
  DataKey Varchar(50) NOT NULL,
  DataContents image NULL
);

CREATE TABLE dbo.TBL_TEST (
  REG_DATE char(8),
  EMP_NO varchar(8),
  EMP_NM varchar(40),
  JOB varchar(100),
  DEL_YN char(1) default 'N',
  SORT integer);

CREATE INDEX  TBL_TEST_IDX on dbo.TBL_TEST (
	REG_DATE, EMP_NO
);

ALTER TABLE TBL_TEST  ADD USRNM VARCHAR(40) NULL;
ALTER TABLE TBL_TEST ADD PART char(3);
ALTER TABLE TBL_TEST ADD SORT integer;
ALTER TABLE TBL_TEST DROP COLUMN USERNM;

-- Change table name
EXEC sp_rename 'OldTableName', 'NewTableName'

-- Change column name
EXEC sp_rename @objname = 'TableName.OldColumnName',
  @newname = 'NewColumnName'
  @objtype = 'COLUMN'

Select table

-- Concatenate string
SELECT USRID,
       USRNM+'('+USRID+')' USRNM
FROM   MON_Y0T009I
WHERE  DEPT = (SELECT DEPT
        FROM   TBL_TEST
        WHERE  USRID='19191919')
AND    PART<>'000'
ORDER BY DEPT, PART, SORT;

-- Numbering
select row_number() over (order by GRP_ID),a.* from dbo.TBL_TEST a

-- Select from dummy table such as dual in Oracle
select getdate()

-- Limit rows
--MSSQL
select top 10 * from TEST;

--Oracle
select * from TEST rownum<=10;

--Altibase
select * from TEST limit 10;

-- Outer join
Outer Join
select	B.USRID,
	B.DEPT,
	B.PART,
	A.REG_DATE,
	A.REG_TIME,
	A.PART_ID,
	A.DEPT_ID,
	A.PART_NM,
	A.EMP_NO,
	A.EMP_NM,
	A.JOB,
	A.WORK_HIST
from TBL_TEST B left outer join TBL_TEMP A
on	A.EMP_NO = B.USRID
	and A.DEPT_ID = B.DEPT
where B.DEPT = 'AAA';

Insert table

select * into TBL_TEST_tmp  from TBL_TEST;

insert into TBL_TEST
	(REG_DATE, 
	EMP_NO, 
	EMP_NM,
	DEPT_ID,
	DEPT_NM,
	PART_ID, 
	PART_NM, 
	GRP_NM, 
	DEL_ENABLED_YN)
select convert(char(8),getdate(),112)  REG_DATE,
	USRID,
	USRNM,
	DEPT,
	DEPT_NM,
	PART,
	PART_NM,
	GRP_NM,
	'N' 
from MON_Y0T009I
where DEPT='AAA' and PART<>'000';

Update table (transaction)

BEGIN TRANSACTION Restore_Value

update TBL_TEST set srvr_nm =  b.srvr_nm 
from TBL_TEST a, test b where a.srvr_id = b.srvr_id;

COMMIT TRANSACTION
ROLLBACK TRANSACTION

Delete/Truncate table

DELETE FROM TBL_TEST WHERE EMP_NO = '19191919';
COMMIT;

in / not in

select categoryId, category, delete_yn 
from KIDS_Youtube_Category 
where categoryId not in (select categoryId from KIDS_Youtube);

Check for null values (cf. NVL in Oracle)

SELECT isnull(DATATYPE, '') AS DATATYPE, 
    isnull(CONTENTS, '') AS CONTENTS, 
	isnull(FILENAME, '') AS FILENAME
FROM BBS1_CONTENTS
WHERE  ID = 55 AND BBSID = 52

Create sequence

Link : Create sequence microsoft document

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH ] [ INCREMENT BY ] [ { MINVALUE [ ] } | { NO MINVALUE } ] [ { MAXVALUE [ ] } | { NO MAXVALUE } ] [ CYCLE | { NO CYCLE } ] [ { CACHE [ ] } | { NO CACHE } ] [ ; ]

GRANT CREATE SEQUENCE ON SCHEMA::Test TO [AdventureWorks\Larry]

CREATE SCHEMA Test;
CREATE SEQUENCE Test.TestSequence  
    START WITH 0  
    INCREMENT BY 1 ;

-- The following example creates a sequence using the default values.
CREATE SEQUENCE Test.TestSequence ; 

CREATE SEQUENCE SmallSeq AS smallint ;

-- Creating a sequence using all arguments
CREATE SEQUENCE Test.TestSequence  
    AS decimal(3,0)   
    START WITH 125  
    INCREMENT BY 25  
    MINVALUE 100  
    MAXVALUE 200  
    CYCLE  
    CACHE 3 ; 

SELECT * FROM sys.sequences WHERE name = 'TestSequence' ; 

SELECT NEXT VALUE FOR Test.TestSequence;

SELECT cache_size, current_value
  FROM sys.sequences  
  WHERE name = 'TestSequence' ;

-- Short URL samples
CREATE SEQUENCE SQ_SHORT_URL
    START WITH 0  
    INCREMENT BY 1 ;

CREATE TABLE dbo.SHORT_URL (
    ID VARCHAR(100) PRIMARY KEY,
    URL VARCHAR (4096) NOT NULL
);

SELECT NEXT VALUE FOR SQ_SHORT_URL;