SQL Tutorials

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered

2012-01-30T07:58:00.001-08:00

If you run below SQL statement
---------------------------------------------------------
Select * into DBTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\myExcelFile.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

INSERT INTO DBTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\myExcelFile.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
--------------------------------------------------------

and you get an error message like below
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered

Solution
you need to download
"Data Connectivity Components for 2007 office system Driver".
after you download, please restart your server.

you can click link here to download from microsoft site.

you will able to saw your microsoft excel driver 12.0 in your Data Sources(ODBC) in control panel

DTS not support in SQL Server 2008 R2

2012-01-11T01:54:00.000-08:00

Limited SQL Server 2000 DTS Functionality on 64-bit Operating Systems

SQL Server 2008 does not include support for DTS in the following circumstances:

There is no 64-bit design-time or run-time support for DTS packages. On a 64-bit computer, DTS packages, and Integration Services packages that run DTS packages, can run only in 32-bit mode. For more information, see How to: Install Support for Data Transformation Services Packages.
There is also no 32-bit design-time or run-time support for DTS packages on Itanium-based operating systems. Therefore, you cannot create, view, modify, or run DTS packages on Itanium-based operating systems.

Supported SQL Server 2000 DTS Functionality

SQL Server 2008 includes support for the following DTS features:

The DTS runtime, the object model that it exposes, and the dtsrun.exe command prompt utility.
The Execute DTS 2000 Package task, for executing DTS packages within Integration Services packages.
The ActiveX Script task, for backward compatibility only.
The DTS Package Migration Wizard, for migrating DTS packages to the Integration Services package format.
The Upgrade Advisor rules for DTS packages, for identifying potential issues that may be encountered when migrating packages.

more details on http://msdn.microsoft.com/en-us/library/bb500440.aspx

Import Excel Data into Mssql using SQL Statement

2011-12-23T00:26:00.000-08:00

you need to run one line per line, if you run all SQL together.
you will get below error message:
Incorrect syntax near 'sp_configure'

Please Follow below Step
Step 1: sp_configure 'show advanced options', 1
Output Message: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Step 2: reconfigure
Output Message: Command(s) completed successfully.

Step 3: sp_configure 'Ad Hoc Distributed Queries', 1
Output Message: Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

Step 4: reconfigure
Output Message: Command(s) completed successfully.

Step 5: Run your SQL to import Excel Files

Insert Excel Data into New Table (Create New Table)

INSERT INTO myTableName

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')

Insert Excel Data into Existing Table

SELECT * INTO myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')

SQL Code to import Excel Data into New Table in Database

sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

SELECT * INTO myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')

SQL Code to import Excel Data into Existing Table in Database

sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

SELECT * INTO myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')

Database owner is already a user in the database

2011-09-30T04:44:00.000-07:00

Error : Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database

If you are getting above error message while changing the database owner. ‘DBuser’ cannot become the owner of the current database if it already has access/dbo access to the database through an existing alias or user security account within the database. To avoid this, drop the alias or ‘user’ within the current database first. here is the solution.

Use Database_Name;
sp_dropuser ‘DBUser’
sp_changedbowner ‘DBUser’

Back Up the Transaction Log When the Database Is Damaged

2011-09-30T04:36:00.000-07:00

To create a backup of the currently active transaction log

Execute the BACKUP LOG statement to back up the currently active transaction log, specifying:
- The name of the database to which the transaction log to back up belongs.
- The backup device where the transaction log backup will be written.
- The NO_TRUNCATE clause.
  This clause allows the active part of the transaction log to be backed up even if the database is inaccessible, provided that the transaction log file is accessible and undamaged.
Optionally, specify:
- The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.
- The SKIP and INIT clauses to overwrite the backup media, even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.
- The FORMAT clause, when you are using media for the first time, to initialize the backup media and rewrite any existing media header.
  The INIT clause is not required if the FORMAT clause is specified.

This example backs up the currently active transaction log for the MyAdvWorks_FullRM database even though MyAdvWorks_FullRM has been damaged and is inaccessible. However, the transaction log is undamaged and accessible:

BACKUP LOG DBNAME

TO MyAdvWorks_FullRM_log1

WITH NO_TRUNCATE

10 reasons why go for SQL Server 2008

2011-09-29T02:35:00.000-07:00

10. Plug-in model for SSMS. SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind. Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom.

9. Inline variable assignment. I often wondered why, as a language, SQL languishes behind the times. I mean, it has barely any modern syntactic sugar. Well, in this version, they are at least scratching the the tip of the iceberg.

Instead of:

DECLARE @myVar int 
SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Sweet.

8. C like math syntax. SET @i += 5. Enough said. They finally let a C# developer on the SQL team.

7. Auditing. It's a 10 dollar word for storing changes to your data for later review, debugging or in response to regulatory laws. It's a thankless and a mundane task and no one is ever excited by the prospect of writing triggers to handle it. SQL Server 2008 introduces automatic auditing, so we can now check one thing off our to do list.

6. Compression. You may think that this feature is a waste of time, but it's not what it sounds like. The release will offer row-level and page-level compression. The compression mostly takes place on the metadata. For instance, page compression will store common data for affected rows in a single place.

The metadata storage for variable length fields is going to be completely crazy: they are pushing things into bits (instead of bytes). For instance, length of the varchar will be stored in 3 bits.

Anyway, I don't really care about space savings - storage is cheap. What I do care about is that the feature promised (key word here "promises") to reduce I/O and RAM utilization, while increasing CPU utilization. Every single performance problem I ever dealt with had to do with I/O overloading. Will see how this plays out. I am skeptical until I see some real world production benchmarks.

5. Filtered Indexes. This is another feature that sounds great - will have to see how it plays out. Anyway, it allows you to create an index while specifying what rows are not to be in the index. For example, index all rows where Status != null. Theoretically, it'll get rid of all the dead weight in the index, allowing for faster queries.

4. Resource governor. All I can say is FINALLY. Sybase has had it since version 12 (that's last millennium, people). Basically it allows the DBA to specify how much resources (e.g. CPU/RAM) each user is entitled to. At the very least, it'll prevent people, with sparse SQL knowledge from shooting off a query with a Cartesian product and bringing down the box.

Actually Sybase is still ahead of MS on this feature. Its ASE server allows you to prioritize one user over another - a feature that I found immensely useful.

3. Plan freezing. This is a solution to my personal pet peeve. Sometimes SQL Server decides to change its plan on you (in response to data changes, etc...). If you've achieved your optimal query plan, now you can stick with it. Yeah, I know, hints are evil, but there are situations when you want to take a hammer to SQL Server - well, this is the chill pill.

2. Processing of delimited strings. This is awesome and I could have used this feature...well, always. Currently, we pass in delimited strings in the following manner:

exec sp_MySproc 'murphy,35;galen,31;samuels,27;colton,42'

Then the stored proc needs to parse the string into a usable form - a mindless task.

In 2008, Microsoft introduced Table Value Parameters (TVP).

CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int) 
DECLARE @myPeeps PeepsType 
INSERT @myPeeps SELECT 'murphy', 35 
INSERT @myPeeps SELECT 'galen', 31 
INSERT @myPeeps SELECT 'samuels', 27 
INSERT @myPeeps SELECT 'colton', 42

exec sp_MySproc2 @myPeeps

And the sproc would look like this:

CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY) ...

The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc. Say goodbye to all those string parsing routines.

1. Intellisense in the SQL Server Management Studio (SSMS). This has been previously possible in SQL Server 2000 and 2005 with use of 3rd party add-ins like SQL Prompt ($195). But these tools are a horrible hack at best (e.g. they hook into the editor window and try to interpret what the application is doing).

Built-in intellisense is huge - it means new people can easily learn the database schema as they go.

LINQ - Aggregate Operators

2011-08-03T09:37:00.000-07:00

Below code is to show the unique value:

Public Sub LinqSample1()
    Dim arrDistinct() = {1, 1, 1, 2, 2, 3, 4, 4, 5, 5}

    Dim strUniqueFactors = arrDistinct.Distinct().Count()

    Console.WriteLine(strUniqueFactors & " is unique value.")
End Sub

Result:
3 is unique value.

mysql - sql injection prevention

2011-06-20T08:15:00.000-07:00

If you have ever taken raw user input and inserted it into a MySQL database there's a chance that you have left yourself wide open for a security issue known as SQL Injection.

SQL injection is someone inserting a SQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

for PHP users, All you need to do is use the function mysql_real_escape_string.

echo "Escaped Evil Injection:";
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";
$name_evil = mysql_real_escape_string($name_evil);
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";

Result
Escaped Bad Injection:
SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \''

Microsoft Distributed Transaction Coordinator May Stop Responding in a Low Memory Situation

2011-05-10T08:30:00.000-07:00

When a server is in low memory situation, the Microsoft Distributed Transaction Coordinator (MS DTC) process (Msdtc.exe) may stop responding (crash).

When MS DTC tries to manage new transactions, the attempt fails because of a lack of resources.

Workaround
To work around this problem, verify that the memory configuration of the computer is correct, and then correct the memory configuration if it is not.

Microsoft Fix
To resolve this problem, obtain the latest service pack for Windows 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

260910 How to Obtain the Latest Windows 2000 Service Pack

Pro LINQ - Language Integrated Query in C# - 2010

2011-05-03T01:08:00.000-07:00

Pro LINQ - Language Integrated Query in C# - 2010

I found LINQ tutorials for C#, here i just share to who is interest

Download It Now

RESTORE DATABASE using command

2010-06-16T08:36:00.000-07:00

I found this error message when i restore the database using the GUI and it appear this error message "Error 3154: The backup set holds a backup of a database other than the existing database"
It because of trying to restore database on an existing active database.

Solution:
RESTORE DATABASE DatabaseName
FROM DISK = 'C:\myDatabase.bak'
WITH REPLACE

Use WITH REPLACE when using RESTORE command when u saw above error message "Error 3154: The backup set holds a backup of a database other than the existing database"

Tested in MSSQL 2005

SQL @@ROWCOUNT

2010-06-04T19:35:00.000-07:00

Returns the number of rows affected by the last statement. It will let you to do a checking on the record you updated.

If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

Example
USE DB2008;
GO
UPDATE User
SET JobTitle = 'Manager'
WHERE UserID = 'u10021'
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
GO

SQL Injection

2009-11-17T05:33:00.000-08:00

SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.
The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

Example
UserID = Request.form ("userid");
var sql = "select * from UserTable where ID= '" + UserID + "'";

