SQL Server Interview Question:Table

1.) How many character sql server table can have?

table_name can contain a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.

2. What is the distinction between sql server tablename and column name?

The combination of owner.table_name must be unique within the database.

Column names must conform to the rules for identifiers and must be unique in the table.

3. Which occurrences you can omit column name in sql server table?

column_name can be omitted for columns created with a timestamp data type. The name of a timestamp column defaults to timestamp if column_name is not specified.

4. What is computed column in sqlserver?

Is an expression defining the value of a computed column. A computed column is a virtual column not physically stored in the table. It is computed from an expression using other columns in the same table

5. What is the restriction when defining computed column expression?

a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

6 Where can we use computed columns?

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used

7. Can we create a foreign key or default, not null constraint on computed column?

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition.

8. Can we create an index on computed columns?

a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.

For example, if the table has integer columns a and b, the computed column a+b may be indexed, but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

9. can computed column be the target of insert and update?

A computed column cannot be the target of an INSERT or UPDATE statement.

Note Each row in a table can have different values for columns involved in a computed column; therefore the computed column may not have the same value for each row.

The nullability of computed columns is determined automatically by SQL Server based on the expressions used. The result of most expressions is considered nullable even if only non-nullable columns are present because possible underflows or overflows will produce NULL results as well. Use the COLUMNPROPERTY function (AllowsNull property) to investigate the nullability of any computed column in a table. An expression expr that is nullable can be turned into a non-nullable one by specifying ISNULL(check_expression, constant) where the constant is a non-NULL value substituted for any NULL result.

10.) Why do we specify filegroup when creating database?

Where is your table stored in sql server?

Specifies the filegroup on which the table is stored. If filegroup is specified, the table is stored in the named filegroup. The filegroup must exist within the database. If DEFAULT is specified, or if ON is not specified at all, the table is stored on the default filegroup.

ON {filegroup | DEFAULT} can also be specified in a PRIMARY KEY or UNIQUE constraint. These constraints create indexes. If filegroup is specified, the index is stored in the named filegroup. If DEFAULT is specified, the index is stored in the default filegroup. If no filegroup is specified in a constraint, the index is stored on the same filegroup as the table. If the PRIMARY KEY or UNIQUE constraint creates a clustered index, the data pages for the table are stored in the same filegroup as the index.

11.) Can we specify length to a user-defined datatype when creating table?

You cannot specify a length for a user-defined data type in a CREATE TABLE statement.

12. Which are the columns we cannot have default definition?

DEFAULT

Specifies the value provided for the column when a value is not explicitly supplied during an insert. DEFAULT definitions can be applied to any columns except those defined as timestamp, or those with the IDENTITY property

13.) If we drop the table default definition will remain?

DEFAULT definitions are removed when the table is dropped

14.) What are the value we can use for default definition?

Only a constant value, such as a character string; a system function, such as SYSTEM_USER(); or NULL can be used as a default. To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.

constant_expression

Is a constant, NULL, or a system function used as the default value for the column.

What is identity column in sqlserver?

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table.

15) Which are the datatype suitable for identity column?

The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns.

16) How many identity column can we have per table in sqlserver?

Only one identity column can be created per table

17) Can we specify default definition on identity column?

Bound defaults and DEFAULT constraints cannot be used with an identity column

18) What is the seed and increment in identity column?

You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

Seed:Is the value used for the very first row loaded into the table.

Increment:Is the incremental value added to the identity value of the previous row loaded

19) What is ROWGUIDCOL?

Indicates that the new column is a row global unique identifier column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier column

20) How many unique identifier can we have per table?

Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column.

21) Why do we use NEWID function on unique identifier column?

It also does not automatically generate values for new rows inserted into the table. To generate unique values for each column, either use the NEWID function on INSERT statements or use the NEWID function as the default for the column.

22) Can u guarantee uniqueness of row value on unique identifier column?

The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column

23) What are constraints in sql server?

Is an optional keyword indicating the beginning of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint definition. Constraints are special properties that enforce data integrity and they may create indexes for the table and its columns. Constraint names must be unique within a database.

24) What is NULL | NOT NULL in sql server

Are keywords that determine if null values are allowed in the column. NULL is not strictly a constraint but can be specified in the same manner as NOT NULL.

25) What is PRIMARY KEY in sql server?

Is a constraint that enforces entity integrity for a given column or columns through a unique index. Only one PRIMARY KEY constraint can be created per table.

26) What is UNIQUE constraint?

Is a constraint that provides entity integrity for a given column or columns through a unique index. A table can have multiple UNIQUE constraints.

27) What is FOREIGN KEY constraint?

Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column(s) in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table.

28) What is CHECK constraint?

Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns.

29) What is CLUSTERED | NONCLUSTERED ?

Are keywords to indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED and UNIQUE constraints default to NONCLUSTERED.

You can specify CLUSTERED for only one constraint in a CREATE TABLE statement. If you specify CLUSTERED for a UNIQUE constraint and also specify a PRIMARY KEY constraint, the PRIMARY KEY defaults to NONCLUSTERED

30) What is fillfactor in sqlserver?

Specifies how full SQL Server should make each index page used to store the index data. User-specified fillfactor values can be from 1 through 100, with a default of 0. A lower fill factor creates the index with more space available for new index entries without having to allocate new space.

31) What is cascade?

If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table

32) How many tables sql server can have?

SQL Server can have as many as two billion tables per database

33) How many columns per table sql server can have?

1,024 columns per table

34) What is size of the table in sqlserver?

The number of rows and total size of the table are limited only by the available storage. The maximum number of bytes per row is 8,060. If you create tables with varchar, nvarchar, or varbinary columns in which the total defined width exceeds 8,060 bytes, the table is created, but a warning message appears. Trying to insert more than 8,060 bytes into such a row or to update a row so that its total row size exceeds 8,060 produces an error message and the statement fails.

