Which datatype is good for my database field?

Every time at the designing of new database table we stuck for the choosing right datatype for the particular field.

It has been said that if there is right database design have been done, it’s easy to accomplish changes in the application. If you require to change database field type or require to add new field in the table, it costs a lot at later stage.

So with this confusion of choosing right database, I have some check list what to use for the string type field.

Consider following points for your string data field.

àWhat will be max size of field, how many characters could be there?

àIs there any fix length I will have?

àDoes field require using Unicode character?

There are basic for three types of datatype are supported.

Char : this datatype can be used when we know our field’s data. We have prior idea of value. It will be good to use this type.

Varchar: when we are not firm about the value come in the field, this can be the choice.

Now one interesting datatype

Nvarchar: this is same as varchar expect it stores value as Unicode character.

Unicode character are especially useful when we would like to support Multilanguage in application else there is not much reason to use it without any purpose.

Nvarachar datatype occupies double space than the varchar, it should be considered when there will be large amount of data will be feed in the system.

You can check it with following sample

After running query you will notice that varchar datatype takes less space compared to others.

declare @na char(30)

SET @na = 'shailesh'

declare @nam nvarchar(30)

SET @nam = 'shailesh'

declare @name varchar(30)

SET @name = 'shailesh'

select datalength(@na)

select datalength(@nam)

select datalength(@name)

Pass CSV parameter in SQL Stored procedure

Recently, I have been asked to prepare query that accept CSV value and parse that value in stored procedure.

In early, I didn’t give much attention as I thought it was just like the Where clause with IN. If we prepare dynamic query and execute it works well. But we don’t want to do that, we want some solution that should work every time when we pass CSV value.

The solution was to create function and it returns list of value. Here is the function; I have found this function during my search.

CREATE FUNCTION dbo.CSVToList (@CSV varchar(3000))

RETURNS @Result TABLE (Value varchar(30))

AS

BEGIN

DECLARE @List TABLE

(

Value varchar(30)

)

DECLARE

@Value varchar(30),

@Pos int

SET @CSV = LTRIM(RTRIM(@CSV))+ ','

SET @Pos = CHARINDEX(',', @CSV, 1)

IF REPLACE(@CSV, ',', '') <> ''

BEGIN

WHILE @Pos > 0

BEGIN

SET @Value = LTRIM(RTRIM(LEFT(@CSV, @Pos - 1)))

IF @Value <> ''

INSERT INTO @List (Value) VALUES (@Value)

SET @CSV = RIGHT(@CSV, LEN(@CSV) - @Pos)

SET @Pos = CHARINDEX(',', @CSV, 1)

END

END

INSERT @Result

SELECT

Value

FROM

@List

RETURN

END

Now say for example you want to create stored procedure that should return you all state name where we passed id with CSV,

We can pass any number of state id in the @StateId parameter.

CREATE PROCEDURE State_Search

@StateId nvarchar(2000)

AS

BEGIN

SELECT * FROM tblState

WHERE tblState.StateId IN (SELECT * FROM dbo.CSVToLIst(@StateId))

END

Get next identity value in my table For SQL Server

It may require some time to know what will be our identity value before we actually insert record in our table. Usually we tend to find our max id value by selecting max id from records. But it may be possible you deleted records and now you want to know next identity value. SQL server has given a very nice function for it.

SELECT IDENT_CURRENT(‘mytable’)

In above line, mytable is table name, in which we want to find next identity value. Very impressive function!!

Paging stored procedure for the SQL server 2000

Before a while ago, I have posted query for the true paging but it does not work in SQL server as ROWNUM is not available in it.

Here, is the sample stored procedure for SQL Server 2000

CREATE PROCEDURE [dbo].[State_Search]

@SearchText nvarchar(50) = null

,@PageNo int

,@PageSize int

,@TotalRecords int OUTPUT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @StartFrom int

SET @StartFrom = @PageSize * (@PageNo - 1);

CREATE TABLE #tmpState (

RowNo int IDENTITY(1,1) PRIMARY KEY,

