The Benefits And The Differences Between CHAR And VARCHAR Data Types For SQL Server

All of you reading this have encountered these types of data, but have you ever thought about what the differences and advantages of the two?

The obvious difference that comes to mind in length. Yes! You are right. Varchar Char supports of various lengths and supports fixed length. This is the main advantage of the VARCHAR data type. For example, consider the varchar (10) and char (10). You can save the "GEEKEPISODE" Both types of data using the entire length of the target and save the "geek", char data type to use 10 characters in common, but only 4 VARCHAR

From the above examples, it seems better than CHAR VARCHAR all the time. But remember, CHAR was included as a separate type because it has certain advantages over VARCHAR.

VARCHAR use the extra space to store the length of the string stored in it, and mark the end of the string. Considering that the char data type does not use this extra space. This is certainly the advantage of data type CHAR.

Summary:

CHAR

It is used to store a fixed string / character data

It requires no additional space / bytes to capture the length and the end of the string.

Still, if the length of the string data / character is fixed. Example: SSN, systemId, EmpID.

It is faster than VARCHAR, when used in collections and queries.

VARCHAR

Used to store variable-length string.

Requires additional bytes / space to enter the time and the end of the chain.

use, if the length of the data string / character can not be determined at the design stage. Example: name, brief description

 Slower than the CHAR data type when used in collections and queries.

Here the above explanation also applies to data types nchar and nvarchar.

The Script In The Batch File Using Sqlcmd

I often create batch files to run SQL scripts (especially SPS), and then schedule jobs using SQL Agent / Task Scheduler. In this post, I will show you how to create a batch file with SQL commands.

First, the command used to execute SQL queries commnd SQLCMD system. The general syntax of this command is available on the link. I'm going with a single as follows

Server Name sqlcmd-Q-ES-o Query "Output Path"

here

E = secure connection (Windows Authentication)

-Server Name = S

-Q = CommandLineQuery

O = output file path

a simple command prompt can commnad

C: \ Users \ geekEpisodes> sqlcmd-IS "Local-SRV01"-Q "Select top 10 * from MyTestDatabase .. [ForecastHistory]"-o "C: \ New Folder \ output.txt"

All commands you execute the command can be run from batch control / files. to create a batch file with SQL commands to follow the steps below


1: Create a file with a bat or cmd (for example testfile.bat, test.cmd) ...

2: Add the file commands such as

sqlcmd-IS "Local-SRV01"-Q "Select top 10 * from MyTestDatabase .. [ForecastHistory]"-o "C: \ New Folder \ output.txt"

sqlcmd-IS "Local-SRV01"-Q "select count (1) of MyTestDatabase .. [ForecastHistory]"-o "C: \ New Folder \ Output2.txt"

sqlcmd-ES "SRV01 local"-Q "Exec MyTestDatabase .. [spPopulateData]"

3: Now save it.

4: Double-click the file and query the file exeuted. This file can be used in the Task Scheduler and other applications.

String Soundex() And Its Purpose In The SQL

Soundex code was developed to help defeat the effects of all spelling variations, which can occur in similar-sounding name (Smith, Smithe, Smythe, etc.). This allows users to index is based on the Soundex code is based on votes and "no", as it is written.

The Soundex system is not foolproof - after Gough sounding names and Goff (Goff), but a different code is generated for these two names. Soundex can however be more widely used to obtain a desired result (closer to a desired result atleast) This is an algorithm commonly used by many researchers.

Depending on SQL string is available for this and usually come handy. SOUNDEX () function.

It accepts a string (name) as a parameter and returns a 4 character. This code is known as code name soundex / string is sent as a parameter.

For example:

Choose Soundex ('Smith') O / P: S580

Select SOUNDEX ('Smith') O / P: S580

SELECT SOUNDEX ('green') O / P: G652

Another use for this is contrary to a user enters a field where you need to search for specific words, but words can be wrong (even if it can not produce 100% conversion).

Sequence Number As Per Each Group

Use the following to display the sequence numbers as per each group


select
         myGroupID, 
         myGROUPCOUNTER = ROW_NUMBER() OVER (PARTITION BY [myGroupID] ORDER BY [myGroupID])
from
         myGroupTable

Database Files Location in SQL

Use the following to find the location of all database files (.mdf and .ldf) present in SQL server

SELECT physical_name as Path_Name FROM master.sys.master_files

Identity Column in SQL

Use following to make an identity column in a table of SQL database.

CREATE TABLE dbo.TableName(

ID INT IDENTITY(1,1) ,

LittleDescription varchar(50) ,

DetailedDescription varchar(1000)

);

SQL Server Database - Recover From Suspect mode

Sometimes you find your SQL database in suspect mode. This normally happens when your PC abnormally shutdowns or any other major hardware failure. Here i will explain how to recover your precious data from this.

In many cases there will be no change in the size of MDF and LDF files, but more likely LDF is corrupted.

So here is the solution of your problem.

• First make sure that database is in suspect mode
  • Create a new empty database at some safe location
  • Now stop the SQL Server service
  • Replace the old MDF file over newly created
  • Start SQL service and database will try to recover it. (During this database will go to suspect mode)
• Now we will put the database to emergency mode (This can be done by using ALTER DATABASE or following commands)

Sp_configure 'allow updates', 1
GO
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = ''
GO
sp_dboption '', 'single_user', 'true'
GO
Sp_configure 'allow updates', 0
GO
Reconfigure with override
GO

• Try to use DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS) This will rebuild log file and run full repair of the MDF file)

DBCC CHECKDB('', REPAIR_ALLOW_DATA_LOSS)
Go

(*If it gives error use next step)

• Then use DBCC REBUILD_LOG ('','new LDF path') to rebuild the LDF file

DBCC REBUILD_LOG('', 'new LDF path')
Go

• Almost done. Now we will push database to normal mode (Again use either ALTER DATABASE or following commands)
  

Sp_configure 'allow updates', 1
GO
Reconfigure with override
GO
Update sysdatabases set status = 16 where name = ''
GO
Sp_configure 'allow updates', 0
GO
Reconfigure with override
GO

• Restart the engine of Microsoft SQL Server (I have successfully checked on Microsoft SQL 2000)
• Verify your database by checking its tables and data.

* Don't forget to make a daily backup schedule next time :)