10 things you CAN NOT do in SQL Server.

There are few things you cannot do in SQL server. Surprised?? Try some of following points. Please add your comments to the blog after trying.

1. You cannot add data and log files in master and model database.
2. You cannot add User Defined File Groups in master, model and tempdb database.
3. You cannot change the recovery model of tempdb database.
4. You cannot take transaction log backups of master database even if the recovery model of master database is changed to Full.
5. You cannot take any backup of tempdb database.
6. You cannot rename any system database.
7. You cannot allocate Transaction log files under File Groups
8. You cannot change the file group of a file once it is set.
9. You cannot create a new database if model database is in use
10. You cannot change the owner of master, model, tempdb or distribution databas

SQL SERVER: commonly used command

SQL SERVER: commonly used command

 Commands to create, modify, and extract data from databases. The following table lists several of the most commonly-used command.

Command	Use To
CREATE DATABASE	Create a new database. CREATE DATABASE EmpMaster;
USE	Specify the database to work in. USE EmpMaster;
SHOW TABLES	Displays a list of tables in a database. SHOW TABLES EmpMaster;
CREATE TABLE	Add a table to the database and create the attributes for records to be added to the table. CREATE TABLE EmpDetails...
INSERT INTO	Add records to the table. A value must be specified for each attribute in the table. To leave an attribute blank, place two commas together. INSERT INTO EmpDetails VALUES ...
DESCRIBE	See a description of a database object. DESCRIBE EmpDetails;
SELECT	Retrieve information from a table. Clauses include: SELECT Name,Address, Salery from EmpDetails...
	WHERE filters using data from a specified field.
	SORT BY sorts displayed data based on an attribute name.
	GROUP BYdetermines how information in a list is grouped.
UPDATE	Change the values in a record. UPDATE EmpDetails Set Name='Anil' where EmpId=15;
DELETE FROM	Remove records from a table. DELETE FROM  EmpDetails WHERE EmpId=15;
ALTER TABLE	Add, change or remove attributes from tables. ALTER TABLE EmpDetails  ADD COLUMN PostCode;
DROP TABLE	Delete an existing table. DROP TABLE EmpDetails;

SQL Server: Global Temporary tables

This SQL Server tutorial explains how to use the GLOBAL TEMPORARY TABLES in SQL Server (Transact-SQL) with syntax and examples.

Description

GLOBAL TEMPORARY TABLES in SQL Server (Transact-SQL) are tables that are created distinct within the SQL Server sessions.

Syntax

The syntax for CREATE GLOBAL TEMPORARY TABLE in SQL Server (Transact-SQL) is:

CREATE TABLE ##table_name

(

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

);

Parameters or Arguments

table_name

The name of the global temporary table that you wish to create. The name of the global temporary table starts with ## characters.

column1, column2

The columns that you wish to create in the global temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Note

• The name of GLOBAL TEMPORARY TABLES are prefixed with ## characters (ie: ##employees).

Example

Let's look at an example of how to create a GLOBAL TEMPORARY TABLE in SQL Server (Transact-SQL).

For example:

CREATE TABLE #EmpDetails

( employee_id INT PRIMARY KEY,

  last_name VARCHAR(200) NOT NULL,

  first_name VARCHAR(200),

);

This example would create a GLOBAL TEMPORARY TABLE called ## EmpDetails in SQL Server which has 4 columns.

• The first column is called employee_id which is created as an INT datatype and cannot contain NULL values.
• The second column is called last_name which is a VARCHAR datatype (50 maximum characters in length) and also can not contain NULL values.
• The third column is called first_name which is a VARCHAR datatype but can contain NULL values.
• The primary key for the #employees table is the employee_id column.

This ## EmpDetails table is stored in tempdb and SQL Server will automatically delete this table when all users referencing the table have disconnected from the SQL Server session.

SQL Server: LOCAL TEMPORARY TABLES

This SQL Server tutorial explains how to use the LOCAL TEMPORARY TABLES in SQL Server (Transact-SQL) with syntax and examples.

Description

LOCAL TEMPORARY TABLES are distinct within modules and embedded SQL programs within SQL Server sessions. LOCAL TEMPORARY TABLES are stored in tempdb and SQL Server automatically deletes these tables when they are no longer used.

Syntax

The syntax to create a LOCAL TEMPORARY TABLE in SQL Server (Transact-SQL) is:

CREATE TABLE #table_name

(

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

);

Parameters or Arguments

table_name

The name of the local temporary table that you wish to create. The name of the local temporary table starts with the # character.

column1, column2

The columns that you wish to create in the local temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Note

• The name of LOCAL TEMPORARY TABLES are prefixed with the # character (ie: #employees).

Example

Let's look at an example of how to create a LOCAL TEMPORARY TABLE in SQL Server (Transact-SQL).

For example:

CREATE TABLE #EmpDetails

( employee_id INT PRIMARY KEY,

  last_name VARCHAR(200) NOT NULL,

  first_name VARCHAR(200),

);

This example would create a LOCAL TEMPORARY TABLE called #EmpDetails in SQL Server which has 4 columns.

• The first column is called employee_id which is created as an INT datatype and can not contain NULL values.
• The second column is called last_name which is a VARCHAR datatype (50 maximum characters in length) and also can not contain NULL values.
• The third column is called first_name which is a VARCHAR datatype but can contain NULL values.
• The primary key for the #employees table is the employee_id column.

This #EmpDetails table is stored in tempdb and SQL Server will automatically delete this table when the SQL Server session no longer requires it

SQL GROUP Functions

SQL GROUP Functions

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table.

For Example: If you want the number of employees in a particular department, the query would be:

SELECT COUNT (*) FROM EmpDetails WHERE dept = 'HR';

If you want the total number of employees in all the department, the query would take the form:

SELECT COUNT (*) FROM EmpDetails

SQL DISTINCT(): This function is used to select the distinct rows.

For Example: If you want to select all distinct department names from EmpDetails table, the query would be:

SELECT DISTINCT dept FROM EmpDetails

To get the count of employees with unique name, the query would be:

SELECT COUNT (DISTINCT name) FROM EmpDetails;

SQL MAX(): This function is used to get the maximum value from a column.

To get the maximum salary drawn by an EmpDetails, the query would be:

SELECT MAX (salary) FROM EmpDetails;

SQL MIN(): This function is used to get the minimum value from a column.

To get the minimum salary drawn by an EmpDetails, he query would be:

SELECT MIN (salary) FROM EmpDetails;

SQL AVG(): This function is used to get the average value of a numeric column.

To get the average salary, the query would be

SELECT AVG (salary) FROM EmpDetails;

SQL SUM(): This function is used to get the sum of a numeric column

To get the total salary given out to the EmpDetails,

SELECT SUM (salary) FROM EmpDetails;