Differences between nvarchar and varchar in SQL Server

SQL Server provides both datatypes to store character information. For the most part the two datatypes are identical in how you would work with them within SQL Server or from an application.

The difference is that nvarchar is used to store unicode data, which is used to store multilingual data in your database tables. Other languages have an extended set of character codes that need to be saved and this datatype allows for this extension. If your database will not be storing multilingual data you should use the varchar datatype instead. The reason for this is that nvarchar takes twice as much space as varchar, this is because of the need to store the extended character codes for other languages.

271 unique visitors

Website to get conversion techniques of DATETIME in sqlserver

To understand difference between

CONVERT(VARCHAR(20),CONVERT(DATETIME,ExecutionDate),101) is MMDDYYYY

and

CONVERT(VARCHAR(20),CONVERT(DATETIME,ExecutionDate),103) is DDMMYYYY

The website is: -

http://www.sql-server-helper.com/tips/date-formats.aspx

234 unique visitors

SQL Server - Create Temporary Tables & Insert into Temporary Table

DECLARE @tbl table
(
ID int identity,
ProjName varchar(100)
)
INSERT into @tbl(ProjName)
SELECT * from dbo.SemisvToString(@IAAudioEyeProjectNames)

also

CREATE TABLE #AERTTV2TempReportTable
(
ProjectName varchar(200),
TaskName varchar(400), --'CHANGED THIS 200 to 400 '
HoursSpent decimal(18,2),
ExecutionDate varchar(20),
UserName varchar(200)
)

TRUNCATE TABLE #AERTTV2TempReportTable

INSERT INTO #AERTTV2TempReportTable(ProjectName, TaskName, HoursSpent, ExecutionDate, UserName)
SELECT ProjectName, TaskName, HoursSpent, CONVERT(VARCHAR(20),ExecutionDate,101) AS ExecutionDate, UserName
FROM AudioEyeReporting.dbo.TempReportTable
WHERE IsDeleted = 0 AND ExecutionDate BETWEEN @StartDate AND @EndDate
UNION ALL
SELECT ProjectName, TaskName, HoursSpent, CONVERT(VARCHAR(20),CONVERT(DATETIME,ExecutionDate),101) AS ExecutionDate, UserName
FROM TimeTrackerV2.dbo.TempReportTable
WHERE ProjectName IN (SELECT ProjName FROM @tbl) AND ExecutionDate BETWEEN @StartDate AND @EndDate

SELECT * FROM #AERTTV2TempReportTable

SELECT SUM(HoursSpent)
FROM #AERTTV2TempReportTable
DROP TABLE #AERTTV2TempReportTable

234 unique visitors

SQL Server - Function to Get individual values from Comma Separated Values (CSVs)

1. CSV - String

CREATE FUNCTION[dbo].[CsvToString] ( @Array VARCHAR(1000))
RETURNS @StrTable TABLE
(StrValue VARCHAR(1000))
AS
BEGIN
DECLARE @separator CHAR(1)
SET @separator = ','
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(1000)
SET @array = @array + ','
WHILE patindex('%,%' , @array) <> 0
BEGIN
SELECT @separator_position = patindex('%,%' , @array)
SELECT @array_value = left(@array, @separator_position - 1)
INSERT @StrTable
VALUES (Cast(@array_value as VARCHAR(1000)))
SELECT @array = stuff(@array, 1, @separator_position, '')
END
RETURN
END

2. CSV - Int

CREATE Function [dbo].[CsvToInt] ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(1000)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
Insert @IntTable
Values (Cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position, '')
end
return
end

3. SSV(Semicolon Separated Values) - Int

ALTER FUNCTION[dbo].[SemisvToString] ( @Array VARCHAR(1000))
RETURNS @StrTable TABLE
(StrValue VARCHAR(1000))
AS
BEGIN
DECLARE @separator CHAR(1)
SET @separator = ';'
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(1000)
SET @array = @array + ';'
WHILE patindex('%;%' , @array) <> 0
BEGIN
SELECT @separator_position = patindex('%;%' , @array)
SELECT @array_value = left(@array, @separator_position - 1)
INSERT @StrTable
VALUES (Cast(@array_value as VARCHAR(1000)))
SELECT @array = stuff(@array, 1, @separator_position, '')
END
RETURN
END

234 unique visitors

SET NOCOUNT ON; - Improve performance of stored procedure

Benefit: - By Setting NOCOUNT as ON we improve the performance of the stored procedure

Problem: -

One of the biggest things that DBAs try to do on a daily basis is to ensure that their database systems run as fast as possible.  As more and more users access the databases and the databases continue to grow, performance slow downs are almost inevitable. Based on this, DBAs and developers should do everything they possibly can to keep performance related issues in mind early in the database lifecycle.  This is not always easy to do, because of the unknowns and the changes that occur over time, but there are some simple things that can be done and we will touch upon one of these in this tip.

Solution: -

Sometimes even the simplest things can make a difference.  One of these simple items that should be part of every stored procedure is SET NOCOUNT ON.  This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.  This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option.  By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.

Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.

Here is the old template style available in SQL Server 2000 without the SET NOCOUNT ON.

Source: - http://www.mssqltips.com/tip.asp?tip=1226

192 unique visitors

All Databases and All Tables

• Select All Databases from a Server (this is a system stored procedure)
  

sp_databases

• Select All Tables from a Particular Database
  

select name from sysobjects WHERE type = 'U'

106 unique visitors

What is DDL, DML, DCL, TCL in SQL ?

The Data Definition Language (DDL) includes: -
CREATE TABLE - creates new database table
ALTER TABLE - alters or changes the database table
DROP TABLE - deletes the database table
CREATE INDEX - creates an index or used as a search key
DROP INDEX - deletes an index

The Data Manipulation Language (DML) includes: -
SELECT - extracts data from the database
UPDATE - updates data in the database
DELETE - deletes data from the database
INSERT INTO - inserts new data into the database

The Data Control Language (DCL) includes: -
GRANT – gives access privileges to users for database
REVOKE – withdraws access privileges to users for database

The Transaction Control Language (TCL) includes: -
COMMIT – saves the work done
ROLLBACK - restore the database to original since the last COMMIT

70 unique visitors

SQL Server Database ConnectionString

string strCon;
SqlConnection objCon = new SqlConnection();
string mstrConnectionstring = String.Empty;
public DataObject()
{
objCon = new SqlConnection();
strCon = ConfigurationSettings.AppSettings.Get("mstrConnectionstring");
objCon = new SqlConnection(strCon);
objCon.Open();
----------------------------------------
Inside WebConfig. Replace the ( and ) by <>.

(appSettings)(add key="mstrConnectionstring" value="user id= ab;password=**********; server=VAIJAYANTA-PC\SQLEXPRESS; database = Vaijayanta-Test")(/add)(/appSettings)

65 unique visitors

How to take backup of a SQL Server Database Data

How to take backup of a SQL Server Database Data: -

1) Database (right click) -> Tasks -> Backup -> Database -> OK

(the database backup will be done)

42 unique users

Create a Local Version of the SQL Server Database

Qs) How to create a local version of the database if security permissions do not allow to copy a database in a sql server?

Ans) In this procedure the data will no be copied. Follow the steps: -
1) Right click on the Database name at server
2) Tasks
3) Generate Scripts
4) Follow the "SQL Server Script Wizard" steps, step by step as required.
(A script will be generated)
5) Execute this script generated by copying it on a query window in a local sql server.
(All the tables, stored procedures & scripts will be created without the data inside it)

User Defined Function in Sql Server

http://msdn2.microsoft.com/en-us/magazine/cc164062.aspx