Showing posts with label INTRODUCTION. Show all posts

Special data types in SQL SERVER

Posted on Wednesday, 9 May 2012 by SQL Developer

Following data types are not so frequently used in SQL Server. These are the special data types which are used only in specific cases.


Xml:  XML data type is used to hold xml data in SQL Server.

Timestamp ( or ) Rowversion: Timestamp or Rowversion is the data type which is used for maintaining the version numbers for the rows in a table. This is not generally used for the variable. RowVersion or TimeStamp data type is used only if data needs to be monitors for each DML operation. When ever any DML operation occurs on the table in which rowversion or timestamp data type is used, then automatically rowversion column number will be incremented.

Cursor: Cursor data type is used to fetch data from a table row by row.
Cursor is a special data type. This cannot used to create columns in a table. This data type should be used only to declare cursor type variable.

Sql_variant: sql_variant data type can store values of various SQL Server supported data types.That is a sql_variant data type column can hold int, char ,text etc., data. This data type can be used to define column data type,declare variables, input/output parameters to stored procedures etc., This data type can hold 8016 bytes of information.

Table:  Table data type is generally used in functions (user defined functions) which return Table as output.

Uniqueidentifier: This data type holds 16 bit GUID information. Uniqueidentifier stores the information in Hexa decimal format.

Hierarchyid: This data type stores the graphical information like organizational charts etc.,


Above data types are not so frequently used.So, much details about these data types are not provided in this article.

Posted in | Leave a comment

Unicode Character data types

by SQL Developer

Unicode Characters data type stores data using UNICODE UCS-2 character set.
General non-Unicode character data types store the date using limited character set, where as Unicode Character data type stores all the characters using Unicode Standard.

Unicode Character data type uses double the space as that of Non-Unicode data types.
That is each character in Unicode character data type used 2 Bytes of space.


Nchar : It’s a fixed length Unicode Character data type. For a field with data type as NCHAR(N), server used 2N bytes of space.

Nvarchar:  It’s a variable length Unicode data type. For a field with data type as NVARCHAR(N) and containing M characters of data, space needed is 2M Bytes.


Both NCHAR and NVARCHAR data types can hold maximum of 4000 Unicode characters only.

Ntext: In order to hold the Unicode data which contains more than 4000 Unicode characters, NTEXT is used . NTEXT database can hold up to 2^30 – 1 = 1,073,741,823 bytes of data.

Posted in | Leave a comment

Binary Character data types

by SQL Developer

Following data types are used to hold the data in binary format

Binary: This is a fixed length Binary data type.

Syntax for using Binary data type is : Binary(N)

N varies from 1 to 8000.

Space required to hold a value with data type Binary(N) is N Bytes.


Varbinary: This is a variable length binary data type.

Syntax for using Varbinary data type is : Varbinary(N)

N varies from 1 to 8000.

Size of the Varbinary data type depends on the number of binary characters in the value.

Varbinary(Max) will be used to hold the binary characters whose length exceeds 8000 bytes.


Image:  This data type will be used to hold variable length binary data which needs space from 0 to 2^31-1 (2,147,483,647) bytes.

Posted in | Leave a comment

Date and Time Data Types

by SQL Developer

 Following data types are used to hold the date and time values in variables/columns.
 
Date : This data type holds only date in the variables. This data type will not hold the time values.

Size of the date data type is : 3 Bytes

Format for Date data type is:  YYYY-MM-DD

Default value for this data type is : 1900-01-01

Date data type can hold the values from 0001-01-01  to 9999-12-31

Time:

This data type holds the time data till 100nano seconds accuracy.

Syntax for Time data type is :

Time(N)
  (OR)
Time

N = Precision (Indicates number of digits after seconds)

Format for the Time Data type with precision 7 is: HH:MM:SS.NNNNNNN

N can vary from 0 to 7

That is Time data type can store the time from 00:00:00.0000000 through 23:59:59.9999999

Size of Time data type is: 5 Bytes

Smalldatetime:

Smalldatetime data type cannot hold the time to milliseconds precison. This data type can hold only time to seconds level.

That is Format for Smalldatetime data type is: YYYY-MM-DD HH:MM:SS

This data type requires 4 bytes of space.

Minimum date that Smalldatetime data type can hold is : 1900-01-01
Maximum that Smalldatetime data type can hold is : 2079-12-31

This data type is not frequently used since the range of the date values it can hold is very limited.


Datetime:

This is most frequently used data type to hold the date and time related data.

Format of the Datetime data type is: YYYY-MM-DD HH:MM:SS.NNN

Minimum and maximum dates that can be accommodated in datetime field is 1/1/1753 and 12/31/9999

