BEFORE Triggers: type

Showing posts with label type. Show all posts

Sunday, March 25, 2012

Best Replication Type please

Hello,
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
PeterSnapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> Hello,
>> We are implementing a high availablity solution to our
SQL
>> Servers, so if our production server goes down we have a
>> standby server ready to go.
>> So we would like to know the best type of replication,
>> bascially we want to update the stand-by server every 5
>> minutes. We cannot use the log shipping as we backup the
>> transaction log every 10 minutes.
>> Personally I think we should be using snapshot
>> replication, does anyone have any major disagreements
with
>> this ?
>> Thanks
>> Peter
>>
>
>.
>|||I suggest you read the two SQL Server HA books available at MS "Patterns and Practices" web site.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
> >--Original Message--
> >Snapshot replication would give you a complete copy of
> the publication ....
> >Probably some sort of incremental thing might be
> better... perhaps
> >transactional...
> >
> >But be aware-- replication does NOT replicate system
> tables, so
> >permissions, etc will not be replicated..
> >
> >Also, SQL Replication is not very friendly about Schema
> changes ,
> >adding/dropping columns, or changing data types...
> >
> >For these reasons, may people use log shipping.
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> >> Hello,
> >>
> >> We are implementing a high availablity solution to our
> SQL
> >> Servers, so if our production server goes down we have a
> >> standby server ready to go.
> >>
> >> So we would like to know the best type of replication,
> >> bascially we want to update the stand-by server every 5
> >> minutes. We cannot use the log shipping as we backup the
> >> transaction log every 10 minutes.
> >>
> >> Personally I think we should be using snapshot
> >> replication, does anyone have any major disagreements
> with
> >> this ?
> >>
> >> Thanks
> >> Peter
> >>
> >>
> >
> >
> >.
> >|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
>> Thanks Wayne,
>> I would love to use Log Shipping, but I'm assuming that
it
>> produces the same internal result to the log file as a
log
>> file backup, if that the case then the backup we have in
>> place will not work. This is somewhat of a 'legacy'
>> backup, and one I have been specifically told not to
touch.
>> Again thanks for help and I will look into what you have
>> said.
>> Peter
>> >--Original Message--
>> >Snapshot replication would give you a complete copy of
>> the publication ....
>> >Probably some sort of incremental thing might be
>> better... perhaps
>> >transactional...
>> >
>> >But be aware-- replication does NOT replicate system
>> tables, so
>> >permissions, etc will not be replicated..
>> >
>> >Also, SQL Replication is not very friendly about Schema
>> changes ,
>> >adding/dropping columns, or changing data types...
>> >
>> >For these reasons, may people use log shipping.
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >
>> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> >> Hello,
>> >>
>> >> We are implementing a high availablity solution to
our
>> SQL
>> >> Servers, so if our production server goes down we
have a
>> >> standby server ready to go.
>> >>
>> >> So we would like to know the best type of
replication,
>> >> bascially we want to update the stand-by server
every 5
>> >> minutes. We cannot use the log shipping as we backup
the
>> >> transaction log every 10 minutes.
>> >>
>> >> Personally I think we should be using snapshot
>> >> replication, does anyone have any major disagreements
>> with
>> >> this ?
>> >>
>> >> Thanks
>> >> Peter
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||If you are thinking of replication rather than clustering or log shipping, then transactional with queued updating subscribers may be used in this situation. Snapshot could be used but this depends on the size of your system - creating and sending over bcp files of your complete database every 5 mins might not be acceptable. By default, standard transactional will not take defaults and identity columns (although the schema can be scripted), so it's easier to use the queued updating subscribers option which also gives the ability to send back to the publisher any changes subsequently made to the subscriber, if the publisher becomes online in the future
Regards
Paul Ibison

Best Replication Type please

Best practise/architecture question

I need to load a lot of Excel, CSV, ... etc. files. These files have hundreds of columns and I need to validate the data. Some are simple range type checking, some are more complex checking involve multiple columns.

There may have several hundreds of such rules. And I may need to let the program to automatically correct some invalid data in the future.

Where to implement it in SSIS?
Or just load the files without any checking (all type to text), and checking using T-SQL?