The user is prompted to enter the User ID. If she or he enters "jack", the query assembled by the script looks similar to the following:

select * from UserTable where ID = 'jack'

However, assume that the user enters the following:

jack; drop table UserTable--

In this case, the following query is assembled by the script:

select * from UserTable where ID = 'jack''; drop table UserTable--'

The semicolon (;) denotes the end of one query and the start of another. The double hyphen (--) indicates that the rest of the current line is a comment and should be ignored. If the modified code is syntactically correct, it will be executed by the server. When SQL Server processes this statement, SQL Server will first select all records in UserTable where ID is jack. Then, SQL Server will drop UserTable.

Input character	Meaning in Transact-SQL
;	Query delimiter.
'	Character data string delimiter.
--	Comment delimiter.
/* ... */	Comment delimiters. Text between /* and */ is not evaluated by the server.
xp_	Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.

Select Statement for Different Database

2009-09-12T06:50:00.000-07:00

Database SQL Syntax
- Different databases using different sql statement

DB2
select * from table fetch first 10 rows only

Informix
select first 10 * from table

Microsoft SQL Server and Access
select top 10 * from table

MySQL and PostgreSQL
select * from table limit 10

Oracle 8i
select * from (select * from table) where rownum <= 10

Linked Servers

2009-08-10T07:18:00.000-07:00

A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:

Remote server access.
The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
The ability to address diverse data sources similarly.

When setting up a linked server, register the connection information and data source information with SQL Server. After registration is success, that data source can always be referred to with a single logical name.

You can manage a linked server definition with stored procedures or through SQL Server Enterprise Manager:

With stored procedures:

- Create a linked server definition using sp_addlinkedserver. To view information about the linked servers defined in a given instance of SQL Server, use sp_linkedservers. For more information, see sp_addlinkedserver and sp_linkedservers.

- Delete a linked server definition using sp_dropserver. You can also use this stored procedure to remove a remote server. For more information, see sp_dropserver.

With SQL Server Enterprise Manager:

- Create a linked server definition using the SQL Server Enterprise Manager console tree and the Linked Servers node (under the Security folder). Define the name, provider properties, server options, and security options for the linked server. For more information about the various ways a linked server can be set up for different OLE DB data sources and the parameter values to be used, see sp_addlinkedserver.

- Edit a linked server definition by right-clicking the linked server and clicking Properties.

- Delete a linked server definition by right-clicking the linked server and clicking Delete.

How Long Stored Procedure stay in Sql Server 2000 cache?

2009-08-08T05:59:00.001-07:00

SQL Server 2000

once the execution plan is generated for a Stored Procedure, it stays in the procedure cache. Lazy writer only keep looking and throwing out unused plans out of the cache "only when space is needed in cache".

Below are some documented and undocumented DBCC commands available in SQL Server 2000 to deal and find more information about SQL Server cache.

To Monitor the cahce:

DBCC SQLPERF (LRUSTATS)
DBCC CACHESTATS
DBCC MEMORYSTATUS
DBCC PROCCACHE
To clean the cache:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

You can read more from SQL 2000 Topic under "Lazy Writer", 'Freeing and Writing Buffer Pages' at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_8unn.asp

awe enabled Option - MsSql 2005

2009-05-11T06:43:00.000-07:00

In Windows Server 2003, you able change the Address Windowing Extensions (AWE) API to provide access to physical memory in excess of the limits set on configured virtual memory. The specific amount of memory you can use depends on hardware configuration and operating system support.

Windows Server 2003, Standard Edition supports physical memory up to 4 gigabytes (GB).
Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.

Extra Note

You are not running Express or Workgroup version of SQL Server. Please check here for further details about limitations in different versions of SQL Server.
you have enabled awe enabled option and set max server memory to the maximum memory you can allocate to SQL Server. This is applicatiable for 32 but versions of OS and not required in 64 bit version of Windows servers. Please check here for further details and how to configure the memoryfor SQL Server

COMPUTE (Transact-SQL) - SQL Server 2005

2009-04-30T21:31:00.000-07:00

Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set.

USE Database;
GO
SELECT CustomerID, OrderDate, SubTotal, TotalDue
FROM Sales.SalesOrderHeader
WHERE ID = 1
ORDER BY OrderDate
COMPUTE SUM(SubTotal), SUM(TotalDue);

MERGE statement - SQL Server 2008 New Feature

2009-04-30T21:20:00.000-07:00

The idea behind the MERGE statement is that the developer can construct TSQL data-manipulation language (DML) statements in which INSERT, UPDATE, or DELETE can occur in the same statement, based on different search conditions. I think this idea is very cool. The ability to complete multiple statements within one statement could potentially lead to less coding and increased performance.
In addition to this statement, another great feature has been added to the INSERT statement. In SQL Server 2008, the developer can issue multiple rows to be inserted without using a SELECT statement as the INSERT statement source. Instead, the VALUE clause of the INSERT statement can be used to specify sets of values separated by parentheses and commas.

sp_helpfile

2009-01-16T08:20:00.000-08:00

sp_helpfile returns the physical names and attributes of the current database.

Attributes that returns are name, fileid, filename, filegroup, size, maxsize, growth and usage.

- it for ms Sql Server

Database Engine Stored Procedures

2009-01-16T08:15:00.000-08:00

sp_add_data_file_recover_suspect_db	sp_droptype
sp_add_log_file_recover_suspect_db	sp_executesql
sp_addextendedproc	sp_getapplock
sp_addextendedproperty	sp_getbindtoken
sp_addmessage	sp_help
sp_addtype	sp_helpconstraint
sp_addumpdevice	sp_helpdb
sp_altermessage	sp_helpdevice
sp_attach_db	sp_helpextendedproc
sp_attach_single_file_db	sp_helpfile
sp_autostats	sp_helpfilegroup
sp_bindefault	sp_helpindex
sp_bindrule	sp_helplanguage
sp_bindsession	sp_helpserver
sp_certify_removable	sp_helpsort
sp_configure	sp_helpstats
sp_control_plan_guide	sp_helptext
sp_create_plan_guide	sp_helptrigger
sp_create_plan_guide_from_handle	sp_indexoption
sp_create_removable	sp_invalidate_textptr
sp_createstats	sp_lock
sp_cycle_errorlog	sp_monitor
sp_datatype_info	sp_procoption
sp_dbcmptlevel	sp_recompile
sp_dbmmonitoraddmonitoring	sp_refreshview
sp_dbmmonitorchangealert	sp_releaseapplock
sp_dbmmonitorchangemonitoring	sp_rename
sp_dbmmonitordropalert	sp_renamedb
sp_dbmmonitordropmonitoring	sp_resetstatus
sp_dbmmonitorhelpalert	sp_serveroption
sp_dbmmonitorhelpmonitoring	sp_setnetname
sp_dbmmonitorresults	sp_settriggerorder
sp_dboption	sp_spaceused
sp_dbremove	sp_tableoption
sp_delete_backuphistory	sp_unbindefault
sp_depends	sp_unbindrule
sp_detach_db	sp_updateextendedproperty
sp_dropdevice	sp_updatestats
sp_dropextendedproc	sp_validname
sp_dropextendedproperty	sp_who
sp_dropmessage

Clear Transaction Log in SQL Server 2005 database

2008-12-10T07:44:00.000-08:00

Backup DB
Detach DB
Rename Log file
Attach DB
New log file will be recreated
Delete Renamed Log file.

Above just is 1 of the way to clear the transaction log file

SQL Combine 2 Different Table

2008-12-01T05:02:00.000-08:00

This SQL is use to combine 2 different table together for other purpose.
SELECT ISNULL(a.Col1,b.Cola) , ISNULL(a.Col2,b.Colb)
FROM Table1 a
FULL JOIN Table2 b
ON a.Col1 = b.Cola

Here is 2 Different Table,
Table Name : Salary

Name	Salary
emil	1000
rayden	2000

Table Name: Flight

FligtTicket	Price
Kuala Lumpur	265
Bangkok	878

Below Sql is use to combine above 2 Tables Become below 1 Table
SELECT ISNULL(a.Name,b.FlightTicket) AS Col1, ISNULL(a.Salary,b.Price) AS Col2 FROM Salary a
FULL JOIN Flight b ON a.Name = b.FlightTicket

Col1	Col2
emil	1000
rayden	2000
Kuala Lumpur	265
Bangkok	878

DBCC FREEPROCCACHE

2008-11-27T07:49:00.000-08:00

DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache can causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.
DBCC FREEPROCCACHE WITH NO_INFOMSGS;

DBCC SQLPERF(logspace)

2008-11-27T07:42:00.000-08:00

DBCC SQLPERF(logspace) is an absolutely functional command if you are only interested in consumption of your database log files. It provides the cumulative size for each log file for each database on the SQL Server instance as well as the amount of space consumed (as a percentage of total log file size). A drawback is the fact that the results are an aggregate for the database.

logspace can be 1 of the value below:
UMSStats - SQL thread management
WaitStats - resources, wait types
IOStats - outstanding reads & writes (note: always zeros)
RAStats - read ahead activity (note: always returns zeros)
Threads - I/O / CPU / memory usage per thread
SpinLockStats - statistics on spinlocks
UMSSpinStats - statistics on UMS
NetStats - ODS statistics
LRUStats - LRU-MRU chain statistics (Note: free page scan always zero)

DBCC SHOWFILESTATS WITH NO_INFOMSGS

2008-11-27T07:28:00.000-08:00

This DBCC command provides us the data files for the current database that you select. DBCC SHOWFILESTATS or DBCC SHOWFILESTATS WITH NO_INFOMSGS is the command to display the current database File Name, Database Name, Total Extend, Used Extent, Field ID and File Group.

Get the ID of a Newly Inserted Row Using SCOPE_IDENTITY()

2008-11-21T18:29:00.000-08:00

SCOPE_IDENTITY() command will give you the last identity value that was generated in the current scope.

@@Identity will return the last identity value that was generated in the current session but in any scope.

If you database got trigger to insert a new row, @@Identity will give you the id that generated by trigger instead of ID of the last row you inserted.

In order to get the ID of the row that you inserted, which is just what SCOPE_IDENTITY() will give you.

Below is the SQL to get the last inserted ID in the current scope that need to execute together with the commands.

