Datatype for Primary Key

I'd like to use uniqueidentifier in my database as Primary Key
Is this a good idea?
How does it effect performance?
Plz help
ApogeeDo you mean a GUID? If so I would think this is quite a long field for a
primary key to be based on.
If you do use it, and you are generating a random one every time, I would
make sure the index isn't clustered, because you won't be inserting to the
bottom of the table.
"Apogee" <developer@.bitefish.net> wrote in message
news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> I'd like to use uniqueidentifier in my database as Primary Key
> Is this a good idea?
> How does it effect performance?
> Plz help
> Apogee
>|||What disadvantages would this have? (not clustered)
Apogee
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:eeHKXxbUDHA.1692@.TK2MSFTNGP11.phx.gbl...
> Do you mean a GUID? If so I would think this is quite a long field for a
> primary key to be based on.
> If you do use it, and you are generating a random one every time, I would
> make sure the index isn't clustered, because you won't be inserting to the
> bottom of the table.
> "Apogee" <developer@.bitefish.net> wrote in message
> news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> > I'd like to use uniqueidentifier in my database as Primary Key
> >
> > Is this a good idea?
> > How does it effect performance?
> >
> > Plz help
> >
> > Apogee
> >
> >
>|||I don't know a huge amount about this subject but I believe using a
clustered index, the data is actually stored in the order of the index. A
clustered index is therefore the fastest type. A non clustered is a normal
index, which contains the information you have included in your index, and
also a link to where the actual record is stored.
If you insert lots of records in the middle of a clustered index, the server
will have to do a certain amount or re-jigging of the data to keep it in the
actual order of the primary key.
I would suggest using a clustered index if you are using an auto increment
primary key, and a non clustered index if you are inserting random values.
If the tables are small, or without much activity, this is largely
irrelevant though.
Ryan
"Apogee" <developer@.bitefish.net> wrote in message
news:Oi88I%23bUDHA.2200@.TK2MSFTNGP11.phx.gbl...
> What disadvantages would this have? (not clustered)
> Apogee
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:eeHKXxbUDHA.1692@.TK2MSFTNGP11.phx.gbl...
> > Do you mean a GUID? If so I would think this is quite a long field for
a
> > primary key to be based on.
> >
> > If you do use it, and you are generating a random one every time, I
would
> > make sure the index isn't clustered, because you won't be inserting to
the
> > bottom of the table.
> >
> > "Apogee" <developer@.bitefish.net> wrote in message
> > news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> > > I'd like to use uniqueidentifier in my database as Primary Key
> > >
> > > Is this a good idea?
> > > How does it effect performance?
> > >
> > > Plz help
> > >
> > > Apogee
> > >
> > >
> >
> >
>|||I don't like using a GUID for an artificial primary key. It takes up 4 times
as much space as an int (16 bytes vs 4 bytes), and as the primary key is
referenced in other tables and indexes, this can add up to a large amount of
unnecessary space in your database, negatively impacting performance. You
can store more than 2 billion rows in a table when you have a IDENTITY
column starting at 1 with an INT datatype, and that is enough for most
applications. If it isn't you can always use a BIGINT (8 bytes) datatype.
Using GUIDs also makes debugging more difficult than using identity, because
humans are better at remembering 1-2-3 than at remembering 32 character
hexadecimal strings. And with IDENTITY the inserts are generated in order,
which can help debugging as well.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Apogee" <developer@.bitefish.net> wrote in message
news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> I'd like to use uniqueidentifier in my database as Primary Key
> Is this a good idea?
> How does it effect performance?
> Plz help
> Apogee
>

Dataflow with Workflow

I know the idea was to seperate workflow and dataflow, but I have come across a scenario where it would be useful for a branch of a dataflow to wait until another branch has finished.

I have some transactional data which records events for the start and end of a session. I want to build a list of unique sessions with the start and end date. I currently have the list of events sorted by time, followed by a conditional split for the start and end events. I can then insert all of the start events and would like to wait until all of the starts are inserted before updating them with their relevant end times.

Is this achievable?