(BTW, I don't have biztalk server).

Thanks in advance.

Best practise of storing a column which is of string type

Hi,

Hi I am currently using SQL Server 2005.

Whats the difference between nvarchar & varchar datatypes in SQL server and which is opted best to store strings.

Thanks,

Uma Ramiya

nvarchar is used commonly used to store national characters / unicode characters. so if your front end application accepts unicode characters to be stored to your db then you should use nvarchar. if not use varchar instead
|||

I use ASp.Net 2.0 as front end. In that case what should I use

Thanks,

Uma Ramiya

|||its not dependent on asp.net. it depends on the localization of your application e.g. does your asp.net application can be viewed, transalated and/or accepts inputs with chinese , korean characters, etc. then you should use nvarchar. if you application only stores english characters then use varchar
|||

If you want to store english (latin based) string then you can use varchar.

If you want to store non-english string (ex. Japanese, Hindi, Tamil etc) then you have to use NVarchar.

Nvarchar uses Unicode (which supports all the alphabets/numbers)

Varchar Uses ASCII CODE (which supports only english alphabets/numbers)

Nvarchar each char occupies 2 Byte

Varchar each char occupies 1 Byte

Nvarchar max length in SQL Server 2000 = 4000

Varchar max length in SQL Server 2005 = 8000

If your app supports (or planned to support on future) Globilization/Localization then use the NVarchar

Otherwise Varchar is more enough..

Thursday, March 8, 2012

Best Practice & Other

Hi All
I have the following replication setup:
Replication Type: Transactional
Database Size: Circa 35gb
Articles: All articles are published and are required to be at the
subscriber.
Server1: Publisher, SQL 2000 Sp3 (W2k3 sp1)
Server2: Distributor, SQL 2000 Sp4 (W2k3 R2 sp1)
Server3: Subscriber SQL 2000 Sp3 (W2k3 sp1)
Server 3 has the pull subscription.
Other Info: Server 2 did have SQL2005 installed. It's since been
uninstalled and resolved some transactional issues.
This is the environment that I have inherited.
Based on reading around, this appears to be an acceptable best
practice method. I might press against throwing SP4 on server 1 and
server 3.
Are there any amazing troubleshooting tips for this process around?
there are times when the transactional replication does not work. I
think it times out so I'm looking at changing the time out to 3000
seconds.
One issue I've found is that there was a duplicated transaction that
managed to get through. This crashed the envrionment and there was no
choice but to create a new snapshot - it takes hours. How could we
best avoid this? If we deleted these transactions from the subscriber
(difficult due to referential integrity) how would the system know to
replicate them again?
Another one that we've had is where the old transactions are held in
the log. I need to flush them out so that the log can be truncated and
then reduced in size. Any further hints and tips?
I'm simply after some good practice methods to help troubleshoot and
plan the replication process since we're investing time and resource
in it rather heavily. We'll move to 2005 once things are stable.
Thanks in advance for the help.
Simon
Hi Paul
Thanks for the advice. Can you think of any really good
troubleshooting methods when things are gone wrong. I don't mind how
generic they are, it's just good to excercise the brain on new ways of
handling problems.
Cheers
Simon

Wednesday, March 7, 2012

Best Performance?

Assume a heavy transaction environment.
Let's say we have the same database model,
same hardware, file usage, type of data, queries etc. on two servers.
Server A is configured with a RAID 5 on all the data disks (9 of them),
and Server B is configured with RAID 5 on the data disks (5 of them) but
transaction log and tempdb is separated to its own mirrored disks (4 of
them - 2 each).
Which SQL server will run faster in terms of query response time AND write
performance? Database in Server A or Server B?Assumimng disc io is the limiting factor then I would say B would be =most likely to outperform A. But even better would be lose the raid 5 =and use 0+1 on 4 discs. or possibly on 6 and lose the separation of =tempdb. Of course this will lose you some space over the raid 5, but =will perform better. Hardware raid vs S/W will alo make a difference. =How much separating tempdb gives you totally depends on the number of =queries generating large temporary results.
Opinions are likely to vary a lot and you haven't given much info to go =on.
Mike John
"Rick" <bob@.bob.net> wrote in message =news:3f858779$0$103$8f4e7992@.newsreader.goldengate.net...
> Assume a heavy transaction environment.
> > Let's say we have the same database model,
> same hardware, file usage, type of data, queries etc. on two servers.
> Server A is configured with a RAID 5 on all the data disks (9 of =them),
> and Server B is configured with RAID 5 on the data disks (5 of them) =but
> transaction log and tempdb is separated to its own mirrored disks (4 =of
> them - 2 each).
> > Which SQL server will run faster in terms of query response time AND =write
> performance? Database in Server A or Server B?
> >|||I completely agree with Mike John's remark to drop RAID5 completely,
certainly for a 'heavy transaction environment' as you describe. Check out
this link for some reasons why
http://www.miracleas.dk/BAARF/BAARF2.html
(OK it's Oracle/Unix, but it is as tru for the SQLServer/Win world too..)
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:uRds7fpjDHA.2676@.TK2MSFTNGP11.phx.gbl...
Assumimng disc io is the limiting factor then I would say B would be most
likely to outperform A. But even better would be lose the raid 5 and use 0+1
on 4 discs. or possibly on 6 and lose the separation of tempdb. Of course
this will lose you some space over the raid 5, but will perform better.
Hardware raid vs S/W will alo make a difference. How much separating tempdb
gives you totally depends on the number of queries generating large
temporary results.
Opinions are likely to vary a lot and you haven't given much info to go on.
Mike John
"Rick" <bob@.bob.net> wrote in message
news:3f858779$0$103$8f4e7992@.newsreader.goldengate.net...
> Assume a heavy transaction environment.
> Let's say we have the same database model,
> same hardware, file usage, type of data, queries etc. on two servers.
> Server A is configured with a RAID 5 on all the data disks (9 of them),
> and Server B is configured with RAID 5 on the data disks (5 of them) but
> transaction log and tempdb is separated to its own mirrored disks (4 of
> them - 2 each).
> Which SQL server will run faster in terms of query response time AND write
> performance? Database in Server A or Server B?
>|||On Thu, 9 Oct 2003 11:06:17 -0500, "Rick" <bob@.bob.net> wrote:
>Assume a heavy transaction environment.
> Let's say we have the same database model,
>same hardware, file usage, type of data, queries etc. on two servers.
>Server A is configured with a RAID 5 on all the data disks (9 of them),
>and Server B is configured with RAID 5 on the data disks (5 of them) but
>transaction log and tempdb is separated to its own mirrored disks (4 of
>them - 2 each).
>Which SQL server will run faster in terms of query response time AND write
>performance? Database in Server A or Server B?
Well, like the other guys said, one would probably anticipate B
running a bit faster, but it's also going to depend on how much data
is cached, that is, on RAM size, and just how heavily tempdb gets
used, and for that matter, how heavily the log gets used. If it were
100% simple queries, A might even win, for a more usual mix of 90%
simple queries, 5% complex queries, and 5% highly localized updates,
you're probably close to the break-even point, assuming you're not
running short of disk IO bandwidth or CPU in either case A or case B.
J.

Saturday, February 25, 2012

Best Data type to hole phonenumber

Whats the best type to hold a phone number in this format with a column in my sql table...should i use varchar? or something else....any help would be great!!

Is this phone number US-only or is it international?

Thanks

Waseem

|||its only US|||

Take a look at this example

http://www.rampant-books.com/t_super_sql_128_user-defined_data_types.htm

Hope this helps

Best Data Type for...

What is the best data type to use to store numbers with limited decimal
places - ie. 1.25?
ThanksKeith,
Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
precision and scale, with scale referring to the maximum number of decimal
places (2 in the example above). Have a look in BOL at "decimal data type,
overview".
HTH,
Paul Ibison|||Thank you
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVihrFQLEHA.644@.tk2msftngp13.phx.gbl...
> Keith,
> Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
> precision and scale, with scale referring to the maximum number of decimal
> places (2 in the example above). Have a look in BOL at "decimal data type,
> overview".
> HTH,
> Paul Ibison
>

Best Data Type for...

What is the best data type to use to store numbers with limited decimal
places - ie. 1.25?
Thanks
Keith,
Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
precision and scale, with scale referring to the maximum number of decimal
places (2 in the example above). Have a look in BOL at "decimal data type,
overview".
HTH,
Paul Ibison
|||Thank you
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVihrFQLEHA.644@.tk2msftngp13.phx.gbl...
> Keith,
> Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
> precision and scale, with scale referring to the maximum number of decimal
> places (2 in the example above). Have a look in BOL at "decimal data type,
> overview".
> HTH,
> Paul Ibison
>

Best Data Type for a Tracking ID?

Hello,
We're in the table design process and could really use outside thoughts on
our options.
The table in question will grow to several million records. This table will
update and insert depending on what the tracking ID does as it lives out it
day long life span.
Example Stored Proc:
UPDATE tbTracking
SET myCount = myCount +1
WHERE trackingID = @.trackingID AND productID = @.productID
IF (@.@.rowcount = 0)
BEGIN
INSERT INTO tbTracking (...) VALUES (...) ;
END
Everyday at midnight the table will be truncate just afer many other queries
generated reports and store cumulative data into other tables.
Our goal is extreme performance, with that in mind what is the best data
type for trackingID and productID? We have the luxury to make them whatever
is best, as this is a completely new project.
Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like,
but we're in need of advise from those with more experience with these
matters.
Thank you for your time.
MarkMark S. wrote:

> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
This doesn't make much sense to me. As described it isn't clear whether
trackingid is a surrogate key or not. If it isn't then I don't
understand what its purpose is. What is the business meaning of
trackingid? Why isn't the datatype predetermined? What key or keys
exist in this table?
Maybe we are talking surrogate keys here, in which case see:
http://www.aspfaq.com/show.asp?id=2504

> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
A guiding in principle in data warehouse applications is usually to
capture the data at the finest possible level of granularity. I expect
you've considered this, but I just thought it worth restating for the
benefit of all.
David Portas
SQL Server MVP
--|||Who or what determines the value of TrackingID?
If this key is to uniquely identify an event (such as a support call), then
perhaps (8 byte) datetime would be the logical choice. It has inherent
meaning, and unless you have hundreds of support calls coming in per minute,
it is unlikely that this key value would be duplicated, and retry logic on
the insert procedure or statement can handle the unlikely event that does.
There may even be a need to purge the tbTracking table based on < a specific
date/time rather than truncating the entire table at the end of the day.
As for ProductID, this should be an existing attribute in your database.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||In my experiences, int data type is the best performer especially when it
comes to indexing, joins, etc. (This comment relates to 32-bit O/S and SQL
Server 2000.)
I would make a meaningless key:
TrackingRowId int, identity, primary key, clustered
ProductRowId int, identity, primary key, clustered
SQL Server takes care of assigning the next number. The clustered index
will always insert data at the end of the data pages which avoids page split
s.
If you want to reset the tracking id each midnight, simply change the reseed
the tables as part of your processing.
Personally...
I would NOT use a character string for a key because its slower to compare
strings.
I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
butt when you have to write a manual query to pull data.
Finally, I have to reiterate David's point of capturing data at the lowest
level. Are you sure you want to summarize and truncate on a nightly basis.
What if a bug is discovered? You won't have the original data to recreate
the totals. What if a new way of summarizing is added? You won't have the
original data to drill into or summarize.
And, to reiterate JT's point of having a datetime column that can be very
handy. It is debateable whether to use it as the key or not. If you're
truncating the tables each night, it may be a waste.
Just my two cents,
Joe
"Mark S." wrote:

> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
>
>|||Gentleman thank you for your feedback.
Joe, other than the UniqueIndentifier being unfriendly for humans, do you
find it faster than INTs?
As to th other questions and caveats, all that has been considered
previously, and wasn't mentioned in my question in the interests of brevity
and an attempt to focus my question on a single point. Regardless, thank you
for your full consideration.
Cheers,
Mark
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:50A0324B-1446-4044-A850-706B59A04169@.microsoft.com...
> In my experiences, int data type is the best performer especially when it
> comes to indexing, joins, etc. (This comment relates to 32-bit O/S and
> SQL
> Server 2000.)
> I would make a meaningless key:
> TrackingRowId int, identity, primary key, clustered
> ProductRowId int, identity, primary key, clustered
> SQL Server takes care of assigning the next number. The clustered index
> will always insert data at the end of the data pages which avoids page
> splits.
> If you want to reset the tracking id each midnight, simply change the
> reseed
> the tables as part of your processing.
> Personally...
> I would NOT use a character string for a key because its slower to compare
> strings.
> I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
> butt when you have to write a manual query to pull data.
> Finally, I have to reiterate David's point of capturing data at the lowest
> level. Are you sure you want to summarize and truncate on a nightly
> basis.
> What if a bug is discovered? You won't have the original data to recreate
> the totals. What if a new way of summarizing is added? You won't have
> the
> original data to drill into or summarize.
> And, to reiterate JT's point of having a datetime column that can be very
> handy. It is debateable whether to use it as the key or not. If you're
> truncating the tables each night, it may be a waste.
> Just my two cents,
> Joe
> "Mark S." wrote:
>|||The last line of the article David suggested
http://www.aspfaq.com/show.asp?id=2504
Says alot about GUI() not being optimized, if anyone disagrees, feel free to
speak up:
"the wider datatype leads to a drop in index performance (if clustered, each
insert almost guaranteed to 'dirty' a different page), and an increase in
storage requirements; " and five other cons.
Thank you.|||To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
WITH FILLFACTOR = 100
This causes every new row to be added at the end of the table--minimizing
index maintenance and eliminating page splits. Use caution, however: since
SQL Server doesn't automatically reorganize indexes, a high volume of DELETE
activity will cause the index to become sparse, which can reduce SELECT
performance, but it will not affect INSERT performance.
The INT datatype matches the word size of most Intel processors (32-bit), so
comparisons require fewer CPU cycles.
The stored procedure is an example of what not to do. It's a recipie for
primary key constraint violations. There's nothing that prevents two
transactions from trying to INSERT the same information at the same time.
Most of the time what will happen is that one connection will succeed with
the INSERT and the other will UPDATE the newly inserted row, but a collision
will occur if identical UPDATE statements occur simultaneously on two
unbound connections followed by (since the row doesn't yet exist)
simultaneous identical INSERT statements.
The correct method is to use something like this:
BEGIN TRAN
IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
UPDATE
ELSE
INSERT
COMMIT
Some people use the following instead, but I prefer the above method since
it is easier to read and understand
BEGIN TRAN
INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
IF @.@.ROWCOUNT = 0
UPDATE
COMMIT
Note that there isn't any marked reduction in performance or concurrency
between this and your sample, because UPDLOCK doesn't block SELECTs, and any
blocking that does occur is necessary to maintain integrity. Without the
EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
anyway which involves reading the index page into memory. With the EXISTS
clause, the SELECT reads the index page and applies an update lock, and the
INSERT or UPDATE simply transition from an update lock to an exclusive lock
in memory--no additional physical read is necessary.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||Thank you very much.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23oG2PTDDGHA.3980@.TK2MSFTNGP12.phx.gbl...
> To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY
> CLUSTERED WITH FILLFACTOR = 100
> This causes every new row to be added at the end of the table--minimizing
> index maintenance and eliminating page splits. Use caution, however:
> since SQL Server doesn't automatically reorganize indexes, a high volume
> of DELETE activity will cause the index to become sparse, which can reduce
> SELECT performance, but it will not affect INSERT performance.
> The INT datatype matches the word size of most Intel processors (32-bit),
> so comparisons require fewer CPU cycles.
> The stored procedure is an example of what not to do. It's a recipie for
> primary key constraint violations. There's nothing that prevents two
> transactions from trying to INSERT the same information at the same time.
> Most of the time what will happen is that one connection will succeed with
> the INSERT and the other will UPDATE the newly inserted row, but a
> collision will occur if identical UPDATE statements occur simultaneously
> on two unbound connections followed by (since the row doesn't yet exist)
> simultaneous identical INSERT statements.
> The correct method is to use something like this:
> BEGIN TRAN
> IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
> UPDATE
> ELSE
> INSERT
> COMMIT
> Some people use the following instead, but I prefer the above method since
> it is easier to read and understand
> BEGIN TRAN
> INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
> IF @.@.ROWCOUNT = 0
> UPDATE
> COMMIT
> Note that there isn't any marked reduction in performance or concurrency
> between this and your sample, because UPDLOCK doesn't block SELECTs, and
> any blocking that does occur is necessary to maintain integrity. Without
> the EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
> anyway which involves reading the index page into memory. With the EXISTS
> clause, the SELECT reads the index page and applies an update lock, and
> the INSERT or UPDATE simply transition from an update lock to an exclusive
> lock in memory--no additional physical read is necessary.
> "Mark S." <marks@.yahoo.com> wrote in message
> news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
>

Best data type for a range

I want to know what the best datatype is for a situation like this e.g.if I have a field "age" and the data has a range i.e. 22-30, 31-50 etc, what is the best data type to use for this scenario.

Similarly if I have a field that whereby you use a range for example 1-2 in one record but in another you get an integer value of 0 for instance, what again would be the best datatype.

Many thanks

Integers.

|||

can integers support certain characters such as hypens (-) etc

|||

right not sure how to implement this - the data can't go as 21-30 as it will subract the two values, how I would i add this as a range

|||

If you want the column to stroe values in the form 21-30 etc then varchar(<some length>). The second case also varchar. If I understood your situation correctly.

Or maybe if you are trying to store for each row a lower limit and a higher limit for age, then how about having two columns lower_age_limit and higer_age_limit each perhaps of the tinyint datatype. Or a seperate table altogether for the the age limit, something like tblAgeLimit (id int identity(1,1), lower_limit tinyint, upper_limit tinyint) and then linking the id to the table you need.

|||

Master81:
can integers support certain characters such as hypens (-) etc

No. Sorry - I didn't realise that was the value you wanted to store. You would have to use a varchar.

Friday, February 24, 2012

best approach

I've been banging my head for a while now, and it is sore! :-P

I'm a best practice/Microsoft approach type of person and want to make sure I do things correctly.

I have a database, kind of like a forum.

Obviously executing multiple queries in one "batch" (stored proc) would have an impact on the performance.

Now, I would like to give a more detailed/specific error back to the caller (either by aid of error code or whatever) with such situations like...

"EditReply"

Edit reply takes the threadID, replyID and userID.

Before actually commiting the changes, it needs to check:

1) does the user exist in the database? (during the editing of the reply, perhaps the user may have been deleted before running the stored proc, who knows)

2) does the thread exist?

3) does the reply exist?