string sqlString = "INSERT INTO Table(Col1, Col2) VALUES (@Col1, @Col2); SELECT SCOPE_IDENTITY () As NewID";
SqlCommand objCmd = new SqlCommand(sqlString, MyConnection);
objCmd.Connection.Open();
objCmd.Parameters.Add(new SqlParameter("@Col1", Col1Value));
objCmd.Parameters.Add(new SqlParameter("@Col2", Col2Value);

SqlDataReader dataReader = objCmd.ExecuteReader();

if (dataReader.HasRows)
{
dataReader.Read();
newRowID = Convert.ToInt32(dataReader["NewID"]);
}

dataReader.Close();

SQL Update Multiple Columns

2008-11-14T06:55:00.000-08:00

Below Sql use to update multiple column for a table in 1 shot

*****************

UPDATE Table1
SET (Col1, Col2, Col3, Col4) =
(SELECT Col1a, Col2a, Col3a, Col4a FROM Table2 WHERE Col5a=Table1.Col5)
WHERE Col5 IN (SELECT Col5a FROM Table2 )

*****************

Identity column not getting sequential values

2008-11-14T04:37:00.000-08:00

Identity values are not sequential. Even if you do not delete rows, you can end up with missing identity values. If a transaction inserts a new record to the table and ROLLS BACK, you can end up with a missing identity value.

Connect to Sql Server 2005 Express for Window SharePoint Service

2008-10-07T08:07:00.000-07:00

If you wanna to connect to the sql server 2005 express after u install window share point to your window server 2003 and when u try to connect to the ServerName\Microsoft##SSEE, it return you error.
Actualy you can't connect to WSS database remotely because network connectivity is disabled by default. ( or without SQL Server Agent Services not installed)

You can use below as ur server name like the image below, it will let you to connect to your share point database

\\.\pipe\mssql$microsoft##ssee\sql\query

The Hibernate Query Language - HQL

2008-08-21T05:42:00.000-07:00

NHibernate is equipped with an extremely powerful query language that looks very much like SQL. Hibernate Query Language (HQL) is fully object-oriented, understanding notions like inheritence, polymorphism and association.

The simplest possible Hibernate query is of the form:
- "From Employee" - (similar to SQL "Select * From Employeetbl"
* Employee is a mapping class
if you map the table Employeetbl to a mapping class, so you can use the HQL to select from the mapping file.

Example of Mapping Class for NHibernate
<class table="Employeetbl" name="Employee" lazy="false">
<id type="string" column="Employeeid" name="Employeeid">
<generator class="increment">
</id>
<property type="string" column="EmployeeName" name="EmployeeName"></property>
</class>

NHibernate

2008-08-09T00:33:00.000-07:00

NHibernate is a port of Hibernate Core for Java to the .NET Framework. It handles persisting plain .NET objects to and from an underlying relational database. Given an XML description of your entities and relationships, NHibernate automatically generates SQL for loading and storing the objects. Optionally, you can describe your mapping metadata with attributes in your source code.

NHibernate uses the following ways to retrieve objects from the database:

Hibernate Query Language (HQL)
Query By Criteria (QBC) and Query BY Example (QBE) using Criteria API
Native SQL queries

NHibernate can work on these Database

Microsoft SQL Server 2005/2000
Oracle
Microsoft Access
Firebird
PostgreSQL
DB2 UDB
MySQL
SQLite

NHibernate

SQL Server Express Edition Comparison Table

2008-07-26T10:25:00.001-07:00

Express Edition Products for SQL Server Compared
Feature	SQL Server 2005 Express Edition	SQL Server 2005 Express Edition with Advanced Services	SQL Server 2005 Express Edition Toolkit
Database Engine	x	x
Client Components	x	x	x
Full Text Search		x
Reporting Services		x
Management Studio Express		x	x
Business Intelligence Developer Studio			x

Comparation between the SQL Server Database Between:

1. SQL Server 2005 Express Edition

2. SQL Server 2005 Express Edition with Advanced Services

3. SQL Server 2005 Express Edition Toolkit

SQL Server 2005 Management Studio Express

2008-07-26T10:15:00.000-07:00

Free to download, free to redistribute, free to embed, and easy for new developers to use immediately, a simple but powerful development environment for building data-driven applications.

The advanced query optimization engine of SQL Server 2005 delivers fast performance with full-text search. Load databases easily through the XCopy feature. Native XML support ensures that your Web environment can easily interoperate with other applications through Web services.

SQL Server 2005 Reporting Services provides a complete, server-based platform for creating, managing, and delivering traditional and interactive reports. Easily create sophisticated reports with tables, charts, and graphics, and then embed them in applications.

Configure SQL Server Express to dynamically tune database parameters to respond to changing usage characteristics. SQL Server Express delivers user updates directly using Microsoft Update, easily plugs in to most enterprise management environments, and supports either silent installs or GUI-based setup and installations.

SQL Server Express Edition

List of DBCC in SQL SERVER 2005

2008-07-25T17:47:00.000-07:00

DBCC stands for database consistency checker. We use these commands to check the consistency of the databases.
Example, maintenance, validation task and status checks.

DBCC CHECKALLOC - Check consistency of disk allocation.
DBCC CHECKCATALOG - Check catalog consistency
DBCC CHECKCONSTRAINTS - Check integrity of table constraints.
DBCC CHECKDB - Check allocation, and integrity of all objects.
DBCC CHECKFILEGROUP - Check all tables and indexed views in a filegroup.
DBCC CHECKIDENT - Check identity value for a table.
DBCC CHECKTABLE - Check integrity of a table or indexed view.
DBCC CLEANTABLE - Reclaim space from dropped variable-length columns.
DBCC dllname - Unload a DLL from memory.
DBCC DROPCLEANBUFFERS - Remove all clean buffers from the buffer pool.
DBCC HELP - Help for DBCC commands.
DBCC INPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC OPENTRAN - Display information about recent transactions.
DBCC OUTPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC PROCCACHE - Display information about the procedure cache
DBCC SHOW_STATISTICS - Display the current distribution statistics
DBCC SHRINKDATABASE - Shrink the size of the database data and log files.
DBCC SHRINKFILE - Shrink or empty a database data or log file.
DBCC SQLPERF - Display transaction-log space statistics. Reset wait and latch statistics.
DBCC TRACE - Enable or Disable trace flags
DBCC UPDATEUSAGE - Report and correct page and row count inaccuracies in catalog views
DBCC USEROPTIONS - Return the SET options currently active
DBCC deprecated commands

New Feature in MySQL 6.0

2008-07-24T08:07:00.000-07:00

1. The new Falcon transactional storage engine.

2. Support for additional Unicode character sets: utf16, utf32, and 4-byte utf8. These character sets support supplementary Unicode characters; that is, characters outside the Basic Multilingual Plane (BMP).

3. BACKUP DATABASE and RESTORE statements for backup and restore operations.

4. Optimizer enhancements for faster subqueries and joins, including batched index access of table rows for sequences of disjoint ranges by the MyISAM and InnoDB storage engines.

5.The syntax for the LOCK TABLES statement has been extended to support transactional table locks that do not commit transactions automatically. Following LOCK TABLES ... IN SHARE MODE or LOCK TABLES ... IN EXCLUSIVE MODE, you can access tables not mentioned in the LOCK TABLES statement. You can also issue LOCK TABLES statements that acquire transactional locks many times in succession, adding additional tables to the locked set, and without unlocking any tables that were locked previously. When using LOCK TABLES with IN SHARE MODE or IN EXCLUSIVE MODE, tables are not unlocked until the transaction ends.
Transactional locks acquired with LOCK TABLES are released when the transaction ends, either explicitly with COMMIT or ROLLBACK, or implicitly due to a statement that causes implicit commit or because the connection ends.

6. Support for extended comments for tables, columns, and indexes.

More details, visit here http://dev.mysql.com/doc/refman/6.0/en/mysql-nutshell.html

SQLyog

2008-07-24T07:55:00.000-07:00

SQLyog GUI is the most powerful MySQL manager and admin tool, combining the features of MySQL Query Browser, Administrator, phpMyAdmin and various other MySQL Front Ends and MySQL clients in a single intuitive interface.

http://www.webyog.com/faq/index.html

LINQ to SQL in C#

2008-07-09T08:19:00.000-07:00

This article provides an introduction to employing LINQ to SQL within a Windows Forms application.
Article will address the incorporation of LINQ to SQL into a win forms project, how to use LINQ to SQL to select, insert, update, and delete data, and how to use LINQ to SQL to execute stored procedures.

The article shows some simple examples of LINQ to SQL; from it you can see how easy it is to query against single and related tables and to write filtered queries, execute stored procedures, perform aggregation, and how to insert, update, and delete records from the database.

DataClasses1DataContext dc = new DataClasses1DataContext();
var q =
from a in dc.GetTable()
where a.CustomerID.StartsWith("A")
orderby a.OrderDate ascending
select a;
dataGridView1.DataSource = q;

click here for full article

LINQ tutorials convert a Linq Query resultset to DataTable object

2008-07-09T08:15:00.000-07:00

This LINQ tutorials convert a Linq Query resultset to DataTable object.
The tutorials attached with the source code

full article, click here

LINQ Begginer Tutorials

2008-07-09T08:10:00.000-07:00

In this tutorial, we looked at the current state of today's data access story. Then we looked at how LINQ and the new language features in C# 3.0 solve these issues by providing us with a consistent set of Standard Query Operators that we can use to query any collection that implements IEnumerable. In this installment, we only focused on in-memory collections of data in order to avoid the confusion that most people have when mixing LINQ with DLinq and XLinq, but rest assured these will be covered in the future installments of this article. Furthermore, because LINQ is just a set of methods that adhere to the naming conventions for the Standard Query Operators, anybody can implement their own LINQ based collections for accessing any other type of data.

More Details, please visit the link

Relational database management system (RDBMS)

2008-06-26T07:58:00.000-07:00

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular commercial and open source databases currently in use are based on the relational model.

A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.

Not be enough virtual memory - SQL Server

2008-06-23T05:50:00.000-07:00

For each updated database, SQL Server allocates at least one 64-KB block for use in formatting log records before they are written to disk. This allocation occurs when the first log record is generated for the database, such as during an INSERT, UPDATE, or DELETE statement. Depending on the activity and the size of the generated log records, subsequent modifications might trigger the allocation of additional 64-KB allocations. SQL Server 7.0 will allocate no more than three 54-KB blocks. In SQL Server 2000, the upper number of allocations for each database is a function of the number of processors that SQL Server is configured to use.

Solution
Use the -g startup parameter to leave additional, unreserved virtual memory available for these database allocations. The -g parameter is documented in the Readme.txt of the SQL Server 7.0 service pack, and in SQL Server 2000 Books Online. The "More Information" section in this article includes the settings that Microsoft recommends you use to determine the appropriate value for this setting.

Sql Server 2005 - Twelve Tips For Optimizing Query Performance

2008-06-23T05:33:00.000-07:00

1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level

Original Article

LINQ - .NET Language Integrated Query

2008-06-10T05:43:00.000-07:00

LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it.

LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model.

Developers can use LINQ with any data source. They can express efficient query behavior in their programming language of choice, optionally transform/shape data query results into whatever format they want, and then easily manipulate the results. LINQ-enabled languages can provide full type-safety and compile-time checking of query expressions, and development tools can provide full intellisense, debugging, and rich refactoring support when writing LINQ code.

LINQ supports a very rich extensibility model that facilitates the creation of very efficient domain-specific operators for data sources. The "Orcas" version of the .NET Framework ships with built-in libraries that enable LINQ support against Objects, XML, and Databases.

For More details about the LINQ :
Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control
Part 6: Retrieving Data Using Stored Procedures
Part 7: Updating our Database using Stored Procedures
Part 8: Executing Custom SQL Expressions
Part 9: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the control)

SQL IDENTITY_INSERT

2008-04-27T21:54:00.000-07:00

SQL IDENTITY_INSERT is allow values to be inserted into the identity column of a table.
Identity column creates a numeric sequence value for you automatic.

It only alow one table in a session can have the IDENTITY_INSERT property set to ON.
If you need to change the table, you need to set the existing table IDENTITY_INSERT back to OFF.

SET IDENTITY_INSERT TableName OFF

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

SET IDENTITY_INSERT TableName ON

SQL Get Rows Number

2008-04-15T06:06:00.000-07:00

Below SQL Statement return rows number for each row in a partition.

select R1.CategoryName,
rank = (select count(distinct R2.CategoryName) from categories R2 where R1.CategoryName <= R2.CategoryName) from categories R1 Order by Rank

Below is the result after use the row number sql statement:

DBCC DBREINDEX

2008-03-28T21:46:00.000-07:00

When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can cause SQL Server to perform unnecessary data reads, so a queries performance against a heavy fragmented table can be very poor.

Rebuilding an index is a more efficient way to reduce fragmentation in comparison with dropping and re-creating an index, this is because rebuilding an index is done by one statement.

The DBCC DBREINDEX statement cannot automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. You can write your own script to rebuild all the indexes on all the tables in a database

TableName - Is the name of the table in your DataBase.
Index_name - Is the name of the index to rebuild. (refer to image below)
Fillfactor - Is the percentage of space on each index page to be used for storing data when the index is created or rebuilt. Default is 100.

Syntax
DBCC DBREINDEX ('TableName','Index_Name',fillfactor)

Example
DBCC DBREINDEX ('Categories')
DBCC DBREINDEX ('Categories','Categoryname',80)

- It Can improve your database perfomance.

WITH RECOMPILE - Re Compile Execution Query Plan

2008-03-19T18:59:00.000-07:00

WITH RECOMPILE is specified SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Ideally, you should not use this option because by using this option, you lose most of the advantages you get by substituting SQL queries with the stored procedures. However, you can use this option during early development stages of the project where database structure is still evolving and stored procedures should be recompiled as a result of these changes.

Below is the simple example for using With Recompile in creating STORE PROCEDURE
CREATE PROCEDURE sp_MyTable WITH RECOMPILE
AS
Select Column1, column2 from Table
GO

Except the above method, you also can using with Recompile when u Execute the Store procedure in Query Analyzer.

Below is the example for using With Recompile in Query analyzer
EXEC sp_MyTable1 'Parameter1', 'Parameter2' WITH RECOMPILE

This tells SQL Server to throw away the existing query plan or do not cache the previous execution query plan and build another one query plan but only this once.

sp_changedbowner - SQL Change DB Owner

2008-02-28T23:03:00.000-08:00

- Changes the owner of the current database.

After sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database.

The owner of the master, model, or tempdb system databases cannot be changed.
To display a list of the valid login values, execute the sp_helplogins stored procedure.
Executing sp_changedbowner with only the login parameter changes database ownership to login and maps the aliases of users who were previously aliased to dbo to the new database owner.

Only members of the sysadmin fixed server role can execute sp_changedbowner.

Example
This example makes the user Emil the owner of the current database and maps
existing aliases to the old database owner to Emil.

EXEC sp_changedbowner 'Emil'

Overview of DTS Package

2008-02-27T17:28:00.000-08:00

DTS Introduction

Many organizations need to centralize data to improve corporate decision-making. However, their data may be stored in a variety of formats and in different locations. Data Transformation Services (DTS) addresses this vital business need by providing a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations supported by DTS connectivity. By using DTS tools to graphically build DTS packages or by programming a package with the DTS object model, you can create custom data movement solutions tailored to the specialized business needs of your organization.

DTS Basics

Data Transformation Services (DTS) provides a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You create a DTS solution as one or more packages. Each package may contain an organized set of tasks that define work to be performed, transformations on data and objects, workflow constraints that define task execution, and connections to data sources and destinations. DTS packages also provide services, such as logging package execution details, controlling transactions, and handling global variables.

DTS supplies a number of tasks that are part of the DTS object model that can be accessed graphically, through DTS Designer, or programmatically. These tasks, which can be configured individually, cover a wide variety of data copying, data transformation, and notification situations. For example:

Importing and exporting data
DTS can import data from a text file or an OLE DB data source (for example, a Microsoft Access 2000 database) into SQL Server. Alternatively, data can be exported from SQL Server to an OLE DB data destination (for example, a Microsoft Excel 2000 spreadsheet). DTS also allows high-speed data loading from text files into SQL Server tables.
Transforming data
DTS Designer includes a Transform Data task that allows you to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. DTS Designer also includes a Data Driven Query task that allows you to map data to parameterized queries.
Copying database objects
With DTS, you can transfer indexes, views, logins, stored procedures, triggers, rules, defaults, constraints, and user-defined data types in addition to the data. In addition, you can generate the scripts to copy the database objects.
Sending and receiving messages to and from other users and packages
DTS includes a Send Mail task that allows you to send an e-mail if a package step succeeds or fails. DTS also includes an Execute Package task that allows one package to run another as a package step, and a Message Queue task that allows you to use Message Queuing to send and receive messages between packages.
Executing a set of Transact-SQL statements or Microsoft ActiveX scripts against a data source
The Execute SQL and ActiveX Script tasks allow you to write your own SQL statements and scripting code and execute them as a step in a package workflow.

SQL Server 2008 New DATETIME DataTypes

2008-02-21T05:51:00.000-08:00

The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are DATE, TIME, DATETIMEOFFSET and DATETIME2. IN addition to these newly introduced data types, there are new DATETIME functions all well.

TIME Datatype
DECLARE @dt as DATE
SET @dt = getdate()
SELECT @dt

23:48:04.0570000

The range for the DATE datatype is from 00:00:00.0000000 through 23:59:59.9999999..

DATETIME2 Data Type
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
SELECT @dt7
2007-10-28 22:11:19.7030000

for above example, (7) is the Fraction

Fraction Output
0 2007-10-28 22:11:20
1 2007-10-28 22:11:19.7
2 2007-10-28 22:11:19.70
3 2007-10-28 22:11:19.703
4 2007-10-28 22:11:19.7030
5 2007-10-28 22:11:19.70300
6 2007-10-28 22:11:19.703000
7 2007-10-28 22:11:19.7030000

Maps an existing database user to a SQL Server login using SP_CHANGE_USERS_LOGIN

2008-02-04T20:45:00.000-08:00

Automatically mapping a user to a login, creating a new login if it is required
The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password 123456 if the login Emil does not exist.

EXEC SP_Change_Users_Login 'Auto_Fix','Emil',Null,'123456'

- it is useful when u restore a existing DB to new Server. After u restore the DB, user in DB cannot link to SQL Server Login. So, you need to use the sql command instead of SQL Server GUI to update user Access Right.

AUTO_FIX
Auto_FixLinks a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.

extra
Use sp_change_users_login to link a database user in the current database with a SQL Server login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing user permissions. The new login cannot be sa, and the user cannot be dbo, guest, or an INFORMATION_SCHEMA user.
sp_change_users_login cannot be executed within a user-defined transaction

Identify your SQL Server version and edition

2008-02-04T20:37:00.000-08:00

This article show you your current Microsoft SQL Server version number and the corresponding product or service pack (SP) level. It also describes how to identify the specific edition Microsoft SQL Server.

SELECT @@VERSION
Result - Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Result - 8.00.2039 , SP4 , Standard Edition

SQL SET ROWCOUNT

2007-09-08T07:57:00.000-07:00

SQL SET ROWCOUNT statement gives administrators some control over the execution of "runaway" queries issued by naive users of SQL. This statement stops the execution of a query as soon as a specified number of rows has been retrieved. In this way, system resources are not wasted, but users can see at least a partial result set. An informational message is displayed after the result set, indicating that SET ROWCOUNT is in effect.
If a fully executed query happens to return the exact number of rows specified by the ROWCOUNT value, the query-termination message is still displayed.

Example of SQL SET ROWCOUNT
SELECT * FROM EMPLOYEES
- It will return 10 rows of records.

After add SET ROWCOUNT
SET ROWCOUNT 5
SELECT * FROM EMPLOYEES
- It will return 5 rows of records instead of 10 row.
(Bacause you set the RowCount to 5)

SQL SET ROWCOUNT

SQL Split Function

2007-09-06T20:16:00.000-07:00

This SQL Split Function is use to SPLIT a sentences based on the Delimeter.
Delimeter is a string character used to identify substring limits.

Below is Split Function in SQL
DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String ='SQL,TUTORIALS'
SET @Delimiter = ','
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END

Result
- SQL
- TUTORIALS

* Copy blue color Sql split function with change @String variable to test the result in your query analyzer.

SQL Split Function

SQL Alternative Count

2007-07-08T05:14:00.000-07:00

Both SQL Query below returns same result but the speed of retrieving data are different.
Copy Below Both Sql Query to Query analyzer to test it. Customers is a Table Name.

1. SELECT rows FROM sysindexes WHERE id = OBJECT_ID('Customers') AND indid <>
You can use sysindexes system table to get number of ROWS. This column contains the total row count for each table in your database. So, you can use the following select statement instead of Below one:

2. SELECT Count(*) FROM Customers
This SQL query performs full table scan to get the row count.

So, you can improve the speed of the SQL query using the Above First SQL instead of Second SQL Query

SQL Alternative Count

SQL Server DataType

2007-06-22T08:18:00.000-07:00

All Sql DataType

bigint
Integer data from -2^63 through 2^63-1

binary
Fixed-length binary data with a maximum length of 8,000 bytes

bit
Integer data with either a 1 or 0 value

char
Fixed-length character data with a maximum length of 8,000 characters

datetime
Date and time data from January 1, 1753, through December 31, 9999,with an accuracy of 3.33 milliseconds

decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1

float
Floating precision number data from -1.79E + 308 through 1.79E + 308

image
Variable-length binary data with a maximum length of 2^31 - 1 bytes

int
Integer data from -2^31 through 2^31 - 1

money
Monetary data values from -2^63 through 2^63 - 1

nchar
Fixed-length Unicode data with a maximum length of 4,000 characters

ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 characters

nvarchar
Variable-length Unicode data with a maximum length of 4,000 characters

numeric
Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1

real
Floating precision number data from -3.40E + 38 through 3.40E + 38

smalldatetime
Date and time data from January 1, 1900, through June 6, 2079,with an accuracy of one minute

smallint
Integer data from -2^15 through 2^15 - 1

smallmoney
Monetary data values from -214,748.3648 through +214,748.3647

sysname

text
Variable-length data with a maximum length of 2^31 - 1 characters

timestamp

tinyint
Integer data from 0 through 255

varbinary
Variable-length binary data with a maximum length of 8,000 bytes

varchar
Variable-length data with a maximum of 8,000 characters

uniqueidentifier
A globally unique identifier

SQL SET ROWCOUNT
SQL Split Function
SQL Alternative Count
SQL Server DataType
SQL WHILE Statement
SQL CASE Function
SQL IF...ELSE Statement
SQL First and Last Day Of a Month
SQL Statement - get Total Working Day in a month
SQL EXISTS
SQL Subquery
SQL UNION ALL
SQL Union
SQL Mathematic Functions
SQL String Functions
SQL CAST and CONVERT
MySql Total Day of Month
SQL DATEDIFF Function
SQL Dateparts and Abbreviations
SQL DATEADD Function
SQL Statement Last Day of a month
SQL Aggregate Function - Syntax and Example
SQL DATENAME Function - Year, Quarter, Month, Day,...
SQL Inner Join, Left Join and Right Join
Get Number of Row in Select Statement
Select Statement for Get Number of Days in a Month...
SQL Statement Example for SELECT, UPDATE and DELET...
SQL Statement Example for UPDATE and DELETE
All Topic in Sql Tutorials and ProgrammingSchools
SQL Syntax - SELECT,UPDATE and DELETE
What is SQL?

SQL WHILE Statement

2007-06-22T06:34:00.000-07:00

SQL While sets a condition for the repeated execution of an SQL statement or statement block. The SQL statements are executed repeatedly as long as the specified condition is return TRUE. The WHILE loop can be controlled from inside the loop with the CONTINUE, BREAK and GOTO keywords.

BREAK statement will exit you from the currently processing WHILE loop.
GOTO statement will break out of all WHILE loops, no matter how many nested WHILE statements.
CONTINUE statement will skips executing the rest of the statements between the CONTINUE statement and the END statement of the current loop and starts executing at the first line of the current WHILE loop.

WHILE Syntax
WHILE Boolean_expression
{ Sql Statement Block }

Below is simple example of WHILE Statement
DECLARE @counter INT
SET @counter = 0
WHILE @counter <>
   BEGIN
      SET @counter = @counter + 1
      PRINT 'The counter : ' + CAST(@counter AS CHAR)
   END
Value :
The counter : 1
The counter : 2
The counter : 3
The counter : 4
The counter : 5

Below is example of WHILE Statement with CONTINUE and BREAK
- It show you the using of Continue and Break in WHILE Statement and the IF...ELSE Statement.
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter <>
   BEGIN
      SET @Counter = @Counter + 1
      IF @Counter <>
         PRINT 'The counter : ' + CAST(@Counter AS CHAR)
      ELSE IF @Counter > 3 AND @Counter <>
         BEGIN
            CONTINUE
            PRINT 'No Counter Here'
      END
      ELSE IF @Counter > 13 AND @Counter <>
         BREAK
      ELSE
         PRINT 'The counter : ' + CAST(@Counter AS CHAR)
   END
Value :
The counter : 1
The counter : 2
The counter : 3
The counter : 10
The counter : 11
The counter : 12
The counter : 13

Below is example of WHILE Statement with CONTINUE, BREAK and GOTO
- It show you the using of Continue, Break and Goto in WHILE Statement and the IF...ELSE Statement.
DECLARE @N1 INT
DECLARE @N2 INT
SET @N1 = 0
SET @N2 = 0
WHILE @N1 <>
   BEGIN
   SET @N1 = @N1 + 1
      WHILE @N2 <>
         BEGIN
         SET @N2 = @N2 + 1
         IF @N2 = 3 and @N1 = 1
            GOTO BREAK_OUT
         ELSE
            PRINT 'Value N1 is ' + CAST(@N1 AS CHAR(1)) + ' Value N2 is ' + CAST(@N2 AS CHAR(1)) END
         SET @N2 = 0
      END
   BREAK_OUT:
Value :
Value N1 is 1 Value N2 is 1
Value N1 is 1 Value N2 is 2

SQL CASE Function

2007-06-21T07:46:00.000-07:00

SQL Case evaluates a list of conditions and returns one possible result expressions.

CASE has two formats:
1. Simple CASE Function - Compares an expression to determine the result.
2. Searched CASE Function - Evaluates a set of Boolean expressions to determine the result.

CASE Syntax
1. Simple CASE function:
CASE input_expression
WHEN when_expression THEN Result
ELSE result_expression
END

2. Searched CASE function:
CASE
WHEN Boolean_expression THEN Result
ELSE result_expression
END

1. Simple CASE Function
Evaluates input_expression and find the match with when_expression. If found, it will return the Result and if not found, it will return the ELSE result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

DECLARE @Type varchar(20)
SET @Type = 'Programming'

SELECT
   CASE @Type
      WHEN 'Sql' THEN 'sqltutorials.blogspot.com'
      WHEN 'Programming' THEN 'programmingschools.blogspot.com'
      WHEN 'Travel' THEN 'travelyourself.blogspot.com'
      ELSE 'Not yet categorized'
      END
Value = programmingschools.blogspot.com

If SET @Type = 'Picture', then Return value = Not yet categorized

2.Searched CASE Function
Evaluates Boolean_expression for each WHEN clause and returns result_expression of the first Boolean_expression that evaluates to TRUE.
If no Boolean_expression evaluates to TRUE, SQL Server returns the ELSE result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

DECLARE @Price integer
SET @Price = (20-9)

SELECT
   CASE
      WHEN @Price IS NULL THEN 'Not yet priced'
      WHEN @Price < color="#ff0000">THEN 'Very Reasonable Price'
      WHEN @Price >= 10 AND @Price < color="#ff0000">THEN 'Reasonable Price'
      ELSE 'Expensive book!'
   END
Value = Reasonable Price

If SET @Price = (30-1), then return Value = Expensive book!

SQL IF...ELSE Statement

2007-06-21T06:46:00.000-07:00

SQL IF...ELSE Statement used to test a condition. IF...ELSE Statement using in execution of a Transact-SQL statement (Store Procedure or T-SQL) and Trigger.
IF tests can be nested after another IF or following an ELSE. There is no limit to the number of nested levels.

IF condition is satisfied and the Boolean expression returns TRUE, it will executed IF Block Sql statement.
IF condtion is not satisfied and the Boolean expression returns FALSE, it will executed ELSE Block Sql Statement query.

Syntax for IF...ELSE
IF ( Boolean_expression )
BEGIN
Sql Statement Block
END
ELSE
BEGIN
Sql Statement Block
END

Below is simple example of IF...ELSE Statement With 1 IF...ELSE Block
For Boolean_expression part, you can replace with your condition to match with your Sql query.
It also can using Exists in the Condition to check the existence of a Sql Statement or Row.
IF (3>2)
   BEGIN
   SELECT 'Correct'
   END
ELSE
   BEGIN
   SELECT 'Wrong'
   END
Value = Correct

IF (3<2)
   BEGIN
   SELECT 'Correct'
   END
ELSE
   BEGIN
   SELECT 'Wrong'
   END
Value = Wrong

Below is another Example of More than 1 IF...ELSE Sql Statement Block
IF (10<2)
   SELECT 'Correct'
ELSE
   SELECT 'Wrong'
   IF ((10-2) > 5)
      SELECT 'More Than 5'
Value = Wrong
Value = More Than 5

If your IF...ELSE Block Sql Statement only have 1 Sql query, you no need to include the BEGIN...END. If your IF...ELSE Block Sql Statement have more than 1 Sql query, you need to include the BEGIN...END to let it executed all the Sql Query in your Sql Query Block.

SQL First and Last Day Of a Month

2007-06-17T07:07:00.000-07:00

In This SQL Tutorial show you retrieve First Day and Last Day for a month using SQL Query.
It can dynamically to retrieve First Day and Last day for a month using SQL Query.

Example SQL Statement or Query below show you how to retrieve First Day or Last Day for a month. To display the date in different format, click here.

DECLARE @Today DATETIME
SELECT @Today = '6/17/2007'

Get First Day of a Month Using SQL Query
SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-3,@Today))
Value = 2007-03-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-2,@Today))
Value = 2007-04-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-1,@Today))
Value = 2007-05-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))
Value = 2007-06-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,1,@Today))
Value = 2007-07-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,2,@Today))
Value = 2007-08-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,3,@Today))
Value = 2007-09-01 00:00:00.000