Does anyone else think it would be a good idea to be able to set precendence across multiple branches of a data flow?

Does anyone have a better solution?

I know this is the wrong forum, but is there a way to model this against the transactional data in SSAS, I will move this question to the SSAS forum if anyone can think this would work!

Philip Coupar

Unfortunately, setting execution precendences in between branches in the data flow is not supported today. Until we redesign the engine to support this feature, I would consider other possibilities to solve the problem, such as using a combination of task flow and data flow.

|||A simple strategy is to use raw files. Stage the updates in a raw file and use a second Data Flow Task to load them and run the updates. Raw Files are very efficient.|||

Philip Coupar wrote:

I know the idea was to seperate workflow and dataflow, but I have come across a scenario where it would be useful for a branch of a dataflow to wait until another branch has finished.

I have some transactional data which records events for the start and end of a session. I want to build a list of unique sessions with the start and end date. I currently have the list of events sorted by time, followed by a conditional split for the start and end events. I can then insert all of the start events and would like to wait until all of the starts are inserted before updating them with their relevant end times.

Is this achievable?

Does anyone else think it would be a good idea to be able to set precendence across multiple branches of a data flow?

Does anyone have a better solution?

I know this is the wrong forum, but is there a way to model this against the transactional data in SSAS, I will move this question to the SSAS forum if anyone can think this would work!

Philip Coupar

Phil,

As Darren said, raw files are the solution here.

On a similar vein though I have requested some functionality that would allow you to prioritise destinations:

Intrinsic Flow Priority
(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058)

Would this help you?

-Jamie

|||

Raw files may well be very efficient, and space may be cheap but I already have a stage table with 4 million transaction rows which breaks out to 2 million start events and 2 million end events. I don't really want to read 4 million rows from a set of text files into a SQL Server table to read back out all the rows while applying some transformation logic to it, to split on one of the transformed columns to put half the data back out to a file, so that at some later point I can pick up that file again and update my 2 million start events with their end time.

That is just the incremental data, to rebuild the entire model would involve reprocessing 250 Million transaction rows, the pipeline uses buffers to manage the number of rows in memory, the raw file in this case would require 125 Million rows to be written out. I know I could batch up the rebuild into 4 Million rows sets, but I quite like the "Read Once, Transform Many, Write Once" mentality.

|||

Jamie Thomson wrote:

Phil,

As Darren said, raw files are the solution here.

On a similar vein though I have requested some functionality that would allow you to prioritise destinations:

Intrinsic Flow Priority
(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058)

Would this help you?

-Jamie

That looks great, I hope anyone else following this tread will have a look and vote. My only additional request to your proposal would be to include the OLEDB Command Component as well as the destinations so that precendence could be specified over inserts and updates.

As well as the scenario above we have a number of data feeds which have data columns split across multiple source files and not all sets of file contain distinct data sets, sometime a file can contain data items that relate to data from a previous set of files. This introduces exactly the same scenario where I would like to insert all new rows and after that use other data in the data flow to update these or existing rows.

|||

Philip Coupar wrote:

Jamie Thomson wrote:

Phil,

As Darren said, raw files are the solution here.

On a similar vein though I have requested some functionality that would allow you to prioritise destinations:

Intrinsic Flow Priority
(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058)

Would this help you?

-Jamie

That looks great, I hope anyone else following this tread will have a look and vote. My only additional request to your proposal would be to include the OLEDB Command Component as well as the destinations so that precendence could be specified over inserts and updates.

Agree with that totally.

Note to anyone else. Don't just vote - that is useless. Add a use case for why you want this functionality (as Phil has done).

Philip Coupar wrote:

As well as the scenario above we have a number of data feeds which have data columns split across multiple source files and not all sets of file contain distinct data sets, sometime a file can contain data items that relate to data from a previous set of files. This introduces exactly the same scenario where I would like to insert all new rows and after that use other data in the data flow to update these or existing rows.

Hmmm...the scenario I have in mind is that you either insert a row or update it. But I guess there is no reason why this "Intrinsic Flow Priority" feature couldn't be used to both insert a record and then update it. Interesting....