if the conditions are met, only then will it go ahead and update the database. Now that is 3 queries, and 4 statements overall to make a change to a field/table.

Obviously if one of the commands returns false, in other words if say "does the thread exist" returns 0 (thread doesnt exist) it will return back to the caller an errorcode, which they will handle in their application. Thats all fine but the question is

Am I doing this correctly? (no) - how can I improve this? What do I need to think about?

Of course I would like to give a more detailed error back to the caller (aid of errorcode designed in the application overall) instead of just "no, databases not updated".

In this situation, am I wrongly assuming that the database designers use this type of approach?

Please help, I value your feedback and suggestions. I want to improve and think of the right lines of doing these things.

A lot of the business logic of a database should be encorporated into the table design.

If you insert into a table which is logically linked to another table then there should be foreign keys and check constraints in place to enforce the business logic at table level. This would cover you for user and threads, as the replies table must have a foreign key constraint to the user and threads tables.

if the reply does not exist then nothing is going to get updated anyway, you can use the @.@.rowcount system variable to check how many rows were effected by the last statement.

So with the table constraints enforcing the business logic you should be able to go ahead and make the update regardless of whether it is valid or not. Your application will need to handle SQL errors and report them back to the user and it will need to get that @.@.rowcount system variable to see if the table was actually updated

