RULE II: Applying RULE on User Defined Data Type

Read Prev Article: A backward compatibility feature ‘RULE’ in SQL Server

RULES are constraint that bound to either column or User defined data type, they restrict the data entry to follow certain condition. They are quite different from Constraint which we will discuss in next series of article.

In previous article we saw, how we can create rule on Table Column, in this post we will see how we can create RULE on User Defined Data Type

                                  

Creating User Defined Data Type

create type phoone_number_type from varchar(13) not null

go

Creating Table using User Defined Data Type

create table PersonContacts

(pid int identity(1,1),

name varchar(10),

phone_number phoone_number_type)

Creating RULE

create rule phone_no_constraint

as

len(@phone) = 13 and

substring(@phone,3,1) = '-' and

isnumeric( left(@phone,2)) =1 and

isnumeric(right(@phone,10)) =1

Command(s) completed successfully.

Binding Rule to the column

exec sp_bindrule 'phone_no_constraint', 'phoone_number_type'

Rule bound to table column.

Now since Rules are bind to the column, lets try to insert value to the table. Here we have rule for Phone number is ’XX-XXXXXXXXX’ and X is integer type. Lets try to insert record against to Rule defined to Phone number and see what will be the result of insert.

insert into PersonContacts values('Neha','9820663556')

Msg 513, Level 16, State 0, Line 1

A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'MyTest', table 'dbo.PersonContacts', column 'phone_number'.

The statement has been terminated.

An attempt to insert phone number not following Rules does not allow to insert record to the table.

Lets insert a proper record in the table.

insert into PersonContacts values('Lopa','91-9099956156')

(1 row(s) affected)

Let’s query the table to conform insertion.

select * from PersonContacts

pid         name       phone_number

----------- ---------- -------------

4           Lopa       91-9099956156

Unbind RULE

exec sp_unbindrule 'phoone_number_type'

Rule unbound from table column.

Drop Rule

drop rule phone_no_constraint

Command(s) completed successfully.

My enjoyment is my code, but u r taking over my space J

 

Post Reference: Vikram Aristocratic Elfin Share

A backward compatibility feature ‘RULE’ in SQL Server

RULES are constraint that bound to either column or User defined data type, they restrict the data entry to follow certain condition. They are quite different from Constraint which we will discuss in next series of article. Lets see how we can create RULE on column

Creating RULE

create rule phone_no_constraint

as

len(@phone) = 13 and

substring(@phone,2,1) = '-' and

isnumeric( left(@phone,2)) =1 and

isnumeric(right(@phone,10)) =1

Command(s) completed successfully.

Let’s create a table with a phone number field and check how we can assign Rule to the Phone number field.

create table PersonContacts

(pid int identity(1,1),

name varchar(10),

phone_number varchar(13))

Command(s) completed successfully.

Binding Rule to the column

exec sp_bindrule 'phone_no_constraint', 'PersonContacts.phone_number'

Rule bound to table column.

Now since Rules are bind to the column, lets try to insert value to the table. Here we have rule for Phone number is ’XX-XXXXXXXXX’ and X is integer type. Lets try to insert record against to Rule defined to Phone number and see what will be the result of insert.

insert into PersonContacts values('Snehal','9820663556')

Msg 513, Level 16, State 0, Line 1

A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'MyTest', table 'dbo.PersonContacts', column 'phone_number'.

The statement has been terminated.

An attempt to insert phone number not following Rules does not allow to insert record to the table.

Lets insert a proper record in the table.

insert into PersonContacts values('Rinny','91-9099956156')

(1 row(s) affected)

Let’s query the table to conform insertion.

select * from PersonContacts

pid         name       phone_number

----------- ---------- -------------

3           Rinny      91-9099956156

Unbind RULE

exec sp_unbindrule 'PersonContacts.phone_number'

Rule unbound from table column.

Drop Rule

drop rule phone_no_constraint

Command(s) completed successfully.

Life without CODE is now happening…But oh my code, you are positive current to my love

 

Post Reference: Vikram Aristocratic Elfin Share

Can we create Parameterized View in SQL Server?

SQL Server doesn’t have parameterized view but close to it they came up with Table Value User Defined Function in SQL Server 2000.

If it did exist, the syntax would be 

create view ParamterView

(@empName varchar(10))

AS

 select * from employee where emp_name like @empName

Msg 102, Level 15, State 1, Procedure ParamterView, Line 2

Incorrect syntax near '@empName'.

Msg 137, Level 15, State 2, Procedure ParamterView, Line 4

Must declare the scalar variable "@empName".

But unfortunately we don’t have luxury to create parameterized view like this.

But we have alternative approach to stimulate the same using Inline table value function

create function pv_studentInfo (@student_id int)

returns table

as

return

(

    select * from Student

    where id = @student_id

)

Go

Lets call this view using select statement.

select * from pv_studentInfo(1)

id          name

----------- ------------

1           vikram

(1 row(s) affected)

View offer performance benefit against Function by allowing indexes on it, where as you cannot create indexes on Function but yes you can wrap indexed indexed view in Function.

Your code silently induce oxygen to your business partner.

 

Post Reference: Vikram Aristocratic Elfin Share

Why Temp table Name has maximum of 116 character length, whereas other has 128??? and SP_Server_Info

 

EXEC SP_SERVER_INFO is a procedure which gives server related information from your SQL Server; it returns a list of attribute names and matching values for attributes.

attribute_id attribute_name attribute_value

1 DBMS_NAME Microsoft SQL Server

2 DBMS_VER Microsoft SQL Server 2012 - 11.0.3000.0

10 OWNER_TERM owner

11 TABLE_TERM table

12 MAX_OWNER_NAME_LENGTH 128

13 TABLE_LENGTH 128

14 MAX_QUAL_LENGTH 128

15 COLUMN_LENGTH 128

16 IDENTIFIER_CASE MIXED

17 TX_ISOLATION 2

18 COLLATION_SEQ charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52

19 SAVEPOINT_SUPPORT Y

20 MULTI_RESULT_SETS Y

22 ACCESSIBLE_TABLES Y

100 USERID_LENGTH 128

Here we can see the TABLE_NAME attribute is 128, which means you can have table name 128 character long, though, a regular tablename can be 128 character long, a temporary table cannot be longer than 116 character

Why temporary table name has maximum character length of 116, whereas others have 128, let’s find out the technical reason behind it.

CREATE TABLE #AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA(i INT)

CREATE TABLE #B(i INT)

SELECT LEN(name) 'Length', name

FROM tempdb.sys.objects

WHERE TYPE = 'u'

AND name LIKE '#AAAAAA%'

OR name LIKE '#B%'

Whatever object name you give for temp table, SQL is going to pad the value with underscores and few “magic” bit as the end and would cover complete 128 characters. Have a closer look at length of magic bit “000000000058” and “000000000059” in above output [yeah, its 12]. Those are needed because you are allowed to create same name temp table by different sessions.  The length of magical number is 12 so 128 – 12 = 116 is the max length which is allowed for temp objects as SQL adds 12 chars at the end.

Be in this world with your code; make sure your code speak loud!

 

Post Reference: Vikram Aristocratic Elfin Share