default clustered index when selecting primary key

Hi,
I have a list of table which I need to replicate to another database. First
of all, why do I need a primary key to achieve replication (sorry I am new
with replication)
Then I've noticed that if I just click the primary key button from EM on
Design table, then it defaults to a clustered index on the column I've
selected. Does anybody knwow why it defaults to clustered? I am not too kee
n
on clustered indexes for my database.
Thanks,
Panos.Panos Stavroulis. wrote:

> Hi,
> I have a list of table which I need to replicate to another database. Firs
t
> of all, why do I need a primary key to achieve replication (sorry I am new
> with replication)
Without a key the database can't uniquely identify a row in a table.
Every table should have a key. Why would you want a table without one?

> Then I've noticed that if I just click the primary key button from EM on
> Design table, then it defaults to a clustered index on the column I've
> selected. Does anybody knwow why it defaults to clustered? I am not too k
een
> on clustered indexes for my database.
I don't think there's a very good reason why it defaults to clustered.
You can easily change the setting so the default doesn't matter very
much. Also, I would not usually create indexes or keys in Enterprise
Manager. EM is very inefficient in the way it implements schema
changes. Usually it's better to write TSQL for your schema mods. EM
will even script the change for you to review if that helps you get
started.
Most of the time it does pay to have a clustered index on every table.
Tables without clustered indexes should be the exception rather than
the rule.
David Portas
SQL Server MVP
--|||> Does anybody knwow why it defaults to clustered?
A design decision MS made some 12 years ago. Perhaps because you can only ha
ve one CL IX on a table
and you can only have one PK for a table? Important to notice is that you ar
e free to override that
default.

> I am not too keen
> on clustered indexes for my database.
Why not? Clustered indexes are a very important performance tools, and most
DBAs avoid heap tables
and try to decide carefully which index to be the clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:E07ADE7B-5424-43E1-AE34-936AF21D27AC@.microsoft.com...
> Hi,
> I have a list of table which I need to replicate to another database. Firs
t
> of all, why do I need a primary key to achieve replication (sorry I am new
> with replication)
> Then I've noticed that if I just click the primary key button from EM on
> Design table, then it defaults to a clustered index on the column I've
> selected. Does anybody knwow why it defaults to clustered? I am not too k
een
> on clustered indexes for my database.
> Thanks,
> Panos.|||You are opening a good subject here. Well the reason I don't use clustered
indexes for many tables is because I don't need to do a select statements
where col_id between 10 and 200 etc if column col_id was my primary key.
I normally select individual records. In fact which way do you think it's
faster? if I have a non clustered index and select where col_id = 100 or a
clustered index?
Thanks.
"Tibor Karaszi" wrote:

> A design decision MS made some 12 years ago. Perhaps because you can only
have one CL IX on a table
> and you can only have one PK for a table? Important to notice is that you
are free to override that
> default.
>
> Why not? Clustered indexes are a very important performance tools, and mos
t DBAs avoid heap tables
> and try to decide carefully which index to be the clustered index.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:E07ADE7B-5424-43E1-AE34-936AF21D27AC@.microsoft.com...
>|||> I normally select individual records. In fact which way do you think it's
> faster? if I have a non clustered index and select where col_id = 100 or a
> clustered index?
A clustered index will be marginally faster.
But are you really saying that you have no range queries (lower selectivity
search arguments) at all
in the database? What about joins? Grouping? Sorting? Clustered indexes can
beneficial for a wide
type of operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:4AFF7C5A-C43F-4C27-AA5B-9A6F65DAAE6E@.microsoft.com...
> You are opening a good subject here. Well the reason I don't use clustered
> indexes for many tables is because I don't need to do a select statements
> where col_id between 10 and 200 etc if column col_id was my primary key.
> I normally select individual records. In fact which way do you think it's
> faster? if I have a non clustered index and select where col_id = 100 or a
> clustered index?
> Thanks.
> "Tibor Karaszi" wrote:
>|||I don't think you quite grasp the point of a clustered index.
I really don't feel like going into it right now, but let's look at your
example:
With a nonclustered index several index pages must be searched before
reaching the 100 value, while with a clustered index this is done in two
quick steps:
1) find the page where the 100 value resides; and
2) only go to that page.
ML
http://milambda.blogspot.com/|||The explanation that I remember best from an old DBA friend of mine is
that a clustered index is like page numbers in a book, whereas
non-clustered indexes are like the index in the back; although the
analogy is not a perfect fit, it does explain the relationship between
clustered and nonclustered indexes. Without page numbers in a
sequential order (clustering), it's tough to find the topic you're
looking for.
Stu|||Thanks for the answers. OK how about this.
We have a query that joins 2 tables, file & file_detail linked by column
col_id which is integer. If I was making a query to select all files and
their detail which are between 100 and 1000, then I would expect that this
query will be faster if I had a clustered index on the table.
However, I personally would expect if the query was "give me file 250" then
the non-clustered solution will be faster? Do you agree with this. How about
covering on non-clustered indexes, I would expect the non clustered option t
o
be faster. Thank you.
"Tibor Karaszi" wrote:

> A clustered index will be marginally faster.
> But are you really saying that you have no range queries (lower selectivit
y search arguments) at all
> in the database? What about joins? Grouping? Sorting? Clustered indexes ca
n beneficial for a wide
> type of operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:4AFF7C5A-C43F-4C27-AA5B-9A6F65DAAE6E@.microsoft.com...
>|||> We have a query that joins 2 tables, file & file_detail linked by column
> col_id which is integer. If I was making a query to select all files and
> their detail which are between 100 and 1000, then I would expect that this
> query will be faster if I had a clustered index on the table.
Cluster on which table and which column(s)? It is possible that a clustered
index o the fireign key
column in the file_detail table will improve that join, but that depends on
a lot of other factors.

> However, I personally would expect if the query was "give me file 250" the
n
> the non-clustered solution will be faster?
Non-clustered index on what? For a query with high-selectivity, a clustered
index on the search
column will still outperform a nonc-lustered (but the ihger selectivity, the
more marginally) as SQL
Server doesn't have to fetch each row in the datapage. Unless the non-cluste
red index covers the
query, of course.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:CE190BBB-A52C-4D06-9AA8-8E33D983B643@.microsoft.com...
> Thanks for the answers. OK how about this.
> We have a query that joins 2 tables, file & file_detail linked by column
> col_id which is integer. If I was making a query to select all files and
> their detail which are between 100 and 1000, then I would expect that this
> query will be faster if I had a clustered index on the table.
> However, I personally would expect if the query was "give me file 250" the
n
> the non-clustered solution will be faster? Do you agree with this. How abo
ut
> covering on non-clustered indexes, I would expect the non clustered option
to
> be faster. Thank you.
>
> "Tibor Karaszi" wrote:
>|||Sorry meant to say clustered index on col_id on both tables in one case and
nonclustered index on both tables in the second.
So basically basically since for most of the queries you need more
information (columns) than the columns contained in the index (covering
situation) then it's better to create a clustered index on the table as long
as the data arrive in the database in a sequential order and you don't get
page breaks.
Also do you have a view on clustered indexes on tables which are over 8K
long, ie maximum size of a page? Thanks.
"Tibor Karaszi" wrote:

> Cluster on which table and which column(s)? It is possible that a clustere
d index o the fireign key
> column in the file_detail table will improve that join, but that depends o
n a lot of other factors.
>
> Non-clustered index on what? For a query with high-selectivity, a clustere
d index on the search
> column will still outperform a nonc-lustered (but the ihger selectivity, t
he more marginally) as SQL
> Server doesn't have to fetch each row in the datapage. Unless the non-clus
tered index covers the
> query, of course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:CE190BBB-A52C-4D06-9AA8-8E33D983B643@.microsoft.com...
>

Dedub query

I am trying to join two table using a primary key, my problem is that one table has multiple listing of that primary key, I only want to join to the primary key once. Can anyone show me how this can be done?

Table1

acct_no sale_am tran_cd

123 50 2

123 54 1

113 20 9

124 30 7

Table2

acct_no exp_am res_am

123 50 20

113 24 30

124 60 10

What I need:

acct_no sum(sale_am) sum(exp_am) sum(res_am)

123 104 50 20

113 20 24 30

124 30 60 10

Thanks

There are several possibilities. And each one could provide different resultsets.

Please let us know what is your expected output and we can better assist you.

|||