|||

Many thanks for this

Yes, the tables are all constrainted and relationships are in place and so on.

However I understand I can use the @.@.rowcount to check how many rows where effected by the statement, but if for instance the row was not updated, I would like to know why it was not updated, this could be for example, the user did not exist, in which case I would return an errorcode back to the caller, who handles this particular error code.

I am wondering if this is best design, as the user would like to know what went wrong (such as they got deleted whilst making a change to something, or the thread got deleted whilst they where making a change etc...) therefore in the current stage, there are multiple IF EXISTS statement, and IF EXISTS returns false, they then would get returned back a specific error code from within the application back to the caller.

is this a bad design? how better can they/I make it?

|||

I would also consider looking at another approach wherein the database stored procedures do the data consistency check and return pre-defined error code which the application maintains (via an enum). The application can then check the status code returned by the stored proc and proceed based on that code. Something like this...

enum ReplyOperationStatus {
Success = 0, UserNotFound = 1, ReplyNotFound = 2, ThreadNotFound = 3, UnknownError = 4
};

stored proc : UpdateReply

if not exists (select null from threads where threadId = @.threadId) return 3

if not exists (select null from users where userId = @.userId) return 1

if not exists (select null from thread_replies where threadId = @.threadId AND replyId = @.replyId) return 2