RowId int NOT NULL

);

INSERT INTO #tmpState(RowId)

SELECT CHECKSUM([State].StateGuid) FROM [State]

WHERE (@SearchText IS NULL OR StateCode LIKE @SearchText OR

StateName LIKE @SearchText OR StateDesc LIKE @SearchText)

SET @TotalRecords = (SELECT COUNT(*) FROM #tmpState);

SELECT

[State].*

FROM #tmpState

INNER JOIN [State] ON CHECKSUM([State].StateGuid) = #tmpState.RowId

WHERE #tmpState.RowNo > @StartFrom AND #tmpState.RowNo <= (@StartFrom + @PageSize)

ORDER BY #tmpState.RowNo;

SELECT @TotalRecords AS TotalRecords;

DROP TABLE #tmpState;

END

Simple SQL Operation

Yes in this post you will see many a things of SQL Server which is very small; it may be useful in our day to day life.

Firstly just imagine you want to insert values in table, certainly you go for below approach. Let me create a table for our testing

CREATE TABLE MyTable(TestName VARCHAR(25))

Now for data insert you use ..

INSERT INTO MyTable(TestName)

VALUES ('ONE')

GO

INSERT INTO MyTable(TestName)

VALUES ('TWO')

GO

But you can do achieve same thing with below script where we have used UNION ALL

INSERT INTO MyTable(TestName)

SELECT 'ONE'

UNION ALL

SELECT 'TWO'

UNION ALL

SELECT 'THREE'

UNION ALL

SELECT 'FOUR'

UNION ALL

SELECT 'FIFVE'

UNION ALL

SELECT 'SIX'

GO

Hope this makes sense when we want to insert multiple records in table

Now let’s Go through another game, yes of-course this is some kind of game, you are playing in SQL server.

We want to insert Record to table from another table.

At that time we have two choices ( I am assuming that you have created table from above script and we will use that table in our below example)

1) New table is already created and you want to insert data in it.

CREATE TABLE MyTestTable(TempName VARCHAR(25))

INSERT INTO MyTestTable(TempName)

SELECT TestName FROM MyTable

SELECT * FROM MyTestTable

DROP TABLE MyTestTable

2) You want to create table at the time of insertion operation no table exists before it. In below MyTestTable is not created before but it will be created once we execute this

SELECT TestName

INTO MyTestTable

FROM MyTable

SELECT * FROM MyTestTable

DROP TABLE MyTestTable

Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.

You might get this error if you try to attach an mdf file resides in the data directory (App_Data folder) in websites.

This is because your sql server does not have permission to create user instance of file to be attached.

Your connection string might look like this.

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"

It can be fixed by executing following statement in the sql server, where you wish to attach.

exec sp_configure 'user instances enabled', 1

reconfigure

Restart your SQL server and try to run your application.

Note: this can be useful if you work with the local database. In most cases remote server does not allow us to run above query, you need to contact your hosting company for it.

Query on schema of database

In sql server we create database object like tables, function, procedure etc. Some time we need to know how many tables or procedure are there.. at that time below sql query will be useful.

SQL provides query to execute on the object of itself.

List all the Database

SELECT * FROM sys.databases

SELECT * FROM master.dbo.sysdatabases

List all the tables in the Database

SELECT * FROM sysobjects WHERE type = 'U'

SELECT * FROM information_schema.tables

SELECT * FROM sys.tables

List all the Procedure in the Database

SELECT * FROM sys.procedures

SELECT * FROM sysobjects WHERE type = 'P'

List all the Function in the Database

SELECT * FROM sysobjects where type='fn'

Above syntax work correct in different version of sql server. Hope it will be useful.

SQL Script to Find Week Days of a Year and J-Query

hi,go through below links

Get Date of All Weekdays or Weekends of the Year(By Pinal Dave)

Change Image Opacity on MouseOver using jQuery (Suprotim Agarwal)

True paging using SQL script

The system gets overloaded when we fetch complete data from the database and display a fixed sized of
data per page.

