Hi experts ,
My developer sent me a query to review which is looks like
ALTER PROCEDURE [dbo].[PDFDocumentCreate]
Is that something i can change datatypes like he is using DocumentData for CHAR(10 ) .
please help me out thanks
asked Apr 19, 2017 at 04:00 PM in Default
Choosing the right datatype is very important for performance. From what I can see there are some strange datatypes that I should investigate further. Like DocumentDate, DocumentTime, CreationDate, CreationTime. Personally I think they should be stored in date vs time columns as it will save some bytes, but You should investigate how the parameters are used. If they are used in where clauses or join clauses, You have to make sure that the columns have the same datatype and lenght as the parameter, otherwise you'll get an implicit conversion that will cause performance issues
answered Apr 24, 2017 at 05:54 PM
I agree with Håkan Winther on this especially the part about making the input variables match the data types in the underlying table (especially when this appears to be a C.R.U.D. procedure to create new rows in a table) unless the stored procedure does actually do some checking/error trapping or some other process that requires either date/time as a character based entry (which could probably be avoided if the correct temporal data types were actually used, which would make it self checking).
I'll also add that if the underlying table is, in fact, using CHAR(10) for any kind of date or time, then that constitutes a serious design flaw that will have an impact on data integrity (you could enter non-date/time info in the columns) and any kind of criteria that looks for date/time ranges, sorting, etc, etc, and the related performance of all such things even if ISO formatting is used religiously.
answered Apr 30, 2017 at 03:14 PM