... perform the update ...

return 0

|||

I agree, but flip it around. I will just be generic and use 2000 style errors. Use try...catch if you are using 2005 to catch errors and then you can have a bit more control.

stored proc : UpdateReply

declare @.error int, @.rowcount int

... perform the update ...

set @.error = @.@.error, @.rowcount = @.@.rowcount

if it is an error due to some key violation, it will be raised, so handle it. A good example is having a unique index on a post guid that you generate on the client. This guid would keep them from duplicating a post.

if @.@.rowcount = 0
begin --now see why not
if not exists (select null from threads where threadId = @.threadId) return 3
if not exists (select null from users where userId = @.userId) return 1
if not exists (select null from thread_replies where threadId = @.threadId AND replyId = @.replyId)
return 2
end

return 0

This way you only waste effort to check for things on a failure, which should be infrequent.

|||Wow, thats wonderful. Thanks for the tip Louis.|||Thanks very much, that is pretty much exactly what I already have!! awesome, thanks a bunch :-D|||

Nice expansion on my post with some code there Loius...

I was just wondering though... is it worthwhile having to do those 3 selects in the event of a failure? If the table constraints are set up properly then the SQL Server error message could be just reformatted by the calling application and then returned to the user...

|||Sam - sorry i dont quite follow - do you mean that if there was a SQL general error? if so, then an exception is thrown (assuming we are working with .NET) back to the caller which they handle automatically.|||

