Hello reader i have come with the new blog post in this blog i have discribed about the some conman syntax for Sql server using with query like Create database, Alter database, Drop Database, Attach a database, Create a database snapshot, Create table, Alter table, Delete Table, Insert Table, using Select query to insert into table and many more.. i have write this post for inspired to learn from microsoft MSDN and other book.
CREATE DATABASE
Syntax
CREATE DATABASE dbnm
[ CONTAINMENT = { NONE | PARTIAL } ]
[ ON
[ PRIMARY ] <filespec> [ ,...n ]
[ , <filegroup> [ ,...n ] ]
[ LOG ON <filespec> [ ,...n ] ]
]
[ COLLATE collationName ]
[ WITH <option> [,...n ] ]
[;]
<option> ::=
{
FILESTREAM ( <filestreamOption> [,...n ] )
| DEFAULT_FULLTEXT_LANGUAGE = { lcid | languageName | languageAlias }
| DEFAULT_LANGUAGE = { lcid | languageName | languageAlias }
| NESTED_TRIGGERS = { OFF | ON }
| TRANSFORM_NOISE_WORDS = { OFF | ON}
| TWO_DIGIT_YEAR_CUTOFF = <twoDigitYearCutoff>
| DB_CHAINING { OFF | ON }
| TRUSTWORTHY { OFF | ON }
}
<filestreamOption> ::=
{
NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
| DIRECTORY_NAME = 'dirName'
}
<filespec> ::=
{
(
NAME = logicalFileName ,
FILENAME = { 'osFileName' | 'filestreamPath' }
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { maxSize [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growthIncrement [ KB | MB | GB | TB | % ] ]
)
}
<fileGroup> ::=
{
FILEGROUP filegroup name [ [ CONTAINS FILESTREAM ] [ DEFAULT ] | CONTAINS MEMORY_OPTIMIZED_DATA ]
<filespec> [ ,...n ]
}
ATTACH DATABASE
CREATE DATABASE dbNm
ON <filespec> [ ,...n ]
FOR { { ATTACH [ WITH <attachDatabaseOption> [ , ...n ] ] }
| ATTACH_REBUILD_LOG }
[;]
<attachDatabaseOption> ::=
{
<serviceBrokerOption>
| RESTRICTED_USER
| FILESTREAM ( DIRECTORY_NAME = { 'directoryName' | NULL } )
}
DATABASE SNAPSHOT
CREATE DATABASE databaseSnapshotName
ON
(
NAME = logicalFileName,
FILENAME = 'osFileName'
) [ ,...n ]
AS SNAPSHOT OF sourceDatabaseName
[;]
EXAMPLE
here i give you a example of the create database
USE master;
GO
CREATE DATABASE Emp
ON
( NAME = Emp_dat,
FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL13.MSSQLSERVER\MSSQL\DATA\mpdat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Emp_log, FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL13.MSSQLSERVER\MSSQL\DATA\Emplog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO
ALTER DATABASE
Syntax
ALTER DATABASE { dbnm | CURRENT }
{
MODIFY NAME = newDatabaseName
| COLLATE collationName
| <file_andFilegroupOptions>
| <set_databaseOptions>
}
[;]
<file_andFilegroupOptions >::=
<addOrModifyFiles>::=
<filespec>::=
<addOrModifyFilegroups>::=
<filegroupUpdatabilityOption>::=<setDatabaseOptions>::=
<optionspec>::=
<autoOption> ::=
<changeTrackingOption> ::=
<cursorOption> ::=
<databaseMirroringOption> ::=
<dateCorrelationOptimizationOption> ::=
<dbEncryptionOption> ::=
<dbStateOption> ::=
<dbUpdateOption> ::=
<dbUserAccessOption> ::= <delayedDurabilityOption> ::= <externalAccessOption> ::=
<FILESTREAMOptions> ::=
<HADROptions> ::=
<parameterizationOption> ::=
<queryStoreOptions> ::=
<recoveryOption> ::=
<serviceBrokerOption> ::=
<snapshotOption> ::=
<sqlOption> ::=
<termination> ::=
EXAMPLE
USE master;
GO
ALTER DATABASE Emp
Modify Name = EmpDb;
GO
DROP DATABASE
SYNTAX
DROP DATABASE { databaseName | databaseSnapshotName } [ ,...n ] [;]
EXAMPLE
DROP DATABASE EmpDb;
CREATE TABLE
SYNTAX
CREATE TABLE tableName(
col1 datatype,
col2 datatype,
.....
colN datatype,
PRIMARY KEY( one or more columns )
);
EXAMPLE
CREATE TABLE Emp(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
ADDRESS VARCHAR (250) ,
PRIMARY KEY (ID)
);
ALTER TABLE
SYNTAX
ALTER TABLE table_name
ADD column_name datatype
EXAMPLE
alter table emp add Age int(5)
SYNTAX
ALTER TABLE table_name
DROP COLUMN column_name
EXAMPLE
alter table emp drop Age
SYNTAX
ALTER TABLE table_name
ALTER COLUMN column_name datatype
EXAMPLE
alter table emp alter Age nvarchar(4)
SYNTAX
INSERT [INTO] tableORview [(columnList)] dataValues
EXAMPLE
INSERT INTO emp(ID, Name)
VALUES (1, 'krunal');
SELECT TABLE WITH OPTIONS
SYNTAX
SELECT *|<field1>,<field2> FROM <Table_NAME>
Example
select * from Emp
OR
SELECT TOP 1000 [ID]
,[Name]
,[Address]
FROM [EMPDB].[dbo].[Emp]
Copy all the columns from one table to existing another table
INSERT INTO Emp2
SELECT * FROM Emp1;
Copy only mention columns we want to insert into existing another table
SYNTAX
INSERT INTO Emp2
(columns))
SELECT columns
FROM Emp1;
EXAMPLE
INSERT INTO Emp2(ID,Name)
SELECT ID,Name FROM Emp1
WHERE Address='Surat';
UPDATE TABLE
SYNTAX
UPDATE tableName
SET column1=value1,column2=value2,...
WHERE column=value;
EXAMPLE
UPDATE Emp
SET Name='Nil', Address='Dubai'
DELETE TABLE
SYNTAX
DELETE FROM tableName
WHERE column=value;
EXAMPLE
DELETE FROM Emp
WHERE Name='Nil' AND Address='Dubai';
WHERE Name='Nil';
i have all the syntax are make using to learn and reference from Microsoft MSDN .
know more about the Sql please visit or refer to MSDN site.
Learn About more please visit Microsoft MSDN Site
so i complete my post now and i come with another post later,any help regarding this post and any query please leave the comment or contact me via email,i try to solve your issue as soon as possible
Thanks
Krunal patel
(Software Developer)
My Popular post click on Link to view
- Introduction of MS SQL Server
- Create a Database in MS Sql Server 2008 R2
- Create a View in MS Sql Server 2008 R2
- Create a New Database User In Sql Server 2008 R2
- Create a New Stored Procedure in Sql Server Step by Step
- Create a Database Backup in Sql Server 2008 R2
- Restore Database in Sql Server 2008 R2
- Sql Server 2008 R2 Connect Via Internet