Default value for the datetime field is 1900-01-01 00:00:00.000

Size of Datetime data type is 8Bytes.

Datetime2:

Datetiem2 data type is extension for Datetime data type.

Datetime data type will hold only 3 digits after seconds. That is it can hold the time value only till 1000milliseconds precision.

Where as Datetime2 can hold the time to 100nanoseconds precision. This data type will be used when time needs to captured at nanoseconds precision level.

Format for Datetime2 data type is: YYYY-MM-DD HH:MM:SS.NNNNNNN

Syntax for Datetiem2 data type is: Datetime2(N)

N = Precision. This indicates the number of digits after dot(decimal point) in time part.


Size of Datetime2 data type varies based on the Precision value.

If N < 3, then Datetime2 data type used 6 Bytes
If N= 3,4 ; then Datetime2 data type uses 7 Bytes of Space
If N>4, then Datetime2 data type uses 8 Bytes of Space

N value can vary from 0 to 7.

Default value for N is 7.


Minimum and Maximum dates that Datetime2 can hold is 1st Jan, 1753 and 31st Dec, 9999


Datetimeoffset:

Datetimeoffset data type can hold the time value till 100nanoseconds accuracy.

Datetimeoffset have a special feature which differentiates this data type from other date and time data types.  That is Datetimeoffset data type will hold the time zone indicators along with the data and time.

Format for Datetimeoffset data type is :

YYYY-MM-DD HH:MM:SS.NNNNNNN <+/-> hh:mm


Example:

Indian standard time 5hrs 24mins 42 sec 9876542 milliseconds on 1st Jan, 2012 will be displayed as :

2012-01-01 05:24:42.9876542 +05:30

Datetimeoffset data type uses 10Bytes of space.

Posted in | Leave a comment

Character string data types

by SQL Developer

Character string data types are used to hold alpha numeric characters and string of alpha numeric characters.

Following data types are part of Character string data types:

Char : It’s a fixed length character data type.  

Syntax for Char data type is :

Char(N)

N = Number of character that the variable / column should hold.

N can vary from 1 to 8000

Size of the Character data type will be N bytes irrespective of whether N characters  data is accommodated or not in the variable/column.


Varchar : This is also used to hold character data. Since the size of this data type varies based on the data in the variable/column ,this data type is named as Varchar.

Syntax for Char data type is :

Varchar(N)

N = Number of character that the variable / column should hold.

N can vary from 1 to 8000

If a variable holds only M characters in VARCHAR(N) data type , then data base will allocate M bytes for that variable.  (M should be less than N).

Example:

Declare @pChar          CHAR(10)
SET @pChar               =  ‘ABCD’

Declare @pVarChar    VARCHAR(10)
SET @pVarChar   =  ‘ABCD’

In the above example @pChar uses 10bytes of space.
But, @pVarChar uses only 4bytes of space .

Text :  Char and Varchar data types can hold maximum of 8000 character only.
To hold the data which need more space than 8000 character TEXT data type can be used.

Text data type can hold data/string up to 2GB size.

Maximum number of characters allowed in TEXT data type is: 2^31-1 (2,147,483,647)
That is maximum size of TEXT data type is: 2,147,483,647

Posted in | Leave a comment

Approximate Numeric Data types

by SQL Developer

Approximate numeric data types will be used to hold floating precision values.
FLOAT and REAL are the examples for approximate numeric values or floating precision values.
FLOAT data type allows the Floating precision number data with the following valid values: -1.79E + 308 through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
REAL data type allows the Floating precision number data with the following valid values: -3.40E + 38 through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.

Syntax for the FLOAT data types is:

Float (N):

  (OR)

Float

N indicates the number of bits that are used to store the mantissa of the float number

Size of the float data type varies based in the N value.

If N is between 1 and 24, then float data type used 4 bytes.
If N is between 25 and 53, then float data type used 8 bytes.

Default value of N is 53. That is by default float data type used 8 bytes and Real data type uses 4 bytes.

Posted in | Leave a comment

Exact Numeric data types

by SQL Developer

These are the data types which store numeric data. This data base stores both natural numbers and fraction values.

Exact numeric data types are:

BIT              :  This data type stores only 2 values. That is it stores only 0 and 1.
TINYINT    :  This data type is used to hold small natural numbers (< 255)
SMALLINT:  Used to hold small natural numbers (< 32,767)
INT              :  This is frequently used data type. Used to hold natural numbers ( <2^31)
BIGINT       :  Not frequently used. It will hold numbers which are very large. This data       
Type is used to hold integer values which requires more storage space           than it needed for INT.

Following data types will be used to hold currency values.

SMALLMONEY  :  This data type can hold up to currency 214,748.3647.
MONEY                :  This data type is generally used in money calculation.