Get Last Day of a Month Using SQL Query
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,-2,@Today))
Value = 2007-03-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,-1,@Today))
Value = 2007-04-30 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,0,@Today))
Value = 2007-05-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,1,@Today))
Value = 2007-06-30 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,2,@Today))
Value = 2007-07-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,3,@Today))
Value = 2007-08-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,4,@Today))
Value = 2007-09-30 00:00:00.000

SQL Statement - get Total Working Day in a month

2007-06-15T03:15:00.000-07:00

SQL Statement below show you how to using SQL to get total working day in a month.
SQL Total working day in this SQL Statement is exclude saturday and sunday as working day.

Below is SQL Statement that get Total Working Day in a month.

DECLARE @my int
DECLARE @myDeduct int
DECLARE @day INT
DECLARE @mydate DATETIME

SET @mydate = getdate()

SET @myDeduct = 0
SET DateFirst 1 -- Set it monday=1 (value)

--Saturday and Sunday on the first and last day of a month will Deduct 1
IF (DATEPART(weekday,(DATEADD(dd,-(DAY(@mydate)-1),@mydate))) > 5)
SET @myDeduct = @myDeduct + 1

IF (DATEPART(weekday,(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))) > 5)
SET @myDeduct = @myDeduct + 1

SET @my = day(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))