35) Why sqlvariant column generate warning?

CREATE TABLE statements that include a sql_variant column can generate the following warning:

The total row size (xx) for table 'yy' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.

This warning occurs because sql_variant can have a maximum length of 8016 bytes. When a sql_variant column contains values close to the maximum length, it can overshoot the row's maximum size limit.

36) How many indexes can we have per table?

Each table can contain a maximum of 249 nonclustered indexes and 1 clustered index. These include the indexes generated to support any PRIMARY KEY and UNIQUE constraints defined for the table.

37) What is the order when default, identity, rowguidcol are specified?
SQL Server does not enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints are specified in a column definition.

What is Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:
A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.


When you create local or global temporary tables, the CREATE TABLE syntax supports constraint definitions with the exception of FOREIGN KEY constraints.

If a FOREIGN KEY constraint is specified in a temporary table, the statement returns a warning message indicating that the constraint was skipped, and the table is still created without the FOREIGN KEY constraints.

Temporary tables cannot be referenced in FOREIGN KEY constraints.
Consider using table variables instead of temporary tables.
Temporary tables are useful in cases when indexes need to be created explicitly on them, or when the table values need to be visible across multiple stored procedures or functions. In general,
table variables contribute to more efficient query processing

Notes PRIMARY KEY Constraints Features
A table can contain only one PRIMARY KEY constraint.

The index generated by a PRIMARY KEY constraint cannot cause the number of indexes on the table to exceed 249 nonclustered indexes and 1 clustered index.

If CLUSTERED or NONCLUSTERED is not specified for a PRIMARY KEY constraint, CLUSTERED is used if there are no clustered indexes specified for UNIQUE constraints.

All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.

Notes UNIQUE Constraints Features
If CLUSTERED or NONCLUSTERED is not specified for a UNIQUE constraint, NONCLUSTERED is used by default.
Each UNIQUE constraint generates an index. The number of UNIQUE constraints cannot cause the number of indexes on the table to exceed 249 nonclustered indexes and 1 clustered index.

Notes FOREIGN KEY Constraints Features
When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned.

FOREIGN KEY constraints are applied to the preceding column unless source columns are specified.

FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers.

FOREIGN KEY constraints can reference another column in the same table (a self-reference).
The REFERENCES clause of a column-level FOREIGN KEY constraint can list only one reference column, which must have the same data type as the column on which the constraint is defined.

The REFERENCES clause of a table-level FOREIGN KEY constraint must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.

CASCADE may not be specified if a column of type timestamp is part of either the foreign key or the referenced key.

It is possible to combine CASCADE and NO ACTION on tables that have referential relationships with each other. If SQL Server encounters NO ACTION, it terminates and rolls back related CASCADE actions. When a DELETE statement causes a combination of CASCADE and NO ACTION actions, all the CASCADE actions are applied before SQL Server checks for any NO ACTION.

A table can contain a maximum of 253 FOREIGN KEY constraints.
FOREIGN KEY constraints are not enforced on temporary tables.

A table can reference a maximum of 253 different tables in its FOREIGN KEY constraints.

FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table or in a UNIQUE INDEX on the referenced table.

Notes DEFAULT Definitions Features
A column can have only one DEFAULT definition.

A DEFAULT definition can contain constant values, functions, SQL-92 niladic functions, or NULL. The table shows the niladic functions and the values they return for the default during an INSERT statement.

constant_expression in a DEFAULT definition cannot refer to another column in the table, or to other tables, views, or stored procedures.

DEFAULT definitions cannot be created on columns with a timestamp data type or columns with an IDENTITY property.

DEFAULT definitions cannot be created for columns with user-defined data types if the user-defined data type is bound to a default object.

Notes CHECK Constraints Features
A column can have any number of CHECK constraints, and the condition can include multiple logical expressions combined with AND and OR. Multiple CHECK constraints for a column are validated in the order created.

The search condition must evaluate to a Boolean expression and cannot reference another table.

A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.

CHECK CONSTRAINTS and rules serve the same function of validating the data during INSERT and DELETE statements.
When a rule and one or more CHECK constraints exist for a column or columns, all restrictions are evaluated.

Notes on Additional Constraint Information
An index created for a constraint cannot be dropped with the DROP INDEX statement; the constraint must be dropped with the ALTER TABLE statement. An index created for and used by a constraint can be rebuilt with the DBCC DBREINDEX statement.

Constraint names must follow the rules for identifiers, except that the name cannot begin with a number sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint. The constraint name appears in any error message about constraint violations.

When a constraint is violated in an INSERT, UPDATE, or DELETE statement, the statement is terminated. However, the transaction (if the statement is part of an explicit transaction) continues to be processed. You can use the ROLLBACK TRANSACTION statement with the transaction definition by checking the @@ERROR system function.

If a table has FOREIGN KEY or CHECK CONSTRAINTS and triggers, the constraint conditions are evaluated before the trigger is executed.

38) To Report a table which command do you use?
For a report on a table and its columns, use sp_help or sp_helpconstraint.

39) To rename a table which command do you use?
To rename a table, use sp_rename.

40) What is the use of sp_depends
For a report on the views and stored procedures that depend on a table, use sp_depends.

41) What is the use of sp_spaceused?
Space is generally allocated to tables and indexes in increments of one extent at a time. When the table or index is created, it is allocated pages from mixed extents until it has enough pages to fill a uniform extent. After it has enough pages to fill a uniform extent, another extent is allocated each time the currently allocated extents become full. For a report about the amount of space allocated and used by a table, execute sp_spaceused.