Category: SQL

SQL, SQL Server, T-SQL

SQL Behaviour – Assignment of value to Variable

We are going to see the SQL Behaviour while assigning value to variable, we thought of writing this post since recently we came across issues in real time during data assignment and processing.

Case 1:- First let’s see the example of direct value assignment in SQL,

declare @valueAssign nvarchar(50)='', @valueIntAssign int
set @valueAssign='YYYY'
set @valueIntAssign=1111
select @valueAssign as Fullname_DirectAssign
select @valueIntAssign as Number_DirectAssign

Output:-

Case 2:- Now we will see the Random value assignment in SQL, there are 3 rows in the table but it randomly picked the data from 3rd row during the execution.

declare @valueAssign nvarchar(50)='', @valueIntAssign int
set @valueAssign='YYYY'
set @valueIntAssign=1111
select @valueAssign as Fullname_DirectAssign
select @valueIntAssign as Number_DirectAssign

drop table if exists DataValueAssign

create table DataValueAssign(
id int,
fullname nvarchar(50),
Age int
)
insert into DataValueAssign values (1,'AAAA',19),(2,'CCCC',54),(3,'BBBB',34)
---Data Value Assigned Randomly to the Variable--
select @valueAssign=fullname,@valueIntAssign=age from DataValueAssign
select @valueAssign as Fullname_Randomly, @valueIntAssign as Age_Randomly

Output:-

Case 3:- Now let’s see how we can handle this Random value assignment behaviour by using the “Order by” and “Top” functions and assign the appropriate values to the variable and use it for data processing.

declare @valueAssign nvarchar(50)='', @valueIntAssign int
set @valueAssign='YYYY'
set @valueIntAssign=1111
select @valueAssign as Fullname_DirectAssign
select @valueIntAssign as Number_DirectAssign

drop table if exists DataValueAssign

create table DataValueAssign(
id int,
fullname nvarchar(50),
Age int
)
insert into DataValueAssign values (1,'AAAA',19),(2,'CCCC',54),(3,'BBBB',34)
---First Data Value Assigned to the Variable using order by ID--
select @valueAssign=fullname,@valueIntAssign=age from DataValueAssign order by ID desc
select @valueAssign as Fullname_FirstValue_using_Orderby,@valueIntAssign as Age_FirstValue_using_Orderby
---Top 1 Data Value Assigned to the Variable using order by AGE desc--
select top 1 @valueAssign=fullname,@valueIntAssign=age from DataValueAssign order by age desc
select @valueAssign as Fullname_Top1_Orderby_desc, @valueIntAssign as Age_Top1_Orderby_desc
---Top 1 Data Value Assigned to the Variable using order by AGE--
select top 1 @valueAssign=fullname,@valueIntAssign=age from DataValueAssign order by age
select @valueAssign as Fullname_Top1_Orderby_asc, @valueIntAssign as Age_Top1_Orderby_asc

Output:-

Be aware while assigning value to SQL variable and make sure that the expected value was set to variable, enjoy exploring SQL, Happy Learning !!!

Thanks for reading this post, Please share it to your friends if you liked it 🙂

Linked Server, SQL

Error Message: Server is not configured for RPC in SQL Server

Recently one of my colleague reached out to me with an error message as : “Server is not configured for RPC”

This a typical issue with Linked Server. When we configure a linked server, we need to set up the right values for RPC & RPC Out . We can see these values in SSMS -> Right click on Linked Server -> Properties -> Server Options as in the below screen shot.

RPC / RPC OutRPC stands for Remote Procedure Call and allows you to run stored procedures on the Linked Server.  RPC enables Remote Procedure Calls from the specified server and RPC OUT enables Remote Procedure Calls to the specified server.

Hope this helps; If you enjoyed this blog post, feel free to share it with your friends!

DataTypes, SQL

Computed columns in SQL Server

What are computed columns in SQL Server

Computed columns are virtual columns that does not physically store the data in SQL Server unless it is marked as PERSISTED column. Computed columns can be defined by expression that can use other columns of the same table or static expressions.

Limitations

  1. It cannot be part of INSERT/UPDATE statement.
  2. It cannot defined as FOREIGN KEY/DEFAULT or NOT NULL constraint.

How to add a computed column while creating a table?

Create Table SampleComputedTable
(
	EmpName varchar(50),
	DOB Date,
	Age as Datediff(year,DOB,GETDATE())
)

--Today is -> '2021-07-25'; for simple explantion, the Age is calculated only year part.
Insert into SampleComputedTable Values ('Latheesh','2019-07-25')

Select * From SampleComputedTable

Drop table SampleComputedTable

How to add a computed column to an existing table? 

Alter Table SampleComputedTable Add AgeInMonths as DateDiff(month,DOB,Getdate())

