A big issue when try to Alter user define table type structure TIP #108

 

I hope all of you aware of  User define table type (a table value parameter) which we discussed earlier in TIP #57.

Now recently one interesting incident happened. We are using a user define table type in few stored procedure and due to some business requirement change we need to change /update data type of a particular column from TINYINT to SMALLINT.

Now this change was seems very simple you just need to change a column’s data type  but when you are going to do this, you will find this is not pretty straight forward (if the User define table type is referred in different tables).

If you go through standard steps you need to follow below steps (for  a column data type change)

1) Create a new User define table  type

2) Replaced old User define table  type with new user define table type in each stored procedures

3) Remove old User define table type.

And , I would like to say a big thanks  Mr.Norlado  who post an alternative on stackoverflow

below is the alternative steps

1) Rename the existing table type with following command

EXEC sys.sp_rename ‘dbo.StudentTableType’, ‘zStudentTableType’;

2) Create Table type with your  changes which you want


CREATE TYPE dbo.StudentTableType AS TABLE(
    StudentId INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    ClassId SMALLINT — changed from tinyInt to smallint
);

3. Update the reference in sql entities

DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + ‘.’ + referencing_entity_name
FROM sys.dm_sql_referencing_entities(‘dbo.StudentTableType’, ‘TYPE’);

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;

4. Now drop the renamed table type

DROP TYPE dbo.zStudentTableType;

This 4 steps helped me a lot.

I hope this may help you as well.

Enjoy!!!

RJ!!!

Advertisements

An interesting way of aliasing TIP#107

I recently gone through something and found a unique way of aliasing. I thought it must be share so other techies also aware of it (or might be you already aware of it).

See below example

SELECT *
FROM (VALUES (‘Rajat’,30),
             (‘Sandeep’,40),
             (‘Sunil’,35),
             (‘Shreya’,50),
             (‘Virendra’,45)) AS T(Name,Runs)

aliasing

Now when you it  you will get following result

Aliasing_result

I hope you may like this tips.

Enjoy!!!

RJ!!!

Kill–use this weapon carefully in SQL SERVER . TIP #90

When we heard “Kill” then first impression of this word is very bad. We always scare with this word.

In real world we never want this action  from anyone but in SQL Server case it is very helpful and help us many times.

This feature we can use but very carefully. It is something like we have to kill a cruel giant not the common innocent person.

Lets understand this by an example.

Suppose we wrote a stored procedure or function and by mistake we wrote a condition which is never ending. We didn’t realize this and when we run the stored procedure/function the SQL Server hangs , system is slow and many other issues occur and our stored procedure which we run still running and laughing on us.

In such situation we have to kill that particular stored procedure execution statement which is cruel giant and eating the resources.

See below statement

WHILE 1=1
BEGIN
  SELECT ‘Infinite Loop’
END

It is never ending loop. and it is running on our SQL SERVER and will never stop.

Now first step is to Identify the process id for this statement for this we can use either sp_Who or sp_who2 which we discussed in TIP #82.

Now when we run it you will find on Database “IndiandotnetDB” we have a SPID 53 which is last statement on below image. This SPID 53 is reference of above while loop which we have to stop any ways.

sp_who2_Inadiandotnet

Now we know SPID so we can stop or KILL  as shown below the syntax is very simple of KILL

KILL SPID

Kill_Process_Indiandtnet

Now we are good to go.

Note: Please use this KILL statement very carefully.

I hope this tip will help you some where to KILL or stop SQL SERVER enemies.

Enjoy !!!

RJ!!!

PWDCOMPARE–a hidden function of SQL SERVER TIP #88

 

I the last tip TIP#88 we saw how to encrypt a password. Now in this tip I would like to share how to check encrypted password ?

Means once you stored your encrypted password in database now next step is to compare that particular password with your input password and return results accordingly.

The Syntax of the PWDCOMPARE   is very simple

PWDCOMPARE(‘Password plain text’, ‘Password encrypted form’)

This function return 1 if plain text and hash value  are matched else return o.

For example

Lets suppose we have created a table with 3 columns like userId, username and password

as shown below

DECLARE @tblLogin AS TABLE (UserId INT IDENTITY,
                            Username  VARCHAR(100),
                            EncryptedPassword NVARCHAR(MAX))

Now suppose we have inserted 2 rows in to it wit encrypted password

INSERT INTO @tblLogin VALUES (‘Indiandotnet’,PWDENCRYPT(N’MyPassword’))
INSERT INTO @tblLogin VALUES (‘SQLRaaga’, PWDENCRYPT(N’Test’))

Now, Suppose we have want to write a query which return rows from @tbllogin whose password is Test then it should return SQL Raaga for this

I have to write following query

SELECT * FROM @tblLogin WHERE PWDCOMPARE(N’Test’,EncryptedPassword) = 1