Assuming you want the latest tran_cd value, you can just use a derived table and the ROW_NUMBER() windowed function:

select *

from (select acct_no, sale_am, row_number() over (partition by acct_no order by tran_cd desc) as rowNbr

from table1) as table1
join table2
on table1.acct_no = table2.acct_no

and table1.rowNbr = 1

If this is something that you do often, especially something that needs a lot of performance, I might consider implementing a current_row_flag in your table to denote the row you want to usually use (especially if those rows don't change much)

|||

Code Snippet

createtable #Table1( acct_no int, sale_am money, tran_cd int)

insertinto #Table1

select 123, 50, 2

union allselect 123, 54, 1

union allselect 113, 20, 9

union allselect 124, 30, 7

createtable #Table2( acct_no int, exp_am money, res_am money)

insertinto #Table2

select 123, 50, 20

union allselect 113, 24, 30

union allselect 124, 60, 10

select t1.acct_no,sum(t1.sale_am)as sale_am,

sum(t2.exp_am)as exp_am,sum(t2.res_am)as res_am

from #Table1 t1

innerjoin #Table2 t2

on t1.acct_no = t2.acct_no

groupby t1.acct_no

sql

Decrypting from replicated table

I have a replicated table with an encrypted SSN column. In the source
database, we have created the master key, certificate, and symmetric key. I
s
there a way for me to decrypt the SSN from the replicated table, or do I hav
e
to perform a distributed query to the source database in order to get the
decrypted SSN?You can do a distributed query or you can setup the same master key,
certificate, and symmetric key on the subscriber.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Arnel" <Arnel@.discussions.microsoft.com> wrote in message
news:4EF93674-2A25-441B-BDA2-BE3261AC55D1@.microsoft.com...
>I have a replicated table with an encrypted SSN column. In the source
> database, we have created the master key, certificate, and symmetric key.
> Is
> there a way for me to decrypt the SSN from the replicated table, or do I
> have
> to perform a distributed query to the source database in order to get the
> decrypted SSN?|||How can I create the same keys in a different database?
"Michael Hotek" wrote:

> You can do a distributed query or you can setup the same master key,
> certificate, and symmetric key on the subscriber.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Arnel" <Arnel@.discussions.microsoft.com> wrote in message
> news:4EF93674-2A25-441B-BDA2-BE3261AC55D1@.microsoft.com...
>
>|||Use the KEY_SOURCE and the IDENTITY_VALUE parameters of CREATE SYMMETRIC
KEY. For additional information, see
http://msdn2.microsoft.com/en-us/library/ms188357.aspx and the last section
of this post: http://blogs.msdn.com/lcris/archive...14/481434.aspx.
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arnel" <Arnel@.discussions.microsoft.com> wrote in message
news:03E00687-3EB6-4182-AEE3-94C4BC3DA0F7@.microsoft.com...[vbcol=seagreen]
> How can I create the same keys in a different database?
> "Michael Hotek" wrote:
>|||Thank you for the clarification. However, please correct my understanding
here:
1. I will need to drop my existing, original symmetric key and recreate
using the KEY_SOURCE and IDENTITY_VALUE params.
2. I will then need to "re-encrypt" my data using that new symmetric key
3. In the database, with the replicated table, I will need to create a new
symmetric key using the same params from the original key.
Please clarify any misunderstanding I have about the process. Thanks.
"Laurentiu Cristofor [MSFT]" wrote:

> Use the KEY_SOURCE and the IDENTITY_VALUE parameters of CREATE SYMMETRIC
> KEY. For additional information, see
> http://msdn2.microsoft.com/en-us/library/ms188357.aspx and the last sectio
n
> of this post: http://blogs.msdn.com/lcris/archive...14/481434.aspx.
> --
> Laurentiu Cristofor [MSFT]
> Software Design Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Arnel" <Arnel@.discussions.microsoft.com> wrote in message
> news:03E00687-3EB6-4182-AEE3-94C4BC3DA0F7@.microsoft.com...
>
>|||Don't drop the original symmetric key before re-encrypting, you will need it
to decrypt the existing data. Otherwise, this will be indeed the process:
create new key using the params, decrypt with old key and reencrypt with the
new one, then in the replicated database recreate the key from the same
params.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arnel" <Arnel@.discussions.microsoft.com> wrote in message
news:A68460ED-9912-40AC-9788-9A1C5E2FD15E@.microsoft.com...[vbcol=seagreen]
> Thank you for the clarification. However, please correct my understanding
> here:
> 1. I will need to drop my existing, original symmetric key and recreate
> using the KEY_SOURCE and IDENTITY_VALUE params.
> 2. I will then need to "re-encrypt" my data using that new symmetric key
> 3. In the database, with the replicated table, I will need to create a
> new
> symmetric key using the same params from the original key.
> Please clarify any misunderstanding I have about the process. Thanks.
> "Laurentiu Cristofor [MSFT]" wrote:
>

DecryptByPassPhrase not decrypting varchar columns after copying a database

I have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:

DECLARE @.pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @.pss OUTPUT

SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator

Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.

Also this same code works correctly and as expected by decrypting the encrypted fields in production.|||

A couple of observations first:

(1) Why are you converting the columns to varchar(max)? That should not be necessary.
(2) What is the reason you are using the "1, convert(varbinary, 23)" arguments? I don't see how those could be helpful.

From you description, it appears that the data was mangled during the transfer. What is the type of the encrypted columns?

Also, given that this appears to be a copy database problem, your question may be better directed to the SQL Server Tools General forum. If the column contains a different value after the copy, the decryption is expected to fail.

Thanks
Laurentiu

|||

Mr. Cistofor,

1. It made sense at the time.
2. I must have added that to confuse myself and others later on (job security or bad programming - you decide).
3. Data mangled in transfer, yep thats what it be.
4. Wrong msg board. Sorry, I was in a hurry and I used writting the post as a way to think it through instead of prepping more before submission. My bad dog!

Thanks for the time, consideration and consultation,

Mike512

|||

Ok, you are of course free to write code as you wish, but I wanted to point out that you are doing unnecessary operations. (1) may not be costly, but for (2) you are forcing an additional hash computation per value, which is expensive and will degrade the performance of your queries - it also doesn't serve much purpose from a security point of view.

Thanks
Laurentiu

sql

declaring primary/foreign keys in sql

Hi

If i have two foreign keys in a table which make up my primary key, do i actually write in the SQL that they are the primary keys?

I know you can't declare more than one primary key in a table, eg

CREATE TABLE FACILITIES(
venue_id INTEGER PRIMARY KEY REFERENCES VENUE NOT NULL,
facility_id INTEGER PRIMARY KEY REFERENCES FACILITY_TYPE NOT NULL
)

...so do i just reference them as foreign keys? :S

eg

CREATE TABLE FACILITIES(
venue_id INTEGER REFERENCES VENUE NOT NULL,
facility_id INTEGER REFERENCES FACILITY_TYPE NOT NULL
)

thanks in advance!create table facilities
( venue_id integer not null references venue
, facility_id integer not null references facility_type
, primary key (venue_id, facility_id)
)|||create table facilities
( venue_id integer not null references venue
, facility_id integer not null references facility_type
, primary key (venue_id, facility_id)
)

Ahh right, i see, so you have to define the primary key seperatly! - i have a 450+ page book on database design and it doesn't mention how to do that :mad: ...thanks again r937|||oh, it's gotta be in there somewhere!!

i cannot imagine a database book that overlooks a composite primary key

tell me, does it discuss how to implement a many-to-many relationship? what does the table in the middle look like?|||It tells you that you can create a primary key using two foreign keys (composite) and shows it in the ERD, but i couldn't seem to find an example on how to actually create it!|||too bad, eh -- well, now you know :)

and of course your database system's documentation would cover it in its CREATE TABLE syntax

DECLARE in SQL CE

Can I use DECLARE in SQL 2005 Compact, and if not, how do I do INSERTs into tables which have columns with Primary Key constraints?

Matt

You do not have to specify a value for fields with primary key/identity constraints. See this help topic for more info:

http://msdn2.microsoft.com/en-us/library/ms174633.aspx

|||Sorry, I didn't mean primary key constraints. I meant, how do I insert into Table1 if it has a Column which is a Foreign Key referencing Table2?

Matt
|||If you are referring to IDENTITY columns, you can query "SELECT @.@.IDENTITY" (on the same open connection where you just did the INSERT), and use this value as the foeign key in the next INSERT.