USE [MASTER]
GO
IF EXISTS (SELECT NAME FROM SYS.DATABASES WHERE NAME = N'EMPLOYEE TEST')
DROP DATABASE [EMPLOYEE TEST]
GO
CREATE DATABASE [EMPLOYEE TEST]
GO
USE [EMPLOYEE TEST]
GO
IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = N'EMPLOYEE')
DROP TABLE [EMPLOYEE]
GO
CREATE TABLE EMPLOYEE
(EMPID INT, FNAME VARCHAR(50),LNAME VARCHAR(50))
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021110, 'MICHAEL', 'POLAND')
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021110, 'MICHAEL', 'POLAND')
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021115, 'JIM', 'KENNEDY')
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2121000, 'JAMES', 'SMITH')
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2011111, 'ADAM', 'ACKERMAN')
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (1021710, 'MARIAH', 'MANDEZ')
GO
SELECT EMPID, FNAME, LNAME FROM EMPLOYEE
go
SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME
INTO EMPLOYEE2 FROM EMPLOYEE ORDER BY EMPID
go
SELECT ROWID, EMPID, FNAME, LNAME FROM EMPLOYEE2
go
SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE
go
WITH [EMPLOYEE ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE)
SELECT * FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID =4
go
WITH [EMPLOYEE ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE)
DELETE FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID =4
