Easy Question

I've read some post that highly recommend not putting a clustered pk on identity columns. Rather use a non-clustered pk for identity pk columns. Does anyone have opinions about this? I have several reference tables that have the following structure. Since joins are made on the pk column I would guess that it would be beneficial to make the pk the clustered index, then again maybe it's better to make one of my unique indexes clustered. Any thoughts?

CREATE TABLE [company] (
[company_id] [int] IDENTITY (1, 1) NOT NULL ,
[co_abbrev] [char(10)] NOT NULL ,
[co_name] [varchar(50)] NOT NULL ,
CONSTRAINT [pk_company] PRIMARY KEY CLUSTERED
(
[company_id]
) WITH FILLFACTOR = 90
)

CREATE UNIQUE INDEX [ux_company_01] ON [dbo].[company]([co_name]) WITH FILLFACTOR = 90

CREATE UNIQUE INDEX [ux_company_02] ON [dbo].[company]([co_abbrev]) WITH FILLFACTOR = 90If table includes field datetime and you are going to select data from this table by date - create clustered index on it.

BOL:Consider using a clustered index for:

-Columns that contain a large number of distinct values.
-Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
-Columns that are accessed sequentially.
-Queries that return large result sets, etc.sql

Easiest way to copy a MS-SQL Database from one machine to another

Can anyone recommend the easiest way to get a full copy of a database from
one server to another. The servers are not part of the same organization or
network.

I have received a backup of the database created with enterprise manager but
am unable to restore it into a database of the same name on my server.

Thanks,

Kevin"Kevin" <noemail@.provided.com> wrote in message
news:tr_Zd.699401$8l.384177@.pd7tw1no...
> Can anyone recommend the easiest way to get a full copy of a database from
> one server to another. The servers are not part of the same organization
> or
> network.
> I have received a backup of the database created with enterprise manager
> but
> am unable to restore it into a database of the same name on my server.
> Thanks,
> Kevin

http://support.microsoft.com/defaul...kb;en-us;314546

Backup and restore is usually the easiest way - if you're having a problem
restoring a backup, then I suggest you post whatever error message you get.
You can also try restoring from Query Analyzer:

restore database MyDB from disk = 'c:\temp\db.bak'

Sometimes errors in QA are more informative than what EM tells you. See the
full RESTORE syntax in Books Online for moving file locations etc.

Simon|||Did you checked the paths to database files before restoring?

If you are using the EM restore database function, the database name
doesn't matter. You can name your new database as you would like to.
The only importand thing is, check the path, where the database should
be restored to.

If the backup was done on other server, probably the Db files was
located under different path (for example: C:\Program
Files\Database\database_name.mdf").

Lets say, that you try to restore it over database BUBU. In this case,
you can have totaly diff. path and of course filenames ... (for
example: "C:\Databases\BUBU_Data.mdf). Therefore, you'll get an error
message, that the files does not match. This is because why, database
reads information from backup about location of database ...

Greatings

Matik|||Simon,

I've tried restoring from QA, the directories for the other server and my
server are different so I am using the MOVE option for the data and log
files. The same problem occurs that I was getting using EM, it complains
that the logical file being restored is not part of my database. I've tried
this without a database called 'Test', as well as after creating one and
using the RESTORE FILELISTONLY but the error message is always the same:

here's the code and error message:

RESTORE DATABASE Test FROM DISK = 'C:\Backups\Project Recovery\Sample
Data\RebillingMarch102005.SQLBackup'
WITH NORECOVERY,
MOVE 'g:\mssql\data\rebilling_Data.MDF' TO 'C:\Program Files\Microsoft
SQL Server\MSSQL\Data\Rebilling_Data.mdf',
MOVE 'E:\MSSQL\log\rebilling_Log.LDF' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\Rebilling_Log.ldf'

response:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'g:\mssql\data\rebilling_Data.MDF' is not part of database
'Test'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Thanks,

- Kevin

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:42387d3d$1_3@.news.bluewin.ch...
> "Kevin" <noemail@.provided.com> wrote in message
> news:tr_Zd.699401$8l.384177@.pd7tw1no...
> > Can anyone recommend the easiest way to get a full copy of a database
from
> > one server to another. The servers are not part of the same organization
> > or
> > network.
> > I have received a backup of the database created with enterprise manager
> > but
> > am unable to restore it into a database of the same name on my server.
> > Thanks,
> > Kevin
> http://support.microsoft.com/defaul...kb;en-us;314546
> Backup and restore is usually the easiest way - if you're having a problem
> restoring a backup, then I suggest you post whatever error message you
get.
> You can also try restoring from Query Analyzer:
> restore database MyDB from disk = 'c:\temp\db.bak'
> Sometimes errors in QA are more informative than what EM tells you. See
the
> full RESTORE syntax in Books Online for moving file locations etc.
> Simon|||Instead of 'g:\mssql\data\rebilling_Data.*MDF' use
'rebilling_Data.*MDF' and
rebilling_Log.*LDF'

Madhivanan|||The first name after "MOVE" should be the logical file name, not the
physical. In your case, probably, "rebilling_Data" and "rebilling_Log"
are the logical names you need to use.