SQL SERVER 2008 -FileStream Data Types

FileStream Data Types

FileStream data types are a new, interesting feature in SQL SERVER 2008. Basically,

the database engine will store all of the data associated with the column in a disk

file as opposed to the actual database. You might have used a similar home-grown

scheme in earlier versions of SQL, but this integrates everything nicely into

SQL Server.

In order to use FileStream, you must first enable it. This is accomplished via the

sp_FILESTREAM_configure

Management Studio under

Once FileStream is enabled, a file group must be added to the database in order

for it to be able to use FileStream data types.

FileStream has the following limitations:

system stored procedure, or via the GUI inadvanced settings.

■■

 

■■

 ■■

I hope it will help .Native encryption is not possible by SQL Server for FileStream data.

Database snapshots are not supported for FileStream data.

Database mirroring cannot be configured in databases with FileStream data.

SQL SERVER : update command

The SQL UPDATE general syntax looks like this:
UPDATE Table1

SET Column1 = Value1, Column2 = Value2

WHERE Some_Column = Some_Value
Database: Adventure Works
Schema: Person
Table: Address
Example:

Update Address

set AddressLine1 ='123 – jkv road'

Where AddressID = 10

The SQL UPDATE clause changes the data in already existing database row(s) and usually we need to add a conditional SQL WHERE clause to our SQL UPDATE statement in order to specify which row(s) we intend to update.

If we don’t specify a WHERE clause in the SQL expression above, all Address AddressLine1 will be updated to '123 – jkv road', so be careful with the SQL UPDATE command when you use.

We can update several database table rows at once, by using the SQL WHERE clause in our UPDATE statement. For example if we want to change the PostalCode for all Address with City  'Bothell' (we have 2 in our example Address table), we need to use the following SQL UPDATE statement:
Update Address set PostalCode = 98011 Where City  = 'Bothell'

SQL Server :- SQL INSERT INTO syntax

Hi dear fnds  welcome to know about sql insert into command :-

Its very easy follow  below note :-

The SQL INSERT INTO syntax has Two main use and the result of either of them is adding a new rows into the database table.
The first syntax and example form of the INSERT INTO SQL clause doesn't specify the column names where the data will be inserted, but just their values:
INSERT INTO Table1 VALUES (value1, value2, value3…)
insert into table2 values (1,'jaiverma','DBA')
The second form of the SQL INSERT INTO command, specifies both the columns and the values to be inserted in them:
INSERT INTO Table1 (Column1, Column2, Column3…)
VALUES (Value1, Value2, Value3…)
insert into table2 (employee_id, emp_name, emp_role)
values(1,'jaiverma','DBA')
As you might already have think, the number of the columns in the second INSERT INTO syntax form must match the number of values into the SQL statement; otherwise you will get an error.
If we want to insert a new row into our Address table, we are going to use one of the following Two SQL statements:

Database: Adventure Works
Schema: Person
Table: Address

1.

INSERT INTO address

VALUES (5, '1226 Shoe St.', 'India', 'Dehli',79,98011,'FBAFF93',

'1999-01-20 00:00:00.000')

2.

INSERT INTO

address(AddressID,AddressLine1,AddressLine2,City,StateProvinceID,

PostalCode,rowguid,ModifiedDate)

VALUES (5, '1226 Shoe St.', 'India', 'Dehli',79,98011,'FBAFF93',

'1999-01-20 00:00:00.000')

The result of the execution of either of the 2 INSERT INTO SQL statements will be a new row added to our Address database table.
 If you want to enter data for just a some of the table columns, you’ll have to use the second syntax form of the SQL INSERT INTO clause, because the first form will produce an error if you haven’t supplied values for all columns.
To insert only the AddressID and Addressline1 columns, execute the following SQL statement:

INSERT INTO

address(AddressID,AddressLine1) VALUES (5, '1226 Shoe St.')

SQL SERVER :- WHERE Clause

SQL WHERE

IT IS VERY SIMPLE AND EASY TO LEARN ABOUT WHERE CLAUSE....

The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query. We are going to use the address table, to illustrate the use of the SQL WHERE command.
Database: Adventure Works
Schema: Person
Table: Address

