Iteration in a stored procedure

I have a storde procedure which compares one column of a table against each
of the other columns in the same table. If it finds a 'one' in each column,
it counts the number of 'paired data' . As there can be up to 60 columns and
60 rows in any table , can anyone suggest a shorter , more efficient way of
writing the following code? Any assistanc ewould be appreciated. Thank you.
ALTER PROCEDURE GetXXXXXXAll
/*
(
@.IdNumber = 1
)
*/
AS
Begin
/* SET NOCOUNT ON */
SELECT COUNT ([1]) As Expr1,
COUNT([1] + [2]) AS Expr2, COUNT([1] + [3]) AS Expr3, COUNT([1]
+ [4]) AS Expr4, COUNT([1] + [5]) AS Expr5,
COUNT([1] + [6]) AS Expr6,COUNT([1] + [7]) AS Expr7, COUNT([1] +
[8]) AS Expr8, COUNT([1] + [9]) AS Expr9,
COUNT([1] + [10]) AS Expr10, COUNT([1] + [11]) AS Expr11,
COUNT([1] + [12]) AS Expr12,COUNT([1] + [13]) AS Expr13,
COUNT([1] + [14]) AS Expr14, COUNT([1] + [15]) AS Expr15,
COUNT([1] + [16]) AS Expr16,COUNT([1] + [17]) AS Expr17,
COUNT([1] + [18]) AS Expr18, COUNT([1] + [19]) AS Expr19,
COUNT([1] + [20]) AS Expr20,COUNT([1] + [21]) AS Expr21,
COUNT([1] + [22]) AS Expr22, COUNT([1] + [23]) AS Expr23,
COUNT([1] + [24]) AS Expr24,COUNT([1] + [25]) AS Expr25,
COUNT([1] + [26]) AS Expr26, COUNT([1] + [27]) AS Expr27,
COUNT([1] + [28]) AS Expr28,COUNT([1] + [29]) AS Expr29,
COUNT([1] + [30]) AS Expr30,
COUNT ([2]) As Expr31,
COUNT([2] + [3]) AS Expr32, COUNT([2] + [4]) AS Expr33,
COUNT([2] + [5]) AS Expr34,
COUNT([2] + [6]) AS Expr35,COUNT([2] + [7]) AS Expr36, COUNT([2]
+ [8]) AS Expr37, COUNT([2] + [9]) AS Expr38,
COUNT([2] + [10]) AS Expr39, COUNT([2] + [11]) AS Expr40,
COUNT([2] + [12]) AS Expr41,COUNT([2] + [13]) AS Expr42,
COUNT([2] + [14]) AS Expr43, COUNT([2] + [15]) AS Expr44,
COUNT([2] + [16]) AS Expr45,COUNT([2] + [17]) AS Expr46,
COUNT([2] + [18]) AS Expr47, COUNT([2] + [19]) AS Expr48,
COUNT([2] + [20]) AS Expr49,COUNT([2] + [21]) AS Expr50,
COUNT([2] + [22]) AS Expr51, COUNT([2] + [23]) AS Expr52,
COUNT([2] + [24]) AS Expr53,COUNT([2] + [25]) AS Expr54,
COUNT([2] + [26]) AS Expr55, COUNT([2] + [27]) AS Expr56,
COUNT([2] + [28]) AS Expr57,COUNT([2] + [29]) AS Expr58,
COUNT([2] + [30]) AS Expr59,
COUNT ([3]) As Expr60,
COUNT([3] + [4]) AS Expr61, COUNT([3] + [5]) AS Expr62,
COUNT([3] + [6]) AS Expr63,COUNT([3] + [7]) AS Expr64, COUNT([3]
+ [8]) AS Expr65, COUNT([3] + [9]) AS Expr66,
COUNT([3] + [10]) AS Expr67, COUNT([3] + [11]) AS Expr68,
COUNT([3] + [12]) AS Expr69,COUNT([3] + [13]) AS Expr70,
COUNT([3] + [14]) AS Expr71, COUNT([3] + [15]) AS Expr72,
COUNT([3] + [16]) AS Expr73,COUNT([3] + [17]) AS Expr74,
COUNT([3] + [18]) AS Expr75, COUNT([3] + [19]) AS Expr76,
COUNT([3] + [20]) AS Expr77,COUNT([3] + [21]) AS Expr78,
COUNT([3] + [22]) AS Expr79, COUNT([3] + [23]) AS Expr80,
COUNT([3] + [24]) AS Expr81,COUNT([3] + [25]) AS Expr82,
COUNT([3] + [26]) AS Expr83, COUNT([3] + [27]) AS Expr84,
COUNT([3] + [28]) AS Expr85,COUNT([3] + [29]) AS Expr86,
COUNT([3] + [30]) AS Expr87,
COUNT ([4]) As Expr88,
COUNT([4] + [5]) AS Expr89,
COUNT([4] + [6]) AS Expr90,COUNT([4] + [7]) AS Expr91, COUNT([4]
+ [8]) AS Expr92, COUNT([4] + [9]) AS Expr93,
COUNT([4] + [10]) AS Expr94, COUNT([4] + [11]) AS Expr95,
COUNT([4] + [12]) AS Expr96,COUNT([4] + [13]) AS Expr97,
COUNT([4] + [14]) AS Expr98, COUNT([4] + [15]) AS Expr99,
COUNT([4] + [16]) AS Expr100,COUNT([4] + [17]) AS Expr101,
COUNT([4] + [18]) AS Expr102, COUNT([4] + [19]) AS Expr103,
COUNT([4] + [20]) AS Expr104,COUNT([4] + [21]) AS Expr105,
COUNT([4] + [22]) AS Expr106, COUNT([4] + [23]) AS Expr107,
COUNT([4] + [24]) AS Expr108,COUNT([4] + [25]) AS Expr109,
COUNT([4] + [26]) AS Expr110, COUNT([4] + [27]) AS Expr111,
COUNT([4] + [28]) AS Expr112,COUNT([4] + [29]) AS Expr113,
COUNT([4] + [30]) AS Expr114,
COUNT ([5]) As Expr115,
COUNT([5] + [6]) AS Expr116,COUNT([5] + [7]) AS Expr117,
COUNT([5] + [8]) AS Expr118, COUNT([5] + [9]) AS Expr119,
COUNT([5] + [10]) AS Expr120, COUNT([5] + [11]) AS Expr121,
COUNT([5] + [12]) AS Expr122,COUNT([5] + [13]) AS Expr123,
COUNT([5] + [14]) AS Expr124, COUNT([5] + [15]) AS Expr125,
COUNT([5] + [16]) AS Expr126,COUNT([5] + [17]) AS Expr127,
COUNT([5] + [18]) AS Expr128, COUNT([5] + [19]) AS Expr129,
COUNT([5] + [20]) AS Expr130,COUNT([5] + [21]) AS Expr131,
COUNT([5] + [22]) AS Expr132, COUNT([5] + [23]) AS Expr133,
COUNT([5] + [24]) AS Expr134,COUNT([5] + [25]) AS Expr135,
COUNT([5] + [26]) AS Expr136, COUNT([5] + [27]) AS Expr137,
COUNT([5] + [28]) AS Expr138,COUNT([5] + [29]) AS Expr139,
COUNT([5] + [30]) AS Expr140
.
.
. etc
.
.
COUNT([60] + [60]) AS Expr1859
FROM XXXXXXFrequencyTable
WHERE ([1] = 1) OR
([1] = 1) AND ([2] = 1) OR
([1] = 1) AND ([3] = 1) OR
([1] = 1) AND ([4] = 1) OR
([1] = 1) AND ([5] = 1) OR
([1] = 1) AND ([6] = 1) OR
([1] = 1) AND ([7] = 1) OR
([1] = 1) AND ([8] = 1) OR
([1] = 1) AND ([9] = 1) OR
([1] = 1) AND ([10] = 1) OR
([1] = 1) AND ([11] = 1) OR
([1] = 1) AND ([12] = 1) OR
([1] = 1) AND ([13] = 1) OR
([1] = 1) AND ([14] = 1) OR
([1] = 1) AND ([15] = 1) OR
([1] = 1) AND ([16] = 1) OR
([1] = 1) AND ([17] = 1) OR
([1] = 1) AND ([18] = 1) OR
([1] = 1) AND ([19] = 1) OR
([1] = 1) AND ([20] = 1) OR
([1] = 1) AND ([21] = 1) OR
([1] = 1) AND ([22] = 1) OR
([1] = 1) AND ([23] = 1) OR
([1] = 1) AND ([24] = 1) OR
([1] = 1) AND ([25] = 1) OR
([1] = 1) AND ([26] = 1) OR
([1] = 1) AND ([27] = 1) OR
([1] = 1) AND ([28] = 1) OR
([1] = 1) AND ([29] = 1) OR
([1] = 1) AND ([30] = 1) OR
([2] = 1) OR
([2] = 1) AND ([3] = 1) OR
([2] = 1) AND ([4] = 1) OR
([2] = 1) AND ([5] = 1) OR
([2] = 1) AND ([6] = 1) OR
([2] = 1) AND ([7] = 1) OR
([2] = 1) AND ([8] = 1) OR
([2] = 1) AND ([9] = 1) OR
([2] = 1) AND ([10] = 1) OR
([2] = 1) AND ([11] = 1) OR
([2] = 1) AND ([12] = 1) OR
([2] = 1) AND ([13] = 1) OR
([2] = 1) AND ([14] = 1) OR
([2] = 1) AND ([15] = 1) OR
([2] = 1) AND ([16] = 1) OR
([2] = 1) AND ([17] = 1) OR
([2] = 1) AND ([18] = 1) OR
([2] = 1) AND ([19] = 1) OR
([2] = 1) AND ([20] = 1) OR
([2] = 1) AND ([21] = 1) OR
([2] = 1) AND ([22] = 1) OR
([2] = 1) AND ([23] = 1) OR
([2] = 1) AND ([24] = 1) OR
([2] = 1) AND ([25] = 1) OR
([2] = 1) AND ([26] = 1) OR
([2] = 1) AND ([27] = 1) OR
([2] = 1) AND ([28] = 1) OR
([2] = 1) AND ([29] = 1) OR
([2] = 1) AND ([30] = 1) OR
([3] = 1) OR
([3] = 1) AND ([4] = 1) OR
([3] = 1) AND ([5] = 1) OR
([3] = 1) AND ([6] = 1) OR
([3] = 1) AND ([7] = 1) OR
([3] = 1) AND ([8] = 1) OR
([3] = 1) AND ([9] = 1) OR
([3] = 1) AND ([10] = 1) OR
([3] = 1) AND ([11] = 1) OR
([3] = 1) AND ([12] = 1) OR
([3] = 1) AND ([13] = 1) OR
([3] = 1) AND ([14] = 1) OR
([3] = 1) AND ([15] = 1) OR
([3] = 1) AND ([16] = 1) OR
([3] = 1) AND ([17] = 1) OR
([3] = 1) AND ([18] = 1) OR
([3] = 1) AND ([19] = 1) OR
([3] = 1) AND ([20] = 1) OR
([3] = 1) AND ([21] = 1) OR
([3] = 1) AND ([22] = 1) OR
([3] = 1) AND ([23] = 1) OR
([3] = 1) AND ([24] = 1) OR
([3] = 1) AND ([25] = 1) OR
([3] = 1) AND ([26] = 1) OR
([3] = 1) AND ([27] = 1) OR
([3] = 1) AND ([28] = 1) OR
([3] = 1) AND ([29] = 1) OR
([3] = 1) AND ([30] = 1) OR
([4] = 1) OR
([4] = 1) AND ([5] = 1) OR
([4] = 1) AND ([6] = 1) OR
([4] = 1) AND ([7] = 1) OR
([4] = 1) AND ([8] = 1) OR
([4] = 1) AND ([9] = 1) OR
([4] = 1) AND ([10] = 1) OR
([4] = 1) AND ([11] = 1) OR
([4] = 1) AND ([12] = 1) OR
([4] = 1) AND ([13] = 1) OR
([4] = 1) AND ([14] = 1) OR
([4] = 1) AND ([15] = 1) OR
([4] = 1) AND ([16] = 1) OR
([4] = 1) AND ([17] = 1) OR
([4] = 1) AND ([18] = 1) OR
([4] = 1) AND ([19] = 1) OR
([4] = 1) AND ([20] = 1) OR
([4] = 1) AND ([21] = 1) OR
([4] = 1) AND ([22] = 1) OR
([4] = 1) AND ([23] = 1) OR
([4] = 1) AND ([24] = 1) OR
([4] = 1) AND ([25] = 1) OR
([4] = 1) AND ([26] = 1) OR
([4] = 1) AND ([27] = 1) OR
([4] = 1) AND ([28] = 1) OR
([4] = 1) AND ([29] = 1) OR
([4] = 1) AND ([30] = 1) OR
([5] = 1) OR
([5] = 1) AND ([6] = 1) OR
([5] = 1) AND ([7] = 1) OR
([5] = 1) AND ([8] = 1) OR
([5] = 1) AND ([9] = 1) OR
([5] = 1) AND ([10] = 1) OR
([5] = 1) AND ([11] = 1) OR
([5] = 1) AND ([12] = 1) OR
([5] = 1) AND ([13] = 1) OR
([5] = 1) AND ([14] = 1) OR
([5] = 1) AND ([15] = 1) OR
([5] = 1) AND ([16] = 1) OR
([5] = 1) AND ([17] = 1) OR
([5] = 1) AND ([18] = 1) OR
([5] = 1) AND ([19] = 1) OR
([5] = 1) AND ([20] = 1) OR
([5] = 1) AND ([21] = 1) OR
([5] = 1) AND ([22] = 1) OR
([5] = 1) AND ([23] = 1) OR
([5] = 1) AND ([24] = 1) OR
([5] = 1) AND ([25] = 1) OR
([5] = 1) AND ([26] = 1) OR
([5] = 1) AND ([27] = 1) OR
([5] = 1) AND ([28] = 1) OR
([5] = 1) AND ([29] = 1) OR
([5] = 1) AND ([30] = 1)
.
.
. etc
.
([60] = 1) AND ([60] = 1)
ENDonecorp wrote:
> I have a storde procedure which compares one column of a table against eac
h
> of the other columns in the same table. If it finds a 'one' in each column
,
> it counts the number of 'paired data' . As there can be up to 60 columns a
nd
> 60 rows in any table , can anyone suggest a shorter , more efficient way o
f
> writing the following code? Any assistanc ewould be appreciated. Thank you
.
>
Holy moley!!! What in the world is this for? If the values of these
fields are truly limited to 1 or 0, you could probably do something like
SUM([1] * [2]) AS Expr2 instead of COUNT([1] + [2]) AS Expr2, and
eliminate the WHERE clause entirely. If [1] = 1 and [2] = 0, the SUM is
going to increase by zero (1 * 0), essentially the same effect you're
getting by counting WHERE ([1] = 1 AND [2] = 1).
Does that make sense?|||Hi There,
I hope this help
Select 1 R ,1 val1,1 val2,1 val3 , 1 val4 into dummy
insert into dummy Select 2 R ,1 val1,1 val2,1 val3 , 1 val4
insert into dummy Select 3 R ,1 val1,1 val2,1 val3 , 4 val4
Select * from dummy
Select *,
(
case when val1=1 then 1 else 0 end +
case when val2=1 then 1 else 0 end +
case when val3=1 then 1 else 0 end +
case when val4=1 then 1 else 0 end
)/2 As Pairs
From dummy
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Tracy McKibben wrote:
> onecorp wrote:
> Holy moley!!! What in the world is this for? If the values of these
> fields are truly limited to 1 or 0, you could probably do something like
> SUM([1] * [2]) AS Expr2 instead of COUNT([1] + [2]) AS Expr2, and
> eliminate the WHERE clause entirely. If [1] = 1 and [2] = 0, the SUM is
> going to increase by zero (1 * 0), essentially the same effect you're
> getting by counting WHERE ([1] = 1 AND [2] = 1).
> Does that make sense?|||Hi,
Thanks for your post!
From your description, I understand that:
A table of your database includes 60 columns and 60 rows.
You wanted to get a 60*60 matrix, in which any item ([x,y]) value
represents the count when column x equals column y and both of their values
are 1.
If I have misunderstood, please feel free to let me know.
If the column type is bit, Tracy's idea is wonderful. I also recommend you
use SUM expression to retrieve the count value.
Otherwise, in this case, the where clause can be written as "WHERE
[1]+[2]+[3]+...+[60]>0".
However if the column type is not bit and the value may be out of 0 and 1,
the where clause should be separated.
I recommend you write a function which can count by accepting the two
parameters of each coordinates.
And then you can get all items count by this way:
SELECT proc_selcount(1,1) as [1_1],proc_selcount(1,2) as
[1_2],proc_selcount(1,3) as [1_3],...,proc_selcount(60,60) as [60_60] into
MATRIX;
SELECT * FROM MATRIX;
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Can you give the table definition and the insert script for the data? I
have got the result you had mailed.
Moreover, is it always 60 columns or will it change?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi,
Since I didn't get the data, I created with a temp table which has 6
columns.
It can be extended to 60 columns or how many ever you want, just add those
columns in the pivot and unpivot expression.
Let me know if this was what you expected.
-- Insert script
Select 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] into #temp
insert into #temp Select 1 ,1 ,0 , 1,0,0
insert into #temp Select 1 ,1 ,1 , 1 ,1,1
insert into #temp Select 1 ,0 ,0 , 1,1,0
insert into #temp Select 1 ,1 ,1 , 0,0,1
insert into #temp Select 0 ,1 ,1 , 0,1,0
--Query
with cte as(
select row_id, loc, val from
(select row_number() over (order by [1]) as row_id, * from #temp) as a
unpivot (val for loc in ([1],[2],[3],[4],[5],[6])) as U
), cte1 as(
select a.loc as loc,b.loc as loc2,sum(a.val*b.val) as cnt from cte a, cte b
where a.row_id = b.row_id
and a.loc <= b.loc
group by a.loc,b.loc
)
select * from cte1
pivot ( sum(cnt) for loc2 in ([1],[2],[3],[4],[5],[6])) as P
Hope this helps.
-Omni|||Thank you to all for their assistance.
To Omnibuzz
....my apologies for the lack of a reply, but it was probably dut to time
differences etc.
I originally derived the table by using in-line SQL. I was then advised that
that was not a good idea, and hence came up with the stored procedure (one
example of my attempts being the one I posted ).
Regarding the table definition:
At the first block of code referring to NUmber [1] I used create table.
At each block thereafter, I used 'insert into table' thereby building up
each row.
To display the results, I used a gridview control to obtain the data from
the tbale created in the stored procedure and a 'sort' to get the rows in th
e
right order.
In your reply you wrote:
-- Insert script
Select 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] into #temp
insert into #temp Select 1 ,1 ,0 , 1,0,0
insert into #temp Select 1 ,1 ,1 , 1 ,1,1
insert into #temp Select 1 ,0 ,0 , 1,1,0
insert into #temp Select 1 ,1 ,1 , 0,0,1
insert into #temp Select 0 ,1 ,1 , 0,1,0
Why in the select stmt is it 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] and
not
1 [1],1 [2],1 [3] , 1 [4] , 1 [5], 1 [6] ?
What do the 'ones' and 'zeroes' mean in this exp ? insert into #temp Select
1 ,1 ,0 , 1,0,0
Dear Mr WANG,
Can you please tell me what proc_selcount means?
Thank you
"onecorp" wrote:

> I have a stored procedure which compares one column of a table against eac
h
> of the other columns in the same table. If it finds a 'one' in each column
,
> it counts the number of 'paired data' . As there can be up to 60 columns a
nd
> 60 rows in any table , can anyone suggest a shorter , more efficient way o
f
> writing the following code? Any assistance would be appreciated. Thank you
.
> ALTER PROCEDURE GetXXXXXXAll
> /*
> (
> @.IdNumber = 1
> )
> */
> AS
> Begin
> /* SET NOCOUNT ON */
> SELECT COUNT ([1]) As Expr1,
> COUNT([1] + [2]) AS Expr2, COUNT([1] + [3]) AS Expr3, COUNT([1
]
> + [4]) AS Expr4, COUNT([1] + [5]) AS Expr5,
> COUNT([1] + [6]) AS Expr6,COUNT([1] + [7]) AS Expr7, COUNT([1]
+
> [8]) AS Expr8, COUNT([1] + [9]) AS Expr9,
> COUNT([1] + [10]) AS Expr10, COUNT([1] + [11]) AS Expr11,
> COUNT([1] + [12]) AS Expr12,COUNT([1] + [13]) AS Expr13,
> COUNT([1] + [14]) AS Expr14, COUNT([1] + [15]) AS Expr15,
> COUNT([1] + [16]) AS Expr16,COUNT([1] + [17]) AS Expr17,
> COUNT([1] + [18]) AS Expr18, COUNT([1] + [19]) AS Expr19,
> COUNT([1] + [20]) AS Expr20,COUNT([1] + [21]) AS Expr21,
> COUNT([1] + [22]) AS Expr22, COUNT([1] + [23]) AS Expr23,
> COUNT([1] + [24]) AS Expr24,COUNT([1] + [25]) AS Expr25,
> COUNT([1] + [26]) AS Expr26, COUNT([1] + [27]) AS Expr27,
> COUNT([1] + [28]) AS Expr28,COUNT([1] + [29]) AS Expr29,
> COUNT([1] + [30]) AS Expr30,
> COUNT ([2]) As Expr31,
> COUNT([2] + [3]) AS Expr32, COUNT([2] + [4]) AS Expr33,
> COUNT([2] + [5]) AS Expr34,
> COUNT([2] + [6]) AS Expr35,COUNT([2] + [7]) AS Expr36, COUNT([
2]
> + [8]) AS Expr37, COUNT([2] + [9]) AS Expr38,
> COUNT([2] + [10]) AS Expr39, COUNT([2] + [11]) AS Expr40,
> COUNT([2] + [12]) AS Expr41,COUNT([2] + [13]) AS Expr42,
> COUNT([2] + [14]) AS Expr43, COUNT([2] + [15]) AS Expr44,
> COUNT([2] + [16]) AS Expr45,COUNT([2] + [17]) AS Expr46,
> COUNT([2] + [18]) AS Expr47, COUNT([2] + [19]) AS Expr48,
> COUNT([2] + [20]) AS Expr49,COUNT([2] + [21]) AS Expr50,
> COUNT([2] + [22]) AS Expr51, COUNT([2] + [23]) AS Expr52,
> COUNT([2] + [24]) AS Expr53,COUNT([2] + [25]) AS Expr54,
> COUNT([2] + [26]) AS Expr55, COUNT([2] + [27]) AS Expr56,
> COUNT([2] + [28]) AS Expr57,COUNT([2] + [29]) AS Expr58,
> COUNT([2] + [30]) AS Expr59,
> COUNT ([3]) As Expr60,
> COUNT([3] + [4]) AS Expr61, COUNT([3] + [5]) AS Expr62,
> COUNT([3] + [6]) AS Expr63,COUNT([3] + [7]) AS Expr64, COUNT([
3]
> + [8]) AS Expr65, COUNT([3] + [9]) AS Expr66,
> COUNT([3] + [10]) AS Expr67, COUNT([3] + [11]) AS Expr68,
> COUNT([3] + [12]) AS Expr69,COUNT([3] + [13]) AS Expr70,
> COUNT([3] + [14]) AS Expr71, COUNT([3] + [15]) AS Expr72,
> COUNT([3] + [16]) AS Expr73,COUNT([3] + [17]) AS Expr74,
> COUNT([3] + [18]) AS Expr75, COUNT([3] + [19]) AS Expr76,
> COUNT([3] + [20]) AS Expr77,COUNT([3] + [21]) AS Expr78,
> COUNT([3] + [22]) AS Expr79, COUNT([3] + [23]) AS Expr80,
> COUNT([3] + [24]) AS Expr81,COUNT([3] + [25]) AS Expr82,
> COUNT([3] + [26]) AS Expr83, COUNT([3] + [27]) AS Expr84,
> COUNT([3] + [28]) AS Expr85,COUNT([3] + [29]) AS Expr86,
> COUNT([3] + [30]) AS Expr87,
> COUNT ([4]) As Expr88,
> COUNT([4] + [5]) AS Expr89,
> COUNT([4] + [6]) AS Expr90,COUNT([4] + [7]) AS Expr91, COUNT([
4]
> + [8]) AS Expr92, COUNT([4] + [9]) AS Expr93,
> COUNT([4] + [10]) AS Expr94, COUNT([4] + [11]) AS Expr95,
> COUNT([4] + [12]) AS Expr96,COUNT([4] + [13]) AS Expr97,
> COUNT([4] + [14]) AS Expr98, COUNT([4] + [15]) AS Expr99,
> COUNT([4] + [16]) AS Expr100,COUNT([4] + [17]) AS Expr101,
> COUNT([4] + [18]) AS Expr102, COUNT([4] + [19]) AS Expr103,
> COUNT([4] + [20]) AS Expr104,COUNT([4] + [21]) AS Expr105,
> COUNT([4] + [22]) AS Expr106, COUNT([4] + [23]) AS Expr107,
> COUNT([4] + [24]) AS Expr108,COUNT([4] + [25]) AS Expr109,
> COUNT([4] + [26]) AS Expr110, COUNT([4] + [27]) AS Expr111,
> COUNT([4] + [28]) AS Expr112,COUNT([4] + [29]) AS Expr113,
> COUNT([4] + [30]) AS Expr114,
> COUNT ([5]) As Expr115,
> COUNT([5] + [6]) AS Expr116,COUNT([5] + [7]) AS Expr117,
> COUNT([5] + [8]) AS Expr118, COUNT([5] + [9]) AS Expr119,
> COUNT([5] + [10]) AS Expr120, COUNT([5] + [11]) AS Expr121,
> COUNT([5] + [12]) AS Expr122,COUNT([5] + [13]) AS Expr123,
> COUNT([5] + [14]) AS Expr124, COUNT([5] + [15]) AS Expr125,
> COUNT([5] + [16]) AS Expr126,COUNT([5] + [17]) AS Expr127,
> COUNT([5] + [18]) AS Expr128, COUNT([5] + [19]) AS Expr129,
> COUNT([5] + [20]) AS Expr130,COUNT([5] + [21]) AS Expr131,
> COUNT([5] + [22]) AS Expr132, COUNT([5] + [23]) AS Expr133,
> COUNT([5] + [24]) AS Expr134,COUNT([5] + [25]) AS Expr135,
> COUNT([5] + [26]) AS Expr136, COUNT([5] + [27]) AS Expr137,
> COUNT([5] + [28]) AS Expr138,COUNT([5] + [29]) AS Expr139,
> COUNT([5] + [30]) AS Expr140
> .
> .
> . etc
> .
> .
> COUNT([60] + [60]) AS Expr1859
>
> FROM XXXXXXFrequencyTable
> WHERE ([1] = 1) OR
> ([1] = 1) AND ([2] = 1) OR
> ([1] = 1) AND ([3] = 1) OR
> ([1] = 1) AND ([4] = 1) OR
> ([1] = 1) AND ([5] = 1) OR
> ([1] = 1) AND ([6] = 1) OR
> ([1] = 1) AND ([7] = 1) OR
> ([1] = 1) AND ([8] = 1) OR
> ([1] = 1) AND ([9] = 1) OR
> ([1] = 1) AND ([10] = 1) OR
> ([1] = 1) AND ([11] = 1) OR
> ([1] = 1) AND ([12] = 1) OR
> ([1] = 1) AND ([13] = 1) OR
> ([1] = 1) AND ([14] = 1) OR
> ([1] = 1) AND ([15] = 1) OR
> ([1] = 1) AND ([16] = 1) OR
> ([1] = 1) AND ([17] = 1) OR
> ([1] = 1) AND ([18] = 1) OR
> ([1] = 1) AND ([19] = 1) OR
> ([1] = 1) AND ([20] = 1) OR
> ([1] = 1) AND ([21] = 1) OR
> ([1] = 1) AND ([22] = 1) OR
> ([1] = 1) AND ([23] = 1) OR
> ([1] = 1) AND ([24] = 1) OR
> ([1] = 1) AND ([25] = 1) OR
> ([1] = 1) AND ([26] = 1) OR
> ([1] = 1) AND ([27] = 1) OR
> ([1] = 1) AND ([28] = 1) OR
> ([1] = 1) AND ([29] = 1) OR
> ([1] = 1) AND ([30] = 1) OR
> ([2] = 1) OR
> ([2] = 1) AND ([3] = 1) OR
> ([2] = 1) AND ([4] = 1) OR
> ([2] = 1) AND ([5] = 1) OR
> ([2] = 1) AND ([6] = 1) OR
> ([2] = 1) AND ([7] = 1) OR
> ([2] = 1) AND ([8] = 1) OR
> ([2] = 1) AND ([9] = 1) OR
> ([2] = 1) AND ([10] = 1) OR
> ([2] = 1) AND ([11] = 1) OR
> ([2] = 1) AND ([12] = 1) OR
> ([2] = 1) AND ([13] = 1) OR
> ([2] = 1) AND ([14] = 1) OR
> ([2] = 1) AND ([15] = 1) OR
> ([2] = 1) AND ([16] = 1) OR
> ([2] = 1) AND ([17] = 1) OR
> ([2] = 1) AND ([18] = 1) OR
> ([2] = 1) AND ([19] = 1) OR
> ([2] = 1) AND ([20] = 1) OR
> ([2] = 1) AND ([21] = 1) OR
> ([2] = 1) AND ([22] = 1) OR
> ([2] = 1) AND ([23] = 1) OR
> ([2] = 1) AND ([24] = 1) OR
> ([2] = 1) AND ([25] = 1) OR
> ([2] = 1) AND ([26] = 1) OR
> ([2] = 1) AND ([27] = 1) OR
> ([2] = 1) AND ([28] = 1) OR
> ([2] = 1) AND ([29] = 1) OR
> ([2] = 1) AND ([30] = 1) OR
> ([3] = 1) OR
> ([3] = 1) AND ([4] = 1) OR
> ([3] = 1) AND ([5] = 1) OR
> ([3] = 1) AND ([6] = 1) OR
> ([3] = 1) AND ([7] = 1) OR
> ([3] = 1) AND ([8] = 1) OR
> ([3] = 1) AND ([9] = 1) OR
> ([3] = 1) AND ([10] = 1) OR
> ([3] = 1) AND ([11] = 1) OR
> ([3] = 1) AND ([12] = 1) OR
> ([3] = 1) AND ([13] = 1) OR
> ([3] = 1) AND ([14] = 1) OR
> ([3] = 1) AND ([15] = 1) OR
> ([3] = 1) AND ([16] = 1) OR
> ([3] = 1) AND ([17] = 1) OR
> ([3] = 1) AND ([18] = 1) OR
> ([3] = 1) AND ([19] = 1) OR
> ([3] = 1) AND ([20] = 1) OR
> ([3] = 1) AND ([21] = 1) OR
> ([3] = 1) AND ([22] = 1) OR
> ([3] = 1) AND ([23] = 1) OR
> ([3] = 1) AND ([24] = 1) OR
> ([3] = 1) AND ([25] = 1) OR
> ([3] = 1) AND ([26] = 1) OR
> ([3] = 1) AND ([27] = 1) OR
> ([3] = 1) AND ([28] = 1) OR
> ([3] = 1) AND ([29] = 1) OR
> ([3] = 1) AND ([30] = 1) OR
> ([4] = 1) OR
> ([4] = 1) AND ([5] = 1) OR
> ([4] = 1) AND ([6] = 1) OR
> ([4] = 1) AND ([7] = 1) OR
> ([4] = 1) AND ([8] = 1) OR
> ([4] = 1) AND ([9] = 1) OR
> ([4] = 1) AND ([10] = 1) OR
> ([4] = 1) AND ([11] = 1) OR
> ([4] = 1) AND ([12] = 1) OR
> ([4] = 1) AND ([13] = 1) OR
> ([4] = 1) AND ([14] = 1) OR
> ([4] = 1) AND ([15] = 1) OR
> ([4] = 1) AND ([16] = 1) OR
> ([4] = 1) AND ([17] = 1) OR
> ([4] = 1) AND ([18] = 1) OR
> ([4] = 1) AND ([19] = 1) OR
> ([4] = 1) AND ([20] = 1) OR
> ([4] = 1) AND ([21] = 1) OR
> ([4] = 1) AND ([22] = 1) OR
> ([4] = 1) AND ([23] = 1) OR
> ([4] = 1) AND ([24] = 1) OR
> ([4] = 1) AND ([25] = 1) OR
> ([4] = 1) AND ([26] = 1) OR
> ([4] = 1) AND ([27] = 1) OR
> ([4] = 1) AND ([28] = 1) OR
> ([4] = 1) AND ([29] = 1) OR
> ([4] = 1) AND ([30] = 1) OR
> ([5] = 1) OR
> ([5] = 1) AND ([6] = 1) OR
> ([5] = 1) AND ([7] = 1) OR
> ([5] = 1) AND ([8] = 1) OR
> ([5] = 1) AND ([9] = 1) OR
> ([5] = 1) AND ([10] = 1) OR
> ([5] = 1) AND ([11] = 1) OR
> ([5] = 1) AND ([12] = 1) OR
> ([5] = 1) AND ([13] = 1) OR
> ([5] = 1) AND ([14] = 1) OR
> ([5] = 1) AND ([15] = 1) OR
> ([5] = 1) AND ([16] = 1) OR
> ([5] = 1) AND ([17] = 1) OR
> ([5] = 1) AND ([18] = 1) OR
> ([5] = 1) AND ([19] = 1) OR
> ([5] = 1) AND ([20] = 1) OR
> ([5] = 1) AND ([21] = 1) OR
> ([5] = 1) AND ([22] = 1) OR
> ([5] = 1) AND ([23] = 1) OR
> ([5] = 1) AND ([24] = 1) OR
> ([5] = 1) AND ([25] = 1) OR
> ([5] = 1) AND ([26] = 1) OR
> ([5] = 1) AND ([27] = 1) OR
> ([5] = 1) AND ([28] = 1) OR
> ([5] = 1) AND ([29] = 1) OR
> ([5] = 1) AND ([30] = 1)
> .
> .
> . etc
> .
> ([60] = 1) AND ([60] = 1)
> END|||The way I understood...
The values in the columns 1 through 6 can have 0s or 1s.
And you want to find the count of all possible pair of columns where both
have the value 1. Its just a sample data. You can have it as all 1s or all 0
s.
Did you run the code and check?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi,
Thanks for your response.
The proc_selcount is a custom function that can count any one or two
columns equaling 1.
However this name is not canonical, you can specify the name according to
your standard.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Itens of the report in lines and not columns