For detail  take a look of below snap

PWDCompare_Indiandotnet

 

I hope you understand with above provided  example.

 

Enjoy !!!

RJ!!

How to Encrypt password in SQL Server ? TIP #87

Security is always a concern for every database developer. How to secure valuable information is one of the major and important aspect.

The first approach toward security to have a strong username & password and the next step is to have password in encrypted form.

Now this article will help you to encrypt your password in hash. Isn’t it interesting ?

So SQL Server provided a function by using that particular simple function we can encrypt a password from plain text to hash.

The valuable function is PWDENCRYPT.  By the name it is clear that it will crease the password.

The syntax is very simple PWDENCRYPT(N’String which you want to encrypt’)

see below snap for more detail.

PWDEncrypted_Indiandotnet

I hope this tip help you to secure your password.

Enjoy !!

Thanks

RJ

How easy to determine table dependencies ? TIP # 86

 

Determine the table dependencies is challenging sometime but we can easily resolve this by using a simple stored procedure which  SQL Server provides.

By using this stored procedure we can easily determine all the dependencies of particular table.

The stored procedure is sp_msdependencies

We can use this stored procedure as shown below

Execute sp_msdependencies ‘tableName’

For example I am using Adventureworks2012 and I want to know the dependencies of product table then I have to write following command

Use AdventureWorks2012
Go
EXEC sp_msdependencies ‘[Production].[Product]’
GO

When I run this command I get result as shown in below figure

sp_msdependecies_Indiandotnet

I hope this tip may help you somewhere.

Thanks for reading.

Enjoy !!!

Rj !!!

“sp_helptext“ – Useful command to view detail TIP #85

 

It might be already known to you but I thought for sharing because I frequently use this command and it is very useful command.

When someone wants to determine detail of a function or stored procedure he/she can use this useful command.

The syntax is very simple. Just write

sp_helptext Storedprocedure/ functionname

For example If I want to determine detail of a stored procedure “proc_FindStudentUsingCorrect” then I have to write following command

sp_helptext proc_FindStudentUsingCorrect

see below snap for detail

sp_helptext

 

You can now copy the result text and check what exactly written in the stored procedure or function.

I hope you will use it in your day to day practice.

Enjoy!!!

RJ

How to determine free space on each fixed drive of server machine using SQL SERVER ? TIP #75

 

When you do  SQL Server maintenance one of important aspect is available space on server drive because your SQL SERVER data is dependent on space Smile.

Now what you need to do to get free space from each drive ?

Just create a simple job which run on daily basis which send you space report on daily basis.

This available space report help you to prepare yourself for next step.

The job will content a simple SQL statement which is

EXEC MASTER..xp_fixeddrives

When you run this command in your SQL Management Studio you will get a tabular result which have 2 columns which is drive & Free space in MB.

As shown in below figure

fixed_drive_space_by_Sql_server_indiandotnet

I hope this might help you somewhere.

Thanks & Enjoy!!!

RJ!!

How to replace a specific string from a large string with Write feature ? TIP #72

 

This is very interesting feature and I recently come to know this awesome feature.

Lets understand this by an example below

Suppose we have a student table with following structure  as shown in below image

structure 

We have a detail column with VARCHAR(MAX) .

Now as it is VARCHAR(MAX) column it may content a large amount of data currently it has following data as shown in below figure

Default_data

Suppose we want to replace a specific string from this large column value like we want to replace “interested in” with “Always”  of  studentId = 1 then

we can use write function easily.

With the help of Write we can update a specific text/string  of a large column.

syntax of Write is as shown below

UPDATE TABLENAME

SET COLUMNNAME.WRITE (ReplacedWithString, startPosition, length)

WHERE clause

Now lets understand this by above student example where we want to replace “Interested in” which is doubled by mistake with “always

writeFunction

So when you run above query you will get following result when you run select command.

result_after_Write

great we have replaced the string which we want.

The main benefit of this the entire column value is not logged. suppose you have 2 GB data in your column then instead of logging 2 GB data at the time update only few KB will be logged.

I hope this might helpful to you somewhere.

Thanks !!!

RJ!!!

How explicitly insert default value ? TIP #72

 

Suppose, sometimes you just need  entry in the table with all the default values.

For example you have a Student table which looks like as below

CREATE TABLE #tmpStudent (StudentId INT IDENTITY(1,1),
                          FirstName VARCHAR(100) DEFAULT ‘RAJAT’,
                          Course    VARCHAR(100) DEFAULT ‘MATHS’)

Now , suppose for in some situation we need just default entry for example in my case I am just trying to insert 100 rows with default value so I need to write below statement

INSERT INTO #tmpStudent (FirstName ,Course ) VALUES (Default, default)

Now when I run the above statement. It insert default value of FirstName,course

I hope this might be helpful to you somewhere.

Enjoy!!!

RJ!!