Print Name (field from dataset) On Every Page (Page Header)

Hello,

I am using the ReportViewer control and Client-Side Processing to display a Training Report that works great.

However, I need find a way to have one of the fields of my dataset display on every page of the report (Page Header).

I receive this message "The Value expression for the textbox FName refers to a field. Fields cannot be used in page headers or footers".

Is there a way to get this field to appear on every page of the report (preferably at the top).

Thanks for any help that you can provide me.

This is easy all what you need to to is to add ne parameters which get his value from your dataset fileds and then use this parameters in your page header or footer read this turtorial it will help you

http://technet.microsoft.com/en-us/library/bb508810.aspx

read the section titled " Assigning report parameter values using consolidated fields "

|||

hey

I told the answerin different post ( http://forums.asp.net/t/1160246.aspx ), but repeat it for you

you should define a Parameter for your report from Layout section ,then ( right click insome empty place in outeside of your report table, you'll see a context menu with 4 Items , select the Report Parameter) ,then create a Textbox inside your Header or Footer , then rightclick onthe textbox and and select Expression , then put the followingcode in it .

=Parameters!ReportFooterParameterName.Value

goodluck ,|||

I appreciate the help. I am not quite sure what to do when setting up the Report Parameters to fill this textboxt with the field from my dataset. Would you mind giving me a little guidance in this area?

I am getting the message A Value expression used for the report parameter 'Name' refers to a field. Fields cannot be used in report parameter expressions.

Thanks again!

|||

ok ,

let me describe it for you

there is 2 different type of value in reporting server ,

1 ) the filed that you provide by DataBase that will display like Filelds!DBFiledName.Value

2 ) the filed that you provide by getting information fromuser or from yourexisting Query this type of values called Parameters and will display asParameters!ReportFooterParameterName.Value

when you want to show oneparameter inHeader orFooter ,you can't useFields it means all the textbox that you put in header / footer , shouldcomplete byParameters ,

for sending parameters to header or footer , you have2 option ,

Take it FromUser or programmatically fill the Parameter , in commonly uses , we set the parameters Programatically , like

@.myParameter = "hello world!"

when I use the myParameter in Header, itwill display thehello world! ,

but if youuse the Fields inHeader or Footer it cousean error thatyou taken it .

for sending a parameter to your header , pleaseread the post above ,

Thank you , 7 Goodluck

Print hardcopy of table structure

I've just started working with SQL Server Express, and would like to print out a report listing the properties (Ie. field names, data ttypes, length, field description, etc.) of each of the tables in my database.
How do I do this?

The best way would be to use the INFORMATION_SCHEMA views and in this case the one that presents the columns definitions:

SELECT * FROM
INFORMATION_SCHEMA.COLUMNS

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks very much for your help!

Primary Key Ref Count?