Hi,

It would like that my report was in the following way, for example:

Product A

10 Itens 00001 00002 00003 00004 00005

00006 00007 00008 00009 00010

Product B

5 Itens 00020 00021 00022 00023 00024

This is possible?

Thank you

Sandro Borsatto

Look at this blog entry.

http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

It's a computed column processing bug?

Hello everybody!

I have question about indexed and not indexed Persisted columns on sql server 2005. It's a bug?

First?, my version of SQL Server is

Microsoft SQL Server 2005 - 9.00.3186.00 (Intel X86) Aug 11 2007 03:13:58 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Now I create two tables and try four select queries:

Code Snippet

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET NUMERIC_ROUNDABORT OFF

SET QUOTED_IDENTIFIER ON

GO

create table t1 (id int primary key, id_bigint as cast(id as bigint))

GO

create table t2 (id int primary key, id_bigint as cast(id as bigint) persisted)

GO

select * from t1 -- (1)

-- Clustered index scan with two times Compute Scalar

GO

select * from t2 -- (2)

-- Clustered index scan with one times Compute Scalar

GO

create index IX_t2 on t2 (id_bigint)

GO

select * from t2 -- (3)

-- Index Scan with one times Compute Scalar

GO

select * from t2 where id_bigint = 0 -- (4)