Note: MONEY and SAMLLMONEY data types can hold the accurate to ten thousandth of monetary units.
That is these data types can hold up to 4 digits after the decimal point


Size of the above data types  in bytes is given below:

Data Type
Size of the data type in Bytes
BIT
1 Byte
TINYINT   
1 Byte
SMALLINT
2 Bytes
INT
4 Bytes
BIGINT
8 Bytes
SMALLMONEY
4 Bytes
MONEY
8 Bytes


Following data types will be used to hold the numeric data which with at most precision.

NUMERIC, DECIMAL:  Both numeric and Decimal data types are functionally same.

Syntax for these data types is as follows:

Numeric(P,S)
Decimal(P,S)

P : Precision
S : Scale

Precision indicates the total number of digits in the data
Scale indicates the number digits after the decimal point.


Example: for the value 1234.567 ; Precision is 7 , Scale is 4

S(Scale) is always less than P(Precision).
Note:  Maximum Precision for numeric and Decimal Data types is 38. Default Precision is 18.

Size of the NUMERIC and DECIMAL Data types vary based on the precision ( P ) Values.

Below table contains the sizes of these data types based on precision:

Data Type
Size of the data type in Bytes
1-9
5
10-19
9
20-28
13
29-38
17

Posted in | Leave a comment

Data types in SQL Server

by SQL Developer

Data is any piece of information which has some meaning or significance.
Data Type indicates the type of data an object holds. In SQL Server Data types are very important for creating tables, for declaring variables etc.,

In SQL Server Data types are broadly classified into the following types based on the type of data it holds

Data types are mainly used for the following purpose:

  • Creating columns in tables and views
  • For declaring variables
  • For passing variables in stored procedures and functions.

Posted in | Leave a comment

How to Setup a Local Server on SQL Server

Posted on Thursday, 3 May 2012 by SQL Developer

I installed SQL Server 2005 in my machine and tried to connect to my Local server.

But I am repeated encountering the following error while connecting to local server (MachineName\SQLEXPRESS).

Error:

error: 26 - Error Locating Server/Instance Specified 

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

This type of error indicates that SQL Server is installed in your machine , But SQL Server is not running your machine.

How to check whether SQL Server is running on your local system?

