Ehsan Ghanbari

Experience, DotNet, Solutions

Add a column with default value to SQL server

Since releasing Entity Framework code first, I've approximately haven't written any SQL code to Table Migration! I was just thinking about that and tried to add a column to an SQL server table with a default value, here is the code I wrote:

 

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

 

For example, create a table named Product by the following query:

 

CREATE TABLE [dbo].[Product](

[Id] [int] NOT NULL,

[Name] [nvarchar](50) NULL,

[Price] [nvarchar](50) NULL,

 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

After inserting some record to the table, you can add another required column to the table via the query below:

 

ALTER TABLE [dbo].[Product]

        ADD Quality tinyInt 

 CONSTRAINT Product_Quality

    DEFAULT (0)

WITH VALUES



All kinds Relations in sql server

In the simplest form, a relation in SQL is matching data in key columns with the same name in different tables. There are three types of relationships between tables. 

One-to-Many Relationship

Many-to-Many Relationships

One-to-One Relationships

 

One-to-Many Relationships

it is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.

 

Example:

 

CREATE TABLE dbo.Book
(
Pk_Book_Id INT PRIMARY KEY,
Name VARCHAR(50),
ISBN VARCHAR(50)
);

CREATE TABLE dbo.Author
(
Pk_Author_Id INT PRIMARY KEY,
FullName     VARCHAR(50),
MobileNo     CHAR(10),
Fk_Book_Id   INT FOREIGN KEY REFERENCES Book(Pk_Book_Id)
);

INSERT INTO Book VALUES (1, 'C#', 'ISBN3030303');
INSERT INTO Book VALUES (2, 'SQL','ISBN638242');
GO

INSERT INTO dbo.Author VALUES(100,'A','34303',1);
INSERT INTO dbo.Author VALUES(101,'B','453',1);
GO

SELECT * FROM dbo.Book;
SELECT * FROM dbo.Author;

 

 

Many-to-Many Relationships

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both tables A and table B. Run the script below to see a king of many to many samples:

 

CREATE TABLE DimCustomers
(
  cust_id int PRIMARY KEY,

  cust_name varchar(50),
);
 GO
 CREATE TABLE DimAccounts
(
  acct_id int PRIMARY KEY,
  acct_name varchar(50),
  acct_type varchar(20),
);
GO
CREATE TABLE Customer_accounts_junction
(
  cust_id int,
  acct_id int,
  CONSTRAINT cust_acct_pk PRIMARY KEY (cust_id, acct_id),
  CONSTRAINT FK_Cust
     FOREIGN KEY (cust_id) REFERENCES DimCustomers (cust_id),
  CONSTRAINT FK_Acct
      FOREIGN KEY (acct_id) REFERENCES DimAccounts (acct_id),
);
GO
CREATE TABLE Fact_Transactions
(
  trans_id int PRIMARY KEY,
  trans_acct int,
  deposit int,
  CONSTRAINT FK_acct_id
      FOREIGN KEY (trans_acct) REFERENCES DimAccounts (acct_id),
);
GO

 

 

One-to-One Relationships

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints. Run this script in SQL server and see the result in the diagram:

 

CREATE TABLE dbo.Person
(
Pk_Person_Id INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
EmailId VARCHAR(255),
);

CREATE TABLE dbo.PassportDetails
(
Pk_Passport_Id INT PRIMARY KEY,
Passport_Number VARCHAR(255),
Fk_Person_Id INT UNIQUE FOREIGN KEY REFERENCES dbo.Person(Pk_Person_Id)
);

INSERT INTO dbo.Person VALUES ('A','[email protected]');
INSERT INTO dbo.Person VALUES ('B','[email protected]');


INSERT INTO dbo.PassportDetails VALUES (10, '4564533', 1);
INSERT INTO dbo.PassportDetails VALUES (11, '45645695', 2);

SELECT * FROM dbo.Person
SELECT * FROM dbo.PassportDetails;

 

References:

https://technet.microsoft.com/en-us/library/ms190651(v=sql.105).aspx

Example: http://social.technet.microsoft.com/wiki/contents/articles/19854.tutorial-many-to-many-dimension-example-for-sql-server-analysis-services.aspx



An Introduction to basic concepts of SQL Server

I'm going to write about basic concepts of Microsoft  SQL Server in two-part this is the part one! , as I've rarely seen SQL graphical text tutorial (most of them are text only and it does not make good sense for beginners like me), I want to fit more and more picture in this Article! This article is about SQL language fundamentals, and obviously, it is familiar to most of the developers!

Introduction to SQL language 

SQL is a language designed to retrieve and management of data in relational databases, SQL is a language and not all relational databases support SQL, so if you have SQL management studio installed in your system, let's begin!

CRUD operation 

Create, read, update and delete are the most common operation in most software systems and in every Enterprise application.

                  ff

Right click on Databases and create a new database, name it and then right click on that tables and create a table :

dd

     

Now, create the table's field. add ID, FirstName ,LastName ,UserName and Password ,

As shown in the picture below :

d

    

None of the fields accept the null, therefore I left all of the field definition as unchecked.

Now it needs to make ID field of this table as a PrimaryKey (every table should have a field as the primary key as Identity of every record )

 d

Then make the ID field's  Identity property as yes :

d

 

Now, save (ctrl+s) your table and name it Users, you can see your table in object explorer after refreshing, like this :

d

Now, let's insert some data to our table, to do this right click on the table and select Edit to top 200 rows to Add, go thought picture below :

 d

Open the table and insert the data, like this :

d

Now to writing CRUD(create, read, Update and delete) operation, you need to create a query, to do this click on the new query and begin writing the query:

d

First, create how to read from the table with a query language, type code below in query you just created and run(execute) it :

 

Select *
from Users

 

The picture shows the details!

d

Run another select query to select just UserName and Password, like this :

 

Select UserName,Password
from Users

 

Run the query to see the result, looks like this one, yeah?

You just made two select query, rewrite these query and run them and see the result :

 

Select UserName,Password
from Users
where (ID=2)

 

Note: you can use Equal(=), greater than(>), less than(<), greater than and equal (> =), less than, equal (<=)  operand in your where condition.

 

Select UserName,Password
from Users
where (ID between 1 And 2)
Select FirstName, UserName, Password
from Users
where (ID IN (1,2,3,4))
Select *
from Users
where ID <> 1

Select *
from Users
where (UserName='Ehsan')

 

LIKE keyword, try this one :

 

Select *
from Users
where (FirstName LIKE 'Ehsan')

 

You can order the query by "order by"

 

select * from Users
order by "LastName"

 

There are lots of SQL keyword that you can test them 

Top: returns the number of the records, for example, if you want to return 3 number of record in a table you can use TOP key work, see the example

 

         Select TOP 3 from Users

 

Between: used after where clause to customize a select list, for example, try this one

 

Select Top 5 from Users
Where ID between 1 and 3 

 

Alias: you can name a column or table by alias keyword, like below

 

Select Top 3 from Users
Where ID between 1 and 3
As TopBest 

 

SQL has some built-in function, you also can use them in your queries, take a look at this :

 

Select *
from Users
where (len(FirstName)>4) AND (Len(LastName)<6)

 

Len: is a function then returns the length!

Deleting rows in a table one by one could cause some performance issues when you want to completely clear the table, in this case, you can use truncate to clear the table. Notice that truncates doesn't log anything! Look at the syntax :

 

Truncate table Users 

 

you can although create the table in SQL server not only by the wizard, but with code with the syntax :

 

Create Table Users
(
UserId   int,
UserName varchar(30),
Password  varchar(20),
CreationDate datetime,
phoneNumber   int  null
)

 

So, if you remember we created the table data by wizard Not by query now create another query to add data ( by the way every time after creating query save it to use it in next steps), type this code in your new query :

 

Insert into Users
(FirstName,LastName,UserName,Password)
values ('randy','colson','randy','454574')

 

To see the inserted row in the Users table, right click on Select top 1000 rows :

d

So let's dig in the Update operation. I want to Update the FirstName and UserName of the last record I added with Id=3, to do this :

 

Update Users
Set FirstName='michael', UserName='michaelAccount'
where ID='4'

 

And the result of this query :

d

Delete a row from the table is pretty simple, for example :

 

Delete from Users where ID=1

 

As you can see in deleting and updating a table content you should have the table's Id.Y ou saw the simple CRUD operation.



About Me

Ehsan Ghanbari

Hi! my name is Ehsan. I'm a developer, passionate technologist, and fan of clean code. I'm interested in enterprise and large-scale applications architecture and design patterns and I'm spending a lot of my time on architecture subject. Since 2008, I've been as a developer for companies and organizations and I've been focusing on Microsoft ecosystem all the time. During the&nb Read More

Post Tags
Pending Blog Posts
Strategic design
Factory Pattern
time out pattern in ajax
Selectors in Jquery
using Log4net in asp.net MVC4
How to use PagedList In asp.net MVC
Redis as a cache server
Domain driven design VS model driven architecture
What's the DDD-lite?
Multiple submit buttons in asp.net MVC