select (((@my/7) * 5 + (@my%7)) - @myDeduct) as Working_Day_per_month

Return
Value = 21 day (for June 2007)
Value = 22 day (for July 2007)

SQL EXISTS

2007-06-10T00:44:00.000-07:00

Using a subquery to test for the existence of rows.
If it Exists Operators return a row, then the outer query proceeds. If not, the outer query does not execute or return any result or row.

Below is Example for SQL EXISTS

Company	Sales($)	Website
Sql Traniner	2500	http://www.sqltutorials.blogspot.com
BeautyCentury	3000	http://beautycentury.blogspot.com
TravelYourself	2800	http://travelyourself.blogspot.com

Table1

Sales	Website
2900	http://www.sqltutorials.blogspot.com
3000	http://beautycentury.blogspot.com
2800	http://travelyourself.blogspot.com

Table2

SQL EXISTS Statement

SELECT * FROM TABLE1 WHERE EXISTS(SELECT * FROM TABLE2) Result

Company	Sales($)	Website
Sql Traniner	2500	http://www.sqltutorials.blogspot.com
BeautyCentury	3000	http://beautycentury.blogspot.com
TravelYourself	2800	http://travelyourself.blogspot.com

- We also can using the EXISTS in IF...ELSE Statement to checking the existence of a row or value.
- If IF...ELSE return true, it will return the result of Sql statement inside the BEGIN and END.