One more thing Phil, can you make sure you add all the details from your use case on this thread to the connect posting? They'll take more notice of it if you give them more reasons to.

Ta

Jamie

Database Version Logging

Hi i just planning to creating a database version logging, where the
idea is this application running every night give me detail about table
schema change , new sproc , alter/drop sproc and other database
schema changes.
One of difficulty that i facing right now is how to get the username
that change the database schema ( alter sproc , etc ) , do anyone have
any recommendation how i able to get this information ? ThanksWhat version of SQL Server are you using? (2000/2005)
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
> Hi i just planning to creating a database version logging, where the
> idea is this application running every night give me detail about table
> schema change , new sproc , alter/drop sproc and other database
> schema changes.
> One of difficulty that i facing right now is how to get the username
> that change the database schema ( alter sproc , etc ) , do anyone have
> any recommendation how i able to get this information ? Thanks
>|||If you are using SQL Server 2005 you can easily accomplish this by using DDL
Triggers.
There is not easy solution for SQL Server 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"kwangsa" wrote:
> Hi i just planning to creating a database version logging, where the
> idea is this application running every night give me detail about table
> schema change , new sproc , alter/drop sproc and other database
> schema changes.
> One of difficulty that i facing right now is how to get the username
> that change the database schema ( alter sproc , etc ) , do anyone have
> any recommendation how i able to get this information ? Thanks
>|||Hi i m using sql server 2000 , hmm for the "not easy solution" , any
recommendation '
I thinking of using DBCC Log but which field that showing the userid.
Thanks.
Ben Nevarez wrote:
> If you are using SQL Server 2005 you can easily accomplish this by using DDL
> Triggers.
> There is not easy solution for SQL Server 2000.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "kwangsa" wrote:
> > Hi i just planning to creating a database version logging, where the
> > idea is this application running every night give me detail about table
> > schema change , new sproc , alter/drop sproc and other database
> > schema changes.
> >
> > One of difficulty that i facing right now is how to get the username
> > that change the database schema ( alter sproc , etc ) , do anyone have
> > any recommendation how i able to get this information ? Thanks
> >
> >|||Before you spend much time trying to code a solution, look at the various
third party tools used for SarBox and HIPPA compliance logging. It may be
less expensive to get something that is made for the job and taking the time
to kludge something together.
Check out Quest, Idera, Lumigent, SQL AuditPro, and many more I'm sure.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152857175.914841.230840@.b28g2000cwb.googlegroups.com...
> Hi i m using sql server 2000 , hmm for the "not easy solution" , any
> recommendation '
> I thinking of using DBCC Log but which field that showing the userid.
> Thanks.
> Ben Nevarez wrote:
>> If you are using SQL Server 2005 you can easily accomplish this by using
>> DDL
>> Triggers.
>> There is not easy solution for SQL Server 2000.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "kwangsa" wrote:
>> > Hi i just planning to creating a database version logging, where the
>> > idea is this application running every night give me detail about table
>> > schema change , new sproc , alter/drop sproc and other database
>> > schema changes.
>> >
>> > One of difficulty that i facing right now is how to get the username
>> > that change the database schema ( alter sproc , etc ) , do anyone have
>> > any recommendation how i able to get this information ? Thanks
>> >
>> >
>|||hi,
checkout this tools for auditing
http://www.lumigent.com/Downloads
Amol Lembhe
MCDBA
"Arnie Rowland" wrote:
> What version of SQL Server are you using? (2000/2005)
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "kwangsa" <SareCrow@.gmail.com> wrote in message
> news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
> > Hi i just planning to creating a database version logging, where the
> > idea is this application running every night give me detail about table
> > schema change , new sproc , alter/drop sproc and other database
> > schema changes.
> >
> > One of difficulty that i facing right now is how to get the username
> > that change the database schema ( alter sproc , etc ) , do anyone have
> > any recommendation how i able to get this information ? Thanks
> >
>
>|||Hi thx for the sugestion to using 3rd party tools but what i planning
to do is only a simple database logging , and this is just personal
project so i really interested to make a tools like that.
Do anyone know any DBCC log result mean , i mean any documentation
would be really helfpul. THx
Amol Lembhe wrote:
> hi,
> checkout this tools for auditing
> http://www.lumigent.com/Downloads
> Amol Lembhe
> MCDBA
> "Arnie Rowland" wrote:
> > What version of SQL Server are you using? (2000/2005)
> >
> > --
> > Arnie Rowland*
> > "To be successful, your heart must accompany your knowledge."
> >
> >
> >
> > "kwangsa" <SareCrow@.gmail.com> wrote in message
> > news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
> > > Hi i just planning to creating a database version logging, where the
> > > idea is this application running every night give me detail about table
> > > schema change , new sproc , alter/drop sproc and other database
> > > schema changes.
> > >
> > > One of difficulty that i facing right now is how to get the username
> > > that change the database schema ( alter sproc , etc ) , do anyone have
> > > any recommendation how i able to get this information ? Thanks
> > >
> >
> >
> >|||You could have a Profiler trace running (from a second server) , restricted
to DDL activities only, and then have sprocs that harvest the information
you seek from the trace table.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152873932.402525.309830@.h48g2000cwc.googlegroups.com...
> Hi thx for the sugestion to using 3rd party tools but what i planning
> to do is only a simple database logging , and this is just personal
> project so i really interested to make a tools like that.
> Do anyone know any DBCC log result mean , i mean any documentation
> would be really helfpul. THx
>
> Amol Lembhe wrote:
>> hi,
>> checkout this tools for auditing
>> http://www.lumigent.com/Downloads
>> Amol Lembhe
>> MCDBA
>> "Arnie Rowland" wrote:
>> > What version of SQL Server are you using? (2000/2005)
>> >
>> > --
>> > Arnie Rowland*
>> > "To be successful, your heart must accompany your knowledge."
>> >
>> >
>> >
>> > "kwangsa" <SareCrow@.gmail.com> wrote in message
>> > news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
>> > > Hi i just planning to creating a database version logging, where the
>> > > idea is this application running every night give me detail about
>> > > table
>> > > schema change , new sproc , alter/drop sproc and other database
>> > > schema changes.
>> > >
>> > > One of difficulty that i facing right now is how to get the username
>> > > that change the database schema ( alter sproc , etc ) , do anyone
>> > > have
>> > > any recommendation how i able to get this information ? Thanks
>> > >
>> >
>> >
>> >
>