Here are the steps to Check whether SQL Server is running in local system or not.

  1. Go to Control Panel ---- > Administrative Tools --- >Services 
  2. If SQL Server is running on your system, the you should be able to find SQL Server (<Instance number> in Services.
 If SQL Server is not found in Services , that indicates SQL Server is not running in your local system.
Resolution for this error would be to reinstall the SQL once again.

Check Hardware and Software requirements for installing SQL Server before reinstalling SQL Server in your system.

 

Posted in , | Leave a comment

Minimum Hardware Requirements for SQL SERVER 2005 Installation

by SQL Developer

Software and Hardware requirements for installing SQL Server vary from one version to another version of SQL Server.
Also Software and Hardware requirements are different for 34bit system and 64 bit system.

Here is the list of Hardware and software   requirements for installing QL Server 2005 in both 32 bit system and 64 bit System:

Hardware Requirements:

  1. Computer with Intel or compatible Pentium III 600 MHz.
  2. Minimum of 192 MB of RAM
  3. 100 MB of available hard disk space
  4. SQL Server graphical tools require VGA or higher resolution: at least 1,024x768 pixel resolution.
  5. A Microsoft mouse or compatible pointing device is required.
  6. A CD or DVD drive, as appropriate, is required for installation from CD or DVD media
Software Requirements:
  1. IIS(Internet Information Services ) Should be installed in your System.
  2. Internet Explorer 6.0 or later version
  3. The .NET Framework 2.0


Posted in | 2 Comments

Download SQL Server management studio 2005 for windows xp 32 bit

by SQL Developer

SQL Server 2005 can be downloaded for free from the following path:

http://www.microsoft.com/en-us/download/details.aspx?id=8961




Posted in | Leave a comment

SQL SERVER TABLES (PART - 3)

Posted on Thursday, 29 March 2012 by SQL Developer

So far in the earlier blogs i have discussed various ways to create table. In this blog I will Summarise what all we have learnt in the previous sessions about Tables.

  • Tables are the Building blocks of any database.
  • Data ( any piece of information) in SQL Server or in any other database will be stored in tables.
  • Tables are composed of rows and columns.Data in the table is stored in rows and columns. Each Row and Column in table holds specific information.
  • Command used to create table is : CREATE TABLE < Table Name> ( Columns...)
  • Table creation is a DML Command.

We will learn more about data integrity, normalisation forms of database, Keys, indexes,stored procedures , triggers and many more in the comings sessions.

Keep watching this space for more tips and tricks in SQL Server.

Posted in | Leave a comment

Introduction to SQL SERVER

Posted on Friday, 13 January 2012 by SQL Developer

SQL is an acronym for Structured Query Language. It is a standard language for accessing databases. Microsoft SQL Server is a relational database server(DBMS), developed by Microsoft. It stores and retrieves data as requested by other software applications, be it those on the same computer or those running on another computer across a network.


Microsoft has launched various versions of SQL SERVER since it entered enterprise-level database market.Some of the popular versions of SQL SERVER are:
  • SQL Server 2000
  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2 (SQL Server 2010)
  • SQL Server 2012
Microsoft SQL Server 2000 is a full-featured relational database management system (RDBMS) that offers a variety of administrative tools to ease the burdens of database development, maintenance and administration.Some of the administrative tools in SQL Server 2000 are Enterprise Manager, Query Analyzer, SQL Profiler, Service Manager, Data Transformation Services and Books Online. We will discuss about these tools in deteail in coming sessions.


Microsoft SQL Server 2005  released in October 2005, is the successor to SQL Server 2000. Following are the new features added in SQL SERVER 2005 : .
  • ETL tool (SQL Server Integration Services or SSIS or Business Intelligence Studio)
  • SQL CLR
  • Reporting Server
  • OLAP and data mining server (Analysis Services)
  • Messaging technologies like Service Broker and Notification Services.
  • SQL Server 2005 Supports Xml Data type.
  • It has new indexing algorithms, syntax and better error recovery systems.
Microsoft SQL Server 2008  released in August 2008, is the successor to SQL Server 2005.
Enhancements in SQL Server 2008 are:
  • New data type FILESTREAM is introduced in SQL Server 2008.Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system.
  •  Full-text search functionality is newly added in SQL Server 2008.This simplifies management and improves performance
  • Flat Earth and Round Earth are newly introduced data types to represent Geometric and Geographic data.
  • SQL Server includes better compression features, which also helps in improving scalability.
  • SQL Server 2008 supports the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be built around the Entity Data Model.
Microsoft  SQL Server 2008 R2 or Microsoft  SQL Server 2010  was released on April 2010. New features in SQL Server 2008 R2 are :
  • Master data management or Master Data Services : Its a central management of master data entities and hierarchies.
  • Multi Server Management, a centralized console to manage multiple SQL Server 2008 instances and services including relational databases, Reporting Services, Analysis Services & Integration Services.
  • SQL Server 2008 R2 includes a number of new services, including PowerPivot for Excel and SharePoint, Master Data Services, StreamInsight, Report Builder 3.0, Reporting Services Add-in for SharePoint, a Data-tier function in Visual Studio that enables packaging of tiered databases as part of an application, and a SQL Server Utility named UC (Utility Control Point), part of AMSM (Application and Multi-Server Management) that is used to manage multiple SQL Servers.
Microsoft  SQL Server 2012 is the latest version of SQL Server .It was announced to be last version to natively support OLE DB and instead to prefer ODBC for native connectivity. This announcement has caused some controversy.


Though we have many versions of SQL Server , 2005 and 2008 SQL Server versions are popular.

Here is the comparision of various SQL server versions:



Posted in | 5 Comments

Introduction to Database Management System

Posted on Thursday, 12 January 2012 by SQL Developer

Data is some piece of information that should be stored for future manipulations. The system which provides such a facility is called Database Management System or DBMS.
Data for future purpose can be saved in either a text file or excel sheet or in some other place.
The simplest form to store a data for later retrieval is using a text file.
Example : you may want to store your friends name and phone numbers to use it latter.This kind of storage is called flat file storage or unstructured storage.

But ,flat files are not suitable to large data such as storing employee details in an organization. To overcome this we need some system which should perform the storing,retrieving, manipulating and querying operations on the data and give output to us. This kind of system is called Database Management System.

DBMS : Database Management System which organizes and stores data in a structural way for fast retrieval and for data manipulations.

Some of the well known Database Management Systems are Microsoft SQL Server, Sybase, Oracle, IBM DB2, PostgreSQL, MySQL and SQLite.


Every DBMS has it is own language to write the commands. All database queries are standardized under a common language called Structured Query Language(SQL). SQL is a language used to storing,retrieving, manipulating data in a relational database.SQL also contains statements for defining and administering the objects in a database. SQL is the language supported by most relational databases.

All most all the databases store information in tables. Some of the frequently used terms in SQL are:
Column – A finite unit of data. It is represented by one of the data type.
Row – A collection of Columns.
Tables – Collection of rows.
Database – Collection of tables and other objects.

Posted in | 1 Comment