Hi,
I am using SQL Server replication.
I have published articles and my subscribers are some users with pocket pc.
I have a couple of tables with Identity primary keys.
I have already set them to "Not for replication".
My questions:
For tables with identity values, say tblContact, when different users add new contacts new numbers are generated.
What happens when they sync? if two users added a new records , and both have the same number, will sql server consider one as updating the other? Or it will add both with new numbers to the master table and update both subscriptions.
In other words, how should the autonumber primary keys be handled in replication? should I create a composite key, say userID+autonumber to avoid such a situation?
I hope my question is clear.
Thanksmake your identity keys guids (sql type uniqueidentifier, set default property to Newid() ) or random integers. choice depends on number of records your table will have. ie: < 1billion, could probably get away with random integer with low risk of collision, > 1 billion better use guids. I personally use guids for most stuff because I don't have to ever worry about collisions and storage is not an issue, but this may be an issue for you on a pocketpc.
hth
burt|||btw...
if you need the new identity right away and your provider doesn't support refreshing the underlying value you may just wish to generate your own guid (don't set a default on the field). That's one other advantage to this method.
another way: if you're using stored procedures you could add an output parameter to return the newly created guid. this is especially helpful if your programming language doesn't have built in facilities to create a guid.|||I like the newid() idea, but if you are set on identity values, you can make them mutually exclusive sets. This will require you, however, to declare how many servers participate in the replication at the time of setup, because there won't be much going back (although I have not read up on dbcc fixidentity (sp?) lately).
Server 1 has
col1 int identity (1, 3) primary key
Server 2 has
col1 int identity (2, 3) primary key
Server 3 has
col1 int identity (3, 3) primary key
This way, server 1 has values 1,4,7,10,13,...
server 2 has 2,5,8,11,...
and server three has 3,6,9,12,...
Server 4 gets no break.|||Thanks for your reply,
I read up the Identity range and GUID in books online.
But I have a question:
Instead of using guid and identity range, what if we use a composite key, for example use an identity column combined with the userID?
How is that?|||then you'd might as well just find the natural key, assuming one exists?
compound primary keys, while fully supported, will make your queries much more complex.
the project i'm working on now has several compound primary keys (one with 4 fields in the key) and the queries are a nightmare is some places - especially because this is a big project with well over 100 stored procs.
note also that i'm not sure you can do a "compound foreign key". in other words you may still have to have an artificial primary key to establish relationships.
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Thursday, March 29, 2012
how organize a junction table?
Hi
I am importing XML documents with XML bulk load.
The tables look like this (kind of) with a junction table C connecting tableA and tableB.
tableA(nameID integer PRIMARY Key IDENTITY, name string)
tableB(placeID integer PRIMARY Key IDENTITY , placename string)
tableC(placeID integer FOREIGN Key, nameID integer FOREIGN key)
NameID and placeID have the property identity, and the xsd is mapped so that they are inserted in tableC as well as their parent table when they are generated.
My problem now is, how do I make sure that each placename or name only occur once with one ID? When trying to insert a placename that already exist in the database, I dont want to generate a new placeID for it. Instead, I would like to fetch the existing placeID and inserting it with the new nameID in tableC. It should be a quite common problem, since most many to many relationships would result in a junction table. As mentioned, I am loading the data with XML bulk load, I dont know if that would complicate matter..
All help will be greatly appreciated!
regards, Helenayou're right, it is a common problem
when using a surrogate key, such as an identity column, remember that each table must actually have a "real" primary key (called a candidate key in data modelling terminology)
all you have to do is declare a unique constraint on the real key, and the database will then prvent you from entering duplicates
rudy|||Thanks for your reply.
The Identity columns in tableA and tableB are also the "real" primary key. The "real" key in tableC would be a composite key made up by tableA and tableB's primary keys. Name in tableA and placename in tableB are declared unique. The composite key in tableC should also be unique, then the database will prevent duplicates values. But when trying to insert a new name but an old placename for example, how do I get the new nameID but the old placeID into tableC? It feels like I need to do a search to see if the placename exists, if no just insert data, if yes fetch placeID for that particular placename and insert. And this would be necessary whenever a junction table is involved.
/Helena|||perhaps you could do some research on the difference between surrogate and natural keys, but i assure you, even if you declare an identity column as a primary key, so that as far as the database is concerned it is the primary key, it isn't the "real" primary key, not from a modelling perspective, not in regard to duplicates
yes, make the composite key in tableC its primary key
as for what to do with potential duplicates, and how to find the original entry, so duplcates are not entered, and so that the tableC row links the correct parent rows, i'm sorry, i cannot help you, i've never written an XSD application
perhaps you could eliminate the dupes from the data before you attempt to load it with XML
rudy|||Ok, I see your point..
Eliminating the duplicates is unfortunately not an option.
Anyway, thanks for your help!sql
I am importing XML documents with XML bulk load.
The tables look like this (kind of) with a junction table C connecting tableA and tableB.
tableA(nameID integer PRIMARY Key IDENTITY, name string)
tableB(placeID integer PRIMARY Key IDENTITY , placename string)
tableC(placeID integer FOREIGN Key, nameID integer FOREIGN key)
NameID and placeID have the property identity, and the xsd is mapped so that they are inserted in tableC as well as their parent table when they are generated.
My problem now is, how do I make sure that each placename or name only occur once with one ID? When trying to insert a placename that already exist in the database, I dont want to generate a new placeID for it. Instead, I would like to fetch the existing placeID and inserting it with the new nameID in tableC. It should be a quite common problem, since most many to many relationships would result in a junction table. As mentioned, I am loading the data with XML bulk load, I dont know if that would complicate matter..
All help will be greatly appreciated!
regards, Helenayou're right, it is a common problem
when using a surrogate key, such as an identity column, remember that each table must actually have a "real" primary key (called a candidate key in data modelling terminology)
all you have to do is declare a unique constraint on the real key, and the database will then prvent you from entering duplicates
rudy|||Thanks for your reply.
The Identity columns in tableA and tableB are also the "real" primary key. The "real" key in tableC would be a composite key made up by tableA and tableB's primary keys. Name in tableA and placename in tableB are declared unique. The composite key in tableC should also be unique, then the database will prevent duplicates values. But when trying to insert a new name but an old placename for example, how do I get the new nameID but the old placeID into tableC? It feels like I need to do a search to see if the placename exists, if no just insert data, if yes fetch placeID for that particular placename and insert. And this would be necessary whenever a junction table is involved.
/Helena|||perhaps you could do some research on the difference between surrogate and natural keys, but i assure you, even if you declare an identity column as a primary key, so that as far as the database is concerned it is the primary key, it isn't the "real" primary key, not from a modelling perspective, not in regard to duplicates
yes, make the composite key in tableC its primary key
as for what to do with potential duplicates, and how to find the original entry, so duplcates are not entered, and so that the tableC row links the correct parent rows, i'm sorry, i cannot help you, i've never written an XSD application
perhaps you could eliminate the dupes from the data before you attempt to load it with XML
rudy|||Ok, I see your point..
Eliminating the duplicates is unfortunately not an option.
Anyway, thanks for your help!sql
Wednesday, March 28, 2012
How much space does the long character data type use.
We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
One of the tables we use have a COMMENT field which is a
Long Character datatype. In SQL the data type show up
as 'Text'. How does this data type utilize disk space?
Would the size grow based on the number of characters
entered in the field or does the system reserve space as
it does with other data type. How much space does it
reserve when a field is the long character datatype.
There is a 16 byte overhead on the text, ntext, and image data types.
If your data in these columns is small, look at "Text in Row Data" in BOL.
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> One of the tables we use have a COMMENT field which is a
> Long Character datatype. In SQL the data type show up
> as 'Text'. How does this data type utilize disk space?
> Would the size grow based on the number of characters
> entered in the field or does the system reserve space as
> it does with other data type. How much space does it
> reserve when a field is the long character datatype.
>
|||Hi Barry,
Thanks you for replying.
What is puzzling me is that when I run sp_help on the
table I see that the EMPLID field is CHAR with a length of
11 which I can understand because you cannot enter more
than 11 characters in the EMPLID field. However the
COMMENT field is TEXT with a length of 16 but you can
enter much more than 16 characters.
How is this possible and how does it affect the disk space
utilization?
>--Original Message--
>There is a 16 byte overhead on the text, ntext, and image
data types.
>If your data in these columns is small, look at "Text in
Row Data" in BOL.
>--
>Barry McAuslin
>Look inside your SQL Server files with SQL File Explorer.
>Go to http://www.sqlfe.com for more information.
>"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>
>.
>
|||The max length for text data is 2GB and the reported size is the 'text in
row' size. With the default length of 16, only a pointer to the separate
text pages is stored in the data row. You can adjust the 'text in row size
to control how much text data is stored in the data row but the max length
is still 2GB.
See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Emerson" <edixon64@.hotmail.com> wrote in message
news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...[vbcol=seagreen]
> Hi Barry,
> Thanks you for replying.
> What is puzzling me is that when I run sp_help on the
> table I see that the EMPLID field is CHAR with a length of
> 11 which I can understand because you cannot enter more
> than 11 characters in the EMPLID field. However the
> COMMENT field is TEXT with a length of 16 but you can
> enter much more than 16 characters.
> How is this possible and how does it affect the disk space
> utilization?
> data types.
> Row Data" in BOL.
|||Hi Dan,
Thanks a lot. This clears up my question.
>--Original Message--
>The max length for text data is 2GB and the reported size
is the 'text in
>row' size. With the default length of 16, only a pointer
to the separate
>text pages is stored in the data row. You can adjust
the 'text in row size
>to control how much text data is stored in the data row
but the max length
>is still 2GB.
>See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for
details.[vbcol=seagreen]
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Emerson" <edixon64@.hotmail.com> wrote in message
>news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
of[vbcol=seagreen]
space[vbcol=seagreen]
image[vbcol=seagreen]
in[vbcol=seagreen]
Explorer.[vbcol=seagreen]
2000.[vbcol=seagreen]
is a[vbcol=seagreen]
up[vbcol=seagreen]
space?[vbcol=seagreen]
space as
>
>.
>
One of the tables we use have a COMMENT field which is a
Long Character datatype. In SQL the data type show up
as 'Text'. How does this data type utilize disk space?
Would the size grow based on the number of characters
entered in the field or does the system reserve space as
it does with other data type. How much space does it
reserve when a field is the long character datatype.
There is a 16 byte overhead on the text, ntext, and image data types.
If your data in these columns is small, look at "Text in Row Data" in BOL.
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> One of the tables we use have a COMMENT field which is a
> Long Character datatype. In SQL the data type show up
> as 'Text'. How does this data type utilize disk space?
> Would the size grow based on the number of characters
> entered in the field or does the system reserve space as
> it does with other data type. How much space does it
> reserve when a field is the long character datatype.
>
|||Hi Barry,
Thanks you for replying.
What is puzzling me is that when I run sp_help on the
table I see that the EMPLID field is CHAR with a length of
11 which I can understand because you cannot enter more
than 11 characters in the EMPLID field. However the
COMMENT field is TEXT with a length of 16 but you can
enter much more than 16 characters.
How is this possible and how does it affect the disk space
utilization?
>--Original Message--
>There is a 16 byte overhead on the text, ntext, and image
data types.
>If your data in these columns is small, look at "Text in
Row Data" in BOL.
>--
>Barry McAuslin
>Look inside your SQL Server files with SQL File Explorer.
>Go to http://www.sqlfe.com for more information.
>"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>
>.
>
|||The max length for text data is 2GB and the reported size is the 'text in
row' size. With the default length of 16, only a pointer to the separate
text pages is stored in the data row. You can adjust the 'text in row size
to control how much text data is stored in the data row but the max length
is still 2GB.
See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Emerson" <edixon64@.hotmail.com> wrote in message
news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...[vbcol=seagreen]
> Hi Barry,
> Thanks you for replying.
> What is puzzling me is that when I run sp_help on the
> table I see that the EMPLID field is CHAR with a length of
> 11 which I can understand because you cannot enter more
> than 11 characters in the EMPLID field. However the
> COMMENT field is TEXT with a length of 16 but you can
> enter much more than 16 characters.
> How is this possible and how does it affect the disk space
> utilization?
> data types.
> Row Data" in BOL.
|||Hi Dan,
Thanks a lot. This clears up my question.
>--Original Message--
>The max length for text data is 2GB and the reported size
is the 'text in
>row' size. With the default length of 16, only a pointer
to the separate
>text pages is stored in the data row. You can adjust
the 'text in row size
>to control how much text data is stored in the data row
but the max length
>is still 2GB.
>See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for
details.[vbcol=seagreen]
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Emerson" <edixon64@.hotmail.com> wrote in message
>news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
of[vbcol=seagreen]
space[vbcol=seagreen]
image[vbcol=seagreen]
in[vbcol=seagreen]
Explorer.[vbcol=seagreen]
2000.[vbcol=seagreen]
is a[vbcol=seagreen]
up[vbcol=seagreen]
space?[vbcol=seagreen]
space as
>
>.
>
How much space does the long character data type use.
We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
One of the tables we use have a COMMENT field which is a
Long Character datatype. In SQL the data type show up
as 'Text'. How does this data type utilize disk space?
Would the size grow based on the number of characters
entered in the field or does the system reserve space as
it does with other data type. How much space does it
reserve when a field is the long character datatype.There is a 16 byte overhead on the text, ntext, and image data types.
If your data in these columns is small, look at "Text in Row Data" in BOL.
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> One of the tables we use have a COMMENT field which is a
> Long Character datatype. In SQL the data type show up
> as 'Text'. How does this data type utilize disk space?
> Would the size grow based on the number of characters
> entered in the field or does the system reserve space as
> it does with other data type. How much space does it
> reserve when a field is the long character datatype.
>|||Hi Barry,
Thanks you for replying.
What is puzzling me is that when I run sp_help on the
table I see that the EMPLID field is CHAR with a length of
11 which I can understand because you cannot enter more
than 11 characters in the EMPLID field. However the
COMMENT field is TEXT with a length of 16 but you can
enter much more than 16 characters.
How is this possible and how does it affect the disk space
utilization?
>--Original Message--
>There is a 16 byte overhead on the text, ntext, and image
data types.
>If your data in these columns is small, look at "Text in
Row Data" in BOL.
>--
>Barry McAuslin
>Look inside your SQL Server files with SQL File Explorer.
>Go to http://www.sqlfe.com for more information.
>"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
>> One of the tables we use have a COMMENT field which is a
>> Long Character datatype. In SQL the data type show up
>> as 'Text'. How does this data type utilize disk space?
>> Would the size grow based on the number of characters
>> entered in the field or does the system reserve space as
>> it does with other data type. How much space does it
>> reserve when a field is the long character datatype.
>>
>
>.
>|||The max length for text data is 2GB and the reported size is the 'text in
row' size. With the default length of 16, only a pointer to the separate
text pages is stored in the data row. You can adjust the 'text in row size
to control how much text data is stored in the data row but the max length
is still 2GB.
See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Emerson" <edixon64@.hotmail.com> wrote in message
news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
> Hi Barry,
> Thanks you for replying.
> What is puzzling me is that when I run sp_help on the
> table I see that the EMPLID field is CHAR with a length of
> 11 which I can understand because you cannot enter more
> than 11 characters in the EMPLID field. However the
> COMMENT field is TEXT with a length of 16 but you can
> enter much more than 16 characters.
> How is this possible and how does it affect the disk space
> utilization?
> >--Original Message--
> >There is a 16 byte overhead on the text, ntext, and image
> data types.
> >
> >If your data in these columns is small, look at "Text in
> Row Data" in BOL.
> >
> >--
> >
> >Barry McAuslin
> >
> >Look inside your SQL Server files with SQL File Explorer.
> >Go to http://www.sqlfe.com for more information.
> >
> >"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
> >news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> >> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> >> One of the tables we use have a COMMENT field which is a
> >> Long Character datatype. In SQL the data type show up
> >> as 'Text'. How does this data type utilize disk space?
> >> Would the size grow based on the number of characters
> >> entered in the field or does the system reserve space as
> >> it does with other data type. How much space does it
> >> reserve when a field is the long character datatype.
> >>
> >>
> >
> >
> >.
> >|||Hi Dan,
Thanks a lot. This clears up my question.
>--Original Message--
>The max length for text data is 2GB and the reported size
is the 'text in
>row' size. With the default length of 16, only a pointer
to the separate
>text pages is stored in the data row. You can adjust
the 'text in row size
>to control how much text data is stored in the data row
but the max length
>is still 2GB.
>See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for
details.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Emerson" <edixon64@.hotmail.com> wrote in message
>news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
>> Hi Barry,
>> Thanks you for replying.
>> What is puzzling me is that when I run sp_help on the
>> table I see that the EMPLID field is CHAR with a length
of
>> 11 which I can understand because you cannot enter more
>> than 11 characters in the EMPLID field. However the
>> COMMENT field is TEXT with a length of 16 but you can
>> enter much more than 16 characters.
>> How is this possible and how does it affect the disk
space
>> utilization?
>> >--Original Message--
>> >There is a 16 byte overhead on the text, ntext, and
image
>> data types.
>> >
>> >If your data in these columns is small, look at "Text
in
>> Row Data" in BOL.
>> >
>> >--
>> >
>> >Barry McAuslin
>> >
>> >Look inside your SQL Server files with SQL File
Explorer.
>> >Go to http://www.sqlfe.com for more information.
>> >
>> >"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>> >news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>> >> We currently use PeopleSoft HRMS on MS SQL SERVER
2000.
>> >> One of the tables we use have a COMMENT field which
is a
>> >> Long Character datatype. In SQL the data type show
up
>> >> as 'Text'. How does this data type utilize disk
space?
>> >> Would the size grow based on the number of characters
>> >> entered in the field or does the system reserve
space as
>> >> it does with other data type. How much space does it
>> >> reserve when a field is the long character datatype.
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
One of the tables we use have a COMMENT field which is a
Long Character datatype. In SQL the data type show up
as 'Text'. How does this data type utilize disk space?
Would the size grow based on the number of characters
entered in the field or does the system reserve space as
it does with other data type. How much space does it
reserve when a field is the long character datatype.There is a 16 byte overhead on the text, ntext, and image data types.
If your data in these columns is small, look at "Text in Row Data" in BOL.
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> One of the tables we use have a COMMENT field which is a
> Long Character datatype. In SQL the data type show up
> as 'Text'. How does this data type utilize disk space?
> Would the size grow based on the number of characters
> entered in the field or does the system reserve space as
> it does with other data type. How much space does it
> reserve when a field is the long character datatype.
>|||Hi Barry,
Thanks you for replying.
What is puzzling me is that when I run sp_help on the
table I see that the EMPLID field is CHAR with a length of
11 which I can understand because you cannot enter more
than 11 characters in the EMPLID field. However the
COMMENT field is TEXT with a length of 16 but you can
enter much more than 16 characters.
How is this possible and how does it affect the disk space
utilization?
>--Original Message--
>There is a 16 byte overhead on the text, ntext, and image
data types.
>If your data in these columns is small, look at "Text in
Row Data" in BOL.
>--
>Barry McAuslin
>Look inside your SQL Server files with SQL File Explorer.
>Go to http://www.sqlfe.com for more information.
>"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
>> One of the tables we use have a COMMENT field which is a
>> Long Character datatype. In SQL the data type show up
>> as 'Text'. How does this data type utilize disk space?
>> Would the size grow based on the number of characters
>> entered in the field or does the system reserve space as
>> it does with other data type. How much space does it
>> reserve when a field is the long character datatype.
>>
>
>.
>|||The max length for text data is 2GB and the reported size is the 'text in
row' size. With the default length of 16, only a pointer to the separate
text pages is stored in the data row. You can adjust the 'text in row size
to control how much text data is stored in the data row but the max length
is still 2GB.
See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Emerson" <edixon64@.hotmail.com> wrote in message
news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
> Hi Barry,
> Thanks you for replying.
> What is puzzling me is that when I run sp_help on the
> table I see that the EMPLID field is CHAR with a length of
> 11 which I can understand because you cannot enter more
> than 11 characters in the EMPLID field. However the
> COMMENT field is TEXT with a length of 16 but you can
> enter much more than 16 characters.
> How is this possible and how does it affect the disk space
> utilization?
> >--Original Message--
> >There is a 16 byte overhead on the text, ntext, and image
> data types.
> >
> >If your data in these columns is small, look at "Text in
> Row Data" in BOL.
> >
> >--
> >
> >Barry McAuslin
> >
> >Look inside your SQL Server files with SQL File Explorer.
> >Go to http://www.sqlfe.com for more information.
> >
> >"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
> >news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> >> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> >> One of the tables we use have a COMMENT field which is a
> >> Long Character datatype. In SQL the data type show up
> >> as 'Text'. How does this data type utilize disk space?
> >> Would the size grow based on the number of characters
> >> entered in the field or does the system reserve space as
> >> it does with other data type. How much space does it
> >> reserve when a field is the long character datatype.
> >>
> >>
> >
> >
> >.
> >|||Hi Dan,
Thanks a lot. This clears up my question.
>--Original Message--
>The max length for text data is 2GB and the reported size
is the 'text in
>row' size. With the default length of 16, only a pointer
to the separate
>text pages is stored in the data row. You can adjust
the 'text in row size
>to control how much text data is stored in the data row
but the max length
>is still 2GB.
>See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for
details.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Emerson" <edixon64@.hotmail.com> wrote in message
>news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
>> Hi Barry,
>> Thanks you for replying.
>> What is puzzling me is that when I run sp_help on the
>> table I see that the EMPLID field is CHAR with a length
of
>> 11 which I can understand because you cannot enter more
>> than 11 characters in the EMPLID field. However the
>> COMMENT field is TEXT with a length of 16 but you can
>> enter much more than 16 characters.
>> How is this possible and how does it affect the disk
space
>> utilization?
>> >--Original Message--
>> >There is a 16 byte overhead on the text, ntext, and
image
>> data types.
>> >
>> >If your data in these columns is small, look at "Text
in
>> Row Data" in BOL.
>> >
>> >--
>> >
>> >Barry McAuslin
>> >
>> >Look inside your SQL Server files with SQL File
Explorer.
>> >Go to http://www.sqlfe.com for more information.
>> >
>> >"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>> >news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>> >> We currently use PeopleSoft HRMS on MS SQL SERVER
2000.
>> >> One of the tables we use have a COMMENT field which
is a
>> >> Long Character datatype. In SQL the data type show
up
>> >> as 'Text'. How does this data type utilize disk
space?
>> >> Would the size grow based on the number of characters
>> >> entered in the field or does the system reserve
space as
>> >> it does with other data type. How much space does it
>> >> reserve when a field is the long character datatype.
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
Monday, March 26, 2012
How much performance gained by qualifying tables with dbo. in the database?
Hello Folks:
The great majority of our queries and stored procedures do not
reference the tables with the dbo.table_name format. Due to some
production issues, we're not facing a situation that is forcing us to
redo our database by scripting it out, making a few modificationsm, and
importing the data right back into it. (please don't aske me why!)
I believe now is a perfect opportunity to owner-qualify all references
to the tables with dbo.table_name in the database scripts, and I
strongly believe the performance gain will be significant.
I need to sell this idea to my boss who thinks the risks of
manupulating the production database scripts is far graver than its
benefits, and he wants to now "how much" performance is gained by doing
this.
What do you folks think is a good answer to him? How can I show some
tangible benefits in terms of "gained performance"?
Thanks for you help!Hi Dan
Using the dbo prefix should help cache hits, and the performance gain will
depend on how busy your server is, it could be very little, but then I would
implement it regardless as it is "using best practices". Check out the
current cache misses using SQL profiler. You would also need to add prefixes
for stored procedures, views etc... At the same time you may want to check
the case being consistent.
If you don't use source code control then maybe this is the time start using
it!!
You can gain confidence in the outcome by comparing the two databases.
You should also do a full regression test, and it sounds like your superior
does not have confidence in the outcome of this.
Make sure that you cover all eventualities in the implementation plan and
have backout strategies at each stage. Do some trail runs (on copies of live
data) to get timings and proof that it works.
John
"Dan Gorrison" wrote:
> Hello Folks:
> The great majority of our queries and stored procedures do not
> reference the tables with the dbo.table_name format. Due to some
> production issues, we're not facing a situation that is forcing us to
> redo our database by scripting it out, making a few modificationsm, and
> importing the data right back into it. (please don't aske me why!)
> I believe now is a perfect opportunity to owner-qualify all references
> to the tables with dbo.table_name in the database scripts, and I
> strongly believe the performance gain will be significant.
>
> I need to sell this idea to my boss who thinks the risks of
> manupulating the production database scripts is far graver than its
> benefits, and he wants to now "how much" performance is gained by doing
> this.
>
> What do you folks think is a good answer to him? How can I show some
> tangible benefits in terms of "gained performance"?
>
> Thanks for you help!
>
The great majority of our queries and stored procedures do not
reference the tables with the dbo.table_name format. Due to some
production issues, we're not facing a situation that is forcing us to
redo our database by scripting it out, making a few modificationsm, and
importing the data right back into it. (please don't aske me why!)
I believe now is a perfect opportunity to owner-qualify all references
to the tables with dbo.table_name in the database scripts, and I
strongly believe the performance gain will be significant.
I need to sell this idea to my boss who thinks the risks of
manupulating the production database scripts is far graver than its
benefits, and he wants to now "how much" performance is gained by doing
this.
What do you folks think is a good answer to him? How can I show some
tangible benefits in terms of "gained performance"?
Thanks for you help!Hi Dan
Using the dbo prefix should help cache hits, and the performance gain will
depend on how busy your server is, it could be very little, but then I would
implement it regardless as it is "using best practices". Check out the
current cache misses using SQL profiler. You would also need to add prefixes
for stored procedures, views etc... At the same time you may want to check
the case being consistent.
If you don't use source code control then maybe this is the time start using
it!!
You can gain confidence in the outcome by comparing the two databases.
You should also do a full regression test, and it sounds like your superior
does not have confidence in the outcome of this.
Make sure that you cover all eventualities in the implementation plan and
have backout strategies at each stage. Do some trail runs (on copies of live
data) to get timings and proof that it works.
John
"Dan Gorrison" wrote:
> Hello Folks:
> The great majority of our queries and stored procedures do not
> reference the tables with the dbo.table_name format. Due to some
> production issues, we're not facing a situation that is forcing us to
> redo our database by scripting it out, making a few modificationsm, and
> importing the data right back into it. (please don't aske me why!)
> I believe now is a perfect opportunity to owner-qualify all references
> to the tables with dbo.table_name in the database scripts, and I
> strongly believe the performance gain will be significant.
>
> I need to sell this idea to my boss who thinks the risks of
> manupulating the production database scripts is far graver than its
> benefits, and he wants to now "how much" performance is gained by doing
> this.
>
> What do you folks think is a good answer to him? How can I show some
> tangible benefits in terms of "gained performance"?
>
> Thanks for you help!
>
Friday, March 23, 2012
How MERGE few databases to big one
Hi All,
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
Regards
Actually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>
|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp
|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
..ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
Regards
Actually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>
|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp
|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
..ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
How MERGE few databases to big one
Hi All,
I have to merge few different databases to one big common database :)
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
--
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database :)
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
--
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>sql
I have to merge few different databases to one big common database :)
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
--
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database :)
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
--
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>sql
How MERGE few databases to big one
Hi All,
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables i
n
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign key
s
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring ove
r
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups> )... that is, physically move the fil
es
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables i
n
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign key
s
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring ove
r
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups> )... that is, physically move the fil
es
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
how many tables a dataset can store at a time?
hello friend,
how many tables a dataset can store at a time? can u tell me.
There is no restriction for normal usage, because you'll encounter OutOfMemoryException before reaching actual limit, which is 2^30 datatablessql
how many tables a dataset can store at a time? can u tell me.
Quote:
Originally Posted by mcasaurabhsumit
hello friend,
how many tables a dataset can store at a time? can u tell me.
There is no restriction for normal usage, because you'll encounter OutOfMemoryException before reaching actual limit, which is 2^30 datatablessql
Wednesday, March 21, 2012
how many records the database is taking per day
Hi everyone,
If I don't have a field for DateEntered in my tables, is
there any System Tables that I can find out how many
records the database is taking per day?
Thank you sooooo much!!!!
AnnieYou could create an ON INSERT trigger to log each time someone INSERTs into your table
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||This is a multi-part message in MIME format.
--=_NextPart_000_0261_01C3600F.F913DBB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_0261_01C3600F.F913DBB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and store the =result in a table or spreadsheet, then calculate the difference from previous day.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie" wrote in message news:021b01c36030$d4=b1baa0$a301280a@.phx.gbl...Hi everyone, If I don't have a field for DateEntered in my =tables, is there any System Tables that I can find out how many records the =database is taking per day?Thank you sooooo much!!!!Annie
--=_NextPart_000_0261_01C3600F.F913DBB0--|||If you are just wanting inserts, then you could build a job to do a
sp_spaceused <table_name> into another table. Just have the job run in what
ever interval you want. You could also export sysindexes as well.
If you are wanting DELETE, UPDATE and INSERT, I have no real good answer. I
keep track of daily DUI, however I have replication in place and that makes
it very easy.
"Annie" <piyomeme@.yahoo.com> wrote in message
news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
> Hi everyone,
> If I don't have a field for DateEntered in my tables, is
> there any System Tables that I can find out how many
> records the database is taking per day?
> Thank you sooooo much!!!!
> Annie|||This is a multi-part message in MIME format.
--=_NextPart_000_0094_01C36009.FA1A36E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
To build on his statement and my other one. SELECT COUNT(*) would work, =but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_0094_01C36009.FA1A36E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
To build on his statement and my other =one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"= wrote in message news:elWpGGDYDHA.1784=@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and store =the result in a table or spreadsheet, then calculate the difference from =previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie" wrote in =message news:021b01c36030$d4=b1baa0$a301280a@.phx.gbl...Hi everyone, If I don't have a field for DateEntered in my =tables, is there any System Tables that I can find out how many =records the database is taking per day?Thank you sooooo =much!!!!Annie
--=_NextPart_000_0094_01C36009.FA1A36E0--|||Agreed
With the information given we know nothing of "Hits" per day. I would personally go with Tom's idea and take a SELECT COUNT(*) from each table put it in a spreadsheet and calculate the differences.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||This is a multi-part message in MIME format.
--=_NextPart_000_0293_01C36013.7CC0EF90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
It depends on how accurate they want the numbers. I'd go for adding a =last updated column on the target tables and then adding a nonclustered =index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would work, =but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie --=_NextPart_000_0293_01C36013.7CC0EF90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It depends on how accurate they want =the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the query,
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks" =wrote in message news:OcXClPDYDHA.1900=@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other =one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"= wrote in message news:elWpGGDYDHA.1784=@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and store =the result in a table or spreadsheet, then calculate the difference from =previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie" wrote in =message news:021b01c36030$d4=b1baa0$a301280a@.phx.gbl...Hi everyone, If I don't have a field for DateEntered in my =tables, is there any System Tables that I can find out how many =records the database is taking per day?Thank you sooooo =much!!!!Annie
--=_NextPart_000_0293_01C36013.7CC0EF90--|||Thank You for helping~
According to Books-On-Line, The value in the timestamp
column is
updated every time a row containing a timestamp column is
inserted or
updated. And the timestamp field is in Binary code.
I found a timestamp field in my DB, is there any way I can
decode the
timestamp's binary code to readable text in order to
identify the record
updated time?
Thank you~~
Annie
>--Original Message--
>Agreed
>With the information given we know nothing of "Hits" per
day. I would personally go with Tom's idea and take a
SELECT COUNT(*) from each table put it in a spreadsheet
and calculate the differences.
>
>
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>.
>|||Thank You for helping~
According to Books-On-Line, The value in the timestamp
column is
updated every time a row containing a timestamp column is
inserted or
updated. And the timestamp field is in Binary code.
I found a timestamp field in my DB, is there any way I can
decode the
timestamp's binary code to readable text in order to
identify the record
updated time?
Thank you~~
Annie
>--Original Message--
>If you are just wanting inserts, then you could build a
job to do a
>sp_spaceused <table_name> into another table. Just have
the job run in what
>ever interval you want. You could also export sysindexes
as well.
>If you are wanting DELETE, UPDATE and INSERT, I have no
real good answer. I
>keep track of daily DUI, however I have replication in
place and that makes
>it very easy.
>
>"Annie" <piyomeme@.yahoo.com> wrote in message
>news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
>> Hi everyone,
>> If I don't have a field for DateEntered in my tables,
is
>> there any System Tables that I can find out how many
>> records the database is taking per day?
>> Thank you sooooo much!!!!
>> Annie
>
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_00C7_01C3600E.297F40C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This would work too, but you are still increasing size and decreasing =response. I understand we do not know the size and other specs, however =it all about least impact on the server and applications. Heck I have =some crappy in house and 3rd party apps that will not let me change =schema without blowing up. As far as accuracy, that is what AVG is =for:)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OrVD8UDYDHA.2236@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they want the numbers. I'd go for adding a =last updated column on the target tables and then adding a nonclustered =index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would =work, but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_00C7_01C3600E.297F40C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
This would work too, but you are still =increasing size and decreasing response. I understand we do not know the =size and other specs, however it all about least impact on the server and applications. Heck I have some crappy in house and 3rd party apps =that will not let me change schema without blowing up. As far as =accuracy, that is what AVG is for:)
"Tom Moreau"= wrote in message news:OrVD8UDYDHA.2236=@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they want =the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the =query,
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks" =wrote in message news:OcXClPDYDHA.1900=@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my =other one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"= wrote in message news:elWpGGDYDHA.1784=@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and =store the result in a table or spreadsheet, then calculate the difference =from previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie" wrote =in message news:021b01c36030$d4=b1baa0$a301280a@.phx.gbl...Hi everyone, If I don't have a field for DateEntered in =my tables, is there any System Tables that I can find out how many records the database is taking per day?Thank you sooooo much!!!!Annie
--=_NextPart_000_00C7_01C3600E.297F40C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_02D0_01C36017.9A333F70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I guess a third possibility is taking the most recent backup and =restoring it onto another box and running the stats there ...
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OLyRUgDYDHA.2448@.TK2MSFTNGP09.phx.gbl...
This would work too, but you are still increasing size and decreasing =response. I understand we do not know the size and other specs, however =it all about least impact on the server and applications. Heck I have =some crappy in house and 3rd party apps that will not let me change =schema without blowing up. As far as accuracy, that is what AVG is =for:)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OrVD8UDYDHA.2236@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they want the numbers. I'd go for adding a =last updated column on the target tables and then adding a nonclustered =index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would =work, but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie --=_NextPart_000_02D0_01C36017.9A333F70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I guess a third possibility is taking =the most recent backup and restoring it onto another box and running the stats =there ...
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks" =wrote in message news:OLyRUgDYDHA.2448=@.TK2MSFTNGP09.phx.gbl...
This would work too, but you are still =increasing size and decreasing response. I understand we do not know the =size and other specs, however it all about least impact on the server and applications. Heck I have some crappy in house and 3rd party apps =that will not let me change schema without blowing up. As far as =accuracy, that is what AVG is for:)
"Tom Moreau"= wrote in message news:OrVD8UDYDHA.2236=@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they want =the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the =query,
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks" =wrote in message news:OcXClPDYDHA.1900=@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my =other one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"= wrote in message news:elWpGGDYDHA.1784=@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and =store the result in a table or spreadsheet, then calculate the difference =from previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie" wrote =in message news:021b01c36030$d4=b1baa0$a301280a@.phx.gbl...Hi everyone, If I don't have a field for DateEntered in =my tables, is there any System Tables that I can find out how many records the database is taking per day?Thank you sooooo much!!!!Annie
--=_NextPart_000_02D0_01C36017.9A333F70--|||This is a multi-part message in MIME format.
--=_NextPart_000_00ED_01C36015.3E3B4FC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Yup, that would work too. I would just dump sysindexes or use =sp_spaceused for the calculations though. I update stats on a regular =basis and the numbers will avg nicely. COUNT(*) will block potentially =unless you use (NOLOCK), in which case you are doing a dirty read which =throws off accuracy.
My only reason for raising all these responses, is that we need to =always design and think high end, highly volitile, high availablity and =big databases. COUNT(*), changing table schema and adding a trigger are =good ideas. They would probably be a great solution in several =environments, just not all.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:O$0OukDYDHA.1916@.TK2MSFTNGP12.phx.gbl...
I guess a third possibility is taking the most recent backup and =restoring it onto another box and running the stats there ...
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OLyRUgDYDHA.2448@.TK2MSFTNGP09.phx.gbl...
This would work too, but you are still increasing size and decreasing =response. I understand we do not know the size and other specs, however =it all about least impact on the server and applications. Heck I have =some crappy in house and 3rd party apps that will not let me change =schema without blowing up. As far as accuracy, that is what AVG is =for:)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OrVD8UDYDHA.2236@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they want the numbers. I'd go for adding =a last updated column on the target tables and then adding a =nonclustered index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would =work, but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_00ED_01C36015.3E3B4FC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Yup, that would work too. I would =just dump sysindexes or use sp_spaceused for the calculations though. I =update stats on a regular basis and the numbers will avg nicely. COUNT(*) will =block potentially unless you use (NOLOCK), in which case you are doing a =dirty read which throws off accuracy.
My only reason for raising all these =responses, is that we need to always design and think high end, highly volitile, high availablity and big databases. COUNT(*), changing =table schema and adding a trigger are good ideas. They would probably be a great =solution in several environments, just not all.
"Tom Moreau"= wrote in message news:O$0OukDYDHA.1916=@.TK2MSFTNGP12.phx.gbl...
I guess a third possibility is =taking the most recent backup and restoring it onto another box and running the stats =there ...
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks" =wrote in message news:OLyRUgDYDHA.2448=@.TK2MSFTNGP09.phx.gbl...
This would work too, but you are =still increasing size and decreasing response. I understand we do not know =the size and other specs, however it all about least impact on the server and applications. Heck I have some crappy in house and 3rd party =apps that will not let me change schema without blowing up. As far as =accuracy, that is what AVG is for:)
"Tom Moreau"= wrote in message news:OrVD8UDYDHA.2236=@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they =want the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the =query,
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks" =wrote in message news:OcXClPDYDHA.1900=@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my =other one. SELECT COUNT(*) would work, but in a large environment =might not be optimal.
"Tom Moreau"= wrote in message news:elWpGGDYDHA.1784=@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is =run SELECT COUNT (*) on the table(s) in question at the same time every day =and store the result in a table or spreadsheet, then calculate the =difference from previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie" =wrote in message news:021b01c36030$d4=b1baa0$a301280a@.phx.gbl...Hi everyone, If I don't have a field for DateEntered in =my tables, is there any System Tables that I can find out how =many records the database is taking per day?Thank you =sooooo much!!!!Annie =
--=_NextPart_000_00ED_01C36015.3E3B4FC0--
If I don't have a field for DateEntered in my tables, is
there any System Tables that I can find out how many
records the database is taking per day?
Thank you sooooo much!!!!
AnnieYou could create an ON INSERT trigger to log each time someone INSERTs into your table
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||This is a multi-part message in MIME format.
--=_NextPart_000_0261_01C3600F.F913DBB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_0261_01C3600F.F913DBB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and store the =result in a table or spreadsheet, then calculate the difference from previous day.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_0261_01C3600F.F913DBB0--|||If you are just wanting inserts, then you could build a job to do a
sp_spaceused <table_name> into another table. Just have the job run in what
ever interval you want. You could also export sysindexes as well.
If you are wanting DELETE, UPDATE and INSERT, I have no real good answer. I
keep track of daily DUI, however I have replication in place and that makes
it very easy.
"Annie" <piyomeme@.yahoo.com> wrote in message
news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
> Hi everyone,
> If I don't have a field for DateEntered in my tables, is
> there any System Tables that I can find out how many
> records the database is taking per day?
> Thank you sooooo much!!!!
> Annie|||This is a multi-part message in MIME format.
--=_NextPart_000_0094_01C36009.FA1A36E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
To build on his statement and my other one. SELECT COUNT(*) would work, =but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_0094_01C36009.FA1A36E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
To build on his statement and my other =one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and store =the result in a table or spreadsheet, then calculate the difference from =previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_0094_01C36009.FA1A36E0--|||Agreed
With the information given we know nothing of "Hits" per day. I would personally go with Tom's idea and take a SELECT COUNT(*) from each table put it in a spreadsheet and calculate the differences.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||This is a multi-part message in MIME format.
--=_NextPart_000_0293_01C36013.7CC0EF90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
It depends on how accurate they want the numbers. I'd go for adding a =last updated column on the target tables and then adding a nonclustered =index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would work, =but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie --=_NextPart_000_0293_01C36013.7CC0EF90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It depends on how accurate they want =the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the query,
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
To build on his statement and my other =one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and store =the result in a table or spreadsheet, then calculate the difference from =previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_0293_01C36013.7CC0EF90--|||Thank You for helping~
According to Books-On-Line, The value in the timestamp
column is
updated every time a row containing a timestamp column is
inserted or
updated. And the timestamp field is in Binary code.
I found a timestamp field in my DB, is there any way I can
decode the
timestamp's binary code to readable text in order to
identify the record
updated time?
Thank you~~
Annie
>--Original Message--
>Agreed
>With the information given we know nothing of "Hits" per
day. I would personally go with Tom's idea and take a
SELECT COUNT(*) from each table put it in a spreadsheet
and calculate the differences.
>
>
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>.
>|||Thank You for helping~
According to Books-On-Line, The value in the timestamp
column is
updated every time a row containing a timestamp column is
inserted or
updated. And the timestamp field is in Binary code.
I found a timestamp field in my DB, is there any way I can
decode the
timestamp's binary code to readable text in order to
identify the record
updated time?
Thank you~~
Annie
>--Original Message--
>If you are just wanting inserts, then you could build a
job to do a
>sp_spaceused <table_name> into another table. Just have
the job run in what
>ever interval you want. You could also export sysindexes
as well.
>If you are wanting DELETE, UPDATE and INSERT, I have no
real good answer. I
>keep track of daily DUI, however I have replication in
place and that makes
>it very easy.
>
>"Annie" <piyomeme@.yahoo.com> wrote in message
>news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
>> Hi everyone,
>> If I don't have a field for DateEntered in my tables,
is
>> there any System Tables that I can find out how many
>> records the database is taking per day?
>> Thank you sooooo much!!!!
>> Annie
>
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_00C7_01C3600E.297F40C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This would work too, but you are still increasing size and decreasing =response. I understand we do not know the size and other specs, however =it all about least impact on the server and applications. Heck I have =some crappy in house and 3rd party apps that will not let me change =schema without blowing up. As far as accuracy, that is what AVG is =for:)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OrVD8UDYDHA.2236@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they want the numbers. I'd go for adding a =last updated column on the target tables and then adding a nonclustered =index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would =work, but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_00C7_01C3600E.297F40C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
This would work too, but you are still =increasing size and decreasing response. I understand we do not know the =size and other specs, however it all about least impact on the server and applications. Heck I have some crappy in house and 3rd party apps =that will not let me change schema without blowing up. As far as =accuracy, that is what AVG is for:)
"Tom Moreau"
It depends on how accurate they want =the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the =query,
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
To build on his statement and my =other one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and =store the result in a table or spreadsheet, then calculate the difference =from previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_00C7_01C3600E.297F40C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_02D0_01C36017.9A333F70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I guess a third possibility is taking the most recent backup and =restoring it onto another box and running the stats there ...
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OLyRUgDYDHA.2448@.TK2MSFTNGP09.phx.gbl...
This would work too, but you are still increasing size and decreasing =response. I understand we do not know the size and other specs, however =it all about least impact on the server and applications. Heck I have =some crappy in house and 3rd party apps that will not let me change =schema without blowing up. As far as accuracy, that is what AVG is =for:)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OrVD8UDYDHA.2236@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they want the numbers. I'd go for adding a =last updated column on the target tables and then adding a nonclustered =index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would =work, but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie --=_NextPart_000_02D0_01C36017.9A333F70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I guess a third possibility is taking =the most recent backup and restoring it onto another box and running the stats =there ...
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
This would work too, but you are still =increasing size and decreasing response. I understand we do not know the =size and other specs, however it all about least impact on the server and applications. Heck I have some crappy in house and 3rd party apps =that will not let me change schema without blowing up. As far as =accuracy, that is what AVG is for:)
"Tom Moreau"
It depends on how accurate they want =the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the =query,
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
To build on his statement and my =other one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and =store the result in a table or spreadsheet, then calculate the difference =from previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_02D0_01C36017.9A333F70--|||This is a multi-part message in MIME format.
--=_NextPart_000_00ED_01C36015.3E3B4FC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Yup, that would work too. I would just dump sysindexes or use =sp_spaceused for the calculations though. I update stats on a regular =basis and the numbers will avg nicely. COUNT(*) will block potentially =unless you use (NOLOCK), in which case you are doing a dirty read which =throws off accuracy.
My only reason for raising all these responses, is that we need to =always design and think high end, highly volitile, high availablity and =big databases. COUNT(*), changing table schema and adding a trigger are =good ideas. They would probably be a great solution in several =environments, just not all.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:O$0OukDYDHA.1916@.TK2MSFTNGP12.phx.gbl...
I guess a third possibility is taking the most recent backup and =restoring it onto another box and running the stats there ...
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OLyRUgDYDHA.2448@.TK2MSFTNGP09.phx.gbl...
This would work too, but you are still increasing size and decreasing =response. I understand we do not know the size and other specs, however =it all about least impact on the server and applications. Heck I have =some crappy in house and 3rd party apps that will not let me change =schema without blowing up. As far as accuracy, that is what AVG is =for:)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OrVD8UDYDHA.2236@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they want the numbers. I'd go for adding =a last updated column on the target tables and then adding a =nonclustered index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would =work, but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_00ED_01C36015.3E3B4FC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Yup, that would work too. I would =just dump sysindexes or use sp_spaceused for the calculations though. I =update stats on a regular basis and the numbers will avg nicely. COUNT(*) will =block potentially unless you use (NOLOCK), in which case you are doing a =dirty read which throws off accuracy.
My only reason for raising all these =responses, is that we need to always design and think high end, highly volitile, high availablity and big databases. COUNT(*), changing =table schema and adding a trigger are good ideas. They would probably be a great =solution in several environments, just not all.
"Tom Moreau"
I guess a third possibility is =taking the most recent backup and restoring it onto another box and running the stats =there ...
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
This would work too, but you are =still increasing size and decreasing response. I understand we do not know =the size and other specs, however it all about least impact on the server and applications. Heck I have some crappy in house and 3rd party =apps that will not let me change schema without blowing up. As far as =accuracy, that is what AVG is for:)
"Tom Moreau"
It depends on how accurate they =want the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the =query,
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
To build on his statement and my =other one. SELECT COUNT(*) would work, but in a large environment =might not be optimal.
"Tom Moreau"
Nope. What you can do is =run SELECT COUNT (*) on the table(s) in question at the same time every day =and store the result in a table or spreadsheet, then calculate the =difference from previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_00ED_01C36015.3E3B4FC0--
Monday, March 19, 2012
How many CAL's/Processor licenses Do I need
I have a client who has a server with Dual processor.About 100 users will b
e accesing the data even though they do not create tables or manipulate the
data.
Please advise"Dan_Allstar1" <Dan_Allstar1@.discussions.microsoft.com> wrote in message
news:CC80F4D1-D871-408A-B6E0-4E42FBD95AA8@.microsoft.com...
> I have a client who has a server with Dual processor.About 100 users will
be accesing the data even though they do not create tables or manipulate the
data.
> Please advise
You'll want to give this a read:
http://www.microsoft.com/sql/howtobuy/serverpluscal.asp
Steve
e accesing the data even though they do not create tables or manipulate the
data.
Please advise"Dan_Allstar1" <Dan_Allstar1@.discussions.microsoft.com> wrote in message
news:CC80F4D1-D871-408A-B6E0-4E42FBD95AA8@.microsoft.com...
> I have a client who has a server with Dual processor.About 100 users will
be accesing the data even though they do not create tables or manipulate the
data.
> Please advise
You'll want to give this a read:
http://www.microsoft.com/sql/howtobuy/serverpluscal.asp
Steve
How many CAL's/Processor licenses Do I need
I have a client who has a server with Dual processor.About 100 users will be accesing the data even though they do not create tables or manipulate the data.
Please advise
"Dan_Allstar1" <Dan_Allstar1@.discussions.microsoft.com> wrote in message
news:CC80F4D1-D871-408A-B6E0-4E42FBD95AA8@.microsoft.com...
> I have a client who has a server with Dual processor.About 100 users will
be accesing the data even though they do not create tables or manipulate the
data.
> Please advise
You'll want to give this a read:
http://www.microsoft.com/sql/howtobuy/serverpluscal.asp
Steve
Please advise
"Dan_Allstar1" <Dan_Allstar1@.discussions.microsoft.com> wrote in message
news:CC80F4D1-D871-408A-B6E0-4E42FBD95AA8@.microsoft.com...
> I have a client who has a server with Dual processor.About 100 users will
be accesing the data even though they do not create tables or manipulate the
data.
> Please advise
You'll want to give this a read:
http://www.microsoft.com/sql/howtobuy/serverpluscal.asp
Steve
How many CAL's/Processor licenses Do I need
I have a client who has a server with Dual processor.About 100 users will be accesing the data even though they do not create tables or manipulate the data.
Please advise"Dan_Allstar1" <Dan_Allstar1@.discussions.microsoft.com> wrote in message
news:CC80F4D1-D871-408A-B6E0-4E42FBD95AA8@.microsoft.com...
> I have a client who has a server with Dual processor.About 100 users will
be accesing the data even though they do not create tables or manipulate the
data.
> Please advise
You'll want to give this a read:
http://www.microsoft.com/sql/howtobuy/serverpluscal.asp
Steve
Please advise"Dan_Allstar1" <Dan_Allstar1@.discussions.microsoft.com> wrote in message
news:CC80F4D1-D871-408A-B6E0-4E42FBD95AA8@.microsoft.com...
> I have a client who has a server with Dual processor.About 100 users will
be accesing the data even though they do not create tables or manipulate the
data.
> Please advise
You'll want to give this a read:
http://www.microsoft.com/sql/howtobuy/serverpluscal.asp
Steve
Monday, March 12, 2012
How long it take to finish replicate data
I have a database A include five tables, and have more than 1,500,000 rows. There is a replica database of A. First of all, there is no data in the two dbs. When I finish inserting data into A, the replica db seems still work, the log file size still changes. How can I know the replication finished or not? How long it will take to finish replicate 1,500,000 rows of data?Is this transactional replication or merge? If it is transactional you can use select * from distribution.dbo.msdistribution_status to determine how many of the rows have been replicated.
Friday, March 9, 2012
how long a table blocked
I have written a stored procedure to list out all tables in which
rows or the table itself is locked. The only information I am
not able to get is the time when the lock occurred. The way I
want is that if I run the procedure it should show all locks
on a table which are at least 5 or x seconds old. This way I can
avoid momentary locks on a table which go away after few seconds.
rows or the table itself is locked. The only information I am
not able to get is the time when the lock occurred. The way I
want is that if I run the procedure it should show all locks
on a table which are at least 5 or x seconds old. This way I can
avoid momentary locks on a table which go away after few seconds.
Which table and column of master database has that information?
Thanks.
--
email id is bogusrkusenet (rkusenet@.sympatico.ca) writes:
> I have written a stored procedure to list out all tables in which
> rows or the table itself is locked. The only information I am
> not able to get is the time when the lock occurred. The way I
> want is that if I run the procedure it should show all locks
> on a table which are at least 5 or x seconds old. This way I can
> avoid momentary locks on a table which go away after few seconds.
> Which table and column of master database has that information?
I'm afraid that this information is not available. There is a
req_lifetime column in syslockinfo, which is carefully document,
but the essence of the documentation is that this is an internal
column.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To my knowledge that info is not available. And maybe not so interesting (my
opinion) Technicaly what is interesting about locked table is the fact that
others have to wait because they want access to the table (or rows or keys
or whatever) in an incompatible mode.
The nice thing about this is that the time information about that fact is
available, but maybe hard to get through a stored proc. But a good sp
programmer should be able figure that out ;-)
The wait information is in sysprocesses, if the 'lastwaittype' is of type
'LCK..' a spid is waiting for a lock (if waittime > 0) (or 'waittype between
0x01 and 0x1f). 'Waitresource' will show what is being waited for TBL/KEY
etc. and waittime how long the spid is waiting now.
I hope that brings you closer to what you want..
--
regards,
Mario
http://www.sqlinternals.com
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:bn458j$sh9a5$1@.ID-75254.news.uni-berlin.de...
> I have written a stored procedure to list out all tables in which
> rows or the table itself is locked. The only information I am
> not able to get is the time when the lock occurred. The way I
> want is that if I run the procedure it should show all locks
> on a table which are at least 5 or x seconds old. This way I can
> avoid momentary locks on a table which go away after few seconds.
> Which table and column of master database has that information?
> Thanks.
> --
> email id is bogus|||"Mario" <nospam@.sqlinternals.com> wrote in message news:3f963fc0$0$58711$e4fe514c@.news.xs4all.nl...
> To my knowledge that info is not available. And maybe not so interesting (my
> opinion) Technicaly what is interesting about locked table is the fact that
> others have to wait because they want access to the table (or rows or keys
> or whatever) in an incompatible mode.
> The nice thing about this is that the time information about that fact is
> available, but maybe hard to get through a stored proc. But a good sp
> programmer should be able figure that out ;-)
> The wait information is in sysprocesses, if the 'lastwaittype' is of type
> 'LCK..' a spid is waiting for a lock (if waittime > 0) (or 'waittype between
> 0x01 and 0x1f). 'Waitresource' will show what is being waited for TBL/KEY
> etc. and waittime how long the spid is waiting now.
> I hope that brings you closer to what you want..
thanks Mario and Erland. With your help I am able to develop the stored procedure
we require.
--
email id is bogus
How lock few tables at ones?
In order to prevent dead lock we need to avoid situation that one
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?
"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order and
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>
|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com
|||in stored procedure
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?
"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order and
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>
|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com
|||in stored procedure
How lock few tables at ones?
In order to prevent dead lock we need to avoid situation that one
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order an
d
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com|||in stored procedure
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order an
d
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com|||in stored procedure
How lock few tables at ones?
In order to prevent dead lock we need to avoid situation that one
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order and
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com|||in stored procedure
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order and
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com|||in stored procedure
How is the row filter clause and join filter should be....
Hi,
I am new to replication, I have the following tables in my
database, and I would like to replicate to other
subscriber using Merge Replication:
tblProduct (Product master)
ProdID (PK)
ProdGrp (FK to Product grouping)
ProdDesc
tblProdGrp (Product grouping)
ProdGrp (PK)
ProdGrpDesc
tblCustomer (Customer master)
CustID (PK)
BranchID (FK to branch master)
OfferGrp (FK to trade offer grouping)
CustName
tblBranch (Branch master)
BranchID (PK)
BranchName
tblTradeOfferGroup (Trade offer grouping)
OfferGrp (PK)
OfferGrpDesc
tblTradeOffer (Trade offer)
OfferID (PK)
OfferGrp (FK to Trade offer grouping)
StartEffDate
EndEffDate
tblTradeOfferProduct (Trade offer's product)
SeqID (PK)
ProdGrp (PK/FK)
OfferID (PK/FK)
OfferQty
*Note: SeqID + ProdGrp + OfferID is unique
I need all rows from tblProduct, tblProdGrp, tblBranch,
tblTradeOfferGroup, tblTradeOffer, tblTradeOfferProduct to
replicate to subscriber, but only single branch's customer
in tblCustomer at subscriber. How should I configure the
row filter and join filter in my Merge Replication?
Currently, I configure as following:-
Row Filter:
tblCustomer row filter tblCustomer.BranchID = '001'
tblProduct <publish all rows>
tblProdGrp <publish all rows>
tblBranch <publish all rows>
tblTradeOfferGroup <publish all rows>
tblTradeOffer <publish all rows>
tblTradeOfferProduct <publish all rows>
Join Filter:
Filtered table Table to filter
tblTradeOfferGroup tblTradeOffer
tblTradeOffer.OfferGrp = tblTradeOfferGroup.OfferGrp
tblTradeOffer tblTradeOfferProduct
tblTradeOfferProduct.OfferID = tblTradeOffer.OfferID
But, tblTradeOfferProduct not replicated over. A conflict
occurs saying FOREIGN KEY constraint etc. The weird case
is, when I synchorise again, the rows publisher's
tblTradeOfferProduct are deleted.
Please advice.
Thank you.
HKM
I think, replicating tblTradeOfferProduct should fix your problem.
Since tblTradeOfferProduct has relations to tblTradeOffer (and I believe to
tblProduct too ) it is better to replicate this table.
Otherwise yuo have to declare all those relations as "NOT FOR REPLICATION"
Since you dont have "NOT FOR REPLICATION" whenever that are constraint
violatins you will see merge failing with constraint violations.
And once some entries fail to propagate to the subscriber, in the next
merge, compensating actions (deletes for all the failed inserts) are made
and hence you will see that those rows vanish from the database.
You can either set the constraints to "NOT FOR REPLICATION" or replicate the
tblTradeOfferProduct table too. One of them should fix the problem
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"HKM" <anonymous@.discussions.microsoft.com> wrote in message
news:04e801c49a0e$f4c9ebf0$a601280a@.phx.gbl...
> Hi,
> I am new to replication, I have the following tables in my
> database, and I would like to replicate to other
> subscriber using Merge Replication:
>
> tblProduct (Product master)
> --
> ProdID (PK)
> ProdGrp (FK to Product grouping)
> ProdDesc
> tblProdGrp (Product grouping)
> --
> ProdGrp (PK)
> ProdGrpDesc
> tblCustomer (Customer master)
> --
> CustID (PK)
> BranchID (FK to branch master)
> OfferGrp (FK to trade offer grouping)
> CustName
> tblBranch (Branch master)
> --
> BranchID (PK)
> BranchName
> tblTradeOfferGroup (Trade offer grouping)
> --
> OfferGrp (PK)
> OfferGrpDesc
> tblTradeOffer (Trade offer)
> --
> OfferID (PK)
> OfferGrp (FK to Trade offer grouping)
> StartEffDate
> EndEffDate
> tblTradeOfferProduct (Trade offer's product)
> --
> SeqID (PK)
> ProdGrp (PK/FK)
> OfferID (PK/FK)
> OfferQty
> *Note: SeqID + ProdGrp + OfferID is unique
> I need all rows from tblProduct, tblProdGrp, tblBranch,
> tblTradeOfferGroup, tblTradeOffer, tblTradeOfferProduct to
> replicate to subscriber, but only single branch's customer
> in tblCustomer at subscriber. How should I configure the
> row filter and join filter in my Merge Replication?
> Currently, I configure as following:-
> Row Filter:
> tblCustomer row filter tblCustomer.BranchID = '001'
> tblProduct <publish all rows>
> tblProdGrp <publish all rows>
> tblBranch <publish all rows>
> tblTradeOfferGroup <publish all rows>
> tblTradeOffer <publish all rows>
> tblTradeOfferProduct <publish all rows>
> Join Filter:
> Filtered table Table to filter
> tblTradeOfferGroup tblTradeOffer
> tblTradeOffer.OfferGrp = tblTradeOfferGroup.OfferGrp
> tblTradeOffer tblTradeOfferProduct
> tblTradeOfferProduct.OfferID = tblTradeOffer.OfferID
> But, tblTradeOfferProduct not replicated over. A conflict
> occurs saying FOREIGN KEY constraint etc. The weird case
> is, when I synchorise again, the rows publisher's
> tblTradeOfferProduct are deleted.
> Please advice.
> Thank you.
> HKM
>
I am new to replication, I have the following tables in my
database, and I would like to replicate to other
subscriber using Merge Replication:
tblProduct (Product master)
ProdID (PK)
ProdGrp (FK to Product grouping)
ProdDesc
tblProdGrp (Product grouping)
ProdGrp (PK)
ProdGrpDesc
tblCustomer (Customer master)
CustID (PK)
BranchID (FK to branch master)
OfferGrp (FK to trade offer grouping)
CustName
tblBranch (Branch master)
BranchID (PK)
BranchName
tblTradeOfferGroup (Trade offer grouping)
OfferGrp (PK)
OfferGrpDesc
tblTradeOffer (Trade offer)
OfferID (PK)
OfferGrp (FK to Trade offer grouping)
StartEffDate
EndEffDate
tblTradeOfferProduct (Trade offer's product)
SeqID (PK)
ProdGrp (PK/FK)
OfferID (PK/FK)
OfferQty
*Note: SeqID + ProdGrp + OfferID is unique
I need all rows from tblProduct, tblProdGrp, tblBranch,
tblTradeOfferGroup, tblTradeOffer, tblTradeOfferProduct to
replicate to subscriber, but only single branch's customer
in tblCustomer at subscriber. How should I configure the
row filter and join filter in my Merge Replication?
Currently, I configure as following:-
Row Filter:
tblCustomer row filter tblCustomer.BranchID = '001'
tblProduct <publish all rows>
tblProdGrp <publish all rows>
tblBranch <publish all rows>
tblTradeOfferGroup <publish all rows>
tblTradeOffer <publish all rows>
tblTradeOfferProduct <publish all rows>
Join Filter:
Filtered table Table to filter
tblTradeOfferGroup tblTradeOffer
tblTradeOffer.OfferGrp = tblTradeOfferGroup.OfferGrp
tblTradeOffer tblTradeOfferProduct
tblTradeOfferProduct.OfferID = tblTradeOffer.OfferID
But, tblTradeOfferProduct not replicated over. A conflict
occurs saying FOREIGN KEY constraint etc. The weird case
is, when I synchorise again, the rows publisher's
tblTradeOfferProduct are deleted.
Please advice.
Thank you.
HKM
I think, replicating tblTradeOfferProduct should fix your problem.
Since tblTradeOfferProduct has relations to tblTradeOffer (and I believe to
tblProduct too ) it is better to replicate this table.
Otherwise yuo have to declare all those relations as "NOT FOR REPLICATION"
Since you dont have "NOT FOR REPLICATION" whenever that are constraint
violatins you will see merge failing with constraint violations.
And once some entries fail to propagate to the subscriber, in the next
merge, compensating actions (deletes for all the failed inserts) are made
and hence you will see that those rows vanish from the database.
You can either set the constraints to "NOT FOR REPLICATION" or replicate the
tblTradeOfferProduct table too. One of them should fix the problem
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"HKM" <anonymous@.discussions.microsoft.com> wrote in message
news:04e801c49a0e$f4c9ebf0$a601280a@.phx.gbl...
> Hi,
> I am new to replication, I have the following tables in my
> database, and I would like to replicate to other
> subscriber using Merge Replication:
>
> tblProduct (Product master)
> --
> ProdID (PK)
> ProdGrp (FK to Product grouping)
> ProdDesc
> tblProdGrp (Product grouping)
> --
> ProdGrp (PK)
> ProdGrpDesc
> tblCustomer (Customer master)
> --
> CustID (PK)
> BranchID (FK to branch master)
> OfferGrp (FK to trade offer grouping)
> CustName
> tblBranch (Branch master)
> --
> BranchID (PK)
> BranchName
> tblTradeOfferGroup (Trade offer grouping)
> --
> OfferGrp (PK)
> OfferGrpDesc
> tblTradeOffer (Trade offer)
> --
> OfferID (PK)
> OfferGrp (FK to Trade offer grouping)
> StartEffDate
> EndEffDate
> tblTradeOfferProduct (Trade offer's product)
> --
> SeqID (PK)
> ProdGrp (PK/FK)
> OfferID (PK/FK)
> OfferQty
> *Note: SeqID + ProdGrp + OfferID is unique
> I need all rows from tblProduct, tblProdGrp, tblBranch,
> tblTradeOfferGroup, tblTradeOffer, tblTradeOfferProduct to
> replicate to subscriber, but only single branch's customer
> in tblCustomer at subscriber. How should I configure the
> row filter and join filter in my Merge Replication?
> Currently, I configure as following:-
> Row Filter:
> tblCustomer row filter tblCustomer.BranchID = '001'
> tblProduct <publish all rows>
> tblProdGrp <publish all rows>
> tblBranch <publish all rows>
> tblTradeOfferGroup <publish all rows>
> tblTradeOffer <publish all rows>
> tblTradeOfferProduct <publish all rows>
> Join Filter:
> Filtered table Table to filter
> tblTradeOfferGroup tblTradeOffer
> tblTradeOffer.OfferGrp = tblTradeOfferGroup.OfferGrp
> tblTradeOffer tblTradeOfferProduct
> tblTradeOfferProduct.OfferID = tblTradeOffer.OfferID
> But, tblTradeOfferProduct not replicated over. A conflict
> occurs saying FOREIGN KEY constraint etc. The weird case
> is, when I synchorise again, the rows publisher's
> tblTradeOfferProduct are deleted.
> Please advice.
> Thank you.
> HKM
>
Wednesday, March 7, 2012
How insert more then 4000 characters?
I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but in one step I can insert maximally 4000 character in uni-code.
Is there another way? Thanks' a lot.
I use such sequence T-SQL:
Declare
@.doc XML,
@.ixml nvarchar(4000)
SELECT @.ixml=(SELECT * FROM SAMPLE "d:SAMPLE" for XML AUTO)
SET @.ixml = 'SET @.Doc.modify(''
declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";
declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields";
insert ('+@.ixml+') into (dfs:myFields/dfs:dataFields)[1] '')'
exec sp_ExecuteSql @.STMT = @.ixml, @.PARAMS = N'@.DOC XML OUT', @.DOC = @.DOC OUT
Since you're using SQL2005 (I know that because of the XML data type), then you can use the new MAX length specification to provide up to 2GB of storage for @.ixml:
nvarchar(MAX)
This is a replacement for the old *LOB ntext data type, but much better because you get the best of both worlds. On one hand, you get the increased capacity that ntext provided, but on the other hand, Large Value Datatypes give you all of the benefits that nvarchar provides (you can compare data, index fields, convert, etc).
varchar(MAX) replaces text
nvarchar(MAX) replaces ntext
varbinary(MAX) replaces image
Thanks’ a lot Jason, it was useful for me, but I wanted to solve another problem:
To modify a XML document this way is too slow, do you know anther way?
You could also create a new XML instance by using FOR XML to bring the relational data and parts of the XML document (extracted with XQuery) together.
Best regards
Michael
Subscribe to:
Posts (Atom)