Database UserLogin suddenly deleted

Anyone has an idea why a user login in one of my databases got suddenly deleted
The user was working okay around 8 Am and suddenly at 9 AM he was disconeccted from the system
After reviewing what went wrong, I noticed his account was no longer in the database users
All I did was to add the account, but why did this happened? I am worried since the system works 24 hrs a day
Any advice
Thank you.I've never heard of such thing ever happened. I'd suspect someone or a
program executed sp_revokedbacces and/or sp_revokeloing/sp_droplogin.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Daniel Roche" <dan_rb@.hotmail.com> wrote in message
news:F16F11F1-3FDB-45A0-B58F-BD4269B6B873@.microsoft.com...
> Anyone has an idea why a user login in one of my databases got suddenly
deleted?
> The user was working okay around 8 Am and suddenly at 9 AM he was
disconeccted from the system.
> After reviewing what went wrong, I noticed his account was no longer in
the database users.
> All I did was to add the account, but why did this happened? I am worried
since the system works 24 hrs a day.
> Any advice?
> Thank you.|||Is there a possibility that the database was restored at 9 AM?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Daniel Roche" <dan_rb@.hotmail.com> wrote in message
news:F16F11F1-3FDB-45A0-B58F-BD4269B6B873@.microsoft.com...
Anyone has an idea why a user login in one of my databases got suddenly
deleted?
The user was working okay around 8 Am and suddenly at 9 AM he was
disconeccted from the system.
After reviewing what went wrong, I noticed his account was no longer in the
database users.
All I did was to add the account, but why did this happened? I am worried
since the system works 24 hrs a day.
Any advice?
Thank you.