Contents

1 테스트 데이터 만들기

2 NewID() 를 이용한 방법

3 TableSample을 이용한 방법

4 RAND()함수 이용하기

CREATE TABLE users
(
uid int,
uname varchar(30)
)
GO

--Inserting 15 rows into the table users
SET NOCOUNT ON
INSERT [users] ([uid],[uname])VALUES(1,'Vyas Kondreddi')
INSERT [users] ([uid],[uname])VALUES(2,'Narayana Kondreddi')
INSERT [users] ([uid],[uname])VALUES(3,'Kondreddi')
INSERT [users] ([uid],[uname])VALUES(4,'Narayana Vyas Kondreddi')
INSERT [users] ([uid],[uname])VALUES(5,'Subhadra Jyothsna')
INSERT [users] ([uid],[uname])VALUES(6,'Geetha Madhuri Vungarala')
INSERT [users] ([uid],[uname])VALUES(7,'Sridhar Vungarala')
INSERT [users] ([uid],[uname])VALUES(8,'Vamsee Mohan')
INSERT [users] ([uid],[uname])VALUES(9,'Gopala Krishna Kondreddi')
INSERT [users] ([uid],[uname])VALUES(10,'Nagaja Devi Kondreddi')
INSERT [users] ([uid],[uname])VALUES(11,'Vijaya Madhuri Govindaraju')
INSERT [users] ([uid],[uname])VALUES(12,'Pamarthy Pradeep Kumar')
INSERT [users] ([uid],[uname])VALUES(13,'Pamarthy Harinath')
INSERT [users] ([uid],[uname])VALUES(14,'Vungarala Sathyanarayana')
INSERT [users] ([uid],[uname])VALUES(15,'Pamarthy Ramakrishna')
SET NOCOUNT OFF
GO

SELECT TOP 1
	uid
,	uname
FROM users
ORDER BY NewID()

WHILE(1=1)
BEGIN
	SELECT TOP 1
		uid
	,	uname
	FROM users TABLESAMPLE(200 ROWS)
	
	IF @@ROWCOUNT > 0
		BREAK
END

--일반적으로..
DECLARE @mx int, @mn int
SELECT @mx = MAX(uid), @mn = MIN(uid) FROM users
SELECT uid,uname [Lucky Winner is...] FROM users WHERE uid = ROUND(@mn + (RAND() * (@mx-@mn)),0)


--만약 중간에 이빨이 빠져서 1개의 Row도 반환하지 못하는 상황이면...

DECLARE @mx int, @mn int, @uid int, @uname varchar(200)
SELECT @mx = MAX(uid), @mn = MIN(uid) FROM users
WHILE(1=1)
BEGIN
	SELECT 
		@uid = uid
	,	@uname = uname 
	FROM users WHERE uid = ROUND(@mn + (RAND() * (@mx-@mn)),0)
	IF @@ROWCOUNT > 0 
		BREAK
END

SELECT @uid, @uname