Although the data is distributed among different pages however it is fetched at a longer period of time

An efficient way is to fetch data per page.

Following example helps you search news and display results accordingly by stored procedure.

CREATE PROCEDURE [dbo].[News_Search]
@SearchText nvarchar(100) --Search text,what we want ot search in news.
,@PageNo int ----Page number
,@PageSize int-----Page size
,@TotalRecords int output--Total how many records are there in search result
AS
BEGIN

SET NOCOUNT ON;
DECLARE @StartFrom int
SET @StartFrom = @PageSize * (@PageNo - 1);
SET @TotalRecords=(SELECT ISNULL(COUNT(*),0)FROM ViwNews
WHERE ViwNews.NewsIsDisable <> 0 and
@SearchText Is Null OR ViwNews.NewsTitle Like @SearchText);

SELECT searchtable.*
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY ViwNews.NewsTitle)AS [RecNo]
,ViwNews.*
FROM ViwNews
WHERE ViwNews.NewsIsDisable <> 0 and
@SearchText Is Null OR ViwNews.NewsTitle Like @SearchText
) AS searchtable
WHERE searchtable.RecNo > @StartFrom And searchtable.RecNo <=(@StartFrom+@PageSize);

END

In above query we have 4 parameters.

1) @SearchText used to pass for the search criteria.
2) @PageNo used to fetch which page number we want to fetch from the result.
3) @PageSize used to determine page size
4) @TotalRecords used to create number of pages
We can determine number of pages by dividing total records with page size.

In query we have used ROW_NUMBER() function which gives Record number obtained in the result sequentially.

Read RowStamp column value from the SQL Database

Hey! have you ever face the problem to read the Value of "RowStamp" data type column.

When you read value through the datareader you will have that value in the data reader but in the .net Row stamp is treated as the array of Byte types

so you need to read the value of reader by

GetSqlBinary(columnindex) method and also needs to convert to Array of bytes then it will work.

Cheers..

Import Data from EXcel Sheet to SQL Server

To get the data from Excel sheet to SQL Server we can use many of SQL server services.
Before we import data using Excel Sheet ..We have to reconfigure our service with below queries..

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Below query will return data from your Excel file and sheet1.
some of the attributes we have used "Microsoft.Jet.OLEDB.4.0" it is driver name. and spcify Excel 8.0.

In database we have specified our file path.

In that HDR specifies that whether our excel sheet can treat first row as Header or not. If we have set it to NO then OLEDB provider assign Column Names F1 to Fn. Same thing will happen if we have not included HDR in our connection string.

In Excel sheet it is possible that column have mixed data type. By default OLEDB provider see the column's value and then depending upon it defined datatype on that column.

If column have numeric value then it will omit anyother value in column and set null for other values.To over come this problem we can use IMEX attribute which brings all data regardless of its type.

If we have set IMEX we will have all data as we have in Excel sheet.


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

How to add Foreignkey Constraints to table?

Hello,

Some time we want to add foreign key constraints to table with out being open up table structure.


This is some useful syntax to add foreign key constraints while we are going to add any new column to table.

ALTER TABLE tblEmployee
ADD CityId INT
CONSTRAINT Fk_tblEmployee_tlkpCity
FOREIGN KEY (CityId)
REFERENCES tlkpCity(lkpTypeId)

Here we have tblEmployee , we want to add new cityId column and this will be foreign key for table tlkpCity.

Can't ALTER COLUMN to IDENTITY

If you ever try to alter existing primary column with integer datatype to identity from sql script you will get error.

Because you cant alter column whose value is filled prior.

you have options like

1) Create new table with same name and now set that column as identity

2) drop old table

But visual studio gives you abilty to do it in design time. In design just modify table structure and set identity to yes.

Internally visual management studio drop your old table and create new table with same name and structure now will copy old data to this new table.

Keep in mind you can't set Identity by altering existing column.

Raise DBConcurrency Exception FROM DataAdapter

Hello!

In .Net Ado.net provides many features of database operation and also you can have disconnected architecture so you have good performance.