Yes a SQL Server generated error

The error text you get when using Query Analyser or Enterprise manager eg:

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_EmployeeSales_Employees'. The conflict occurred in database 'MyDatabase', table 'Employees', column 'EmployeeID'.
The statement has been terminated.

If that is accessible to the callnig application then it could be quickly parsed and returned in a more legible way to the user leaving the data alone for everyone else

|||

I am unsure if you are aware of this, or maybe i've mis interpreted, but that sort of error generated by SQL is thrown back into .NET and will be thrown to the application user so the developer/application has to handle it.

most of the time the error code is also given in the SQLException in .NET so the developer can handle such errors the way they want it.

|||

My thought was that he was doing something like this:

insert into table (columns)
select userId, otherColumns
from user
where user.name = 'fred@.fred.com'

Now, if that user does exist, done. If not, there will be no row inserted. Then you can decide why in the other queries.

Monday, February 13, 2012

BEGINNER: simple Delete trigger

Hello,
I am trying to learn SQL Server. I need to write a trigger which
deletes positions of the document depending on the movement type.
Here's my code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE TRIGGER [DeleteDocument]
ON [dbo].[Documents]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'
DELETE FROM PositionsWZRW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Ruch = 'MM'
DELETE FROM PositionsMM
WHERE Documents.Number IN (SELECT Number FROM deleted);
END