Select * From SampleComputedTable

What are the best practices while creating a computed column?

There is NO way to alter computed column. You will have to drop and recreate it. 

How to find the definition of a computed column?

Select object_name(object_id) TableName, name as ColumnName, definition From sys.computed_columns 
where name='Age' and object_name(object_id) = 'SampleComputedTable'

If you enjoyed this blog post, feel free to share it with your friends!

SQL

FOREIGN KEY Constraints in SQL Server

Foreign Key Constraint is a type of constraint in SQL Server to enforce a relation between two tables with a column or a set of columns to ensure a data integrity.

Let us quickly look at how do we create a foreign key in SQL Server

Create Table ParentTable (ParentCol int Primary Key NOT NULL)
Create Table ChildTable (ChildCol int Primary Key NOT NULL, ParentCol int NULL references ParentTable(ParentCol))

The above will create foreign key from ChildTable to ParentTable on column ParentCol. If we carefully look at the definition of ParentCol in ChildTable, we can see its a NULL-able column. If there is a requirement that we should not have NULL value, then we can make the column as NOT NULL.

Cascade Actions:

As we see in the above screenshot, delete_action/update_action are called as cascade actions. This can be used to define the actions on delete/update operations on Primary table. Let us examine with an example as below.

First off, let us try to insert some data in both tables and try to delete from Parent which has reference record in ChildTable.

Insert into ParentTable Select 100
Insert into ParentTable Select 200
Insert into ParentTable Select 300
Insert into ChildTable Select 1, NULL
Insert into ChildTable Select 2, NULL
Insert into ChildTable Select 3,300

Select * From ParentTable
Select * From ChildTable

--Now try to delete a record from ParentTable which has a corresponding record in ChildTable
Delete From ParentTable where ParentCol =300

The DELETE operation fails as there is a record in ChildTable which prevents the DELETE operation on ParentTable.

Now, let us have a CASCADE action on DELETE to define delete on ChildTable as well.

ALTER TABLE ChildTable DROP constraint[FK__ChildTabl__Paren__58D1301D]

ALTER TABLE [dbo].ChildTable  WITH CHECK ADD  CONSTRAINT [FK__ChildTabl__Paren__58D1301D] FOREIGN KEY(ParentCol)
REFERENCES [dbo].ParentTable (ParentCol)
ON DELETE CASCADE
GO

/*Now try to delete a record from ParentTable which has a corresponding record in ChildTable*/
Delete From ParentTable where ParentCol =300


Select * From ParentTable
Select * From ChildTable

Now, we can see the DELETE operation is successful and the Delete action on the table has been changed to Cascade as below.

Points to ponder:

  1. You need to have the column in the Parent Table part of Primary Key
  2. There would not be any indexes created on the Child table for the referenced key by default. Sometimes, indexing of the foreign key column helps a lots on transactional databases/tables.
  3. Foreign key constraints are advised to create when two tables are tightly coupled and if we use these tables in a join condition to check its existence on Parent tables, because the key constraints takes care of it by default.
  4. If we have a CASCADE update, then SQL Server will not allow to create Instead of Trigger on the table.

If you enjoyed this blog post, feel free to share it with your friends!

SQL

DEFAULT constraint in SQL Server

This is the *simplest* constraint of all other constraints to explain, DEFAULT constraint will set a default value for a column if a value is not specified for the column.

How do we declare a default constraint?

  • While declaring a table
CREATE TABLE People (
    ID int NOT NULL,
    FirstName varchar(255),
    City varchar(255) DEFAULT 'Chennai'
);
--The below insert operation is not specified any value for City, but SQL Server uses default value 'Chennai'
Insert into People(ID,FirstName) Values(1,'Latheesh NK')

--The below insert specified a value for City
Insert into People(ID,FirstName,City) Values(2,'NK Latheesh','Cochin')

Select * from People

Drop Table People
  • Altering an existing table

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
    a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable and you want the Default Value used for Existing records.
If your Column is NOT NULL, then it will automatically use the Default Value for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol‘s value, then it will Default to 0.
If you insert a Record and Specify SomeCol‘s value as NULL (and your column allows nulls), then the Default-Constraint will not be used and NULL will be inserted as the Value.

If you want to add multiple columns you can do it this way for example:

ALTER TABLE YourTable
    ADD Column1 INT NOT NULL DEFAULT 0,
        Column2 INT NOT NULL DEFAULT 1,
        Column3 VARCHAR(50) DEFAULT 'Hello'

Can we drop a DEFAULT constraint?

Yes, we can very well DROP a default constraint.

Can we disable/enable DEFAULT constraint?

No, we cannot disable/enable default constraint, we can only drop and recreate default constraint in SQL Server.

If you enjoyed this blog post, feel free to share it with your friends!