September 25, 2009 at 4:27 am
I was reading an article on here about data transfer. Specifically Incremental Loads. I was understanding the article a bit until the end when the author said he will execute the following query for testing:
setEmailAddress = '_' + EmailAddress
where ContactID % 5 = 0
delete dbo.Contact
where ContactID > 18500
I'm still basic on t-sql so I don't know all the syntax, but what does "% 5 - 0" mean in the where clause?
Any help will be appreciated.
September 25, 2009 at 4:52 am
It's the modulo operator - the remainder after integer division - look it up in Books Online.
I assume it's being used here to execute the update query on 20% of the data at a time.
Run this query to see how the operator works for the integers in the range -20 to 20:
SELECT CONVERT(varchar(4), (N.number - 20)) + ' % 5 = ' AS Expression,
(N.number - 20) % 5 AS Result
FROM master.dbo.spt_values N
WHERE N.type = 'P'
AND N.number <= 40
ORDER BY N.number
Expression Result
----------- -----------
-20 % 5 = 0
-19 % 5 = -4
-18 % 5 = -3
-17 % 5 = -2
-16 % 5 = -1
-15 % 5 = 0
-14 % 5 = -4
-13 % 5 = -3
-12 % 5 = -2
-11 % 5 = -1
-10 % 5 = 0
-9 % 5 = -4
-8 % 5 = -3
-7 % 5 = -2
-6 % 5 = -1
-5 % 5 = 0
-4 % 5 = -4
-3 % 5 = -3
-2 % 5 = -2
-1 % 5 = -1
0 % 5 = 0
1 % 5 = 1
2 % 5 = 2
3 % 5 = 3
4 % 5 = 4
5 % 5 = 0
6 % 5 = 1
7 % 5 = 2
8 % 5 = 3
9 % 5 = 4
10 % 5 = 0
11 % 5 = 1
12 % 5 = 2
13 % 5 = 3
14 % 5 = 4
15 % 5 = 0
16 % 5 = 1
17 % 5 = 2
18 % 5 = 3
19 % 5 = 4
20 % 5 = 0
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply