How To Use ALTER TABLE in SQL Server

Sometime you want to modify exists table in database. You don't want to delete and recreate. So in SQL Server we can use Alter Table to modify table. We can delete columns, add new columns, modify columns name, modify datatype.

Please follow below scripts:

1. Add Column

Syntax: Alter Table TableName ADD NewField DataType

EX: Alter Table tbl_Staff Add DOB DateTime

How to know Last Backup Date in SQL Server

My friend asked me about one problem in SQL Server. He cannot find the last backup date in SQL Server. He asked me help him find scripts to query last backup date. I try a few days for find this scripts. Finally I found one scripts that can query this data.

How to Find Empty Table in SQL Server

Today I have a good tip to share about find empty table in SQL Server. Sometime you created table in database, but you didn’t insert any rows. Once day you want to optimize or want to delete some table, you should run this query to know which empty table. It is necessary to clarifies the empty table for prevent lost your data before you delete it.

5 Ways to Improve Execution Query in SQL Server

SQL Server is the popular products to store data in database. When the database growth up to large we need to care about two things. The first we must care about security. We could make schedule to backup and set policy to the user. And the second we should know when the data is large we will has problem with queries data from database. Why it make queries data slowly? Because it has a lot record, need take time to find data that we want. To increase performance of query in database is not

How to Make High Security in MS SQL Server

Security is very important think that we need to know and careful on your data. As you know data in each organization is kept secret. They don’t allow someone know except theirs staff. Another thing they must prevents the data lost. If your data are stolen it effect to your organization because they will know your secret data. How to prevent above problem? Here are some tips to make high security to prevent your data from hacker or other person that want to steal your data. To make high security on MS SQL Server there are 2 things. One is prevent someone steal and prevent it lost.

MS SQL Server with Khmer Unicode

MS SQL Server is a product of Microsoft that we use it for store data, information and we can easy find that data when you need. MS SQL Server is the database application for manage data or any information of organization. If we compare with MS Access we can say MS SQL Server is better because MS SQL Server have more features, tools, function than MS Access in manage data.  Today I have one topic to share you about how to make MS SQL Server support with Khmer Unicode.

Use Select Case In SQL Server

In Sql Server you can use Select Case for select data from table
bye use Select Case. It is importance to use because you can used it
for select data that have complicate condition.

Please see example as below:

SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price <>
ELSE 'Unknown'
END,
FROM titles

In this example, price is a condition that we want to show Message by quality of price

Count String In SQL Server

T-SQL is the language for find data in database. It can insert, search, update and delete all data. We also can join table or find complexly data(data in other table). we also use T-SQL to make application or function to run in database. Today I want to know some knowledge about count string in SQL Server.

Ex: We have string as below

@str='9-9-99-99-999'

We want to count symbol (-) in this string
Please write this statement:

Declare @countStr varchar(100)
Set @countStr=Len(Replace(@str,'9',''))
Print @countStr

It will show result =4

I will have other tips about SQL Server to share. If you have any problem please comment. I will try to help and find the solution for you.

How to Convert Text To Number In SQL Server

In Sql Server we can convert text to number by use two properties.
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.

1. CAST

Please see example below:

SELECT CAST(YourVarcharCol AS INT) FROM Table

it mean you can convert text to number by use CAST then select field in table that you want to
select and assign data type.

2. CONVERT

SELECT CONVERT(INT, YourVarcharCol) FROM Table

Two properties have different in used.
so if you want which properties you msut see Syntax clearly.

Count Sundays Between Two Dates

Once day I developed one application, the system required to count number of Sundays between duration of date. I tried for 2 hour to find solution to get number of Sundays. And after I finished my work I want to share this tip to other developer. I wrote the scripts to count, we just input parameter @StartDate and @EndDate, it will show result.

Please run this code and test your self. 

declare @StartDate datetime, @EndDate datetime

set @StartDate = '2009-01-01'

set @EndDate = '2010-06-01'



Select Sundays=Count(*) From (Select Top (Datediff (day, @StartDate, @EndDate) +1)

[Date] = dateadd(day, ROW_NUMBER()

Over(order by c1.name, c2.name), convert(char(10),@StartDate-1,110))

From sys.columns c1

cross join sys.columns c2) x

Where datepart(dw,[Date]) = 1;

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

Output: Number of sundays between two date is 74 sundays.

How to Show Data In TreeView

It you have some data in table and want to show it in TreeView
You can write code below:
1. tbl_Data have have data below:
2. Then please write code below:
TreeView1.Nodes.Clear();
private void showtreeview()
SqlCommand cmd= new SqlCommand ();
cmd.Connection =conn;
cmd.CommandType=CommandType.Text;
cmd.CommandText = "Select * from tbl_data order by Leve";
SqlDataReader sqlDR = cmd.ExecuteReader();

TreeView1.Nodes.Clear();

int lvCode = 0;

