Optimal way to remove time from Sql Date Time Column

As there is a separate data type [ date ] in sql server 2008 and on wards but still many  databases are relying on sql server 2000 and sql server 2005 where data type is only datetime and in most of the cases we don’t require time so to remove time part from date developers use different techniques.
Let’s analyze two commonly use techniques to remove time part from date field and know which is better in terms of time.

Select Count(1) From tblNames

508860 records.

For test i have a table of tblNames with almost 508860 dummy records i believe every one should have one dummy table to test each query themselves.

Let’s analyze the two majorly used queries.

Dbcc Freeproccache;
Dbcc Dropcleanbuffers;

Set Statistics Time On;

Select Convert(DateTime, Convert(Varchar, dtCurrentDate, 103), 103)
From tblNames

Set Statistics Time Off;

Firstly Dbcc Freeproccache and Dbcc Dropcleanbuffers is use to clean the cache as this is a very important step before analyzing the result because sql server cache results so if we don’t clean the cache then our result will not be correct.
Convert to datetime is first analyzed as this is a very common technique used but is this the best ???
After running the query 10 times and then taking the average of this query it resulted in average

Cpu time = 531 ms

Now analyze another technique to remove the time part from date time column.

Dbcc Freeproccache;
Dbcc Dropcleanbuffers;

Set Statistics Time On;

Select DateAdd(Day, 0, DateDiff(day, 0, dtCurrentDate))
From tblNames

Set Statistics Time Off;

This query looks unusual on a first look but let’s understand what it does, in the query initially the number of  days difference is calculated between the minimum sql date and the given date.
Note ( 0 means minimum sql date)

Then the resultant number of days are added by 0 which result in removal of time part from date.
Note ( 0 means number of days int)
To understand this query important this is to understand the functions DateAdd and DateDiff.

DateDiff (Interval, StartDate , EndDate) and it returns Integer.
where as
DateAdd(Interval, Increment Value Integer, GivenDate)
and it returns datetime.

Now back to the execution cpu time of this query. though it looks complex to see this conversion but the resultant time is mighty fast and almost half the previous convert to varchar query.
The average execution time after 5 run is

— SQL Server Execution Time:
— CPU time = 266 ms

So always use
 Select DateAdd(Day, 0, DateDiff(day, 0, dtCurrentDate))
From tblNames 
When ever you need to remove time from date time field.

TRANSACTIONS AND OTHER FUNCTIONS:

Transaction:

 

  • Database transactions consist of the following statements.
  • DML statement that makeup one consistent change to the data.
  • One DDL statement.
  • One DCL statement.

 

  • DDL stands for Data Definition Language.
  • DCL stands for Data Control Language.

 

 

NOTE: –

  • Database transaction start when the first executable statement is executed.
  • Transaction and when one of the following events occurs.
  • COMMIT or ROLLBACK is issued.
  • DDL or DCL statement executed.
  • User exits.
  • System crashes (Worst Scenario).

 

 

 

COMMIT or ROLLBACK:

 

 

Transaction

INSERT

UPDATE

INSERT

DELETE

 

 

LAST          SAVEPOINT A                               SAVEPOINT B

COMMIT

 

ROLLBACK TO B

 

ROLLBACK TO A

 

ROLLBACK

 

COMMIT:

  • Ends the current transaction by making all the pending data changes permanent.

 

 

SAVEPOINT name:

  • Marks the save point in the current transaction.

 

 

ROLLBACK TO SAVEPOINT name:

  • Ends the current transaction by discarding the SAVEPOINT and all subsequent changes.

 

 

State Of Data Before COMMIT OR ROLLBACK:

  • Previous state of the data can be recovered.
  • The current user can review the results of the DML operation by using SELECT.
  • Other users cannot view the results of the DML by the current user.
  • The effected rows are locked, other user cannot change the data within the effected rows or the effected rows are locked.

 

 

State Of Data After COMMIT:

  • Data changes are made permanent in the database.
  • The previous state of the data is permanently lost.
  • All users can view the results.
  • Locks on the effected rows are released.

 

Example: (COMMIT)

 

  • All save points are erased

 

Make Change:

 

SQL> UPDATE emp

SET deptno=10

WHERE empno = 7782;

 

Output:

1 row updated.

 

 

 

Commit Change:

 

SQL> COMMIT;

 

Output:

Commit complete.

 

Example: (Rollback)

 

1) SQL> DELETE from emp;

 

Output:

14 rows deleted.

 

 

2) SQL> ROLLBACK;

 

Output:

Rollback complete.

 

Queries:

 

1) SQL> UPDATE emp

SET deptno=20

WHERE empno = 7782;

 

Output:

1 row updated.

 

 

2) SQL> SAVEPOINT update_done;

 

Output:

Savepoint created.

 

 

3) SQL> INSERT INTO dept (deptno, dname, loc)

VALUES (50, ‘DEVELOPMENT’, ‘DETROIT’);

 

Output:

1 row created.

 

 

4) SQL> ROLLBACK TO update_done;

 

Output:

Rollback complete.

 

Creating & Managing Tables:

 

SQL> CREATE TABLE item       Table Name

(itemno  NUMBER(2),

iname   VARCHAR(14),         Data type

loc     VARCHAR(13));

 

Output:

Table created.

 

 

Creating a Table By Using A Sub-Query:

 

SQL> CREATE TABLE employee_in_dept30

AS       Must be Used!

SELECT empno, ename, hiredate, sal, deptno, mgr

FROM emp

WHERE deptno=30;

 

Output:

Table created.

 

 

Adding A Column:

 

command         Table Name

 

SQL> ALTER TABLE dept

 

ADD (job VARCHAR2(9));

 

 

 

 

Keyword   Column        data type

name

 

Output:

Table altered.

 

  • Default value will be “NULL” in the job column after the execution of the upper query.

 

 

 

 

Modifying Column:

SQL> ALTER TABLE dept

MODIFY (dname VARCHAR2(15));

New data type

Output:

Table altered.

 

 

  • Usual conversation is from similar kind of data type for example char can be converted to VARCHAR2 or empno might be change from 4 to 5 numbers.
  • If a column is modified the new data type would be applied only to subsequent addition and all previous rows would have the old data type.

 

 

 

Dropping A Table:

 

SQL> DROP TABLE dept30;

Object

Output:

Table dropped.

 

  • It simply deletes the table.
  • In scripts design to create full database structure all tables that are to created are first dropped.

 

 

 

Changing The Name Of The Object:

 

SQL> RENAME dept TO department;

 

Output:

Table renamed.

 

 

Example Of Object:

  • View
  • Table
  • Index
  • Cursor

 

 

 

Constraints:

 

dept                                                    emp

           deptno, dname, loc                          empno, deptno, ename

 

 

 

 

Primary                                          Primary  Foreign

Ke y                                                Key        Key

 

  • Unique constraint is used for fields that are not primary key and they must be unique, only a single Primary Key per table but multiple unique columns can exist.

 

 

Types Of Constraints:

 

  • Not Null
  • Unique
  • Primary Key                      Mostly Used
  • Foreign Key

 

 

  • Check                   Condition Based

 

 

Example:

 

SQL> CREATE TABLE item(

itemno NUMBER(2),          Type Of Constraint

iname VARCHAR(14),

loc VARCHAR(13),

CONSTRAINT   item_iname_uk    UNIQUE     (iname),

CONSTRAINT   item_itemno_pk PRIMARY KEY  (itemno));

Keyword      Name of constraint                               Constraint

applied on

Output:

Table created.

 

SE1:

Create a table named Empdept20 that contains the ename , sal of all employees in department 20.

 

SE2:

Rename the above table to Emp_dept_20.

 

SE3:

Write a query the ename from the user and then deletes the record of that employee.

 

SE4:

Create a view containing information about all employees in department 20.

 

SE5:

Display information about employees whose name starts with an ‘S’ from SE4.

 

SE6:

Create a table EMP10 that lists employees of department 10. Perform DML on the table after creating savepoints. Perform rollbacks, observe results and give systematic outputs.

How To Delete Values From Database Using SQL In C#…

Now in our early post we learned how to insert the values in the database from coding in C#.

Once you insert a value in the database you should also know how to delete the values from the database.

Deleting is considered to be the easiest thing in SQL, It just takes few lines of code to perform this task but there are some preliminary steps which are involved before performing any SQL query in C#.

Beside the easiness of this query remember the delete query is very power full query and once it is executed then recovering the data is now a piece of cake. Always be fully concentrated while using this query and never write the wrong table name or column name because C# does not show any errors during the compile time,If you have written a wrong query then you have to wait until the code is executed.

There is also a difference between the delete and drop table query for the difference visit our previous blog http://www.farazashraf.wordpress.com. Now lets start the deletion process.

