What is the order in which the SQL query is executed?
The following is the order of executing SQL query:
The query goes to the shared pool that has information like parse tree and execution plan for the corresponding statement.Then validates the SQL statement and validates the source(table). Acquire locks. Checks all the privileges. Execute the query. Fetch the values for SELECT statement Displays the fetched values. To sum up, the sequence is:
SELECT .........
FROM ..........
WHERE ..........
GROUP BY ...........
HAVING .............
FROM ..........
WHERE ..........
GROUP BY ...........
HAVING .............
Explain how to store pdf file in sql server
Create a column as type ‘blob’ in a table. Read the content of the file and save in ‘blob’ type column in a table.
Or store them in a folder and establish the pointer to link them in the database.
What is index segmentation?
A segment is a part of relational data base and consists of one or more extents. Each extent is further divided into blocks. Every segment has an index for holding all of its data for quick data retrieval. Index segments can be created explicitly by using the CREATE INDEX command. Storage parameters for the extents of the index segment can be specified along with this command.
How to replace double quotes by single quotes in sql server?
Using replace function, the double quotes can be replaced by single quotes. The following is an example:
Select replace('"Kemo"','"','''')
Result : 'Kemo'
Result : 'Kemo'
Can you explain how to persist objects, permissions in tempdb?
The tempdb gets created every time when the SQL Server service restarts.
To persist objects and permissions, first step is to create a new procedure which creates the required objects in tempdb. Mark this procedure as a startup procedure, in order to run every time when SQL Server starts.
By using “sp_procopton”, a system procedure, set the start up option. This will automatically create procedure and runs, when the SQL Server is restarted. The syntax of sp_procoption is
sp_procoption @proc_name, @optionName, @optionValue
where
@proc_name is the name of the procedure
@optionName is StartUp
@optionValue is True.
where
@proc_name is the name of the procedure
@optionName is StartUp
@optionValue is True.
What is bit datatype?
A bit datatype is an integer data type which can store either a 0 or 1 or null value.
Bit datatype is used to store boolean information like 1 or 0 (true or false).
What is lock escalation?
Lock escalation are used to convert row locks and page locks into table locks thereby “escalating” the smaller or finer locks. This increases the system performance as each lock is nothing but a memory structure. Too many locks would mean more consumption of memory. Hence, escalation is used.
SQL Server has efficient ways to enhance scalability of the database system. SQL Server 2000 automatically adjusts the granularity of locking for each table used by a query. Parallel Execution Plans is in placed automatically. This split the processing of a SQL statement into pieces that can be run on a different CPU, thus allowing the complete result set to build faster.
Explain the SQL EXISTS statement. Write SQL syntax for the SQL EXISTS statement along with an example.
If the subquery returns at least one row the EXISTS condition is considered "to be met".
Syntax:
SELECT columns FROM tables WHERE EXISTS ( subquery );
SELECT columns FROM tables WHERE EXISTS ( subquery );
Example:
SELECT EmployeeID, Title FROM HumanResources.Employee WHERE EXISTS
SELECT EmployeeID, Title FROM HumanResources.Employee WHERE EXISTS
(SELECT * FROM HumanResources.EmployeeDepartmentHistory WHERE EmployeeID=HumanResources.Employee.EmployeeID AND DepartmentID=4)
Explain the SQL BETWEEN statement. Write SQL syntax for the SQL BETWEEN statement along with an example.
The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The values can be numbers, text, or dates.
Syntax:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
Example:
SELECT EmployeeID, Title FROM HumanResources.Employee
where employeeid between 1 and 10
Explain the SQL CREATE VIEW statement. Write SQL syntax for the SQL CREATE VIEW statement along with an example.
A view is a virtual table. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
Syntax:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Example:
CREATE VIEW [sample] AS
CREATE VIEW [sample] AS
SELECT empno,ename
FROM essemp
WHERE sal > 3000
select * from sample
Explain the SQL UNION ALL statement. Write SQL syntax for the SQL UNION ALL statement along with an example.
The UNION ALL operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement must have the same number of columns with similar data types. It lists ALL records.
Syntax:
SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
Example:
select ename from dbo.EMP
select ename from dbo.EMP
union all
select ename from essemp
What is the difference between UNION and UNION ALL?
UNION command selects distinct and related information from two tables. On the other hand, UNION ALL selects all the values from both the tables.
Explain the SQL SELECT INTO statement. Write SQL syntax for the SQL SELECT INTO statement along with an example.
The SELECT INTO statement selects data from one table and inserts it into a different table. Used often for creating back up’s.
Syntax:
SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename
SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename
Example:
SELECT * INTO employee_Backup FROM employees
SELECT * INTO employee_Backup FROM employees
Explain the SQL MINUS statement. Write SQL syntax for the SQL MINUS statement along with an example.
It returns all rows in the first query that are not returned in the second query. Each statement must have the same number of fields in the result sets with similar data types.
Syntax:
Select field1, field2, . field_n from tables MINUS select field1, field2, . field_n from tables;
Select field1, field2, . field_n from tables MINUS select field1, field2, . field_n from tables;
Example:
Select salary from employee MINUS select salary from manager
Select salary from employee MINUS select salary from manager
Explain the SQL UNION statement. Write SQL syntax for the SQL UNION statement along with an example.
The UNION operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement must have the same number of columns with similar data types. It eliminates duplicates.
Syntax:
SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
Example:
select ename from dbo.EMP
select ename from dbo.EMP
union
select ename from essemp
Explain the SELECT DISTINCT statement. Write SQL syntax for the SELECT DISTINCT statement along with an example
It is used to select unique records from a table. Some tables may have duplicate values for a column. Distinct will eliminate these duplications.
Syntax:
SELECT DISTINCT column_name(s) FROM table_name
Example:
select distinct job from essemp
Explain the SQL GROUP BY statement. Write SQL syntax for the SQL GROUP BY statement along with an example.
It is used with aggregate functions to group the result-set by one or more columns.
Syntax:
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
Example:
select
select
o.Customer, count(*) as ItemCount
from
kemoOrders o
inner join kemoOrderDetails od
on o.OrderID = od.OrderID
group by
o.Customer
Explain the SQL HAVING statement. Write SQL syntax for the SQL HAVING statement along with an example.
It is used to specify some condition along with aggregate functions.
Syntax:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
Example:
select studentid,sum(studentmarks)
select studentid,sum(studentmarks)
from studenttotalmarks
group by studentid
having sum(studentmarks)<250
Explain the SQL TOP statement. Write SQL syntax for the SQL TOP statement along with an example.
It is used to specify the number of records to return. Usually used for large tables.
Syntax:
SELECT TOP number|percent column_name(s) FROM table_name
SELECT TOP number|percent column_name(s) FROM table_name
Example:
SELECT TOP 2 * from essemp
SELECT TOP 2 * from essemp
Explain the SQL LIKE statement. Write SQL syntax for the SQL LIKE statement along with an example.
It is used for pattern matching. % is used to match any string of any length where as _ allows you to match on a single character.
Syntax:
SELECT * FROM table_name WHERE column_name like 'pattern%';
Example:
SELECT * FROM essemp WHERE emp_name like 'ma%';
SELECT * FROM table_name WHERE column_name like 'pattern%';
Example:
SELECT * FROM essemp WHERE emp_name like 'ma%';
Explain the SQL IN statement. Write SQL syntax for the SQL IN statement along with an example.
The IN operator allows you to specify multiple values in a WHERE clause.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
Example:
SELECT * FROM essemp
SELECT * FROM essemp
WHERE ename IN ('james','jones')
Explain the SQL NOT NULL statement. Write SQL syntax for the SQL NOT NULL statement along with an example.
The NOT NULL constraint enforces a column to NOT accept NULL values. This means a column with not null always needs to have a value. This means that you cannot insert a new record, or update a record without adding a value to this field
Example:
CREATE TABLE kemoessemp(
id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(10),
last_name VARCHAR(10),
salary DECIMAL(10,2),
start_Date DATETIME,
region VARCHAR(10),
city VARCHAR(20),
managerid INTEGER
)
Explain the SQL CONCATENATE statement. Write SQL syntax for the SQL CONCATENATE statement along with an example.
This clause combine together (concatenate) the results from several different fields.
Syntax:
CONCAT(str1, str2, str3, ...):
CONCAT(str1, str2, str3, ...):
Example:
SELECT first_name+last_name as fullname,salary FROM kemoessemp WHERE salary < 5000
Explain the SQL SUBSTRING statement. Write SQL syntax for the SQL SUBSTRING statement along with an example.
The Substring function in SQL is used to capture a portion of the stored data
Syntax:
SUBSTR(str,pos,len): Starting with the th character in string and select the next characters.
SUBSTR(str,pos,len): Starting with the th character in string and select the next characters.
Example:
SELECT substring(first_name,1,3) FROM kemoessemp WHERE first_name = 'Robert'
SELECT substring(first_name,1,3) FROM kemoessemp WHERE first_name = 'Robert'
Explain the SQL INTERSECT statement. Write SQL syntax for the SQL INTERSECT statement along with an example.
It allows combining results of two or more select queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Syntax:
Select field1, field2, . field_n from tables INTERSECT select field1, field2, . field_n from tables;
Select field1, field2, . field_n from tables INTERSECT select field1, field2, . field_n from tables;
Example:
Select salary from employee INTERSECT select salary from manager;
Select salary from employee INTERSECT select salary from manager;
Collation is the order that SQL Server uses for sorting or comparing textual data. The temporary database is a system database that inherits it’s collation order from the master. If this temp table is compared to a real one, SQL Server will inform that it cannot resolve collation conflict on = operation.
To solve this, the tables should be created using the COLLATE keyword.
The COMPUTE clause is placed at the end of a query to place the result of an aggregate function at the end of a listing.
e.g.: COMPUTE SUM (TOTAL)
e.g.: COMPUTE SUM (TOTAL)
SELECT CustomerID, OrderDate, SubTotal, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = 279
ORDER BY OrderDate
COMPUTE SUM(SubTotal), SUM(TotalDue)
The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group. The WITH TIES clause can do away with this. It returns the records that have the same value even if the number declared after the TOP has been exceeded.
EXAMPLE:-
SELECT TOP ( 5 ) PERCENT WITH TIES JobTitle = Title,
Department = d.Name,
Gender,
HourlyRate = Rate
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
ON e.EmployeeID = edh.EmployeeID AND EndDate is NULL
INNER JOIN HumanResources.Department d
ON edh.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.EmployeePayHistory eph
ON eph.EmployeeID = e.EmployeeID
ORDER BY Rate DESC
What is a join and explain different types of joins.
Joins are used in queries to explain how different tables are related.
Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs.
OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs.
OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
What are different Types of Join?
A join is typically used to combine results of two tables. A Join in SQL can be:-
- Inner joins
- Outer Joins
- Left outer joins
- Right outer joins
- Full outer joins
- Inner join
An inner join looks for matching records taken from one table from another.
A left outer join limits results to the table in left of JOIN.
A right outer join limits results to the table in right of JOIN.Full outer joins are the combination of left and right outer joins
A left outer join limits results to the table in left of JOIN.
A right outer join limits results to the table in right of JOIN.Full outer joins are the combination of left and right outer joins
What is a self join in SQL Server?
Two instances of the same table will be joined in the query.
Explain Nested Join, Hash Join and Merge Join in SQL query plan.
In nested joins, for each tuple in the outer join relation, the system scans the entire inner-join relation and appends any tuples that match the join-condition to the result set.
Merge join
Merge join If both join relations come in order, sorted by the join attribute(s), the system can perform the join trivially, thus: It can consider the current group of tuples from the inner relation which consists of a set of contiguous tuples in the inner relation with the same value in the join attribute. For each matching tuple in the current inner group, add a tuple to the join result. Once the inner group has been exhausted, advance both the inner and outer scans to the next group.
Merge join If both join relations come in order, sorted by the join attribute(s), the system can perform the join trivially, thus: It can consider the current group of tuples from the inner relation which consists of a set of contiguous tuples in the inner relation with the same value in the join attribute. For each matching tuple in the current inner group, add a tuple to the join result. Once the inner group has been exhausted, advance both the inner and outer scans to the next group.
Hash join
A hash join algorithm can only produce equi-joins. The database system pre-forms access to the tables concerned by building hash tables on the join-attributes.
A hash join algorithm can only produce equi-joins. The database system pre-forms access to the tables concerned by building hash tables on the join-attributes.
What is inner join? Explain with an example.
A SQL server Join is helps to query data from two or more tables between columns of these tables. A simple JOIN returns data for at least one match between the tables. The columns need to be similar. Usually primary key one table and foreign key of another is used.
Syntax:
SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
Example:
SELECT StudentDetails.studentID, StudentDetails.StudentName,StudentTotalMarks.StudentMarks
FROM StudentDetails
INNER JOIN StudentTotalMarks
ON StudentDetails.studentID=StudentTotalMarks.studentID
ORDER BY StudentMarks
What is outer join? Explain Left outer join, Right outer join and Full outer join.
OUTER JOIN: In An outer join, rows are returned even when there are no matches through the JOIN criteria on the second table.
LEFT OUTER JOIN: A left outer join or a left join returns results from the table mentioned on the left of the join irrespective of whether it finds matches or not. If the ON clause matches 0 records from table on the right, it will still return a row in the result—but with NULL in each column.
Example:
select * from StudentDetails
select * from StudentDetails
select * from StudentTotalMarks
select studentdetails.studentid,studentdetails.studentname,studenttotalmarks.studentmarks
from studentdetails
left join studenttotalmarks
on studentdetails.studentid=studenttotalmarks.studentid
RIGHT OUTER JOIN: A right outer join or a right join returns results from the table mentioned on the right of the join irrespective of whether it finds matches or not. If the ON clause matches 0 records from table on the left, it will still return a row in the result—but with NULL in each column.
Example:
select studenttotalmarks.studentid,studenttotalmarks.studentmarks,studentdetails.studentname
select studenttotalmarks.studentid,studenttotalmarks.studentmarks,studentdetails.studentname
from studenttotalmarks
right join studentdetails
on studenttotalmarks.studentid=studentdetails.studentid
FULL OUTER JOIN: A full outer join will combine results of both left and right outer join. Hence the records from both tables will be displayed with a NULL for missing matches from either of the tables.
Example: select studentdetails.studentid,studentdetails.studentname,studenttotalmarks.studentmarks
from studentdetails
full join studenttotalmarks
on studentdetails.studentid=studenttotalmarks.studentid
What is built-in function? Explain its type i.e. Rowset, Aggregate and scalar.
A built in function of sql is used for performing calculations. These are standard functions provided by sql.
Aggregate functions: these functions perform calculation on a column and return a single value. Example: AVG(), SUM(), MIN(), MAX()
Scalar functions: these functions perform calculation on an input value and return a single value. Example: ROUND(), MID(), LCASE(), UCASE()
What are the benefits of User-Defined Functions?
a. Can be used in a number of places without restrictions as compared to stored procedures.
b. Code can be made less complex and easier to write.
c. Parameters can be passed to the function.
d. They can be used to create joins and also be used in a select, where or case statement.
e. Simpler to invoke.
What is user-defined function? Explain its types i.e. scalar and Inline table value user-defined function.
User defined functions are created and defined by the user. They are created as per users needs. They may or may not accept parameters. They can be used to create joins and simple to invoke as compared to stored procedures
Types:
Scalar:- returns values as one of the scalar data types. Text, timestamp, image data types are not supported. It may or may not accept parameters.
Scalar:- returns values as one of the scalar data types. Text, timestamp, image data types are not supported. It may or may not accept parameters.
Inline table:- it returns a table data type. These functions can pass parameters to the sql’s SELECT command. They are similar to views except, they can accept parameters.
What is an index?
Indexes of SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two types. Clustered indexes and non-clustered indexes. Rows in the table are stored in the order of the clustered index key.
There can be only one clustered index per table.
Non-clustered indexes have their own storage separate from the table data storage.
Non-clustered indexes are stored as B-tree structures.
Leaf level nodes having the index key and it's row locater
There can be only one clustered index per table.
Non-clustered indexes have their own storage separate from the table data storage.
Non-clustered indexes are stored as B-tree structures.
Leaf level nodes having the index key and it's row locater
Indexes help us to find data faster. It can be created on a single column or a combination of columns. A table index helps to arrange the values of one or more columns in a specific order.
Syntax:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name
Disadvantages of the Indexes.
Use of indexes slow down Data modification operations (such as INSERT, UPDATE, DELETE).
Every time data changes in the table, all the indexes need to be updated.
Indexes need disk space, the more indexes you have, more disk space is used.
Every time data changes in the table, all the indexes need to be updated.
Indexes need disk space, the more indexes you have, more disk space is used.
Define Clustered and Non-Clustered Index.
Clustered index exists as sorted row on disk.
Clustered index re-orders the table record.
Clustered index contains record in the leaf level of the B-tree.
There can be only one Clustered index possible in a table.
Non-clustered is the index in which logical order doesn’t match with physical order of stored data on disk.
Non-clustered index contains index key to the table records in the leaf level.
There can be one or more Non-clustered indexes in a table.
Clustered index re-orders the table record.
Clustered index contains record in the leaf level of the B-tree.
There can be only one Clustered index possible in a table.
Non-clustered is the index in which logical order doesn’t match with physical order of stored data on disk.
Non-clustered index contains index key to the table records in the leaf level.
There can be one or more Non-clustered indexes in a table.
Unique index is the index that is applied to any column of unique value. A unique index can also be applied to a group of columns.
Explain the 'Fillfactor' concept in Indexes.
The fill factor option is provided for smoothing index data storage and performance.
The percentage of space on each leaf level page to be filled with data is determined by the fill factor value When an index is created. This reserves a percentage of free space for future growth
How do you maintain a fill factor in existing indexes?
Usually the data keeps getting added, deleted, or updated in the table due to which the fill factor is implemented during the index creation itself. Fill factor is not maintained after an index is created.
The changes in the future can be determined only when a new index is created on a table that has an existing data. It is beneficial to set the fill factor then itself.
Maintaining extra space on the data pages obviates the purpose of using the fill factor. The SQL Server would then have to split pages to maintain an amount of free space per the fill factor, on each page. Thus, when data is added filling up the empty space, an index can be created and the fill factor can be re-specified distribute the data again.
What is it unwise to create wide clustered index keys?
A clustered index is a good choice for searching over a range of values. After an indexed row is found, the remaining rows being adjacent to it can be found easily. However, using wide keys with clustered indexes is not wise because these keys are also used by the non-clustered indexes for look ups and are also stored in every non-clustered index leaf entry
What is full-text indexing?
Full text indexes are stored in the file system and are administered through the database. Only one full-text index is allowed for one table. They are grouped within the same database in full-text catalogs and are created, managed and dropped using wizards or stored procedures
What is fill factor and pad index?
A fill factor is a specification done during the creation of indexes so that a particular amount of space can be left on a leaf level page to decrease the occurrence of page splits when the data has to be accommodated in the future.
A pad index specifies index padding. When it is set to ON, then the free space percentage per the fill factor
specification is applied to the intermediate-level pages of the index. When it is set to OFF, the fill factor is not specified and enough space is left for a maximum size row that an index can have
Describe important index characteristics.
The characteristics of the indexes are:
- They fasten the searching of a row.
- They are sorted by the Key values.
- They are small and contain only a small number of columns of the table.
- They refer for the appropriate block of the table with a key value
What are the types of indexes?
Types of indexes:
- Clustered: It sorts and stores the data row of the table or view in order based on the index key.
- Non clustered: it can be defined on a table or view with clustered index or on a heap. Each row contains the key and row locator.
- Unique: ensures that the index key is unique
- Spatial: These indexes are usually used for spatial objects of geometry
- Filtered: It is an optimized non clustered index used for covering queries of well defined data
Describe the purpose of indexes.
- Allow the server to retrieve requested data, in as few I/O operations
- Improve performance
- To find records quickly in the database
Determine when an index is appropriate.
- When there is large amount of data. For faster search mechanism indexes are appropriate.
- To improve performance they must be created on fields used in table joins.
- They should be used when the queries are expected to retrieve small data sets
- When the columns are expected to a nature of different values and not repeated
- They may improve search performance but may slow updates.
syntax and an example for create, rename and delete index.
Create Index:
CREATE INDEX index_name ON table_name (col_1,col_2..);
Example:
Create index index_sample ON employee(emp_id)
Rename Index:
ALTER INDEX index_name RENAME to new_index_name ;
Example:
ALTER INDEX index_sample RENAME to new_sample
Delete index:
drop index [schema.]index [force];
Example:
Drop index new_sample
Define stored procedure.
Stored procedure is a set of SQL commands that have been complied and stored on the database sever. They can be used in the code as and when required since they stored. They need not be complied over and over again. They can be invoked by CALL procedure (..) or EXECUTE procedure(..)
Purposes and advantages of stored procedures:
- Manage, control and validate data
- It can also be used for access mechanisms
- Large queries can be avoided
- Reduces network traffic since they need not be recompiled
- Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code
- Permissions can be granted for stored procedures. Hence, increases security.
- If a large piece of code needs to be performed repeatedly, stored procedures are ideal
- When hundreds of lines of SQL code need to be sent; it is better to use stored procedure through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
- When security is required.
An extended stored procedure compiles as DLL and are created to expand capabilties of user defined stored procedure. It uses xp_ prefix as naming convention.
Define Table.
SQL Server database stores information in a two dimensional objects of rows and columns called table.
Local temporary table is created by prefixing name with pound sign like (#table_name). Global temporary table is created by prefixing name with Double pound sign like (##table_name). Local temporary table is dropped when the stored procedure completes. Global temporary tables are dropped when session that created the table ends.
What is Cascade and restrict in Drop table SQL?
RESTRICT indicates that the table should not be dropped if any dependencies exist. If dependencies are found, an error is returned and the table isn't dropped.
Define SQL Server Tables. Explain Create Table syntax with an example.
SQL server tables are used to store data. Each table is divided into rows and columns. Each row has some data against the specified column. Each column has a data type thereby restricting the type of data it can accept.
Syntax:CREATE TABLE "table_name" ("column 1" "data_type_for_column_1", "column 2" "data_type_for_column_2", ... )
Example:create table kemoess(
ID int,
name nvarchar (10),
salary int,
start_date datetime,
city nvarchar (10),
region char (1)
constraint kemoess_pk primary key(id)
)
Explain how to determine column nullability of a table.
The column nullability can be determined if:
a. source table column is nullable: If any of the source columns are nullable, the result column is nullable
b. Source table column is not nullable; the column in the new table is defined as not null: If all the source columns in the expression are not nullable, the result column is not nullable.
Explain how to reference a table from another database in the same server.
A table can be referred from another database on the same server the following way:
Dbname.tablenameExample:
Select * from Dbtest.customer
Dbname.tablenameExample:
Select * from Dbtest.customer
Explain how to rename a table.
The ALTER table statement can be used to rename a table.
ALTER TABLE table_name
RENAME TO new_table_nameExample:
ALTER TABLE customer
RENAME TO cust_details;
ALTER TABLE table_name
RENAME TO new_table_nameExample:
ALTER TABLE customer
RENAME TO cust_details;
Explain how to change the owner of a table.
Owner of a table can be changed by using:
Sp_changeobjectowner [obj_name] ‘object’, [ @newowner = ] 'owner'Example:
Sp_changeobjectowner ‘customer’, ‘Mark’
Here, customer table is the obj_name while Mark is owner.
Sp_changeobjectowner [obj_name] ‘object’, [ @newowner = ] 'owner'Example:
Sp_changeobjectowner ‘customer’, ‘Mark’
Here, customer table is the obj_name while Mark is owner.
Explain autonumbering and identifier column of a table.
Each table can have a unique identifier that uniquely identifies the row. This identifier can be an auto incremented value generated by system. For e.g. Customer_id Identifier columns can contain values unique within the table on which they are defined. This means that other tables with an identifier column can have the same values. A single, unique identifier column can be created for every table. Such columns are typically used for billing system to avoid duplications.
Explain how to determine if a table has a primary key?
A primary key is usually identified by the keyword PRIMARY KEY. Any column that has the keyword (while creating the table) as PRIMARY KEY means the table has a primary key. The values of this column are always unique.
What is globally unique identifier?
A globally unique identifier is sued to provide a unique reference number. This GUID is unique across tables, servers and locations. The GUID values are unique and hence may be large in number. NEWID() can be used to create such unique values.
How can we determine if a column is an identity column?
Using OBJECTPROPERTY function, one can find if the table has an identity column.Syntax:OBJECTPROPERTY(object_id,proeprty)
Here the property will be TableHasIdentity. SQL Server returns 1 if the table has an identity column, 0 if it doesn't.
Here the property will be TableHasIdentity. SQL Server returns 1 if the table has an identity column, 0 if it doesn't.
Using OBJECTPROPERTY function, one can find if the table has an identity column.Syntax:OBJECTPROPERTY(object_id,proeprty)
Here the property will be TableHasPrimaryKey. SQL Server returns 1 if the table has a primary key, 0 if it doesn't.
Here the property will be TableHasPrimaryKey. SQL Server returns 1 if the table has a primary key, 0 if it doesn't.
What are the purposes of table variables?
a. Table variables can be used to store result set in a SQL server. This result set can be used for processing later.
b. Table variables can be used in stored procedures, user defined functions.
c. Table variables give a better performance
d. Table variables can be used in a SELECT, INSERT, UPDATE statements.
e. Unlike temporary variables, they don’t require a declaration or cleaning up
What are some of the drawbacks of table variables?a. SQL server does not maintain a statistics of table variables. This means that how and where these variables were used cannot be determined because they are washed out as soon as the stored procedure or function is finished execution.
b. Table definition of a table variable cannot be changed after a DECLARE statement. Hence, NO ALTER statement will work.
c. Table variables cannot be used In ROLLBACK of transactions
- View can be created to retrieve data from one or more tables.
- Query used to create view can include other views of the database.
- We can also access remote data using distributed query in a view.
In an indexed view, the data is already computed and stored. Data can be accessed by a unique index. This index is a clustered index. In order to create an index the syntax is
CREATE [UNIQUE], [CLUSTERED | NONCLUSTERED] INDEX index_name
ON {view}
[WITH]
[ON filegrp]
ON {view}
[WITH
[ON filegrp]
Partitioned views allow data in a large table to be split into smaller tables. These small tables are called as member tables. The split is done based on range of data values in one of the columns.
In a distributed portioned view, each member table is on a separate member server. This means that the member tables are distributed. To locate these tables easily, the database name on each server should be same.
Functions of View:-
- Subset data of a table
- Can join multiple tables values into one
- They can act as aggregated tables. i.e. a view can be used to store Sum, average of values
- Views can be nested and can be used for abstraction
- Views can subset data in a table
- They can join multiple tables into one virtual table
- Views can provide security and decrease complexity
- They save space because only their definition is stored.
- They can also be used to create abstraction
- Materialized views are commonly used in data warehousing. They represent a snapshot of the data from remote sources.
Views can create other calculated fields based on values in the real underlying tables
An index view has a unique clustered index created on it. They exist as rows on the disk. Because they are saved on the disk, the response time to a query is fast at the cost of space consumption. They are more commonly used in scenarios when data modification is less.
Syntax:
Create Index CREATE [UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name
Create Index CREATE [UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name
The view is created using the CREATE VIEW synatx
Partitioned view:
Partitioned view joins the horizontally portioned data. This data may belong to one ore more servers. It makes the data appear as one table. A portioned view can either be local or distributed. A local portioned view resides on the same instance of the SQL server while the distributed may reside on a different server.
Syntax:
The view is then created by UNIONing all the tables and an updateable partitioned View results
Server 1 :
CREATE TABLE Customer1 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 32999), ... -- Additional column definitions)
CREATE TABLE Customer1 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 32999), ... -- Additional column definitions)
Similar tables created for Server 2 and 3
Partitioned view for server 1CREATE VIEW Customers AS
SELECT * FROM CompanyDatabase.TableOwner.Customer1
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2
UNION ALL
SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3
SELECT * FROM CompanyDatabase.TableOwner.Customer1
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2
UNION ALL
SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3
Print this post
No comments:
Post a Comment