-- Index Seek with one times Compute Scalar

GO

drop table t1

GO

drop table t2

GO

SET ANSI_PADDING OFF

1. I don't understand why access to computed column raise scalar computation wto times?

2. I don't understand why access to persisted computed column raise any scalar computation?

3. I don't understand why access to persisted computed column over index required any scalar computations?

Can anyone from Microsoft SQL Server Team told me about this mistake?

It's a BUG or I incorrect understand value of the "PERSISTED" word?

--

Thanks with avanced.

WBR, Roman S. Golubin

grominc[at]gmail.com

Bug request on SQL Server 2005 Feedback for this icident.

To solve this problem while Microsoft SQL Server Team not fix this bug, use update with triggers instead persisted computed columns.

--

WBR, Roman S. Golubin

73! GL! RA1OGE/3

|||

It works.

Use 'WITH SCHEMABINDING'

Courtesy of Kent Tegels!

It's a computed column processing bug?

Hello everybody!

I have question about indexed and not indexed Persisted columns on sql server 2005. It's a bug?

First?, my version of SQL Server is

Microsoft SQL Server 2005 - 9.00.3186.00 (Intel X86) Aug 11 2007 03:13:58 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Now I create two tables and try four select queries:

Code Snippet

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET NUMERIC_ROUNDABORT OFF