Unfortunatelly I receive errors which I don't understand:

Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 13
The multi-part identifier "Documents.Numer" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 16
The multi-part identifier "Documents.Number" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 18
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 19
The multi-part identifier "Dokuments.Number" could not be bound.

Please help to correct the code.
Thank you very much!
/RAM/How to forbid deleting Positions if Documents.WasDeleted bit is not
set?
Please help.
/RAM/|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

Hello,
I am trying to learn SQL Server. I need to write a trigger which
deletes positions of the document depending on the movement type.
Here's my code:
>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
>
CREATE TRIGGER [DeleteDocument]
ON [dbo].[Documents]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
>
IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'
DELETE FROM PositionsWZRW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Ruch = 'MM'
DELETE FROM PositionsMM
WHERE Documents.Number IN (SELECT Number FROM deleted);
END
>
Unfortunatelly I receive errors which I don't understand:

I understand the errors, but I understand about as little of your
trigger that SQL Server does. You seem to be making things up out of
thin air. When you say:

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'

What are Documenty and Documents supposed to be? Maybe you mean

IF EXISTS (SELECT *
FROM deleted
WHERE movement IN ('PZ', 'ZW'))

The same goes for

DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);

This would compile if you have a column Documents in PositionsPZZW,
and this columns is of a CLR UDT and had an attribute named Number.
What this really should be, I don't even want to guess, since I know
nothing about PositiosnPZZW.