First we will add the namespace of SQL in our code…

So to add namespace first thing you should know is that your data is on which database platform.

Let say you are working on simple Microsoft platform and your data is on access database then you would be using the OLE DB (Object Linking and Embedding, Database) this is an API designed by Microsoft to work on access database.

STEP 1:

So you will start with

Using System;

Using System.Data.OLeDb;

STEP 2:

after adding the namespace now its time for coding in main.

First you  have to create database connection so it will be

OLeDbConnection conn=new OLeDbConnection();

To set the connection string for the connection you need to have a database link. You can either find this while connection database in visual studio or

you can write this connection string.

this is the connection string for access database 2007.

String Connection=(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Files\database1.accdb;”)

For all the connection strings you can visit my previous post on this blog.
www.farazahraf.wordpress.com

STEP3:

To connect OleDb connection with the connection string you can use this line of code.

conn.ConnectionString=connection;

where conn is the object name OLeDb connection class created above you can use any name.

while connection is string name you can also use any name for this word.

STEP4:

Now coming to our main topic of deleting values in the database.
so for this you will have to open connection with database by using this command.

conn.Open();

this will open connection with the database.

now we will write the query for deletion of values in the database.

OLeDBCommand cmd =new OLeDbCommand(“DELETE FROM TABLE1 WHERE ID=001″,conn) ;
cmd.ExecuteNonQuery();

conn.Close();

The (command) class is used here to insert the commands in the data base which will be executed. Query contains a table name which is  “TABLE1″.

“WHERE” clause is used to specify values from which column and which row should be deleted.

Here Column name is “ID” you can specify any column but mostly column with the primary key is used to delete the values from the table.

The id from the values will be deleted is “001” you can use any key to delete.

*Remember if you will not specify the where clause then every row will be deleted from the table so be aware before using the delete query because this is a very power full query and is hard to recover data back.

Remember to always close the connection with con.Close(); after you are done with the deletion or any other command.

I hope you would have liked this.
For any queries regarding database please comment . I will be pleased to answer your queries …

How to insert values in data base using sql queries…

Now many folks out there are worry that how to insert values in database using SQL queries in C#. Inserting values  while working on oracle would be easy with just a single query but for some people it gets difficult while inserting values from the code in C#.

Now its not too hard as it sounds in fact its very easy. We will first have to add the required namespace for the database we are using and then we start with coding the insert query.

So to add namespace first thing you should know is that your data is on which database platform.

Let say you are working on simple Microsoft platform and your data is on access database then you would be using the OLE DB (Object Linking and Embedding, Database) this is an API designed by Microsoft to work on access database.

STEP 1:

So you will start with

Using System;

Using System.Data.OLeDb;

STEP 2:

after adding the namespace now its time for coding in main.

First you  have to create database connection so it will be

OLeDbConnection conn=new OLeDbConnection();

To set the connection string for the connection you need to have a database link. You can either find this while connection database in visual studio or

you can write this connection string.

this is the connection string for access database 2007.

String Connection=(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Files\database1.accdb;”)

For all the connection strings you can visit my previous post on this blog.
www.farazahraf.wordpress.com

STEP3:

To connect OleDb connection with the connection string you can use this line of code.

conn.ConnectionString=connection;

where conn is the object name OLeDb connection class created above you can use any name.

while connection is string name you can also use any name for this word.

STEP4:

Now coming to our main topic of inserting values in the database.
so for this you will have to open connection with database by using this command.

conn.Open();

this will open connection with the database.

now we will write the query for database insertion.

OLeDBCommand cmd =new OLeDbCommand(“INSERT INTO TABLE1 (SID,SNAME,GPA) Values (‘”Txt_Id”‘,'”Faraz”‘,”Numaric_GPA”),conn);
cmd.ExecuteNonQuery();

conn.Close();

The command class is used here to insert the command in the data base which will be executed. Query contains a table name which is  “TABLE1” 3 columns name where we need to insert data and values contains the actual data from the program to be inserted into database. “Txt_Id” is the name of the text box that contains the id. you can use any control name here or you can also add values of your own as i have inserted my name directly instead of the text box control name. Now apostrophe  around the first 2 values would be bit surprising but i have used apostrophe to show that these values are string in the database and the GPA is not string so apostrophe is not used with it.

Remember to always close the connection with con.Close(); after you are done with the insertion or any other command.

I hope you would have liked this.
For any queries regarding database please comment . I will be pleased to answer your queries …