SET QUOTED_IDENTIFIER ON

GO

create table t1 (id int primary key, id_bigint as cast(id as bigint))

GO

create table t2 (id int primary key, id_bigint as cast(id as bigint) persisted)

GO

select * from t1 -- (1)

-- Clustered index scan with two times Compute Scalar

GO

select * from t2 -- (2)

-- Clustered index scan with one times Compute Scalar

GO

create index IX_t2 on t2 (id_bigint)

GO

select * from t2 -- (3)

-- Index Scan with one times Compute Scalar

GO

select * from t2 where id_bigint = 0 -- (4)

-- Index Seek with one times Compute Scalar

GO

drop table t1

GO

drop table t2

GO

SET ANSI_PADDING OFF

1. I don't understand why access to computed column raise scalar computation wto times?

2. I don't understand why access to persisted computed column raise any scalar computation?

3. I don't understand why access to persisted computed column over index required any scalar computations?

Can anyone from Microsoft SQL Server Team told me about this mistake?

It's a BUG or I incorrect understand value of the "PERSISTED" word?

--

Thanks with avanced.

WBR, Roman S. Golubin

grominc[at]gmail.com

Bug request on SQL Server 2005 Feedback for this icident.

To solve this problem while Microsoft SQL Server Team not fix this bug, use update with triggers instead persisted computed columns.