while (sqlDR.Read())
{
TreeNode tn = new TreeNode();
tn.Text = sqlDR["Level"].ToString() + "-" + sqlDR["Text"].ToString();
tn.Value = sqlDR["Level"].ToString();

if (sqlDR["Level"].ToString().Equals("1")){

TreeView1.Nodes.Add(tn);
TreeView1.Nodes[TreeView1.Nodes.Count - 1].Select();

}else
{
if ( lvCode 0)
{
TreeView1.SelectedNode.ChildNodes "+
" [TreeView1.SelectedNode.ChildNodes.Count-1].Select();
}
}
else if (lvCode > int.Parse(sqlDR["Level"].ToString()))
{
TreeView1.SelectedNode.Parent.Select();
}


TreeView1.SelectedNode.ChildNodes.Add(tn);
}
lvCode = int.Parse(sqlDR["Level"].ToString());


}
}

3. When already to finish your code you can run it.

How to Convert Null Value To Number In SQL Server

Do you know, or you can calculate Null value in SQL Server or not?
when you select some data from table of database, sometime you get
null value. but you want to use this value for calculate to other values.
The solution, you must convert null value to numeric value when your
data is null value.

How to Date Time Format In SQL Server

SQL Server provides a number of options you can use to
format a date/time string. One of the first considerations
is the actual date/time needed. The most common is the
current date/time using getdate(). This provides the
current date and time according to the server providing
the date and time. If a universal date/time is needed,
then getutcdate() should be used. To change the format
of the date, you convert the requested date to a string
and specify the format number corresponding to the
format needed. Below is a list of formats and an example
of the output:

DATE FORMATS
-------------------------------------------------------------------------------------
Format # |Query (current date: 12/30/2006) | Sample
-------------------------------------------------------------------------------------
1 | select convert(varchar, getdate(), 1) | 12/30/06
2 | select convert(varchar, getdate(), 2) | 06.12.30
3 | select convert(varchar, getdate(), 3) | 30/12/06
4 | select convert(varchar, getdate(), 4) | 30.12.06
5 | select convert(varchar, getdate(), 5) | 30-12-06
6 | select convert(varchar, getdate(), 6) | 30 Dec 06
7 | select convert(varchar, getdate(), 7) | Dec 30, 06
10 | select convert(varchar, getdate(), 10) | 12-30-06
11 | select convert(varchar, getdate(), 11) | 06/12/30
101 | select convert(varchar, getdate(), 101) | 12/30/2006
102 | select convert(varchar, getdate(), 102) | 2006.12.30
103 | select convert(varchar, getdate(), 103) | 30/12/2006
104 | select convert(varchar, getdate(), 104) | 30.12.2006
105 | select convert(varchar, getdate(), 105) | 30-12-2006
106 | select convert(varchar, getdate(), 106) | 30 Dec 2006
107 | select convert(varchar, getdate(), 107) | Dec 30, 2006
110 | select convert(varchar, getdate(), 110) | 12-30-2006
111 | select convert(varchar, getdate(), 111) | 2006/12/30
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------

TIME FORMAT
-------------------------------------------------------------------------------------
8 or 108 | select convert(varchar, getdate(), 8) | 00:38:54
9 or 109 | select convert(varchar, getdate(), 9) | Dec 30 2006 12:38:54:840AM
14 or 114 | select convert(varchar, getdate(), 14) | 00:38:54:840
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------

SAMPLE STATEMENT
-------------------------------------------------------------------------------------
select replace(convert(varchar, getdate(),101),'/','') | 12302006
select replace(convert(varchar, getdate(),101),'/','') + | replace(convert(varchar, getdate(),108),':','') 12302006004426
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------

GETDATE() is field that datetime

How to Store Procedure for Update Multi Record In Table

You can update multi record by using store procedure.
If is very easy and good processes, you not write store procedure
a lot of store procedure for update data a lot of row. but if you know about
update multi data you can reduce of write store procedure.
please see store procedure below:


ALTER PROCEDURE [dbo].[Pro_UpdateTblOption]
@optionOne varchar(50),
@optionTwo varchar(50),
@optionThree varchar(50),
@optionFour varchar(50),
@optionFive varchar(50),
@SMS varchar(30) Output
AS
Update tbl_Option Set parametervalue = @optionOne
Where optionID=1;
Update tbl_Option Set parametervalue = @optionTwo
Where optionID=2;
Update tbl_Option Set parametervalue = @optionThree
Where optionID=3;
Update tbl_Option Set parametervalue = @optionFour
Where optionID=4;
Update tbl_Option Set parametervalue = @optionFive
Where optionID=5;
Set @SMS='Update Successful...'

If is help you easy.

How to Store Procedure For Add Data to Table

Using Store Procedure for add data to table is very important
and all developer like use it. because it provide fast and
good process when you add data to table. this is a example
and code for Add data to table.


USE [SecDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[tbl_Group_AddData]
@ID int,
@Name varchar(50),
@Sex int,
@SMS varchar(100) Output
As
If Exists(Select * From tbl_Name Where ID=@ID)
Begin
Set @SMS='Data exist already. Please Change!'
End
Else
Begin
Insert Into tbl_Group
(
ID,
Name,
Sex
)

Values
(
@ID,
@Name,
@Sex
)
Set @SMS='Insert data successful'
End

If can show message when insert successful and
when have data already.