I was wondering, is there any way to get something like a reference count,
or even just an "InUse" yes/no kind of field, for primary keys?
For example, i have a MARKETING_TEXT table, which simply has an ID and TEXT
columns. ID is the primary key. What i want to be able to do is find out,
for a given ID, if there are any children records. But i dont want to do it
using a SELECT COUNT(1) method, because it is a table that is shared among a
bunch of child tables. CATS, PRODS, CAT_LINKS, etc. Plus if a new table gets
added, then it is a maintenance issue to make sure to add a SEL COUNT for
that one.
What i am thinking for, is to be able to determine PK usage before letting a
user do a delete, rather than having them try to delete, and coming back
with an "Unable to delete, FK reference........" error message from the
db.
Any ideas?
Thanks in advance,
Arthur Dent.There is no automatic method to pre-check foreign key violations. You
could code something in an INSTEAD OF trigger or a stored proc but that
would still require you to reference all the related tables. You
wouldn't have to use COUNT though. EXISTS is usually more efficient:
EXISTS
(SELECT *
FROM T1
WHERE ref = ...)
OR EXISTS
(SELECT *
FROM T2
WHERE ref = ...)
OR EXISTS
(SELECT *
FROM T3
WHERE ref = ...)
..
Why don't you just catch the error message and display a more
user-friendly warning in the app? That's potentially more efficient
than checking up-front and to the user it would surely appear the same.
Your client app ought to be able to do that.
In TSQL, although you can't suppress the message you can catch it and
then act accordingly - for example to return an error result code from
an SP.
David Portas
SQL Server MVP
--|||Arthur,
There is not an ease way to do what you want, better to let sql server to
check the existence of a reference to this id. Here is a simple script that
count the references to a single column (a foreign key could be a multi
columns one) and the id's value let us cast it to varchar. If the unique or
primary key being referenced is a multi columns one, then the script should
be modified. I am also inclueding a link where you can read about the risks
of using dynamic sql.
use northwind
go
create table #t (
r_tn sysname,
r_cn sysname,
r_value sql_variant,
f_tn sysname,
f_cn sysname,
cnt bigint
)
declare @.sql nvarchar(4000)
declare @.f_tn sysname
declare @.f_cn sysname
declare @.r_tn sysname
declare @.r_cn sysname
declare @.v sql_variant
set @.r_tn = 'employees'
set @.r_cn = 'employeeid'
set @.v = N'6'
declare my_cursor cursor local static read_only
for
select
object_name(fkeyid),
col_name(fkeyid, fkey1)
from
sysreferences
where
rkeyid = object_id(@.r_tn)
and col_name(rkeyid, rkey1) = @.r_cn
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.f_tn, @.f_cn
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'select ''' + @.r_tn + N''', ''' + @.r_cn + N''', ''' +
convert(varchar(128), @.v) + N''', ''' + @.f_tn + N''', ''' + @.f_cn + N''',
count(*) from ' + quotename(@.f_tn) + N' where ' + quotename(@.f_cn) + N' = ''
'
+ convert(varchar(128), @.v) + N''''
insert into #t
execute sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t
drop table #t
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Arthur Dent" wrote:

> I was wondering, is there any way to get something like a reference count,
> or even just an "InUse" yes/no kind of field, for primary keys?
> For example, i have a MARKETING_TEXT table, which simply has an ID and TEX
T
> columns. ID is the primary key. What i want to be able to do is find out,
> for a given ID, if there are any children records. But i dont want to do i
t
> using a SELECT COUNT(1) method, because it is a table that is shared among
a
> bunch of child tables. CATS, PRODS, CAT_LINKS, etc. Plus if a new table ge
ts
> added, then it is a maintenance issue to make sure to add a SEL COUNT for
> that one.
> What i am thinking for, is to be able to determine PK usage before letting
a
> user do a delete, rather than having them try to delete, and coming back
> with an "Unable to delete, FK reference........" error message from the
> db.
> Any ideas?
> Thanks in advance,
> Arthur Dent.
>
>|||Thanks both for the help. I had a feeling it was a long shot.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:32334D22-327B-49C1-99BB-403EF8DD8545@.microsoft.com...
> Arthur,
> There is not an ease way to do what you want, better to let sql server to
> check the existence of a reference to this id. Here is a simple script
> that
> count the references to a single column (a foreign key could be a multi
> columns one) and the id's value let us cast it to varchar. If the unique
> or
> primary key being referenced is a multi columns one, then the script
> should
> be modified. I am also inclueding a link where you can read about the
> risks
> of using dynamic sql.
> use northwind
> go
> create table #t (
> r_tn sysname,
> r_cn sysname,
> r_value sql_variant,
> f_tn sysname,
> f_cn sysname,
> cnt bigint
> )
> declare @.sql nvarchar(4000)
> declare @.f_tn sysname
> declare @.f_cn sysname
> declare @.r_tn sysname
> declare @.r_cn sysname
> declare @.v sql_variant
> set @.r_tn = 'employees'
> set @.r_cn = 'employeeid'
> set @.v = N'6'
> declare my_cursor cursor local static read_only
> for
> select
> object_name(fkeyid),
> col_name(fkeyid, fkey1)
> from
> sysreferences
> where
> rkeyid = object_id(@.r_tn)
> and col_name(rkeyid, rkey1) = @.r_cn
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.f_tn, @.f_cn
> if @.@.error <> 0 or @.@.fetch_status <> 0 break
> set @.sql = N'select ''' + @.r_tn + N''', ''' + @.r_cn + N''', ''' +
> convert(varchar(128), @.v) + N''', ''' + @.f_tn + N''', ''' + @.f_cn + N''',
> count(*) from ' + quotename(@.f_tn) + N' where ' + quotename(@.f_cn) + N' =
> '''
> + convert(varchar(128), @.v) + N''''
> insert into #t
> execute sp_executesql @.sql
> end
> close my_cursor
> deallocate my_cursor
> select * from #t
> drop table #t
> go
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
>
> "Arthur Dent" wrote:
>

Primary Key falling short

If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:

> Plz tell me what to do if Primary key fall short and execed the max size o
f
> the datatype like integer with Identity increment option.Plz tell me what to do if Primary key fall short and execed the max size of
the datatype like integer with Identity increment option.|||If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:

> Plz tell me what to do if Primary key fall short and execed the max size o
f
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.

Primary Key Convert from Non-Cluster to Cluster Index

How do I convert a primary key field that was created with a non-cluster
index to a
cluster index?
Please help me complete this task.
Thank You,
Script all foreign keys that refer to the PK.
Script all nonclustered indexes
Drop all Foreign keys
Drop all nonclustered indexes
Drop the PK
Create the PK as a clustered index
Create the other non-clustered indexes from the earlier script
Create the foreign key references from the earlier script.
Test this at least twice on a development/test environment.
I recently did this on a table with 26 foreign key references. There is no
short cut.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
> How do I convert a primary key field that was created with a non-cluster
> index to a
> cluster index?
> Please help me complete this task.
> Thank You,
>
>
|||I believe that you can go from nc to cl for a PK or UQ constraint using CREATE INDEX ... WITH
DROP_EXISTING. It is the other way (cl to nc) that you cannot do using DROP_EXISTING. I'd test it,
but I've been working straight for 15 hours now, so time for some sleep... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is no short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>
|||Ahh, come to think about it, the FK's most probably have to be dropped even when using
DROP_EXISTING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is no short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>
|||It was from NC to CL that my latest script covered. 26 Foreign Keys.
Arrgh.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:umz77K86FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I believe that you can go from nc to cl for a PK or UQ constraint using
>CREATE INDEX ... WITH DROP_EXISTING. It is the other way (cl to nc) that
>you cannot do using DROP_EXISTING. I'd test it, but I've been working
>straight for 15 hours now, so time for some sleep... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
>
|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Ahh, come to think about it, the FK's most probably have to be dropped even when using
>DROP_EXISTING.
But won't the EM do all the voodoo for you?
J.
|||> But won't the EM do all the voodoo for you?
Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create the
index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
WITH DROP_EXISTING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> But won't the EM do all the voodoo for you?
> J.
>
|||Your correct, all the Index Tuning Wizard recommends is to drop all indexes
except the cluster index. This is not much help.
What is a good way to analysis tuning indexes?
Thank You,
"Tibor Karaszi" wrote:

> Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create the
> index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
> WITH DROP_EXISTING.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
>
>
|||> What is a good way to analysis tuning indexes?
IMO, the brain. Know the datamodel, the data and work the query and execution plan. ITW can be some
input, but not a replacement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:514AC714-27B8-424F-9890-261FCE34A769@.microsoft.com...[vbcol=seagreen]
> Your correct, all the Index Tuning Wizard recommends is to drop all indexes
> except the cluster index. This is not much help.
> What is a good way to analysis tuning indexes?
> Thank You,
>
> "Tibor Karaszi" wrote:

Primary Key Convert from Non-Cluster to Cluster Index

How do I convert a primary key field that was created with a non-cluster
index to a
cluster index?
Please help me complete this task.
Thank You,Script all foreign keys that refer to the PK.
Script all nonclustered indexes
Drop all Foreign keys
Drop all nonclustered indexes
Drop the PK
Create the PK as a clustered index
Create the other non-clustered indexes from the earlier script
Create the foreign key references from the earlier script.
Test this at least twice on a development/test environment.
I recently did this on a table with 26 foreign key references. There is no
short cut.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
> How do I convert a primary key field that was created with a non-cluster
> index to a
> cluster index?
> Please help me complete this task.
> Thank You,
>
>|||I believe that you can go from nc to cl for a PK or UQ constraint using CREA
TE INDEX ... WITH
DROP_EXISTING. It is the other way (cl to nc) that you cannot do using DROP_
EXISTING. I'd test it,
but I've been working straight for 15 hours now, so time for some sleep... :
-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is n
o short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>|||Ahh, come to think about it, the FK's most probably have to be dropped even
when using
DROP_EXISTING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is n
o short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>|||It was from NC to CL that my latest script covered. 26 Foreign Keys.
Arrgh.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:umz77K86FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I believe that you can go from nc to cl for a PK or UQ constraint using
>CREATE INDEX ... WITH DROP_EXISTING. It is the other way (cl to nc) that
>you cannot do using DROP_EXISTING. I'd test it, but I've been working
>straight for 15 hours now, so time for some sleep... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
>|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Ahh, come to think about it, the FK's most probably have to be dropped even
when using
>DROP_EXISTING.
But won't the EM do all the voodoo for you?
J.|||> But won't the EM do all the voodoo for you?
Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, dro
p the index, create the
index and re-create the foreign keys. I doubt that EM is smart enough to exe
cute CREATE INDEX ...
WITH DROP_EXISTING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.
4ax
.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> But won't the EM do all the voodoo for you?
> J.
>|||Your correct, all the Index Tuning Wizard recommends is to drop all indexes
except the cluster index. This is not much help.
What is a good way to analysis tuning indexes?
Thank You,
"Tibor Karaszi" wrote:

> Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, d
rop the index, create the
> index and re-create the foreign keys. I doubt that EM is smart enough to e
xecute CREATE INDEX ...
> WITH DROP_EXISTING.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v
1l379f8ik3rmlu@.4ax.com...
>
>|||> What is a good way to analysis tuning indexes?
IMO, the brain. Know the datamodel, the data and work the query and executio
n plan. ITW can be some
input, but not a replacement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:514AC714-27B8-424F-9890-261FCE34A769@.microsoft.com...[vbcol=seagreen]
> Your correct, all the Index Tuning Wizard recommends is to drop all indexe
s
> except the cluster index. This is not much help.
> What is a good way to analysis tuning indexes?
> Thank You,
>
> "Tibor Karaszi" wrote:
>

Primary Key Convert from Non-Cluster to Cluster Index

How do I convert a primary key field that was created with a non-cluster
index to a
cluster index?
Please help me complete this task.
Thank You,Script all foreign keys that refer to the PK.
Script all nonclustered indexes
Drop all Foreign keys
Drop all nonclustered indexes
Drop the PK
Create the PK as a clustered index
Create the other non-clustered indexes from the earlier script
Create the foreign key references from the earlier script.
Test this at least twice on a development/test environment.
I recently did this on a table with 26 foreign key references. There is no
short cut.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
> How do I convert a primary key field that was created with a non-cluster
> index to a
> cluster index?
> Please help me complete this task.
> Thank You,
>
>|||I believe that you can go from nc to cl for a PK or UQ constraint using CREATE INDEX ... WITH
DROP_EXISTING. It is the other way (cl to nc) that you cannot do using DROP_EXISTING. I'd test it,
but I've been working straight for 15 hours now, so time for some sleep... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is no short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>> How do I convert a primary key field that was created with a non-cluster
>> index to a
>> cluster index?
>> Please help me complete this task.
>> Thank You,
>>
>>
>|||Ahh, come to think about it, the FK's most probably have to be dropped even when using
DROP_EXISTING.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is no short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>> How do I convert a primary key field that was created with a non-cluster
>> index to a
>> cluster index?
>> Please help me complete this task.
>> Thank You,
>>
>>
>|||It was from NC to CL that my latest script covered. 26 Foreign Keys.
Arrgh.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:umz77K86FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I believe that you can go from nc to cl for a PK or UQ constraint using
>CREATE INDEX ... WITH DROP_EXISTING. It is the other way (cl to nc) that
>you cannot do using DROP_EXISTING. I'd test it, but I've been working
>straight for 15 hours now, so time for some sleep... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
>> Script all foreign keys that refer to the PK.
>> Script all nonclustered indexes
>> Drop all Foreign keys
>> Drop all nonclustered indexes
>> Drop the PK
>> Create the PK as a clustered index
>> Create the other non-clustered indexes from the earlier script
>> Create the foreign key references from the earlier script.
>> Test this at least twice on a development/test environment.
>> I recently did this on a table with 26 foreign key references. There is
>> no short cut.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>> How do I convert a primary key field that was created with a non-cluster
>> index to a
>> cluster index?
>> Please help me complete this task.
>> Thank You,
>>
>>
>>
>|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Ahh, come to think about it, the FK's most probably have to be dropped even when using
>DROP_EXISTING.
But won't the EM do all the voodoo for you?
J.|||> But won't the EM do all the voodoo for you?
Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create the
index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
WITH DROP_EXISTING.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>Ahh, come to think about it, the FK's most probably have to be dropped even when using
>>DROP_EXISTING.
> But won't the EM do all the voodoo for you?
> J.
>|||Your correct, all the Index Tuning Wizard recommends is to drop all indexes
except the cluster index. This is not much help.
What is a good way to analysis tuning indexes?
Thank You,
"Tibor Karaszi" wrote:
> > But won't the EM do all the voodoo for you?
> Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create the
> index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
> WITH DROP_EXISTING.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> > On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> >>Ahh, come to think about it, the FK's most probably have to be dropped even when using
> >>DROP_EXISTING.
> >
> > But won't the EM do all the voodoo for you?
> >
> > J.
> >
> >
>
>|||> What is a good way to analysis tuning indexes?
IMO, the brain. Know the datamodel, the data and work the query and execution plan. ITW can be some
input, but not a replacement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:514AC714-27B8-424F-9890-261FCE34A769@.microsoft.com...
> Your correct, all the Index Tuning Wizard recommends is to drop all indexes
> except the cluster index. This is not much help.
> What is a good way to analysis tuning indexes?
> Thank You,
>
> "Tibor Karaszi" wrote:
>> > But won't the EM do all the voodoo for you?
>> Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create
>> the
>> index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
>> WITH DROP_EXISTING.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "jxstern" <jxstern@.nowhere.xyz> wrote in message
>> news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
>> > On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
>> > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>> >>Ahh, come to think about it, the FK's most probably have to be dropped even when using
>> >>DROP_EXISTING.
>> >
>> > But won't the EM do all the voodoo for you?
>> >
>> > J.
>> >
>> >
>>

primary key autoincrement question.

I have a table in my sqlserver 2000 that has a field IDNO. i want this
field to be my primary key. however i don't want this field to use the
autoincrement feature. when i access this table from vb.net and try to
add a record this field is autoincrementing. how can i disable the
autoincrement of this field yet serves this as my primary key?

thanks in advancego to design, select your id make shure that there is a key symbol next to
it (if not right click and set primary key)
this makes it the primary key the identity is something else -->
autoincrementing set it to false and there you go :)

hope it helps

eric

"jaYPee" <hijaypee@.yahoo.com> wrote in message
news:8vl470phkea0fueivehemm3h882ousjg8f@.4ax.com...
> I have a table in my sqlserver 2000 that has a field IDNO. i want this
> field to be my primary key. however i don't want this field to use the
> autoincrement feature. when i access this table from vb.net and try to
> add a record this field is autoincrementing. how can i disable the
> autoincrement of this field yet serves this as my primary key?
> thanks in advance|||Thank you for the reply. however i can't find an autoincrementing
properties under column properties in order to set it to false.

under column properties of IDNO field i have i only see this
properties:

Description
Default Value
Precision
Scale
Identity
Identity Seed
Identity Increment
Is RowGuid
Formula
Collation

i presume before and until now that i have to set the identity to "no"
but still in my vb.net app when i add record the IDNO field still
increment to the last value + 1.

don't know where can i set the autoincrement to "false"

thanks again

On Tue, 6 Apr 2004 09:41:21 +0200, "EricJ"
<ericReMoVe@.ThiSbitconsult.be.RE> wrote:

>go to design, select your id make shure that there is a key symbol next to
>it (if not right click and set primary key)
>this makes it the primary key the identity is something else -->
>autoincrementing set it to false and there you go :)
>hope it helps
>eric
>"jaYPee" <hijaypee@.yahoo.com> wrote in message
>news:8vl470phkea0fueivehemm3h882ousjg8f@.4ax.com...
>> I have a table in my sqlserver 2000 that has a field IDNO. i want this
>> field to be my primary key. however i don't want this field to use the
>> autoincrement feature. when i access this table from vb.net and try to
>> add a record this field is autoincrementing. how can i disable the
>> autoincrement of this field yet serves this as my primary key?
>>
>> thanks in advance|||these are the ones you are after

> Identity
> Identity Seed
> Identity Increment

yust set the identity to false the rest will follow :)

eric

"jaYPee" <hijaypee@.yahoo.com> wrote in message
news:aos470d3p2gup57707rmeib2907453sn69@.4ax.com...
> Thank you for the reply. however i can't find an autoincrementing
> properties under column properties in order to set it to false.
> under column properties of IDNO field i have i only see this
> properties:
> Description
> Default Value
> Precision
> Scale
> Identity
> Identity Seed
> Identity Increment
> Is RowGuid
> Formula
> Collation
> i presume before and until now that i have to set the identity to "no"
> but still in my vb.net app when i add record the IDNO field still
> increment to the last value + 1.
> don't know where can i set the autoincrement to "false"
> thanks again
> On Tue, 6 Apr 2004 09:41:21 +0200, "EricJ"
> <ericReMoVe@.ThiSbitconsult.be.RE> wrote:
> >go to design, select your id make shure that there is a key symbol next
to
> >it (if not right click and set primary key)
> >this makes it the primary key the identity is something else -->
> >autoincrementing set it to false and there you go :)
> >hope it helps
> >eric
> >"jaYPee" <hijaypee@.yahoo.com> wrote in message
> >news:8vl470phkea0fueivehemm3h882ousjg8f@.4ax.com...
> >> I have a table in my sqlserver 2000 that has a field IDNO. i want this
> >> field to be my primary key. however i don't want this field to use the
> >> autoincrement feature. when i access this table from vb.net and try to
> >> add a record this field is autoincrementing. how can i disable the
> >> autoincrement of this field yet serves this as my primary key?
> >>
> >> thanks in advance|||>> I have a table in my SQL Server 2000 that has a field [sic] IDNO. I
want this field [sic] to be my primary key. <<

You need to read a book on SQL and RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

What does this table model in the real world? Look at the real world
and ask what the key is. It cannot ever be the internal state of the
hardware in which your model resides. You are still thinking that
there are rcord numbers, like a sequential file system in an RDBMS --
you even use the terminology of a sequential file system.

This is totally wrong. There is no "Magic, Universal
one-size-fits-all" way to get a key. Building a data model is work.

Primary Key

I am setting up some tables where I used to have an identity column as the primary key. I changed it so the primary key is not a char field length of 20.

Is there going to be a big performance hit for this? I didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.

EG:

-- Old way
tbProductionLabour
ID (pk)| Descr | fkCostCode
-------
1 | REBAR | 1J

tbTemplateLabour
fkTemplateID | fkLabourID | Manpower | Hours
--------------
1 | 1 | 1 | 0.15

-- New way
tbProductionLabour
Labour | fkCostCode
-------
REBAR | 1J

tbTemplateLabour
fkTemplateID | fkLabour | Manpower | Hours
--------------
1 | REBAR | 1 | 0.15

This is a very basic example, but you get the idea of what I am referring to.

Any thoughts?

MikeI didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.

The light! Don't look at the light!

I guess I'll add you to the "not preffering surrogates" group

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx

Excuse me while I climb back upon my barst...um desk chair...yeah that's right...|||EDIT: Didn't we have this conversation already?|||EDIT: Didn't we have this conversation already?
Probably, but as an in-experienced developer (wannabe) I was wondering if not using a identity field will really make that great of a performance difference.

I think the char(20) for a primary key is better mainly because when I open a table I am not seeing a number which I will have to look up. It also would reduce the number of joins I would have to make (which I guess would be better for performance). But, for looking up values or joining on that field, would the performance reduction be neglable or significant enough to want to use the identity field?

Mike|||Let me ask you this...

What do you think would be faster.

A). An umpteen table join on surrogate keys to get the data, or

B). A SELECT against 1 Table|||Let me ask you this...

What do you think would be faster.

A). An umpteen table join on surrogate keys to get the data, or

B). A SELECT against 1 Table
I would assume option B, but let me ask you this.

What do you think would be faster:
A) Looking up values based on an integer
or
B) Looking up values based on an umpteen char string

?

Mike B|||OK, I'll give you 80/1000 of a second

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 sysname)
CREATE TABLE myTable00(Col1 sysname, Col2 int IDENTITY(1,1))
GO

DECLARE @.x int
SELECT @.x = 1
WHILE @.x < 1000
BEGIN
INSERT INTO myTable99(Col1) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
INSERT INTO myTable00(Col1) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
SELECT @.x = @.x + 1
END

INSERT INTO myTable99(Col1) SELECT 'Brett'

CREATE INDEX myIndex99 ON myTable99(Col1)
CREATE INDEX myIndex00 ON myTable00(Col2)

SELECT COUNT(*) FROM myTable99

DECLARE @.x1 datetime, @.y1 datetime, @.x2 datetime, @.y2 datetime
SELECT @.x1 = GetDate()
SELECT @.x1 AS systime, 'Starting int look up'
SELECT * FROM myTable00 WHERE Col2 = 216784
SELECT @.y1 = GetDate()
SELECT @.y1 AS systime, 'Endinging int look up'

SELECT @.x2 = GetDate()
SELECT @.x2 AS systime, 'Starting sysname look up'
SELECT * FROM myTable00 WHERE Col1 = 'Brett'
SELECT @.y2 = GetDate()
SELECT @.y2 AS systime, 'Endinging int look up'

SELECT DATEDIFF(ms,@.x1, @.y1), DATEDIFF(ms,@.x2, @.y2)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO|||For small tables with no updates, it doesn't matter much. If you start to update the char values you are using as keys, you'll lose hair very quickly. If you add rows (beyond about 100,000 or so), the numeric keys will be significantly faster, due to lower total physical IO.

The short answer boils down to you can use what you want. As you scale upward, the surrogate keys look better and better!

-PatP|||For small tables with no updates, it doesn't matter much. If you start to update the char values you are using as keys, you'll lose hair very quickly. If you add rows (beyond about 100,000 or so), the numeric keys will be significantly faster, due to lower total physical IO.

The short answer boils down to you can use what you want. As you scale upward, the surrogate keys look better and better!

-PatP
Yeah, I pretty much aggree with that. The tables I am refering to will not be that big and they are kind of complex so I thought it would be best to use as many natural keys as possible. Of course there are places in my DB where I use the identity because I don't think the natural keys are all that good to use.

How many people use CompanyName as a natural key in a companies table?

I understand there maybe more then one CompanyName in the table but should they be unique by appending a number or geographical location, or using the CompanyName / Address as the primary key.

My thought is that it is best to use a surrogate here because to carry a company name and address as a forein key to other tables is probably costly.

There is the argument that the address can change but is that really a problem since you can specify "Cascade update related fields" on the other tables?

I would personally love to see something other then a number when I am looking at these tables but....

Mike B|||At least in my opinion, company name stinks as a primary key. We aren't all that big, but we have several hundred companies scattered wildly about North America with the same name, and on a worldwide basis it gets even worse.

JOINs are cheap. SQL Server makes them nearly free IF you keep the FK value small (INT or smaller) and you've got enough RAM in your server.

-PatP|||JOINs are cheap.

That's gotta be the most open ended statement I've heard in a while...

Also... "Company's with the same name scattered around"?

Either they truly are a different company, which means they are separate legal entity, or they are a site for a company...

Are you essentially saying that accessing 1 table would be slower than accessing many?|||Many are franchises, some just reuse common names in different jurisdictions. The net result is that if you look for companies with names like Subway or McDonald's you find hundreds of hits, most (but not all) with separate EIN values.

If you have to haul a fifty byte VARCHAR off the disk versus a four byte integer for every row in a 34 million row table, versus a join to a table cached in RAM, then the JOIN is cheaper than the single table. While SQL Server is good at hiding physical details from the user, some things are still big enough tasks so that smart design beats brute force every time.

-PatP

Previous Row Calculations

Hi all

I am wondering if anyone know a way that you can look up a value from the previous row to do a calculation on. I have a field that I need to subtract the same field from the previous row to validate. Is this possible in a query?

Thanks
KenzieOk, i just composed this script and i hope it could help you

WITH MyTable AS
(SELECT
*,
Num = ROW_NUMBER() OVER(ORDER BY <YourColumn>)
FROM [dbo].<YourTable>)

SELECT
tb.<YourColumn>,
tb.Num,
prev. <YourColumn>
FROM MyTable tb
LEFT JOIN
(SELECT
<YourColumn>,
Num = (ROW_NUMBER() OVER(ORDER BY <YourColumn>) -1)
FROM [dbo].<YourTable>) prev
ON tb.Num = prev.Num

Mind to change <YourColumn> and <YourTable>
let me know if it works (for me it's working)
|||Slight simplification, you can use only the CTE in the self-join like:

WITH t_seq
AS
(
select ROW_NUMBER() OVER(ORDER BY <column_list>) AS seq
from <table>
)
select ...
from t_seq AS t1
left join t_seq AS t2
on t2.seq = t1.seq -1;