IF EXISTS(SELECT * FROM TABLE1 WHERE Company='TravelYourself')
BEGIN
SELECT * FROM TABLE1 WHERE Company='TravelYourself'
END

Result

TravelYourself

2800

http://travelyourself.blogspot.com

SQL Subquery

2007-06-09T07:42:00.000-07:00

Syntax - Subquery
SELECT Statement WHERE ColumnName [Comparison Operator] (SELECT Statement / Values)
Results of one Sql query or Sql statement as the input for another Select query, Insert Into query, Insert From query, Update query and Delete query.
You also can use the result of Subquery as a search condition for using in the IN( ) function or EXISTS operator.

Comparison Operator such as =, >, <, >=, <=, LIKE, IN

Below is example for SQL UNION

Company	Sales($)	Website
Sql Traniner	2500	http://www.sqltutorials.blogspot.com
BeautyCentury	3000	http://beautycentury.blogspot.com
TravelYourself	2800	http://travelyourself.blogspot.com

Table1

Sales	Website
2900	http://www.sqltutorials.blogspot.com
3000	http://beautycentury.blogspot.com
2800	http://travelyourself.blogspot.com

Table2

SQL Subquery Statement

SELECT * FROM TABLE1 WHERE sales in (SELECT Sales FROM TABLE2)
Result

BeautyCentury	3000	http://beautycentury.blogspot.com
TravelYourself	2800	http://travelyourself.blogspot.com

SELECT * FROM TABLE1 WHERE sales LIKE (SELECT Sales FROM TABLE2 WHERE Sales <2900)>
Result

TravelYourself

2800

http://travelyourself.blogspot.com

SELECT * FROM TABLE1where sales > (SELECT Sales FROM TABLE2 WHERE Sales <2900)
Result

BeautyCentury

3000

http://beautycentury.blogspot.com

SQL UNION ALL

2007-06-09T07:07:00.000-07:00

SQL UNION ALL is combine the results of two queries together without filter out the same value(no distinct behavior).
The difference between UNION ALL and UNION is UNION only selects distinct values and UNION ALL selects all values.

Syntax - UNION ALL
(SQL Statement 1)
UNION ALL
(SQL Statement 2)

Below is example for SQL UNION

Company	Sales($)	Website
Sql Traniner	2500	http://www.sqltutorials.blogspot.com
BeautyCentury	3000	http://beautycentury.blogspot.com
TravelYourself	2800	http://travelyourself.blogspot.com

Table1

Sales	Website
2900	http://www.sqltutorials.blogspot.com
3000	http://beautycentury.blogspot.com
2800	http://travelyourself.blogspot.com

Table2

SQL UNION ALL Statement
SELECT Sales FROM TABLE1
UNION ALL
SELECT Sales FROM TABLE2

Result

Sales

2500

3000

2800

2900

3000

2800

SQL Union

2007-06-09T05:54:00.000-07:00

SQL UNION is to join the results of two queries.
UNION only corresponding columns need to be same data type and when using SQL UNION, only distinct values are selected (is similar to SELECT DISTINCT SQL Statement).

Syntax - UNION
(SQL Statement 1)
UNION
(SQL Statement 2)

Below is example for SQL UNION

Company	Sales($)	Website
Sql Traniner	2500	http://www.sqltutorials.blogspot.com
BeautyCentury	3000	http://beautycentury.blogspot.com
TravelYourself	2800	http://travelyourself.blogspot.com

Table1

Sales	Website
2900	http://www.sqltutorials.blogspot.com
3000	http://beautycentury.blogspot.com
2800	http://travelyourself.blogspot.com

Table2

SQL UNION Statement
SELECT Sales FROM TABLE1
UNION
SELECT Sales FROM TABLE2

Result

Sales

2500

2800

2900

3000

SQL Mathematic Functions

2007-06-08T06:38:00.000-07:00

It perform a calculation, usually based on input values and return a numeric value.
Below is All built-in Sql Mathematic Function :
ABS, DEGREES, RAND, ACOS, EXP, ROUND, ASIN, FLOOR, SIGN, ATAN, LOG, SIN, ATN2, LOG10, SQUARE, CEILING, PI, SQRT, COS, POWER, TAN, COT, RADIANS

Example SQL Mathematic Functions - ABS
- Returns the absolute and positive value.
Syntax - ABS ( numeric )
SELECT ABS(-1.0) -- Value = 1.0
SELECT ABS(0.0) -- Value = 0.0
SELECT ABS(1.0) -- Value = 1.0

Example SQL Mathematic Functions - DEGREES
- returns angle in degrees from radian value.
Syntax - DEGREES( numeric)
SELECT DEGREES(1) -- Value = 57 degrees
SELECT DEGREES(2) -- Value = 114 degrees

Example SQL Mathematic Functions - FLOOR
- Returns the largest integer less than or equal to the given numeric expression.
Syntax - FLOOR( numeric)
SELECT FLOOR(32.15) -- Value = 32
SELECT FLOOR(-32.15) -- Value = -33
SELECT FLOOR($32.15) -- Value = 32.0000

Example SQL Mathematic Functions - CEILING
- Returns the smallest integer greater than, or equal to the input value
Syntax - CEILING( numeric)
SELECT CEILING(32.15) -- Value = 33
SELECT CEILING(-32.15) -- Value = -32
SELECT CEILING($32.15) -- Value = 33.0000

Example SQL Mathematic Functions - ROUND
- Return rounded value.
Syntax - ROUND( numeric, length)
SELECT ROUND(32.15 ,1) -- Value = 32.20
SELECT ROUND(32.14 ,1) -- Value = 32.10
SELECT ROUND(32.14998 ,4) -- Value = 32.15000
SELECT ROUND(32.14992 ,4) -- Value = 32.14990
SELECT ROUND(32.92 ,0) -- Value = 33.00

Example SQL Mathematic Functions - POWER
- Returns the value of input to the specified power.
Syntax - POWER( numeric, power(integer))
SELECT POWER(6,2) -- Value = 36 (6*6)
SELECT POWER(9,3) -- Value = 729 (9*9*9)

SQL String Functions

2007-06-07T05:55:00.000-07:00

Sql string function is a built-in string function.
It perform an operation on a string input value and return a string or numeric value.
Below is All built-in Sql string function :
ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE, CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME, STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE, UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM

Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)
SELECT ASCII('a') -- Value = 97
SELECT ASCII('b') -- Value = 98
SELECT ASCII('c') -- Value = 99
SELECT ASCII('A') -- Value = 65
SELECT ASCII('B') -- Value = 66
SELECT ASCII('C') -- Value = 67
SELECT ASCII('1') -- Value = 49
SELECT ASCII('2') -- Value = 50
SELECT ASCII('3') -- Value = 51
SELECT ASCII('4') -- Value = 52
SELECT ASCII('5') -- Value = 53

Example SQL String Function - SPACE
-Returns spaces in your SQL query (you can specific the size of space).
Syntax - SPACE ( integer)
SELECT ('SQL') + SPACE(0) + ('TUTORIALS')
-- Value = SQLTUTORIALS
SELECT ('SQL') + SPACE(1) + ('TUTORIALS')
-- Value = SQL TUTORIALS

Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] )
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial')
-- Value = 27
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 20)
-- Value = 27
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 30)
-- Value = 0 (Because the index is count from 30 and above)

Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )
SELECT REPLACE('All Function' , 'All', 'SQL')
-- Value = SQL Function

Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( 'string' [ , 'quote_character' ] )
SELECT QUOTENAME('Sql[]String')
-- Value = [Sql[]]String]

Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 )
SELECT STUFF('SqlTutorial', 4, 6, 'Function')
-- Value = SqlFunctional
SELECT STUFF('GoodMorning', 5, 3, 'good')
-- Value = Goodgoodning

Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer)
SELECT LEFT('TravelYourself', 6)
-- Value = Travel
SELECT LEFT('BeautyCentury',6)
-- Value = Beauty

Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT('TravelYourself', 6)
-- Value = urself
SELECT RIGHT('BeautyCentury',6)
-- Value = Century

Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)
SELECT REPLICATE('Sql', 2)
-- Value = SqlSql

Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING('SQLServer', 4, 3)
-- Value = Ser

Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string)
SELECT LEN('SQLServer')
-- Value = 9

Example SQL String Function - REVERSE
-Returns reverse a string.
Syntax - REVERSE( string)
SELECT REVERSE('SQLServer')
-- Value = revreSLQS

Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char)
SELECT UNICODE('SqlServer')
-- Value = 83 (it take first character)
SELECT UNICODE('S')
-- Value = 83