--

WBR, Roman S. Golubin

73! GL! RA1OGE/3

|||

It works.

Use 'WITH SCHEMABINDING'

Courtesy of Kent Tegels!

It is possible with cursor?

I need to fill a cursor with 3 columns.
A want to use a Select sprocs (for re-use de code), but this sproc return 15 columns and the 3 a need was not the 3 frist. :confused:
Do I need to map the 15 columns with 15 variables locally? Or they have a way easier?
Thankscan u post the code? some one here might come up with a better solution|||DECLARE cuTEST CURSOR FOR
ItemsSEL

OPEN cuTEST

FETCH NEXT FROM cuTEST
INTO @.col1, @.col2, @.col3, @.col4, @.col5,....

but I need only col2, col10 and col14
ItemsSEL is my Strore Procedure for my complexe Select Use many place in my application ( where a need all result columns...)|||Would it be possible to redesign the ItemsSel procedure into a view? How complex is this procedure?

Issues with Image data

Hello.
We're running SQL Server 2000.
I am considering creating Image columns for one of our databases. Currently
we have a varchar field that holds the path to the actual image which is
stored on an NTFS file system. The average image size is less than 256K.
What issues arise when considering Backup/Restore and Database recovery.
Any other insights are welcome.
Thanks in advance,
Mike"Mike Lopez" <MichaelLopez@.inds.com> wrote in message
news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hello.
> We're running SQL Server 2000.
> I am considering creating Image columns for one of our databases.
> Currently we have a varchar field that holds the path to the actual image
> which is stored on an NTFS file system. The average image size is less
> than 256K.
> What issues arise when considering Backup/Restore and Database recovery.
> Any other insights are welcome.
> Thanks in advance,
> Mike
>
>
There are all kinds of performance issues to think about.
1. Someone may correct me on this one, but.. Each image will store a 16
byte pointer in the row in your regular base table. That pointer will then
point to a private 8k data page where your image will be stored. That means
that each image will take up at least 8k of database storage space and in
your case, a chain of 32 8k data pages that will need to be read from and
written to. That is a killer on disk i/o for SQL Server.
2. Having those images in the same table *could* really slow down the table
on your joins. You may wish to place the images in a 1 to 1 relationship
in a separate table.
2a. If you place those images in a separate table, you *could* put that one
table on it's own filegroup and then perform filegroup backups and restores.
This may improve your backup and recovery strategy especially if the images
don't change often. (eg. You would back up the images filegroup far less.
Restores would be quicker as you only need to do the affected filegroups and
TLogs.)
3. If you use the images frequently (reads/writes) put them on the fastest
read/write drives as possible. I would suggest a RAID 1 (striped - no
parity) and mirror that drive (RAID 0) if you need that type of failsafe.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you Rick. Yes, that helps.
I'm leaning towards keeping the current system intact (image file in a
directory, image file's path in table column). I feel there's too much
overhead involved. I also don't like the idea that the image would not be
directly viewable by third-party image viewers.
Thanks again,
Mike
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eoFr3FO7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Mike Lopez" <MichaelLopez@.inds.com> wrote in message
> news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
>
> There are all kinds of performance issues to think about.
> 1. Someone may correct me on this one, but.. Each image will store a 16
> byte pointer in the row in your regular base table. That pointer will
> then point to a private 8k data page where your image will be stored.
> That means that each image will take up at least 8k of database storage
> space and in your case, a chain of 32 8k data pages that will need to be
> read from and written to. That is a killer on disk i/o for SQL Server.
> 2. Having those images in the same table *could* really slow down the
> table on your joins. You may wish to place the images in a 1 to 1
> relationship in a separate table.
> 2a. If you place those images in a separate table, you *could* put that
> one table on it's own filegroup and then perform filegroup backups and
> restores. This may improve your backup and recovery strategy especially if
> the images don't change often. (eg. You would back up the images
> filegroup far less. Restores would be quicker as you only need to do the
> affected filegroups and TLogs.)
> 3. If you use the images frequently (reads/writes) put them on the
> fastest read/write drives as possible. I would suggest a RAID 1
> (striped - no parity) and mirror that drive (RAID 0) if you need that type
> of failsafe.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>sql

Issues with Image data

Hello.
We're running SQL Server 2000.
I am considering creating Image columns for one of our databases. Currently
we have a varchar field that holds the path to the actual image which is
stored on an NTFS file system. The average image size is less than 256K.
What issues arise when considering Backup/Restore and Database recovery.
Any other insights are welcome.
Thanks in advance,
Mike
"Mike Lopez" <MichaelLopez@.inds.com> wrote in message
news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hello.
> We're running SQL Server 2000.
> I am considering creating Image columns for one of our databases.
> Currently we have a varchar field that holds the path to the actual image
> which is stored on an NTFS file system. The average image size is less
> than 256K.
> What issues arise when considering Backup/Restore and Database recovery.
> Any other insights are welcome.
> Thanks in advance,
> Mike
>
>
There are all kinds of performance issues to think about.
1. Someone may correct me on this one, but.. Each image will store a 16
byte pointer in the row in your regular base table. That pointer will then
point to a private 8k data page where your image will be stored. That means
that each image will take up at least 8k of database storage space and in
your case, a chain of 32 8k data pages that will need to be read from and
written to. That is a killer on disk i/o for SQL Server.
2. Having those images in the same table *could* really slow down the table
on your joins. You may wish to place the images in a 1 to 1 relationship
in a separate table.
2a. If you place those images in a separate table, you *could* put that one
table on it's own filegroup and then perform filegroup backups and restores.
This may improve your backup and recovery strategy especially if the images
don't change often. (eg. You would back up the images filegroup far less.
Restores would be quicker as you only need to do the affected filegroups and
TLogs.)
3. If you use the images frequently (reads/writes) put them on the fastest
read/write drives as possible. I would suggest a RAID 1 (striped - no
parity) and mirror that drive (RAID 0) if you need that type of failsafe.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Thank you Rick. Yes, that helps.
I'm leaning towards keeping the current system intact (image file in a
directory, image file's path in table column). I feel there's too much
overhead involved. I also don't like the idea that the image would not be
directly viewable by third-party image viewers.
Thanks again,
Mike
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eoFr3FO7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Mike Lopez" <MichaelLopez@.inds.com> wrote in message
> news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
>
> There are all kinds of performance issues to think about.
> 1. Someone may correct me on this one, but.. Each image will store a 16
> byte pointer in the row in your regular base table. That pointer will
> then point to a private 8k data page where your image will be stored.
> That means that each image will take up at least 8k of database storage
> space and in your case, a chain of 32 8k data pages that will need to be
> read from and written to. That is a killer on disk i/o for SQL Server.
> 2. Having those images in the same table *could* really slow down the
> table on your joins. You may wish to place the images in a 1 to 1
> relationship in a separate table.
> 2a. If you place those images in a separate table, you *could* put that
> one table on it's own filegroup and then perform filegroup backups and
> restores. This may improve your backup and recovery strategy especially if
> the images don't change often. (eg. You would back up the images
> filegroup far less. Restores would be quicker as you only need to do the
> affected filegroups and TLogs.)
> 3. If you use the images frequently (reads/writes) put them on the
> fastest read/write drives as possible. I would suggest a RAID 1
> (striped - no parity) and mirror that drive (RAID 0) if you need that type
> of failsafe.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Issues with Image data