But for Concurrency you have to take care of one thing.

When you have used storedprocedure for command and want to raise DBConcurrency Exception then it will not raise.

For that you have to set 

SET NOCOUNT OFF; in stored procedure.

Then when you are going to update dataset with DataAdapter's update method. It will raise DBConcurrency Error If no row is affected in stored procedure.

Please do this by testing. As just set SET NOCOUNT ON and same code will not generate any Exception.

This little thing will save many time to handle concurrency and also reduce many line of code.

Remove Data Concurrency From SQL SERVER

First what mean by Data Concurrency.

If our appliction is used by many users. More than one user has opened up a record and simaltenously all user are trying to update that record.

So by whom saved is done in database. If in noraml condition that only detect concurrency when both will save at same time otherwise last save is made to database.

But the solution is that once first user saved record to database then other will not be able to save and inform him that record is changed by some one so please reload that changes.

In this case we have one solution is to use timestamp data field in database table.

timestamp filed will be modified each time when we are changing record. 

Lets have a look on some sample

I have a table Person in my database with fileds

• ID 
• Name
• Notes
• EditStamp  
  

Here EditStamp is timestamp field.

When you will going to add record you simple need to write

INSERT INTO Person (Name,Notes) VALUES ('AAAA','First Person')

as we have kept ID as autoincrement and EditStamp will be added by SQL server you will not to worried about its.

Now when you try to update record. then you will also pass EditStamp of that record

 @EditStamp timestamp

 @ID int

SELECT @EditStamp = EditStamp FROM Person WHERE ID = @ID

UPDATE PERSON 

SET Name ='BBBB'

WHERE  ID = @ID AND EditStamp = @EditStamp

UPDATE PERSON 

SET Name ='CCCC'

WHERE  ID = @ID AND EditStamp = @EditStamp

When you will execute above statement you will surprised that only Name is update with 'BBBB'

It will not update with 'CCCC'

Because after first update value of EditStamp will be changed. 

So with this we can overcome data concurrency problem we can also raise some error and detecting it.

Get Current Identity Value In SQL

Table that contain auto increment field as their primary key, we won't provide that value at the time of insert of data. So after insertion done we need to know which value is assigned to our primary key.

So at the time of insertion we will provide some output parameter as 

@ID AS INT 

After insertion statement just write 

SET @ID = @@IDENTITY 

OR

SET @ID = SCOPE_IDENTITY()

OR

SET @ID = IDENT_CURRENT

above all returns value inserted into identity columns.

Reassing AutoIncrement Field Value

If we have deleted all values in a table that contains auto-increment field. Now if we add any values to table then autoincrement field value would be start from last deleted value + step of increment

Say an example 

you have last value of auto increment field is  10 in deleted records and now want to add new record then increment value would be 11 if step is 1.

So what to do if we want to reevalutate value of autoincrement to 1.

Just fire below syntax 

DBCC CHECKIDENT(table Name,RESEED,0)

This will reevalute value of autoincrement field to 0.

Hope this will be useful ....

Guid Datatype In Access

Many of us have faced this problem. As In SQL Server we have data type Guid but when we use Access as our database. We will wonder for this datatype.

For that we need to use some tric. Its availble but we have not tried so we could not able to use it in access.

For Guid Data Type What should you need to do is as follows:

1) Add Field in design view of table

2) Set datatype of field to Number 

3) In Field properties shown below , set Field Size to "Replication ID" 

Now you have Guid in your access database.

ALTER TABLE Syntax

to change table column datatype, add new column, remove column you can use below syntax rather than to modify from design view of table.

--add new column
ALTER TABLE "Tablename"
ADD  "ColumnName" "datatyepe" "constraints"
 
 --alter table column
ALTER TABLE "TableName"
ALTER COLUMN "ColumnName" "datatype" "constraints"
 
 --drop/remove column from table
ALTER TABLE "TableName"
DROP COLUMN "ColumnName"

datatype can be any valid datatype of sql and constraints is any like not null, null, primary key etc..