Example SQL String Function - LOWER
-Convert string to lowercase.
Syntax - LOWER( string )
SELECT LOWER('SQLServer')
-- Value = sqlserver

Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string )
SELECT UPPER('sqlserver')
-- Value = SQLSERVER

Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )
SELECT LTRIM(' sqlserver')
-- Value = 'sqlserver' (Remove left side space or blanks)

Example SQL String Function - RTRIM
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )
SELECT RTRIM('SqlServer ')
-- Value = 'SqlServer' (Remove right side space or blanks)

SQL CAST and CONVERT

2007-06-05T08:24:00.000-07:00

It converts an expression from one data type to another.
CAST and CONVERT have similar functionality.

SQL CAST and CONVERT Syntax
Using CAST:
CAST ( expression AS data_type )

Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example of SQL Cast and Convert

SQL Cast and Convert - String
SELECT SUBSTRING('CAST and CONVERT', 1, 3)
Return Value = CAS (it get from index 1 to 3)

SELECT CAST('CAST and CONVERT' AS char(3))
Return Value = CAS (it get 3 char only)

SQL Cast and Convert - Date Time
-Converting date time to character data(vachar)
-The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the Without century year(yy).
-Add 100 to a style value to get a four-place year that includes the century year(yyyy).
-Below is example for converting 1 format of date time to different format of date time, so that it can be use in various condition.

Value of current Date Time GETDATE()
SELECT (GETDATE()) = 2007-06-06 23:41:10.153

SELECT CONVERT(varchar,GETDATE(),0)
Return Value = Jun 6 2007 11:07PM
SELECT CONVERT(varchar,GETDATE(),100)
Return Value = Jun 6 2007 11:07PM

SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
Return Value = 06/06/2007

SELECT CONVERT(varchar,GETDATE(),2)
Return Value = 07.06.06
SELECT CONVERT(varchar,GETDATE(),102)
Return Value = 2007.06.06

SELECT CONVERT(varchar,GETDATE(),3)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),103)
Return Value = 06/06/2007

SELECT CONVERT(varchar,GETDATE(),4)
Return Value = 06.06.07
SELECT CONVERT(varchar,GETDATE(),104)
Return Value = 06.06.2007

SELECT CONVERT(varchar,GETDATE(),5)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),105)
Return Value = 06-06-2007

SELECT CONVERT(varchar,GETDATE(),6)
Return Value = 06 Jun 07
SELECT CONVERT(varchar,GETDATE(),106)
Return Value = 06 Jun 2007

SELECT CONVERT(varchar,GETDATE(),7)
Return Value = Jun 06, 07
SELECT CONVERT(varchar,GETDATE(),107)
Return Value = Jun 06, 2007

SELECT CONVERT(varchar,GETDATE(),8)
Return Value = 23:38:49
SELECT CONVERT(varchar,GETDATE(),108)
Return Value = 23:38:49

SELECT CONVERT(varchar,GETDATE(),9)
Return Value = Jun 6 2007 11:39:17:060PM
SELECT CONVERT(varchar,GETDATE(),109)
Return Value = Jun 6 2007 11:39:17:060PM

SELECT CONVERT(varchar,GETDATE(),10)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),110)
Return Value = 06-06-2007

SELECT CONVERT(varchar,GETDATE(),11)
Return Value = 07/06/06
SELECT CONVERT(varchar,GETDATE(),111)
Return Value = 2007/06/06

SELECT CONVERT(varchar,GETDATE(),12)
Return Value = 070606
SELECT CONVERT(varchar,GETDATE(),112)
Return Value = 20070606

SELECT CONVERT(varchar,GETDATE(),13)
Return Value = 06 Jun 2007 23:40:14:577
SELECT CONVERT(varchar,GETDATE(),113)
Return Value = 06 Jun 2007 23:40:14:577

SELECT CONVERT(varchar,GETDATE(),14)
Return Value = 23:40:29:717
SELECT CONVERT(varchar,GETDATE(),114)
Return Value = 23:40:29:717

SELECT CONVERT(varchar,GETDATE(),20)
Return Value = 2007-06-06 23:40:51
SELECT CONVERT(varchar,GETDATE(),120)
Return Value = 2007-06-06 23:40:51

SELECT CONVERT(varchar,GETDATE(),21)
Return Value = 2007-06-06 23:41:10.153
SELECT CONVERT(varchar,GETDATE(),121)
Return Value = 2007-06-06 23:41:10.153

SELECT CONVERT(varchar,GETDATE(),126)
Return Value = 2007-06-06T23:41:10.153

SELECT CONVERT(varchar,GETDATE(),131)
Return Value = 21/05/1428 11:41:10:153PM

MySql Total Day of Month

2007-06-05T07:34:00.000-07:00

SQL Statement below is for MySql Database to get the total day of month.
SELECT DAY(LAST_DAY(now()))

Example of SQL Statement to get the total day of month.
SELECT DAY(LAST_DAY('2007-06-05 22:40:51'))
Return Value = 30

I tested MySql Server 5.0, it is work.

SQL DATEDIFF Function

2007-06-05T06:18:00.000-07:00

Returns the number of date and time boundaries crossed between two dates

SQL DATEDIFF Syntax
DATEDIFF ( DatePart , StartDate , EndDate )

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'

SELECT DATEDIFF(Year, @StartDate, @EndDate) AS NewDate
Return Value = 0 Year

SELECT DATEDIFF(quarter, @StartDate, @EndDate) AS NewDate
Return Value = 1 quarter

SELECT DATEDIFF(Month, @StartDate, @EndDate) AS NewDate
Return Value = 2 Month

SELECT DATEDIFF(dayofyear,@StartDate, @EndDate) AS NewDate
Return Value = 61 day

SELECT DATEDIFF(Day, @StartDate, @EndDate) AS NewDate
Return Value = 61 Day

SELECT DATEDIFF(Week, @StartDate, @EndDate) AS NewDate
Return Value = 9 Week

SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDate
Return Value = 1464 Hour

SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
Return Value = 87840 minute

SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDate
Return Value = 5270400 second

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-06-06'

SELECT DATEDIFF(millisecond, @StartDate, @EndDate) AS NewDate
Return Value = 86400000 millisecond

SQL DATEDIFF Function
SqlTutorials

SQL Dateparts and Abbreviations

2007-06-04T08:32:00.000-07:00

Table below lists the dateparts and abbreviations recognized by Microsoft SQL Server(MS SQL).
Datepart below is suitable using in DATEADD, DATEDIFF, DATENAME and all Date related function.

Datepart	Abbreviations
Year	yy, yyyy
quarter	qq, q
Month	mm, m
dayofyear	dy, y
Day	dd, d
Week	wk, ww
Hour	hh
minute	mi, n
second	ss, s
millisecond	ms

SQL DATEADD Function

2007-06-04T08:04:00.000-07:00

Returns a new datetime value based on adding an interval to the specified date.

SQL DATEADD Syntax
DATEADD ( datepart , number, date )

DECLARE @DateNow DATETIME
SET @DateNow='2007-06-04'
SELECT DATEADD(Year, 3, @DateNow) AS NewDate
Return Value = 2010-06-04 00:00:00.000

SELECT DATEADD(quarter, 3, @DateNow) AS NewDate
Return Value = 2008-03-04 00:00:00.000

SELECT DATEADD(Month, 3, @DateNow) AS NewDate
Return Value = 2007-09-04 00:00:00.000

SELECT DATEADD(dayofyear,3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000

SELECT DATEADD(Day, 3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000

SELECT DATEADD(Week, 3, @DateNow) AS NewDate
Return Value = 2007-06-25 00:00:00.000

SELECT DATEADD(Hour, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 03:00:00.000

SELECT DATEADD(minute, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:03:00.000

SELECT DATEADD(second, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:03.000

SELECT DATEADD(millisecond, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:00.003

SQL Statement Last Day of a month

2007-06-04T07:53:00.000-07:00

SQL Statement below show the Sql query to get the last day of a month value by using sql DATEADD.
Example of SQL Statement to get last day of a month

DECLARE @DateNow DATETIME
SET @DateNow='2007-07-04'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@DateNow)), DATEADD(m,1,@DateNow)) AS LastDay
Return Value = 2007-07-31 00:00:00.000

DECLARE @DateNow DATETIME
SET @DateNow='2007-06-04'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@DateNow)), DATEADD(m,1,@DateNow)) AS LastDay
Return Value = 2007-06-30 00:00:00.000

SQL Aggregate Function - Syntax and Example

2007-06-03T06:37:00.000-07:00

SQL Aggregate Function is built-in functions for counting and calculation (perform a calculation on a set of values and return a single value)
Syntax for built-in SQL functions is - SELECT function(column) FROM table

Name	Salary
Emil	5000
Chang	5000
Emily	4500
Nick	4000

Table Name: A

Below is Example for SQL built-in Function , together with SQL Statement and the answer.

AVG - Average value of columns
Select AVG(Salary) FROM A
Value = 4675

COUNT - number of rows
Select COUNT(*) FROM A
Value = 4

SELECT COUNT (DISTINCT Salary) FROM CUSTOMERS
Value = 3

MAX - Maximun or Highest number in a column
SELECT MAX(Salary) FROM CUSTOMERS
Value = 5000

MAX - Minimum or Lowest number in a column
SELECT MIN(Salary) FROM CUSTOMERS
Value = 4000

SUM - Total number in a column
SELECT SUM(Salary) FROM CUSTOMERS
Value = 18500

SQL DATENAME Function - Year, Quarter, Month, Day, Hour, Minute and Milisecond.

2007-05-30T08:08:00.000-07:00

It return specifies the part of the date name and the DATENAME for Date Time such as Year, Quarter, Month, Day, Hour, Minute and Milisecond.

SQL DATENAME Syntax
DATENAME ( datepart , date )

Example

SELECT GETDATE() = 2007-05-30 23:13:38.763

SELECT DATENAME(year, GETDATE())
SELECT DATENAME(yy, GETDATE())
SELECT DATENAME(yy, GETDATE())
- It will return value = 2007

SELECT DATENAME(quarter, GETDATE())
SELECT DATENAME(qq, GETDATE())
SELECT DATENAME(q, GETDATE())
-It will return value = 2 (because 1 quarter equal to 3 month,Detail see below table)

Month	Quarter Value
January - March	1
April - June	2
July - September	3
October - December	4

SELECT DATENAME(month, GETDATE())
SELECT DATENAME(mm, GETDATE())
SELECT DATENAME(m, GETDATE())
- It will return value = May