The standarad recommendation is that you post:

o CREATE TABLE statements for your tables.
o INSERT statments with sample data.
o In this case: a sample DELETE statement.
o The desired result given the sample.

It also helps to give a little more detailed description of the problem.

By the way, why are there three Positions tables? Maybe there is a good
reason for this, but I have a suspicion that one should do.

Quote:

Originally Posted by

>I understand the errors, but I understand about as little of your
>trigger that SQL Server does. You seem to be making things up out of
>thin air. When you say:
>
IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'

I meant Documents.Movement

Quote:

Originally Posted by

>
>What are Documenty and Documents supposed to be? Maybe you mean
>
IF EXISTS (SELECT *
FROM deleted
WHERE movement IN ('PZ', 'ZW'))

Exactly

Quote:

Originally Posted by

>
>
>The same goes for
>
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
>
>This would compile if you have a column Documents in PositionsPZZW,
>and this columns is of a CLR UDT and had an attribute named Number.
>What this really should be, I don't even want to guess, since I know
>nothing about PositiosnPZZW.

I need:
IF EXISTS (SELECT * FROM deleted WHERE Movement IN ('PZ', 'ZW'))
DELETE FROM PositionsPZZW
WHERE Number IN (SELECT Number FROM deleted);

Quote:

Originally Posted by

>By the way, why are there three Positions tables? Maybe there is a good
>reason for this, but I have a suspicion that one should do.

They have different columns describing items.

Thank you, you have helped me... Problem closed
Could you help me with post "one more question"? Thank you!
/RAM/|||Sorry, too short problem description.
Anyway, I solved.
/RAM/|||R.A.M.,
What was the solution you found? Please post as others might have a
simular problem.
TIA
Rob

R.A.M. wrote:

Quote:

Originally Posted by

Sorry, too short problem description.
Anyway, I solved.
/RAM/

|||On Thu, 06 Jul 2006 10:46:50 +0200, R.A.M. <r_ahimsa_m@.poczta.onet.pl>
wrote:

Quote:

Originally Posted by

>IF EXISTS (SELECT * FROM deleted WHERE Movement IN ('PZ', 'ZW'))
>DELETE FROM PositionsPZZW
>WHERE Number IN (SELECT Number FROM deleted);

That looks dangerous. If one row in DELETED has a 'PZ' value, all
rows in PositionsPZZW that match DELETED will be dropped, even those
that do NOT have 'PZ' or 'ZW'.

How about this alternative:

DELETE FROM PositionsPZZW
WHERE Number IN
(SELECT Number FROM deleted WHERE Movement IN ('PZ', 'ZW'));

It does not require the IF test at all, as if there are no matches it
will do nothing.

Roy Harvey
Beacon Falls, CT|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

Could you help me with post "one more question"? Thank you!

If you repost it, and clarify what you mean. I understood very little
of it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

But the code implies some design problems. What are the logical
differences among
PositionsPZZW, PositionsWZRW and PositionsMM ? This looks like
attribute splitting.

Why are you using triggers instead of DRI actions?|||On 6 Jul 2006 04:39:13 -0700, "rcamarda" <robc390@.hotmail.comwrote:

Quote:

Originally Posted by

>What was the solution you found? Please post as others might have a
>simular problem.
>TIA
>Rob

I decided not to use WasDeleted flag in Documents, so it was enough to
set Delete Rule in FK_Positions_Documents to "No Action".
/RAM/|||BEGINNER: simple Delete trigger

Sunday, March 25, 2012

Best Replication Type please

Best Replication Type please

Best Replication Type please

Best practise/architecture question

Best practise of storing a column which is of string type

Thursday, March 8, 2012

Best Practice & Other

Wednesday, March 7, 2012

Best Performance?

Saturday, February 25, 2012

Best Data type to hole phonenumber

Best Data Type for...

Best Data Type for...

Best Data Type for...

Best Data Type for a Tracking ID?

Best data type for a range

Friday, February 24, 2012

best approach

Monday, February 13, 2012

BEGINNER: simple Delete trigger

BEFORE Triggers

Blog Archive

About Me