SQL Server Performance

Userdefined Data Type

Discussion in 'T-SQL Performance Tuning for Developers' started by Cesar, Sep 16, 2003.

  1. Cesar New Member

    Hello!

    I have a table inside of that I want to define specific Userdefined Data Type so that a field only accept a certain format of data. For instance, eight numbers and one letter (25145218A). Is it possible to define these specific format or others in a field of a table? In that case, how can I do this? Is it better to define it only in the application?

    Thanks,
    Cesar
  2. gaurav_bindlish New Member

    Yes you can create a CHECK constaraint on the same to allow a particular datatype.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. gaurav_bindlish New Member

    See CREATE TABLE for examples on the same..

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  4. Cesar New Member

    Ok gaurav, I' m going to see those examples later.

    Thank you,
    Cesar
  5. bambola New Member

    Cesar - You can create a rule than create a user defined datatype bind it.<br />The rule will look something like this:<pre><br />CREATE RULE rule_name AS (substring(@v, 1, 7) NOT LIKE '%[^0-9]%' ) -- only digits<br /> AND (substring(@v, 8, 1) NOT LIKE '%[^A-Z]%' ) -- only letters<br /></pre><br />Once the rule is created, you can create the user defined datatype as varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /> and use rule_name as it's rule.<br /><br />Bambola.<br />
  6. Cesar New Member

    Thanks Bambola,
    I' ve been reading several concepts about Userdefined Data Types in SQL Server Help reference, and it says that the best option to define a specific data bind it is using Check Constraints instead of Rules, because Check Constraints are included in the Standard SQL-92 (I don#%92t know what does it means but sounds reliable J). Isn#%92t it?
    In affirmative case, where could I find a large reference about Check Constraints syntax explanation and examples to define a specific data bind it? Your example is very good but I don#%92 t understand a big part of the syntax yet.

    Cesar
  7. gaurav_bindlish New Member

    Inclusion in SQL-92 makes the probability of the feature to continue in next versions high.

    As I mentioned before BOL has a good explaination on this.

    Here is an excerpt from Inside SQL Server 2000-

    Although CHECK constraints and rules are essentially equivalent in functionality, CHECK constraints are easier to use and provide more flexibility. CHECK constraints are the preferred mechanism for restricting values in a column; SQL Server provides rules primarily as backward compatibility feature. A CHECK constraint can be conveniently defined when a column is defined, it can be defined on multiple columns, and it has access to all of SQL Server's built-in functions. A rule, however, must be defined and then bound separately to a single column or user-defined datatype, and it has access only to built-in functions that don't reference database objects.

    Both CHECK constraints and rules can require that a value fall within a particular range, match a particular pattern, or match one of the entries in a specified list. An advantage of CHECK constraints is that they can depend on either the value of another column or columns in the row or on the value returned by one of the built-in functions. A rule can't reference other fields. As an example of applying a CHECK constraint or rule, a database containing information on senior citizens could have the CHECK constraint or the rule "age column must contain a value between 65 and 120 years." A birth certificate database could require that the date in the birth_date column be some date prior to the current date.


    HTH.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. Cesar New Member

  9. bambola New Member

    If you are not comfortable with the example above, you can use something like<pre><br />ALTER TABLE table_name <br />ADD CONSTRAINT [CK_name] CHECK <br />([fiels_name] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]')<br /><br />-- example:<br />declare @v varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />select @v = '1234567A'<br />if @v like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'<br />select 'ok'<br />else<br />select 'not ok'<br /><br />select @v = '12A34567'<br />if @v like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'<br />select 'ok'<br />else<br />select 'not ok'</pre><br /><br />Bambola.
  10. Cesar New Member

    Better [<img src='/community/emoticons/emotion-5.gif' alt=';)' />],<br /><br />I have some questions about the example (declare @v varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />...):<br /><br />What is the purpose of the declaration?<br /><br />Where should I write it?<br /><br />The code would not be?:<br />declare @v varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />select @v = '<b><i>column_name</i></b>'<br />if @v like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'<br />select 'ok'<br />else<br />select 'not ok. <i>You must enter 7 numbers and 1 letter</i>'<br /><br /><br /><br />
  11. bambola New Member

    The example was just to show you how it works. What you need to do is add a CONSTRAINT to your column.
    This is the syntax to do that. Change the table_name CK_name and fiels_name, and run it in QA.

    ALTER TABLE table_name
    ADD CONSTRAINT [CK_name] CHECK
    ([fiels_name] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]')

    Bambola.
  12. Cesar New Member

    I have added Check Constraint successfully in the query analyser, but when I try to insert data in that column the error message is '[Microsoft][ODBC SQL Server Driver][SQL Server]Instruction INSERT in conflict with the constraint COLUMN CHECK 'CK_code'.... the instruction finished', and I entered correctly the code format for that column.
  13. bambola New Member

    Could you script your constraint and post it together with the insert statement?

    Bambola.
  14. Cesar New Member

    The script is as follows:

    ALTER TABLE Users
    ADD CONSTRAINT CK_code
    CHECK (User_code LIKE '[0-9][0-9][0-9][0-9]
    [0-9][0-9][0-9][0-9][A-Z]')


    Maybe the problem is the general format of the data (varchar, char, int,..) but I tried with several ways and the problem continues.
  15. Cesar New Member

    Sorry, I haven't used any insert statement, I have inserted directly on table
  16. bambola New Member

    what value are you trying to insert? it should be 8 digits and 1 letter... if it is case sensitive you will need also a-z ([A-Za-z])

    Bambola.
  17. Cesar New Member

    No, the problem is the same every time. The script and data that I insert seems correct..:

    ALTER TABLE Users
    ADD CONSTRAINT [CK_code]
    CHECK ([User_code] LIKE '[0-9][0-9][0-9][0-9]
    [0-9][0-9][0-9][0-9][A-Za-z]')


    DECLARE @V char(9)
    SELECT @V = '12345678X'
    INSERT INTO Users (User_code)
    VALUES (@V)
  18. bambola New Member

    Strange. The same thing happened to me. Then I deleted and recreated the constraint with the exact same code and it worked fine.

    Bambola.
  19. Cesar New Member

    You are lucky, the same thing does not happen to me.
  20. bambola New Member

    It's the line break that is causing the problem :-P
    Try

    ALTER TABLE Users
    ADD CONSTRAINT [CK_code]
    CHECK ([User_code] LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Za-z]')

    Bambola.
  21. Cesar New Member

    Good! [<img src='/community/emoticons/emotion-2.gif' alt=':D' />], it is true.<br /><br />Now works fine. <br /><br />Thank you very much,<br />Cesar

Share This Page