none
what datatype to use

    Question

  • Hi,

    what datatype should I use which as data like hyperlink or Picture and for data like Percentage?

    Thanks

    Friday, July 12, 2013 8:51 PM

Answers

All replies

  • Hi

    1. URL link maximum length is different on different browsers but all support 2048 characters. URL should be only in English so you dont need unicode. there for you can use: varchar(2048)

    2. image have several way like any binary data. the type you can use depend on the SQL server version as this was change a lot. chosing the right type depend on your database and application! there are some "Key Rules" to chose the right typr. check this:

    http://msdn.microsoft.com/en-us/library/gg471497.aspx

    3. Percentage is just a number. you can read on numbers type here:

    http://msdn.microsoft.com/en-us/library/ms187745.aspx

    if you use only integer then you can use tinyint as Percentage can be 0-100

    hope this help :-)


    signature

    Friday, July 12, 2013 9:36 PM
  • just adding one point.

    The percentage value can be a decimal value, based on the precision needed chose the appropriate NUMERIC data type.

    Saturday, July 13, 2013 1:14 AM
  • Hi,

    1. Use Varchar to store Hyperlink

    2. varbinary to store image; read also

    http://sqlmag.com/t-sql/varbinarymax-tames-blob

    3. Decimal(10,4) to store percentage


    Many Thanks & Best Regards, Hua Min

    Saturday, July 13, 2013 2:35 AM
  • Hyperlink:   varchar(255) or varchar(max)

    Image: store in file system, varbinary(max) or filetable (SQL Server 2012)

    Related blog: http://www.sqlusa.com/bestpractices/imageimportexport/

    Percent: smallmoney or decimal

    Related blog:   http://www.sqlusa.com/bestpractices/percentonbase/


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Saturday, July 13, 2013 5:15 AM
  • Just nitpicking, so ignore this message if you feel like it.

    There is no valid argument to use varchar(255) for a URL. Varchar is great, but the 255 is arbitrary. 2048 is a good size, because it is generally considered to be the maximum size for URLs that are supposed to work on all web browsers (including older Internet Explorer versions). Also, 2048 still allows the column to be indexed, where "max" might not.

    For "percent" I think using smallmoney is a poor choice, because it suggests a monetary amount, and not a "multiplier" or "ratio". Depending on the purpose of the "percent" any of the regular numeric data types would be fine, such as tinyint, decimal, float.


    Gert-Jan

    Saturday, July 13, 2013 12:01 PM
  • >2048 is a good size,

    Sorry mate. Disagree. Makes the row too large, impractical.

    How many times do you see URL over 255 bytes? 

    If indeed you are working with very long URL-s, then use varchar(max).

    KB Article: Maximum URL length is 2,083 characters in Internet Explorer


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration



    Saturday, July 13, 2013 1:29 PM
  • Once in billion records is enough to make your architectural not fit!

    * i have wrote the full answer i think on the first response by the way including the explanation about MAX URL.


    signature

    Saturday, July 13, 2013 2:13 PM
  • Hi pituach,

    Why wouldn't you use varchar(max) if you are worried about insane URL lengths like 2083?

    OTHER ISSUE: Can you use your English sig?  Thanks.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Saturday, July 13, 2013 3:13 PM
  • 1. as you know MAX might move the data to different page. 2. You have to make your application work on any browser so u have to limit it to 2048. you will have to check this in the application or this will be bad designed application. since you limit it to 2048 that is the max value in the database.

    * Kalman, please check my article on WIKI. i was asked to start write there for the last 3 month, and this is my first wiki. i want to get comments, notes, insights, and just feedback to get the decision if/how to continue write there. thanks


    signature

    Saturday, July 13, 2013 3:55 PM
  • pituach - I assume this is the TechNet Wiki URL:

    http://social.technet.microsoft.com/wiki/contents/articles/18481.shrink-sql-database-during-restore.aspx

    Impressive article. I will drop a note to Naomi, she is the Wiki expert.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Saturday, July 13, 2013 5:44 PM
  • >2048 is a good size,

    Sorry mate. Disagree. Makes the row too large, impractical.

    How many times do you see URL over 255 bytes? 

    When working with referral URLs, my data says 17.03% of the time. Not quite marginal, is it?


    Gert-Jan

    Saturday, July 13, 2013 7:36 PM
  • Gert-Jan,

    Why wouldn't you use varchar(max) instead of varchar(2083) when dealing with huge URLs?  Thanks.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Saturday, July 13, 2013 8:57 PM
  • Why wouldn't you use varchar(max) instead of varchar(2083) when dealing with huge URLs?

    For all the regular arguments there are for not oversizing a column.

    I don't see any additional argument just because we are talking about a URL here.

    A quick survey of these arguments:

    • According to Dmitri Korotkevitch, SQL Server assumes that variable width columns are 50% full when it estimates the size of the memory grant (4000 bytes for (max)). If the estimate is off the mark, it may affect performance
    • Varchar(max) column data may be stored out-of-row, which may affect performance
    • Hugo Kornelis argues that the data type is partially documentation and that specifying the correct maximum length makes the database easier to understand
    • Many people have made this argument. I like Hugo Kornelis' writeup: People being people, if people CAN input a million characters in a column, someone eventually will. If that data length was not taken into consideration when designing the entire system, this will probably overflow reports and screens
    • An index that includes a varchar(max) column (in the INCLUDE) list cannot be rebuild online, but only offline

    There is also the argument about being able to create an index on the column, but that is not relevant in this case (since 2048 is too big for that too). And the argument that you need SQL Server 2005 or later is hardly an argument anymore.


    Gert-Jan




    Saturday, July 13, 2013 10:18 PM
  • pituach - I assume this is the TechNet Wiki URL:

    http://social.technet.microsoft.com/wiki/contents/articles/18481.shrink-sql-database-during-restore.aspx

    Impressive article. I will drop a note to Naomi, she is the Wiki expert.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Hi Kalman

    1. thank you for the feedback, I appreciate it very much :-)

    Naomi is one of the people that push me to start write the WIKI. I am publishing a blog every 2-3 weeks, but most of my blogs written in Hebrew. I realized she speaks Hebrew, and I think she can read Hebrew.

    2. I saw you requested me to use an English signature . Unfortunately this is not possible because Microsoft Forum interface does not allow to hold several signatures, but only one signature. My signature is in Hebrew as I support  mainly in Hebrew forums where I also A Moderator & Answerer. The target audience of my signature is mostly those who use the forums I manage. The signature includes "proper forum user guide".


    signature

    Sunday, July 14, 2013 6:02 AM
  • Gert-Jan,

    You are making some very good points, but also debatable points.

    smallmoney for percentage has an advantage: you don't have to figure out the parameters to use like with DECIMAL.   It is more important to get the column name right.

    varchar(max) is more attractive to me as a practical database designer than varchar(2048) especially if I am not certain of the actual upper limit of the string.  Even in the case of the currenty discussed URL string, MS documentation refers to 2083(see above).  I also don't like to mix small width columns with varchar(2000)/varchar(max) columns in the same table. I prefer to move large strings to their own table and establish FK-PK connection. Hugo is right, some developer may take advantage of varchar(max) and move in larger strings than the intended max of 2083. But I am right also as a database designer: a 2200 long URL may pop up from somewhere in the next 5 years and my design supports it without table change.  As far varchar(max) optimization concerned, that is a different topic: there is no evidence that varchar(2083) optimizes better.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Monday, July 15, 2013 9:00 AM
  • smallmoney for percentage has an advantage: ... [snipped].   It is more important to get the column name right.

    varchar(max) is more attractive to me as a practical database designer than varchar(2048) ...

    Of course you can make these choices as a database designer, but in my opinion by doing so, you are putting the burden on the application developer.

    The smallmoney won't give a many problems. Maybe just a little confusion.

    But what maximum length should the application be able to handle if you have declared the column as varchar(max)? Are you really expecting the application developer to accommodate 2GB for a URL column? In all screens? In all reports? In all input fields?

    In a similar fashion, you could also ask why you shouldn't declare every non-indexed column as sql_variant? Wouldn't that make life as a database designer even simpler?

    If I were an application developer, if I had to choose between a database where
    A) you did the research and concluded that varchar(2083) was the most appropriate data type for a URL as and decimal(5,2) the most appropriate for a percentage for the company or
    B) you did no research and simply declared the percentage as money or smallmoney, URL as varchar(max), and possibly some other columns as varchar(max) or sql_variant as well...
    ... then I sure know which one I'd prefer.

    Your approach can definitely be a valid one. If it was decided (or if it is company policy) to use the database only as a data store, and to do all data validation in the application, then yours would be the way to go.

    If you want the database to take responsibility of (basic) data validation and integrity then you are skating on thin ice.


    Gert-Jan

    Monday, July 15, 2013 6:46 PM
  • Valid points Gert-Jan.

    The incredibly capable SQL language has a nice feature to prevent unintentional 2GB strings, that is the CHECK constraint:

    CREATE TABLE Hyperlink (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Title nvarchar(255) NOT NULL,
    URL varchar(max)  CHECK (len(URL) < 10000),
    ModifiedDate datetime2 default sysdatetime());

     

    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration



    Tuesday, July 16, 2013 8:12 AM