Hello.
We're running SQL Server 2000.
I am considering creating Image columns for one of our databases. Currently
we have a varchar field that holds the path to the actual image which is
stored on an NTFS file system. The average image size is less than 256K.
What issues arise when considering Backup/Restore and Database recovery.
Any other insights are welcome.
Thanks in advance,
Mike"Mike Lopez" <MichaelLopez@.inds.com> wrote in message
news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hello.
> We're running SQL Server 2000.
> I am considering creating Image columns for one of our databases.
> Currently we have a varchar field that holds the path to the actual image
> which is stored on an NTFS file system. The average image size is less
> than 256K.
> What issues arise when considering Backup/Restore and Database recovery.
> Any other insights are welcome.
> Thanks in advance,
> Mike
>
>
There are all kinds of performance issues to think about.
1. Someone may correct me on this one, but.. Each image will store a 16
byte pointer in the row in your regular base table. That pointer will then
point to a private 8k data page where your image will be stored. That means
that each image will take up at least 8k of database storage space and in
your case, a chain of 32 8k data pages that will need to be read from and
written to. That is a killer on disk i/o for SQL Server.
2. Having those images in the same table *could* really slow down the table
on your joins. You may wish to place the images in a 1 to 1 relationship
in a separate table.
2a. If you place those images in a separate table, you *could* put that one
table on it's own filegroup and then perform filegroup backups and restores.
This may improve your backup and recovery strategy especially if the images
don't change often. (eg. You would back up the images filegroup far less.
Restores would be quicker as you only need to do the affected filegroups and
TLogs.)
3. If you use the images frequently (reads/writes) put them on the fastest
read/write drives as possible. I would suggest a RAID 1 (striped - no
parity) and mirror that drive (RAID 0) if you need that type of failsafe.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you Rick. Yes, that helps.
I'm leaning towards keeping the current system intact (image file in a
directory, image file's path in table column). I feel there's too much
overhead involved. I also don't like the idea that the image would not be
directly viewable by third-party image viewers.
Thanks again,
Mike
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eoFr3FO7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Mike Lopez" <MichaelLopez@.inds.com> wrote in message
> news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
>> Hello.
>> We're running SQL Server 2000.
>> I am considering creating Image columns for one of our databases.
>> Currently we have a varchar field that holds the path to the actual image
>> which is stored on an NTFS file system. The average image size is less
>> than 256K.
>> What issues arise when considering Backup/Restore and Database recovery.
>> Any other insights are welcome.
>> Thanks in advance,
>> Mike
>>
>
> There are all kinds of performance issues to think about.
> 1. Someone may correct me on this one, but.. Each image will store a 16
> byte pointer in the row in your regular base table. That pointer will
> then point to a private 8k data page where your image will be stored.
> That means that each image will take up at least 8k of database storage
> space and in your case, a chain of 32 8k data pages that will need to be
> read from and written to. That is a killer on disk i/o for SQL Server.
> 2. Having those images in the same table *could* really slow down the
> table on your joins. You may wish to place the images in a 1 to 1
> relationship in a separate table.
> 2a. If you place those images in a separate table, you *could* put that
> one table on it's own filegroup and then perform filegroup backups and
> restores. This may improve your backup and recovery strategy especially if
> the images don't change often. (eg. You would back up the images
> filegroup far less. Restores would be quicker as you only need to do the
> affected filegroups and TLogs.)
> 3. If you use the images frequently (reads/writes) put them on the
> fastest read/write drives as possible. I would suggest a RAID 1
> (striped - no parity) and mirror that drive (RAID 0) if you need that type
> of failsafe.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Issue working with symmetric key for encryption

Please be gentle...I am very new to working with SQL.

I have the need to encrypt my columns in SQL 2005. I have created a symmetric key 'SecureKey' as well as a secure certificate 'SecureCert'

I have ran the script to create the key and the certificate successfully. When I run "select * sys.symmetic_keys;" the key shows up...when I run "select * sys.certificates;" the cert shows up.

Here is my issue, in the security folder under my database, these two things do not show up in the appropriate folders. Also when I run my encryption scripts, I am getting an error of

"msg 15151, Level 16, State 1, Line 3

cannot find the symmetric key 'SecureKey', because it does not exist or you do not have permission"

Am I missing something?

Thanks

Did you open the symmetric key before you encrypt data? If the symmetric key is not opened you wont be able to work with it ... anyway here's how it's supposed to be done: (I'm in work so i cant test if code is true but if something is screwed up go to this site:

http://blogs.msdn.com/lcris/search.aspx?q=encryption+sql+server+2005&p=1

this is where i got most of info when i started)

create database demo;

use demo;

-- create the database master key

--since you created certificate already then master key must be set already
create master key encryption by password = 'Pufd&s@.))%';

-- create a simple employee table
create table t_employees (id int primary key, name varchar(300), salary varbinary(300));

-- create a certificate to protect the symmetric key that will encrypt the data
-- the certificate will be encrypted by the database master key
create certificate cert_sk_admin with subject = 'Certificate for accessing symmetric keys - for use by admin';

-- create a key to protect the employee sensitive data, in this case - the salary
create symmetric key sk_employees with algorithm = triple_des encryption by certificate cert_sk_admin;

-- open the key so that we can use it
open symmetric key sk_employees decryption by certificate cert_sk_admin;

-- verify key was opened
select * from sys.openkeys;

-- insert some data
-- we will use the id as an authenticator value to tie the salary to the employee id
insert into t_employees(id, name,salary) values ( 1,'Alice Smith', encryptbykey(key_guid('sk_employees'), '$200000'));
insert into t_employees(id,name,salary) values (2, 'Bob Jones', encryptbykey(key_guid('sk_employees'), '$100000'));

-- see the result; salary is encrypted
select * from t_employees;

-- create a view to automatically do the decryption
-- note that when decrypting we specify that the id should be used as authenticator
create view v_employees as select id, name, convert(varchar(10), decryptbykey(salary)) as salary from t_employees;

-- create another view for charles that will automatically open the key using his certificate and do the decryption
create view v_employees_auto as select id, name, convert(varchar(10), decryptbykeyautocert(cert_id('cert_sk_charles'), salary)) as salary from t_employees;

-- see the result, the decrypted data is available
select * from v_employees;

-- now close the key
close all symmetric keys;

-- verify key was closed
select * from sys.openkeys;

-- use the view that will automatically open the key
select * from v_employees_auto;

-- note that the key is also automatically closed by the above query
-- it is only opened for the duration of the query
select * from sys.openkeys;

|||

Something I have recently learned: if you are testing as a low privileged user, then you must grant privileges to that use to see the key and cert. Something like:

GRANT CONTROL ON CERTIFICATE::[cert_SecretTable_SecretData_Key] TO [user_low_priv]

GRANT VIEW DEFINITION ON SYMMETRIC KEY::[SecretTable_SecretData_Key] TO [user_low_priv]

I have read it is better to use a view, but am having some problems of my own...

this snippet is from

http://blogs.technet.com/keithcombs/archive/2005/11/24/415079.aspx

|||

Thanks for the replies...I now have the key and cert where they belong and they have started working...after I installed SP2.

NOW, I have issues with my decryption. I have ran my scripts to encrypt the data and it is working. I also ran the script to decrypt, and it worked once. I have tried to repeat the process, but with no luck... when I run the decrypt scipt it returns nothing...not a null or anything. I don't understand.

Here is my encryption

I open the key and the certificate

SELECT * FROM dbo.database

GO

UPDATE database

