Thursday, August 25, 2011

Inserting an Image file Using OPENROWSET and BULK

In SQL Server 2005 and 2008, UPDATE and OPENROWSET can be used together to import an
image into a table. OPENROWSET can be used to import a file into a single-row, single-column value.

The basic syntax for OPENROWSET as it applies to this recipe is as follows:

OPENROWSET
( BULK 'data_file' ,
SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB )


CREATE TABLE dbo.Images
(
 ImgID int NOT NULL,
 Img varbinary(max) NOT NULL
)
GO

Next, a row containing the image file will be inserted into the table:

INSERT dbo.Images
(
ImgID,
Img
)
SELECT 1,
BulkColumn
FROM OPENROWSET(BULK
'Path Of the Image File', SINGLE_BLOB) AS x

This next query selects the row from the table:

SELECT Img
FROM Images
WHERE ImgID = 1



Wednesday, August 24, 2011

Using Values As Table Source In Sql Server 2008

Using Values As Table Source In Sql Server 2008

        Insert Multiple Rows without having to retype the initial INSERT table name and column list.Using this same new feature in SQL Server 2008, you can also reference the VALUES list in the FROM clause of a SELECT statement...
        This will Tell US how to reference a result set without having to use a permanent or
temporary table.

Syntax:-

SELECT SrNo, F_Name, L_Name
FROM
(VALUES
(1, 'First_Name', 'Last_Name'),
(2, 'First_Name', 'Last_Name'),
(3, 'First_Name', 'Last_Name'),
(4, 'First_Name', 'Last_Name'),
(5, 'First_Name', 'Last_Name')
Users (SrNo, F_Name, L_Name)

This returns

SrNo    F_Name        L_Name

1        First_Name    Last_Name
2        First_Name    Last_Name
3        First_Name    Last_Name
4        First_Name    Last_Name
5        First_Name    Last_Name

How It Works

This is a new SQL Server 2008 technique for returning a result set to persist
the rows in storage. Breaking down the query, the first row in the SELECT clause listed the column
names:
 

SELECT SrNo, F_Name, L_Name
 
These are not actual column names from a referenced table—but instead are aliased names I
defined later on in the query itself. The next line defined the FROM clause for the data source, followed by a parenthesis encapsulating
the VALUES keyword:
 

FROM
(VALUES
 

The next few lines of code listed rows I wished to return from this query (similar to how I
inserted multiple rows in a single INSERT in the previous Query):
 
(1, 'First_Name', 'Last_Name'),
(2, 'First_Name', 'Last_Name'),
(3, 'First_Name', 'Last_Name'),
(4, 'First_Name', 'Last_Name'),
(5, 'First_Name', 'Last_Name')


Lastly, after the final closing parenthesis for the row list, I defined a name for this data source
and the associated column names to be returned for each column (and to be referenced in the
SELECT clause):
 

Users (SrNo, F_Name, L_Name)

This new technique allowed me to specify rows of a table source without having to actually
create a temporary or permanent table.

Monday, August 22, 2011

Constraint Creation & Dropping Constraints

Syntax For Creating Constraints...

Alter Table <Table_Name>
ADD CONSTRAINT [Constraint_Name] DEFAULT (1) FOR <Column_Name>
GO

Syntax For Dropping The Constraint...

Alter Table <Table_Name>
Drop Constraint [<Constraint_Name>]

To List All the Constraints in a Data Base Here is this Code...

SELECT OBJECT_NAME(OBJECT_ID) AS NameOfTheConstaraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO

Composite Primary Key in Sql Server

Usually we can create One Primary key. Composite Primary key means multiple Column names in a table..
General Primary Key Syntax:-
Create table <Table_name>
(
    Column_names <Data_type>
    --------
   --------
   Primary Key (Column_name)
)

Composite Primary Key  Syntax:-

Create table <Table_name>
(
    Column_names <Data_type>
    --------
   --------
   Primary Key (Column1,Column2)

)

We can Create Foreign Key on Composite Primary Key

Create table <Table_name>
(
    Column_names <Data_type>,
    --------,
   --------,
   Foreign key(TableB Columns) References TableA (Composite Primary Key Columns)

)



Sunday, August 21, 2011

Primary Key On Tables

Primary Key
    The Primary Key Constraint Uniquely identifies each Record in a Table. Primary key Must Contain Unique Values and it won't accept  NULL values.

                                    PRIMARY KEY = UNIQUE + NOT NULL

Each table should have a primary key, and each table can have only one Primary Key..

Syntax For Creating Primary Key..

Method (1) :-

CREATE TABLE SAMPLE
(
   id int identity(1,1) Primary Key,
   First_Name varchar(10) not null,
   Last_Name varchar(10) not null
)

Method (2) :-
CREATE TABLE SAMPLE
(
   id int identity(1,1),
   First_Name varchar(10) not null,
   Last_Name varchar(10) not null
   primary key (id)
)

Method (3):-

CREATE TABLE SAMPLE
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

General Table Definition & Table Creation

Table It's combination of rows,columns and Indexes.. we can perform insertion, deletion, updates on table..

General Syntax for Creating a Table

CREATE TABLE <TABLE NAME>
(
    Column1 <datatype> --- int, varchar(),char(), flloat etc...,
    Column2 <datatype>
    -----
   ------
)

CREATE TABLE SAMAPLE
(
   ID INT,
   COLUMN1 VARCHAR(10)
)

After creating the table we can see the table definition by using the sample query 

Sp_help <table_name> 
         or
Highlight the table and press ALT+F1
         or
Go with the databases and select the database where you created the table and select that table right click on that table click design then we can see the design of the table....

Select database--> select the table -> right click on the table -> select design.....
With these above mentioned commands we can see the deble definition...