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

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.

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)

)

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...

About RDBMS & SQL Concepts....

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What are the Properties of the Relational Tables?

Relational tables have the following six properties:

• Values are atomic.
• Column values are of the same kind.
• Each row is unique.
• The sequence of columns is insignificant.
• The sequence of rows is insignificant.

Each column must have a unique name

What is Normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

What is a Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

 

What is SQL......

Often referred to as Structured Query Language, is a database designed for managing data in relational database management systems (RDBMS). Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.


^History

SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s.
This version, initially called SEQUEL (Structured English Query Language)
The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company.

Maximum How many Tables Can we join in a query

Dear All,

In a Particular query / join maximum How many tables can we join in a query....

A) 100
B) 120
C) 256
D) 530

Well Come to All

Hai,
      Greetings to all, This Blog is Created for Sql Server learners... If you are a Sql server Professional.. If ur having any materials, Scripts, Questions any thing (Related to Sql server) U can post it Here. This will be Very useful for Others who are learning SQL Server...


By
Admin