SET Value = EncryptByKey(Key_GUID('KeyName'), Value);

GO

close symmetric key KeyName;

Now to decrypt

SELECT Value

AS 'Encrypted Value',

CONVERT(varchar, DecryptByKey(Value))

AS 'Decrypted Value'

FROM database;

CLOSE SYMMETRIC KEY KeyName;

|||

Hi, it looks like you are closing your symmetric key after the encryption, which is good practice. To perform the decryption, however, you will need to re-open your symmetric key. Thus before you perform your SELECT, you should once again run the OPEN SYMMETRIC KEY ... statement.

Please let us know if you run into any trouble with this and we will try to answer any questions you have.

Thanks,

Sung

|||

Sung,

Thanks for the reply...I am sorry, I just forgot to put that part of the script in there. I am opening and closing the Key on the decrypt also.

Ron

|||

In:

SELECT Value

AS 'Encrypted Value',

CONVERT(varchar, DecryptByKey(Value))

AS 'Decrypted Value'

FROM database;

you need to specifi length of varchar like this:

CONVERT(varchar(50), DecryptByKey(Value))

or something...

Issue with Identity Columns after replication Changes

Dear all,

We have an application that use merge replication between MSDE and Devices with SQL CE.

Due to a major application changes, we have to change our replication on all our workstation. Our process is the following:

- drop current replication

- recreate our replication

After first check our replication seems to work but after some test we have identify that all identity ranges have been reset on the workstation. As side effect, device start to reuse existing range and also existing value in the range.

- Have you ever encounter this type of issue?

- What can create it?

- Is our approach fully inappropriate?

Please let me know if you need more information,

Best regards,

I have a client (SQL2000<->SqlCE) that is experiencing similar problems. We have been unable to determine a cause as yet.

Symptoms:

-"duplicate key on insert" errors during replication for one table (two subscribers appearing to be sharing the same number range)

-"duplicate key on insert" error with a back-end process inserting data into a publisher table (diff to the one above)

The tables are using automatic identity range management and have been synchronising happily for a long time prior to this.

Does anyone know of any actions that might cause the automatic identity range management to go screwy like this?

|||You can try re-adjusting your identity ranges, look at sp_adjustpublisheridentityrange. Otherwise i'm not sure what could have caused the issue, maybe someone manually reset the identity seed on the dbs?

Issue with Identity Columns after replication Changes

Dear all,

We have an application that use merge replication between MSDE and Devices with SQL CE.

Due to a major application changes, we have to change our replication on all our workstation. Our process is the following:

- drop current replication

- recreate our replication

After first check our replication seems to work but after some test we have identify that all identity ranges have been reset on the workstation. As side effect, device start to reuse existing range and also existing value in the range.

- Have you ever encounter this type of issue?

- What can create it?

- Is our approach fully inappropriate?

Please let me know if you need more information,

Best regards,

I have a client (SQL2000<->SqlCE) that is experiencing similar problems. We have been unable to determine a cause as yet.

Symptoms:

-"duplicate key on insert" errors during replication for one table (two subscribers appearing to be sharing the same number range)

-"duplicate key on insert" error with a back-end process inserting data into a publisher table (diff to the one above)

The tables are using automatic identity range management and have been synchronising happily for a long time prior to this.

Does anyone know of any actions that might cause the automatic identity range management to go screwy like this?

|||You can try re-adjusting your identity ranges, look at sp_adjustpublisheridentityrange. Otherwise i'm not sure what could have caused the issue, maybe someone manually reset the identity seed on the dbs?

Issue with Frreezing columns in Reporting services 2005

I have tried to freeze the leftmost column of my report , by seetting the fixedheader proeprty to 'TRUE' For that column. The columns gets frozen. I also set the background color of that column to 'WHITE' Color. However, when I try to scroll left or right, the next columns seem to overlap to the first column...

ANy ideas how to fix this?

It looks like some of the problem lies with having multiple groups and multiple colors. I have been trying to resolve the same issue.|||

Would you please either attach or send me email me a screenshot so I can better see what is going on? We have some fixed header fixes for the HTML renderer in SP2 and one or two more in the latest Cumulative Update so make sure you are at least running SP2.

Thanks.

|||

I beleive we are runnning SP2 THis is the SP Version, we have, ie

Microsoft SQL Server Reporting Services Version 9.00.3042.00

Is this SP2?

Here is the snapshot- See how the columns are NOT aligned.

Microsoft SQL Server Reporting Services Version 9.00.3042.00 ...

I could not find an option, to attach the screenshot here in this post. Can you pl forward me ur email, so I can email u the screenshot.

Thanks.

|||

It's on my profile: donovans at microsoft dot com

Thanks.

|||

Tarana sent me the information via email which helped me confirm this is a known issue with SQL Server 2005 SP2. A fix has been created and will be available in the next cumulative update due later this month. When it is available, you can find it at http://support.microsoft.com/ph/2855. Note that the build 3161 is the previous update and does not contain this fix.

Thanks.

|||

This update is now available at http://support.microsoft.com/kb/936305. The title of the specific fix is ‘After you install SQL Server 2005 Service Pack 2, table rows in a SQL Server 2005 Reporting Services report are misaligned to the FixedHeader column if the UserSort feature is enabled in the report’.

Issue with CONTAINSTABLE statement

I am using the following query to search all columns in the 'dashboard'
table for the value 'Meets':

SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,
'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]

I have multiple records that contain the word 'Meets', but none are
showing up as a result of this query. Any ideas?

Also, anytime I use a space in my search condition (Meets Expectations
instead of Meets) I am getting an error:
Syntax error occurred near 'Expectations'. Expected '' in search
condition 'Meets Expectations'.

Any ideas?

Thanks in advance.GAH.. Had not run a start_full for the index. All is working now.
Thanks!

Nate wrote:

Quote:

Originally Posted by

I am using the following query to search all columns in the 'dashboard'
table for the value 'Meets':
>
SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,
'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]
>
I have multiple records that contain the word 'Meets', but none are
showing up as a result of this query. Any ideas?
>
Also, anytime I use a space in my search condition (Meets Expectations
instead of Meets) I am getting an error:
Syntax error occurred near 'Expectations'. Expected '' in search
condition 'Meets Expectations'.
>
Any ideas?
>
Thanks in advance.

Issue when exporting to Excel

Hello

I have created a SRS report which I export to Excel.
I have noticed that Excel has added additional columns and the data appears to be overlapping. (link to screenshot below).

When I attempt to create a pivot table from this data, I receive the error:
"The pivot table name is not valid. To create a pivot table report, you must
use data that is organized as a list with labelled columns. If you are changing the name of a pivot table field, you must type a new name for the field.

What have I done wrong?

http://www.adlibsoftware.com/KATHY/excel_output.bmp

I think the problem is related to merged cells.

When exporting, the Excel renderer tries to recreate the report visually as closely as possible. To arrange the various bits of your reports it often needs to merge cells.

I've been battling with this for a while and haven't found a foolproof way of dealing with it. Try to align and size your heading so that it lines up with the table cells. That should minize the amount of cell merging it does.

Issue selecting two rows from two columns

I'm trying to select two rows from two columns and while I can select the columns, the selection of multiple rows is not falling into place. I can pull one row into the report, but not two.

This is what I'm writing

SELECT name, number FROM table
WHERE name="variable1',''variable2'

Something is occurring around the , between variable1 and variable2. I know I doing something wrong, but can't figure it out. I can pull variable1 by itself just fine.

The specific error is:
sql: errorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'variable2' at line 2
number: 1064

Any ideas would be greatly appreciated. I tried searching and could not find an answer to this problem.Found the answer

SELECT name, number FROM table
WHERE name IN('var1', 'var2', 'var3')|||SELECT name, number FROM table
WHERE name in('variable1','variable2')