Chosen Datatype For Primary Key Field And Performance Questions??
May 25, 2007
Hi there,
I have been hired for a couple of weeks to investigate the performance of a sql server 2000 system.
One of the things that strikes me is that all the Primary key (identity field) fileds uses an decimal(18,0) as it's datatype.
An decimal with a precision of 18,0 takes 9 bytes for each column, while an int takes only 4 bytes and and bigint 8 bytes.
Many tables aren't that big, so the values will fit in an int datatype.
1. Is iot a good option to change the decimals columns to an int column ?
2. Many of these columns are indexed by a clustered index. Can the decimal datatype be a performance issue ?
3. sometimes they have deadlocks due page splits. Can this by reduced by changing the data types, while more data fit's into an page?
Thanks in advance,
Greetz,
Patrick de Jong
View 4 Replies
ADVERTISEMENT
Mar 26, 2008
Hi,
I'm attempting to use the following code to display either 'All' or the date value selected by the user from a Report parameter;
=iif(Parameters!FromCheckOutDateDate.Value.ToString = "[Check Out Date].[Date].[All]", "All", "From Date: " + Parameters!FromCheckOutDateDate.Value.ToString.Substring(26,10))
This is throwing an error ('#Error').
I can use the following code with no error, though its not as useful;
=iif(Parameters!FromCheckOutDateDate.Value.ToString = "[Check Out Date].[Date].[All]", "All", "Not all")
I can even use this to display the selected value (i.e. 2007-01-01);
Parameters!FromCheckOutDateDate.Value.ToString.Substring(26,10)
Why can't I use them both in my iif statement?
Can someone please help?
View 2 Replies
View Related
Jan 28, 2015
Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?
create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp
[Code] .....
View 4 Replies
View Related
Jun 8, 2007
We have a production database that sits on a 4 proc server with 4 GB of memory and SAN disk storage via fiber. There are some stored procedures that run and they take approximately 10 minutes to run. A developer has SQL Server installed on his local pc that has 1 2.5 GHz processor and 2 GB of memory and the stored procedures run in approximately 2 minutes. I have updated statistics and rebuilt indexes to no avail. He is questioning why it runs so much faster on his smaller pc compared to the production environment. I have monitored CPU, Memory, and Disk Queue Length and none of these performance counters look concerning to me while the stored procedures are running.
Can anyone out there give me some input on what I could check to figure out why we are experiencing this performance difference?
Thanks,
Corey
View 4 Replies
View Related
Apr 26, 2005
Hi, I'm a webmaster of http://www.jivejewelry.com. Somehow the website seems slows. The developer told me that a datatype in the database design could be causing the problem. I don't believe it that is possible. Is this actually possible? Please help.
View 7 Replies
View Related
Dec 16, 2004
I have a stored procedure that takes less than 1 second in sql query analyzer to return my results.
I run this same SP in ASP.NET using a calendar control and using perf monitor I notice that for me from my dev machine my cpu utilization is sometimes over 40%.Is there any tweaks I can do to help decrease CPU utilization.
View 2 Replies
View Related
Apr 15, 2003
We are new to SQL 2000, and would like to bounce a couple questions off some of you gurus out there.
We are using SQL Server 2000 to build a data repository to assist us in transitioning from our old flatfile legacy system to SAP. We are also looking to use the SQL Server 2000 repository to build a smallish Enterprise Data Warehouse on the same SQL 2000 platform.
Here is our problem:
We have SQL Server 2000 loaded on a little scrapper PC with 1.4GHZ single processor, 1GB of memory, and a single 40 GB IDE drive.
When we are initially loading any of our repository tables the process cruises along pretty well, even respective of trying to locate the record for update before doing an insert. But, if we do something as simple as selecting count(*) against the table that's loading, performance on the load goes to its knees.
We understand we're pretty much at the mercy of the hardware we have (that's the budget), but we'd like to get as much bang as we can out of what's there.
Our questions are:
1. Is there anything we can do with our server configuration (short of new hardware) that will help us?
2. Are there any recommendations as far as monitors to help us better tune this specific configuration?
Thanks in advance for any help.
View 7 Replies
View Related
Apr 8, 2008
Hi,
Somebody please help me with mostly asked question on Performance Tunning Section. I have my client interview schedule for this profile.
Thanks in advance.
-- Chetan
View 3 Replies
View Related
Aug 20, 2001
We have few stored procedures that use nvarchar datatype, this was not issue on SQL server 7.0 but in 2000 becomes a big issue.
For example query that runs for 3 minutes in SQL server 2000 by replacing NVARCHAR to VARCHAR the same query runs for 2 seconds.
The biggest challenge that I have deals with tables and user-defined datatypes of NVARCHAR that has been bounded to the table.
How can I alter those without data corruption?
View 2 Replies
View Related
Feb 3, 2005
Hi all,
I have downloaded an evaluation copy of a SQL Server performance tool (a fancy version of the Profiler) called Speed Coefficient from Imceda. Pretty interesting so far, but some questions are forthcoming (and probably will continue to come as I drill down and learn more about performance thangs).
It tells me I have a recompilation that I did not expect, it says the reason is "object not found at compile time, deferred to run time", but doesn't do too well at specifically telling me which object it is complaining about (yeah, not REALLY a complaint, but perhaps more a "mention", but I digress...)
I thought originally it was, perhaps, an object that I had not referenced correctly, but as it turns out, it is, I believe, referring to a global temporary table one of my procs creates. Upon further reflection/introspection, it makes sense to me that this is the case, since it won't HAVE the temp table object to kick around until it is created at run time.
Does this make sense? If so, I guess this is one of those times where the tool just makes reference to a possible issue, but it's up to the user to understand what the underlying cause of the "mention" is, and to determine if it is "OK" to have the recompilation occur.
View 1 Replies
View Related
Jul 20, 2005
I 'inherited' a group of SQL Server server class machines. They aretrue server technology but the disk sub-systems are lacking. There isone hot-swap backplane that all the drives share (with one SCSIchannel) thusly even though there are three logical drives (composedfrom 6 to 8 hard drives), they all go through one channel. This iscreating a performance issue that is noticable and can be seen invarious performance counters that Microsoft recommended one shouldmonitor in terms of disk I/O. For a cheaper 'fix', I can add aseperate two drive bay (with its own SCSI channel) with mirroreddrives. I would then mostly likely place the transaction log files onthis new channel. Or I could place the indices filegroup files onthis new channel for DBs with mainly searching going on (not muchupdating). If I went this route I would be leaning towards thetransaction log move since the second method would require me movingDBs around quite a bit. Any input on this solution (besides spendingmore money)?What I would prefer to do is get a better server class machine or addan external drive bay solution (not a SAN). I would try to get threeor four SCSI channels in the new hardware to split the differentfile/filegroups out (i.e. transaction logs files, data filegroup,indices filegroup, etc.). My only concern here is: would this moreexpensive solution be worth the money? As far as replacing servers, Ihave only two kinds of experience...replacing somewhat underpoweredservers with slightly less underpowered servers and replacing overkillservers with even more overkill servers. In both cases, the disksub-systems were fairly equivalent from the old system to the new one.Will going the three/four channel route really get data moving along?We have one server in particular that hosts a database (one of many onit) for a web application that gets decent traffic (it is a privatelogin based system for internal use and external use by our clients'agents). Periodically throughout the day, there are 2-5 minute burstswhere performance slows to a crawl. I want to spend more timeprofiling queries and such before recommending we spend more money,but the folks I am working for want quick results and there is quite abit of stored procedure logic to profile and investigate. I know thedisk sub-system is definately in need of an overhaul, but I would liketo get an idea of peformance gains from adding either one additionalchannel over the existing single channel as well as going thethree/four channel route over the existing single channel setup.Any information would be greatly appreciated.Regards,Tony
View 2 Replies
View Related
Feb 23, 2007
Hello everyone. How to make a query that returns the datatype of a field.
Thanks & Best Regard.
-Ron-
View 7 Replies
View Related
Dec 8, 2007
1) Unforgiveablity - this is an advantage and disadvantage of service broker. The advantage is that it forces developers to be precise with the formation and decomposition of messages as well as forcing disciplined use of transactions. The disadvantages lie in the same. With the slightest error in dialog management, the transaction log grows out of control. With any solution that is incorporating SQL Express (and thanks to the limitations for surfacing the problems), the transaction log, invariably - at some point - grows uncontrollably, especially if you have a web application monitoring queues. There really needs to be better checkpoints and obeyance of normal SQL Server contraints. It seems that sometimes service broker has a mind of its own and takes over not only the SQL instance, but the disk and everything else.
2) Administration/Management capabilities - as far as I am concerned, these are non-existent. I find it hard to believe that I can't even script my service broker objects using the native management studio scripting tool. Its ridiculous that - in an enterprise system - I have to manually script out scores of service broker objects. The operations group agrees with me and this does nothing to enhance the reputation of SQL Server as an enterprise player. You can't produce a SP that provides these capabilities? I have had to build in diagnostics and recovery stats by way of user tables that collect and analyze service broker message activity.
3) Internal Activity - what the hell is going on in these internal tables? I can't find any documentation about the use of tempdb or anything else. Please provide something explaining where all the activity occurs and where space is consumed. This is kind of important when operating on restricted platforms, like SQL Express.
4) Locking - seems like you need a more granular locking strategy. This is just conjecture, but it seems when service broker wants; service broker takes. How about letting the application architect/developer decide when service broker takes?
5) Dialog Pool Management - this is very complex to implement and almost all "real" applications need it. You need to provide an out-of-the-box, starter implementation because this leads to problems mentioned in #1. Remember how the VS application wizard killed the Borland dev environment? Well, SQL Server needs an equivalent. Stop making us write the same thing over and over again; and stop making me write the same queries over and over again.
6) Evangelists - Most people don't even know what service broker is, and it makes it a very hard sell. Even experienced SQL developers are not sure how and why it should be used. I have interviewed over 20 pretty strong SQL candidates over the last year and I can't name one that new ANYTHING about service broker.
7) Guidelines for Conversation Cleanup - well, it's a fact of life - we have to cleanup bad conversation handles from time to time. OK, maybe this wouldn't happen if we didn't have careless or error-prone programming, but we do. What is the proper way to cleanup dialogs between two endpoints? How about some sample queries to run on the initiator and the target? Or even an english language description of how to go about cleaning up and starting over with conversations that have gotten into a "bad" state?
8) UDP/Broadcast/Multicast Messages - This is a pretty big problem in my opinion. I can't believe this has not been added to 2008 (or at least, I did not see it in the feature list). Roger Wolter said it would be in the next version when the 2005 articles were written. So, I am supposed to keep cursoring through message endpoints and tricking up the TRANSPORT route to achieve what a broadcast implementation could achieve?
I guess thats enough for now. I look forward to the responses.
View 2 Replies
View Related
Apr 24, 2015
I have a field with a datatype of text. In SQL it looks like:
Body(text,null)
This field is used for notes. For certain records users will enter a date.
20150425
I'm looking to do a select statement that will grab only those records with a date and make them an int field. This doesn't seem to work.
select convert(int, body)
from b
where left(body,2)='20'
View 4 Replies
View Related
Sep 15, 2006
Hello, I am developing my first software product. I am trying to gain high database performance since this will be my main selling point.
My database will contain tens of thoudands of rows and worried that if i make a query the reponse will be too slow to be prectical.
so my question is:
if i made one query on my database and it returned 10 results -- would'nt it be faster if I made 10 queries using the primary key?
Will appreciate any responses.
View 4 Replies
View Related
Apr 30, 1999
Can anyone tell me how to calculate datatype conversion times in SQL Server
7? I have a varchar (15) field that I tried to convert to integer using
the table design GUI in Enterprise Manager. The table holds about
72,000,000 records about 1k apiece in size.
It's been running for about an hour now with no seeable results. In
Performance Monitor I don't see any page reads happening, so is this
indicative that the process died? Enterprise Manager is no longer
responsive - even if I open another session.
I'd like to know how long I can reasonably expect this conversion to take.
Also, how can I abort this request safely if I want to?
Thanks for any insights.
Alex Nguyen
View 1 Replies
View Related
Jul 14, 2014
What sql data type would fit these value? This is timestamp field in the ERP.
2013-05-24-20.03.46.843480
View 3 Replies
View Related
Apr 23, 2015
I am storing xml in an xml datatype field in a table. But when I am generating physical file from the xml field, '&' in the xml field is getting converted into '&' in the physical file. But I want '&' to be intact.
View 5 Replies
View Related
Dec 27, 2007
Please Help me ...
How to set Money datatype decimal field with example .
View 5 Replies
View Related
Jul 25, 2006
Hi all,
Hi am creating a new database not sure which way is the best way to go. Here are the questions
I have a history table which has its an identity field and an identification field which shows what type of history field it is. As in payment, printout, change and so on.... For this field should i use
Full text which has the payment, printout, change and so on and make it easy on the eye and for queries and coded to use the same text all the time
have varchar(2) type of field which holds an understandable field PR, PO, CH and a reference table for it as in PR= PAYMENT, PO = Printout, CH as in change and so on
or have a integer field as in 1, 2, 3 and ref. table 1=payment, 2 = printout, 3=change and so on
I have a address table which holds multiple types of addresses which are really limited count maybe upto 4 different types
have a integer field which links to a reference table as above
have bit fields which can be used to check if it gets in one of the group types
have a full text option like above
Im open for any type of suggestions and i would love to hear the reasons why its picked TY
View 1 Replies
View Related
Sep 29, 2004
Is there an AutoType Datatype for Date field. just like the Identity property for an ID field.
It increments by 1 for each new record.
The same way if i add a record to the table can it automatically put todays date or date and time for the field date created in my table.
The reason is i am uploading the record from xml file(Using SQLXMLBULKLOAD) so i dont have a chance to mention on the front end code or in the XSD file.
Any help would be greatly appreciated.
View 1 Replies
View Related
Jan 18, 2005
i write a small web application to save word files in sql server and i store it as an image datatype
i need to do operation such as search within these files for any word
is there any One Can help me with that ???????????????????????????? Important
View 2 Replies
View Related
Jul 13, 2006
Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?
View 2 Replies
View Related
Nov 30, 2007
Hi everyone.
I have a field that is image datatype. That table is included in a snapshot replication. When I try to update that field - on the publication server, which is the same as distributor server - if the image is too big (in size), an error message appears. When I try to update with images that has less size it works. If I take out the subscriptions, it works for every image size.
Any ideas?
View 3 Replies
View Related
Mar 20, 2008
Hi,
I am facing problem while inserting a Null value into a smalldatetime datatype field in sql server 2000 using code in vb 6.0
Error as : Type mismatch.
Kindly let me know how to insert Null or blank (dtDate = "") into a column.
Regards,
Srinivas Alwala
View 1 Replies
View Related
Nov 16, 2006
We are trying to update a changed DataSet to at table in MSSQL server
The table have an XML field and that gets converted to System.String when we get from the DB when we try to save the dataset the XML field ind the table is set to NULL
How can this be, the DataSet can convert it to System.String when we SELECT it from the DB, but when we save, nothing happens and we get an empty field. Not even an SQL error...
View 4 Replies
View Related
Dec 6, 2004
Hello!
I am trying to run a query on an SQL-server database:
SELECT (Left(Comments, 20)) As shortComment FROM myTable
The Field Comments is a Text field (datatype is text), and I can't get the LEFT function to work with this datatype...
How can I bypass this problem? Can I convert the commentfield to varchar in the QUERY?
noccy
View 1 Replies
View Related
Jul 20, 2005
Hi, I've read conflicting articles on updating an ntext field in acolumn.My ntext field will exceed 8,000 characters (typically twice that size-- but just a text string).One article (I think from MicroSoft) said you could NOT use ntext inan UPDATE statement, but I've seen examples from other people usingit...but don't know if it's related to the size/characters issue.Is this true or not?Thanks very much...Kathy
View 2 Replies
View Related
Aug 3, 2006
Hi,
When i try to insert a value in to a field of datatype float(8), it is storing the wrong values(approximate values).
For example, if i try to insert 2.62 , it takes it as 2.6200000000000001
But i can see the correct values displayed in the frontend.
Heard that there is a fix available for this issue.
Could any of you help me in getting the details about that fix?
Thanks in advance.
~ Chaitanya
View 4 Replies
View Related
Jun 16, 2012
Ok I have upgraded my works database from a poorly designed Access database to a SQL database. The previous system allowed NULL values and duplicates to be inserted into a field that should NOT ALLOW NULL Values or duplicates. Therefore, this issue has now been moved across to my new system as I cannot set these constraints on the field that has multiple NULL values.
My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.
To make things more interesting this field is not a integer type, its a Nvarchar type as its a Hardware ID. Both numerical and characters are require.
View 1 Replies
View Related
Apr 12, 2001
Does anyone has an idea on creating a new field in a table with more then say 10000 rows already in that table, and making that new field as primary key field. None of the fields in that table are unique.
View 2 Replies
View Related
Nov 10, 2000
Hey guys.
I just wanted to ask you a question? we are having a field declared
as BIT and we want that field to be unique..We are not able to declare
as a primary key..Is there anyway to declare as unique from the database
point of view?..please help us out in this issue and i'd appreciate that.
Andrew.
View 5 Replies
View Related
Nov 10, 2003
Hi,
I want to find out the primary key field name of the table using system table information i.e. sysobjects, syscolumns etc.
I tried to find it out but it seems to be very complicated.
Could anybody help me in this regards?
Thanks in advance.
Prasanna.
View 3 Replies
View Related