If we want to select all customers from our database table, having addressID '5' we need to use the following SQL Example:
Database: Adventure Works
Schema: Person
Table: Address

select * from AdventureWorks.Person.Address where AddressID =5

 -- or --

 select * from Address where AddressID =5



In this simple SQL query we used the "=" (Equal) operator in our WHERE criteria:
AddressID =5

But we can use any of the following comparison operators in conjunction with the SQL WHERE clause:
Database: Adventure Works
Schema: Person
Table: Address

<> (Not Equal)
select * from Address where AddressID <>5

> (Greater than)
select * from Address where AddressID >5

>= (Greater or Equal)
select * from Address where AddressID >=5

< (Less than)
select * from Address where AddressID <5

<= (Less or Equal)
select * from Address where AddressID <=5
LIKE (similar to)
 
select * from Address where Addressline1 like ‘1226 Shoe St.’
Note the LIKE syntax is different with the different RDBMS (SQL Server syntax used above). Check the sql Like  article for more details.
Between (Defines a range)
 
. select * from Address where AddressID between 5 and 10

SQL Server :- DISTINCT Keyword

SQL DISTINCT
The SQL DISTINCT clause is used together with the SQL SELECT keyword, to return a dataset with unique entries for certain database table column.




For example if we want to select all distinct ReorderPoint from our Address table, we will use the following SQL DISTINCT statement:

SELECT DISTINCT LastName FROM Customers

Database: Adventure Works

Schema: Person

Table: Address

select distinct(ReorderPoint) from AdventureWorks.Production.Product
-or-
select distinct(ReorderPoint) from  Product

Diff type of SELECT Command In sql

SQL SELECT

The SQL SELECT statement is used to select or fetch data from a SQL database table. This is usually the very first SQL command every SQL new person learns and this is because the SELECT SQL statement is one of the most used SQL commands.
Please have a look at the general SQL SELECT syntax and example:
Syntax:           SELECT Column1, Column2, Column3, FROM Table1
Database:       Adventure Works
Schema:          Person
Table:              Address
Query:            select AddressID,AddressLine1  from Person.Address



The list of column names after the SQL SELECT command determines which columns you want to be returned in your result set. If you want to select all columns from a database table, you can use the following SQL statement:

Database: Adventure Works
Schema: Person
Table: Address
Syntax:           SELECT * FROM Table1
Example:        select *  from Person.Address 
-or-
            select *  from Address 



When the list of columns following the SELECT SQL command is replaced with asterix (*) all table columns are returned. Word of caution here, it’s always better to explicitly specify the columns in the SELECT list, as this will improve your query performance significantly.
The table name following the SQL FROM keyword (in our case Table1) tells the SQL interpreter which table to use to retrieve the data.

How to get Current Database Name in SQL Server ?

Hi this very interesting thing to know  the name of current database in sql server

Plz check the query

It was very simple script

.

SELECT DB_NAME() AS DataBaseName

It will give you the name the database you are running using while running the query.

Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server 2000/05/08

Hi ....

 It is very very simple any body can do it..... follow below post .... and enjoy with sql server

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO
Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO

What is Fill Factor and What is the Best thing for Fill Factor

Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. 

In an SQL Server, the smallest unit is a page, which is made of  Page with size 8K. Every page can store one or more rows based on the size of the row. 

The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. 

There will be no or very little empty space left in the page, when the fill factor is 100.

How to Select Command use in sql server 2008

The Base of Relational Database Management System is a database object called table. Each database consists of one or more tables, which store the database’s data/information. Each table has its own unique name and consists of columns and rows.
The database table columns (called also table fields) have their own unique names and have a pre-defined data types. Table columns can have many attributes defining the column functionality (the column is a primary key, there is an index defined on the column, the column has certain default value, etc.).
While table columns describe the data types, the table rows contain the actual data for the columns.
Here is an example of a simple database table, containing Address data. The first row, listed in bold, contains the names of the table columns:
Database: Adventure Works
Schema: Person
Table: Address
Query : select * from Person.Address
Output:

So Friends Now that we’ve learned what is a database table, we can continue with our MSSQL  tutorial and learn how to manipulate the data within the database tables.