SELECT DATENAME(dayofyear, GETDATE())
SELECT DATENAME(dy, GETDATE())
SELECT DATENAME(y, GETDATE())
- It will return value = 150 (this is calculate total day from 1 jan 2007 until 30 may 2007)

SELECT DATENAME(day, GETDATE())
SELECT DATENAME(dd, GETDATE())
SELECT DATENAME(d, GETDATE())
- It will return value = 30

SELECT DATENAME(week, GETDATE())
SELECT DATENAME(wk, GETDATE())
SELECT DATENAME(ww, GETDATE())
- It will return value = 23 (this is 23rd week from 1 jan 2007)

SELECT DATENAME(hour, GETDATE())
SELECT DATENAME(hh, GETDATE())
- It will return value = 23 (time for 24 hour)

SELECT DATENAME(minute, GETDATE())
SELECT DATENAME(mi, GETDATE())
SELECT DATENAME(n, GETDATE())
- It will return value = 13 (minute)

SELECT DATENAME(second , GETDATE())
SELECT DATENAME(ss, GETDATE())
SELECT DATENAME(s, GETDATE())
- It will return value = 38 (second)

SELECT DATENAME(millisecond , GETDATE())
SELECT DATENAME(ms, GETDATE())
- It will return value = 763 (milisecond)

SQL Inner Join, Left Join and Right Join

2007-05-30T03:54:00.000-07:00

Below is a Example for SQL inner join, SQL Left Join and SQL Right Join.

GuestID	Name
1001	Nick
1002	Jack
1003	Apple

Table A

Product	GuestID
Table	1002
Chair	1003
Almari	1002

Table B

SQL inner join
Example for SQL inner join
- Join more than 1 table to get other table data
1. SELECT A.Name, B.Product FROM A INNER JOIN B ON A.GuestID=B.GuestID
Result :

Name	Product
Jack	Table
Apple	Chair
Jack	Almari

2. SELECT A.Name, B.Product FROM A INNER JOIN B ON A.GuestID=B.GuestID WHERE B.Product = 'tABLE'

Result:

Name	Product
Jack	Table

Get Number of Row in Select Statement

2007-05-30T03:24:00.000-07:00

For SQL Server 2000, you can using below SQL Query or SQL statement to get number of row for a SELECT Statement.

SELECT rank=COUNT(*), A1.countryname, A1.countrycode FROM country A1, country A2 WHERE A1.countryname + A1.countrycode >= A2.countryname + A2.countrycode GROUP BY A1.countryname, A1.countrycode ORDER BY rank

Table (Before using the above SQL Query): SELECT * FROM TABLE

Table (After using the above SQL Query): It add 1 more column to show the number of Row for this table.

For SQL Server 2005, you can using below SQL Query or SQL statement to get number of row for a SELECT Statement.

SELECT rank() OVER (ORDER BY A1.countryname, A1.countrycode) as rank, A1.countryname, A1.countrycode FROM country A1 ORDER BY rank

The result will same as above image.

For more information about SQL Query Syntax or SQL statement Syntax,click here
For more information about Example of SQL Query or SQL statement,click here

Select Statement for Get Number of Days in a Month

2007-05-30T03:18:00.000-07:00

Using SQL statement or SQL Query to calculating the Number of Days in a Month

select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))

Example on may(month), you just copy and paste the SQL Query or SQL statement (Above blue color) to the SQL Query Analyzer. You will get the answer is 31 days for May(month).

SQL Statement Example for SELECT, UPDATE and DELETE

2007-05-29T06:50:00.001-07:00

Below is Sql Statement with Example provided. It will help SQL beginner more understanding about the usage of SQL statement. For more information about SQL Syntax, click here.

Name	Income	Company	Position
Angle	4500	Ajax Tech Limited	Manager
Helen	6900	Programmingschools Limited	CEO
Micheal	4000	SQLTutorial Limited	DBA
Nick	5000	Programmingschools Limited	Programmer

Table Name : Testing

SQL STATEMENT - Example Result with SQL Statement
Select All data from a table (same as above table)- SELECT * FROM TESTING

SELECT NAME,COMPANY FROM TESTING

name	company
Angle	Ajax Tech Limited
Helen	Programmingschools Limited
Micheal	SQLTutorial Limited
Nick	Programmingschools Limited

SELECT DISTINCT COMPANY FROM TESTING

company
Ajax Tech Limited
Programmingschools Limited
SQLTutorial Limited

SELECT COUNT(*) FROM TESTING

SELECT * FROM TESTING WHERE NAME='HELEN'

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	CEO

SELECT * FROM TESTING WHERE NAME='HELEN' AND COMPANY='Programmingschools Limited'

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	CEO

SELECT * FROM TESTING WHERE NAME='HELEN' OR COMPANY='Pragrammingschools Limited'

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	CEO
Nick	5000	Programmingschools Limited	Programmer

SELECT * FROM TESTING WHERE COMPANY IN ('Pragrammingschools Limited')

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	CEO
Nick	5000	Programmingschools Limited	Programmer

SELECT * FROM TESTING WHERE COMPANY LIKE '%PROGRAMMINGSCHOOLS%'

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	CEO
Nick	5000	Programmingschools Limited	Programmer

SELECT * FROM TESTING WHERE ORDER BY COMPANY

Name	Income	Company	Position
Angle	4500	Ajax Tech Limited	Manager
Helen	6900	Programmingschools Limited	CEO
Nick	5000	Programmingschools Limited	Programmer
Micheal	4000	SQLTutorial Limited	DBA

SELECT * FROM TESTING WHERE ORDER BY COMPANY DESC

Name	Income	Company	Position
Micheal	4000	SQLTutorial Limited	DBA
Helen	6900	Programmingschools Limited	CEO
Nick	5000	Programmingschools Limited	Programmer
Angle	4500	Ajax Tech Limited	Manager

SELECT SUM(INCOME),COMPANY FROM TESTING GROUP BY COMPANY

SUM(Income)	Company
4500	Ajax Tech Limited
11900	Programmingschools Limited
4000	SQLTutorial Limited

SELECT COUNT(*),COMPANY FROM TESTING GROUP BY COMPANY

COUNT(*)	COMPANY
1	Ajax Tech Limited
2	Programmingschools Limited
1	SQLTutorial Limited

SELECT SUM(INCOME),COMPANY FROM TESTING GROUP BY COMPANY HAVING (SUM(INCOME)>5000)

SUM(INCOME)	COMPANY
11900	Programmingschools Limited

For Sql Update and Sql Delete statement, click here

SQL Statement Example for UPDATE and DELETE

2007-05-29T06:21:00.000-07:00

UPDATE Statement
UPDATE statement is used to edit and modify the data in a table. Below showing the example for Update and Delete Statement.

Name	Income	Company	Position
Angle	4500	Ajax Tech Limited	Manager
Helen	6900	Programmingschools Limited	CEO
Micheal	4000	SQLTutorial Limited	DBA
Nick	5000	Programmingschools Limited	Programmer

Table Name: Testing

SQL Statement With Example

UPDATE TESTING SET POSITION='BOSS' WHERE NAME='HELEN'
- Position Column updated from CEO to BOSS using the update statement with 1 condition name=helen.

Name	Income	Company	Position
Angle	4500	Ajax Tech Limited	Manager
Helen	6900	Programmingschools Limited	BOSS
Micheal	4000	SQLTutorial Limited	DBA
Nick	5000	Programmingschools Limited	Programmer

UPDATE TESTING SET POSITION='Head of Department' WHERE NAME='HELEN' AND COMPANY='Programmingschools Limited'
- Position Column updated from BOSS to Head of Department using the update statement with 2 condition name=helen and company=Programmingschools Limited.

Name	Income	Company	Position
Angle	4500	Ajax Tech Limited	Manager
Helen	6900	Programmingschools Limited	Head of Department
Micheal	4000	SQLTutorial Limited	DBA
Nick	5000	Programmingschools Limited	Programmer

DELETE Statement
DELETE FROM TESTING WHERE NAME='Angle'
-Delete row using delete statement with condition name=angle.

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	Head of Department
Micheal	4000	SQLTutorial Limited	DBA
Nick	5000	Programmingschools Limited	Programmer

DELETE FROM TESTING
-Whole table deleted

For more information for SELECT Statement, click here

All Topic in Sql Tutorials and ProgrammingSchools

2007-05-27T19:14:00.000-07:00

sqltutorials

programmingschools

SQL Syntax - SELECT,UPDATE and DELETE

2007-05-22T21:02:00.001-07:00

Below is the Lists of possible syntax for SELECT,UPDATE and DELETE

SELECT Syntax - Get Information from a table in database
SELECT ColumnName1,ColumnName2, ColumnName3 FROM TableName
SELECT DISTINCT ColumnName FROM TableName
SELECT COUNT (ColumnName) FROM TableName
SELECT ColumnName FROM TableName WHERE ColumnName = value
SELECT ColumnName FROM TableName WHERE ColumnName1 = value AND ColumnName2 = value
SELECT ColumnName FROM TableName WHERE ColumnName1 = value OR ColumnName2 = value
SELECT ColumnName FROM TableName WHERE ColumnName IN (Value1,Value2)
SELECT ColumnName FROM TableName WHERE ColumnName BETWEEN (Value1 AND Value2)
SELECT ColumnName FROM TableName WHERE ColumnName Like '%Value1%'
SELECT ColumnName FROM TableName ORDER BY ColumnName
SELECT ColumnName1 , SUM(ColumnName2 ) FROM TableName GROUP BY ColumnName1 HAVING (Arithematic Condition)

UPDATE Syntax
UPDATE TableName SET ColumnName = Value WHERE {Condition}
UPDATE TableName SET ColumnName = Value WHERE ColumnName = value

DELETE Syntax
DELETE TableName WHERE {Condition}
DELETE TableName WHERE ColumnName = value

What is SQL?

2007-05-22T07:46:00.001-07:00

SQL (Structured Query Language) is a computer language used to create, retrieve, update and delete data from relational database management systems. SQL works with database programs like MS SQL Server, MS Access, MSDE, MySQL, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.

SQL Data Manipulation Language (DML)
DML is a syntax for executing queries and DML component of SQL comprises have four basic statements:
SELECT - Retrieve rows from tables
UPDATE - Modify the rows of tables
DELETE - Remove rows from tables
INSERT - Add new rows to tables.

SQL Data Definition Language (DDL)
DDL is used to create and destroy databases, database table and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. DML component of SQL comprises have four basic statements:
CREATE - Creates a new database table
ALTER - Alters / changes a database table
DROP - delete a database table