It is OK its NULL only TIP #103

 

We always take NULL very lightly. Like if we are designing database then whether it is necessary or not we allow the data field to accept null.We think it will not affect anything.

Actually , at some point this is not always true.

According to Microsoft NULL is an Unknown value. It is not EMPTY & ZERO. It is just unknown.

Now according to my experience NULL is very sensitive and should be handle with care.

While we are designing the database and in a particular table if a field can not be null according to business rule then please don’t allow NULL in that field for data consistency.

For example if we have a StudentEnrollment table and in the StudentEnrollment table  we have reference of StudentId which is primary key of StudentId and that can not be NULL then please don’t make the field null able.

Let me share some more interesting facts

1) Two null value not equal :-  Let me explain it with an example

Suppose I have tblStudentSource table  which has different columns and a course column which have some null values  as shown below

Indiandotnet_Student_Source_Table

Now see below snap where we are comparing the null value course column. You will find in below snap that there is no result while we have null value in course

NULL_IS_NOT_EQUAL_TO_NULL

2)  Use IS NULL or IS NOT NULL  to handle null able column:-  If a column is null and you want to show all the columns which are null then instead of comparing NULL value with column as shown in above figure use IS NULL as shown in below figure

IS_NULL

In similar way if we want result which not have null course then we can write below query

IS_NOT_NULL

3) Handle NULL carefully with IN clause

Let me explain with an example so we have 2 tables which are tblStudentSource (as shown in above (image 1) and another table which is tblCourse as shown in below figure

tblCourse_Indiandotnet

Now if you see we have course column in tblStudentSource table which has values similar to tblCourse’s course column and tblStudentSource’s course column contain some NULL values

Now suppose we wrote following statement and expecting that it will return all the course which are in tblCourse

NULL_Return_Nothing

Oh !! it is not returning any course. The reason behind it is NULL is not handled properly. Now to get desire result we have to write following statement

ISNOTNULL_WITH_IN 

So , be always careful when your column has null values.

I hope the above points might help you somewhere.

Enjoy !!!

Keep learning & Keep Sharing !!

Thanks

RJ

How to pause execution for particular time or interval ? TIP #71

 

Suppose, We are working on a stored procedure in which some complex operation is going. We are fetching some value from a table and running some custom operations and after ending of this complex operation, we want a pause of suppose 10 seconds to run another job then in such situation we will use

WAITFOR  DELAY HH:MM: SS” 

For example if we want to run select command (second statement) after 10 second delay of first command execution then we will use this as shown in below

INSERT INTO tblStudent (StudentId, StudentName)

VALUES (‘123’,’ABc’)

— in the background we are running some cursor which would take aprox 10 seconds so we are waiting for 10 seconds here

WAITFOR  DELAY 00:00:10

SELECT * FROM tblStudentHistory

In other situation suppose you want to run specific command after particular time then in such cases we will use “WAITFOR TIME  HH: MM: SS”

suppose

“WAITFOR TIME 22:30:45 “   it means SQL statement will pause till 10:30:45 PM

I hope this tips may help you somewhere in your project.

Thanks

Enjoy!!!

RJ!!!

Easy way to find result set of a stored procedure without running it- TIP #32

 

Sometimes , We need to know what is the structure of stored procedure ? , How many columns will be return ?

So to determine this we have easy stored procedure provided by SQL Server which is sp_describe_First_Result_Set.

Below is the template to run it. You can provide your stored procedure name as variable

Execute sp_Describe_First_Result_set ‘ProcedureName’

In below snap I used Adventureworks database and try to find what will be the output columns of the stored procedure “uspgetBillOfMaterials”

As you see in below image after running above query we got all the result set columns

resultset

I hope this tip help you somewhere.

Enjoy !!!

GET all columns of a table–TIP #4

 

To Get all columns information of a table we can use following command

GO

SELECT st.name, sc.name,sc.is_identity AS IdentityColumn,
sc.is_nullable AS NullableColumn,
sc.max_length as colmaxlength,
sty.name as columnType
FROM sys.columns sc
INNER JOIN sys.tables st ON st.object_id  = sc.object_id
INNER JOIN systypes sty on sty.xtype = sc.system_type_id
ORDER By st.name,sc.name
GO

sp_Columns_Detail_Rajat_indiandotnet

Or we can use following statement

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE
FROM [INFORMATION_SCHEMA].[COLUMNS]

sp_column_Details_2

Enjoy Smile !!!

sp_help a way to know detail tip #2

To determine table detail like column name & data types for each individual table ,Index, primary key, foreign keys etc,  sp_help is one of the best way.

Syntax of sp_help is very simple as shown below

sp_help tableName

GO

sp_help_table_name_Rajat_Jaiswal_Indiandotnet

Instead of writing  sp_help tablename you can get same information by highlighting table and press ALT+ F1

Note: if you just right sp_help and run it then all the objects of database will be listed down as a result.

sp_helpDB

Enjoy Smile !!!