How to get all tables without primary key in SQL Server?

There are many ways to find tables without primary key one of them described below.

Select    Table_CataLog as DB_NAME,    Table_Schema as TBL_SCHEMA,    Table_Name as TBL_NAME      from    information_schema.tables T     where    Not Exists(       Select          1       from          information_Schema.Table_Constraints C           where          Constraint_Type='PRIMARY KEY'              and C.Table_Name=T.Table_Name              and C.Table_Schema=T.Table_Schema    )        and Table_Type='BASE TABLE'

This query will fetch all the table's information which do not have the primary keys.

How to get all tables which have primary key in SQL Server?

Sometimes we need to get all the tables that have primary key constraints. There are many ways to get this information.

Select        ISTC.Table_Catalog as BD_Name,        ISTC.Table_Schema AS TBL_SCHEMA,        ISTC.Table_Name AS TBL_NAME,        ISCCU.Column_Name AS COL_NAME,        ISTC.Constraint_Name AS CONSTRAINT_NAME From        information_Schema.Table_Constraints ISTC  INNER JOIN        Information_Schema.constraint_column_usage ISCCU         on ISTC.Constraint_Name=ISCCU.Constraint_Name         AND ISTC.Table_Name=ISCCU.Table_Name  where        Constraint_Type='PRIMARY KEY'

This one is the best query to find out the tables, which have a primary key because information schema provides more information, and all the data can be retrieved with the filtered filter.

Interview Questions and answers for beginners - SQL Server Day 11

Question: What is SQL?

Answer: SQL stands for Structured Query Language, which is used to retrieve, store, and manipulate data in the Database.

Question: What is data?

Answer: In simple terms, the data can be considered an object of the relevant facts. For example, your name, age, height, weight, image, etc.

Question: What is Database?

Answer: The physical collection of data in a systematic way is called a database. The Database gives permission to manage data like a store, retrieve and manipulate.

In one line Database is the collection data, schemas, tables, queries, reports, views, and other objects.

Question: What is the table?

Answer: Table Is the collection of data that has columns and rows. A table has a specified number of columns but can have any number of rows.

Example: Student table

Name	Address	FatherName	DOB	Class
Dilip	Noida	PB Singh	09-12-2001	10
Anil	GKP	MG Singh	08-10-2002	9
Ashish	Delhi	DK Singh	12-02-2012	UKG

Question: What is the field in a column?

Answer: A column is the set of values. This contains a particular data type value in the table. It shows vertically in the table. For the example above table includes columns Name, Address, FatherName, DOB , and Class. Name is the column that contains the student’s name.

Name

Dilip

Anil

Ashish

Question: What is RDBMS? 

Answer:

A Relational Database management system (RDBMS) is the Database management system in which we maintain data records in tables and relationship among and across The data and table. The relationship among the data is also stored in the form of table.

A Relational Database management system (RDBMS) is an additional condition which system supports a tabular structure of the data, with applies of relationships between tables.

A Relational Database management system (RDBMS) can recombine the data items from different files, providing powerful tools for data usages.

Interview Questions and answers - SQL Server Day 10

Question: Can we sort a column using a column alias in SQL Server?

Answer: Yes, we can sort a column using a column alias in SQL Server.

Example: Execute the following query

CREATE TABLE #TEMP (        FNAME VARCHAR(100)        ,LNAME VARCHAR(100)        ,CREATEDDATE DATETIME, ) INSERT INTO #TEMP SELECT 'DILIP','SINGH',GETDATE() UNION ALL SELECT 'ANIL','SINGH',GETDATE() UNION ALL SELECT 'ASHISH','PRATAP', GETDATE() -- In the following query, FNAME alias is FISRT_NAME. We can apply for Order by clause on an alias as below SELECT FNAME AS FIRST_NAME,LNAME AS LAST_NAME FROM #TEMP ORDER BY FIRST_NAME ASC DROP TABLE #TEMP

Question: What is the difference between NULL value, zero, and blank space in SQL Server? Are these the same?

Answer: NULL value is the difference from zero and blank space, a NULL value is unsigned, unavailable, unknown or not applicable while zero is number, and blank space is the character.

Question: If a table contains duplicates row, how can we eliminate from a query result?

Answer: We can eliminate duplicates rows using DISTINCT keywords.

For Example: Execute following query.

CREATE TABLE #TEMP (        FNAME VARCHAR(100)        ,LNAME VARCHAR(100)        ,CREATEDDATE DATETIME, ) INSERT INTO #TEMP SELECT 'DILIP','SINGH',GETDATE() UNION ALL SELECT 'DILIP','SINGH',GETDATE() UNION ALL SELECT 'ASHISH','PRATAP', GETDATE() SELECT DISTINCT FNAME LNAME FROM #TEMP DROP TABLE #TEMP

Question: What is default sorting ordering of ORDER BY CLAUSE in SQL Server?

Answer:  Default sorting order of ORDER By clause is ascending.

Question: Is the following query will run or not in SQL Server?

SELECT FNAME LNAME, COUNT(*) FROM #TEMP

Answer: No, I will give an error '#TEMP.FNAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.’

Right query is.

SELECT FNAME LNAME, COUNT(*) FROM #TEMP GROUP BY FNAME

Interview Questions and answers - SQL Server Day 9

Question: What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

Answer:

nchar and nvarchar can store Unicode characters.

char and varchar cannot store Unicode characters.

char and nchar are fixed-length which will reserve storage space for the number of characters you specify even if you don't use up all that space.

varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar. For more details click here

Question: What is the Difference between @@CONNECTIONS and @@MAX_CONNECTIONS in SQL Server?

Answer:

@@MAX_CONNECTIONS: @@MAX_CONNECTIONS returns the maximum number of synchronous user connections allowed. SQL Server allowed by default a maximum 32767 connections. To avoid many connections it can be configured at the server level.

Note: Maximum connections (32767) are depending on application and server hardware limits.

@@CONNECTIONS: @@CONNECTIONS returns number of connection attempts on SQL Server since SQL Server has been started.

SELECT [ConnectionAttempts] = @@CONNECTIONS,

       [MaximumAllowed] = @@MAX_CONNECTIONS

For more click here 

Question: What is the difference between DATEFIRST and @@DATEFIRST in SQL Server?

Answer: DATEFIRST keyword use to reset the first day of the week in SQL Server and @@DATEFIRST return the current value for the session of DATEFIRST.

For details click here 

Question: What is the Difference between a HAVING CLAUSE and a WHERE CLAUSE in SQL Server?

Answer:

WHERE clause applies to individual rows. While the HAVING clause is like a WHERE clause, but applies only to groups as a whole.

The WHERE clause is applied first to the individual rows in the tables means WHERE clause is fetched data from the memory according to condition. The HAVING clause is applied to the rows in the result set, means first data is fetched from memory to result set then apply HAVING clause on the row of the result set.

We can use WHERE clause with the HAVING clause, but it applies before the GROUP BY clause. We can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function. For more details click here 

Interview Questions and answers - SQL Server Day 8

Question: What is schema in SQL Server? How can we create the schema in SQL Server?

Answer: Schema introduced in SQL Server 2005 which is used for separation, Management and Ownership of the database object. Schema is a distinct namespace that is used to store the database object. It removes the tight coupling of database objects and owners to improve the security administration of database objects.

Create schema using wizard 

Create schema using query

USE [Test]

GO

--Create Schema MySchema

CREATE SCHEMA [MySchema] AUTHORIZATION [dilip] --dilip is the owner (database user)

GO

Question: How to change the schema of the table in SQL Server?

Answer: Suppose we have a table Codefari which has default schema [dbo] and we have to transfer it in schema [blog], the following query may help you.

CREATE SCHEMA Blog

GO

ALTER SCHEMA Blog

TRANSFER dbo.Codefari

GO

Question: How to drop schema in SQL Server?

Answer: If we have permission then using the following query we can drop the schema.

DROP Schema Blog

Interview Questions and answers - SQL Server Day 7

Question: What are ACID properties in SQL Server?

Answer: The ACID rules of a transaction in any database ensure the reliability of data in all transactions in the database.

Atomicity

Atomicity shows the ability of the database to assure that either all tasks of a transaction are performed or none of them.

Consistency

Consistency assures that the database remains consistent state before the transaction and after the transaction is over.

Isolation

Isolation assures that other operations can't be performed in an intermediate state during a transaction. As a result, transactions that run concurrently appear to be serialized.

Durability

Durability is responsible for notifying the transaction is successful; the transaction will persist, not undone. It will survive system failure, and the database system has checked the integrity constraints and no need to abort the transaction.

For example, click here. 

Question: What is a TRANSACTION in SQL Server?

Answer: Transaction is the collection of T-SQL statements that executes in a single unit. A transaction begins with a specific T-SQL statement and ends when all T-SQL statements execute successfully. If any statements fail, then the transaction fails. A transaction has only two types of results success or failure.

For more details, click here.

Question: What are COMMIT and ROLLBACK in SQL Server?

Answer: COMMIT statement use to the committed transaction to save changes in the database successfully.

ROLLBACK statement used to stop the current transaction and un-done the changes made by the transaction.

For more details, click here. 

Question: What is a WITH(NOLOCK) in SQL Server?

Answer: WITH(NOLOCK) used in the SELECT statement to give priority to select statement. WITH(NOLOCK) unlock the table for a SELECT statement to stop another process.

Example

SELECT * FROM TBL WITH(NOLOCK)

Question: GO command in the SQL server?

Answer: The GO command indicates the end of the SQL statements. It is used when there are multiple statements to be executed but sent as a batch.

Question: What is the difference between UNION and UNION ALL in SQL Server?

Answer: UNION operator is used to combining multiple results set into one result set but remove any duplicate rows. Basically, UNION used to performing a DISTINCT operation across all columns in the result set. UNION operator has the extra overhead of removing duplicate rows and sorting results.

UNION ALL operators use to combine multiple results set into one result set, but it does not remove any duplicate result. Actually, this is not removed duplicate rows, so it is faster than the UNION operator if you want to combine multiple results and without duplicate records, then use UNION. For more details click here 

Interview Questions and answers - SQL Server Day 6

Question: When we used UPDATE_STATISTICS commands in the SQL server?

Answer: When bulk changes are done in your database like INSERT, UPDATE, and DELETE because bulk changes may increase your index view fragmentation. UPDATE_STATISTICS Updates query optimization statistics on a table or indexed view.

Question: What is view in SQL Server?

Answer: The view is a virtual table that contains one or more than one field of more than one physical table. The view is used for de-normalization purposes. This is used to join multiple tables and get the data.

Question: What is the property of the relational table in SQL Server?

Answer:

1.       Values are atomic.

2.       Column values are of the same kind.

3.       Each row is unique.

4.       The sequence of columns is insignificant.

5.       The sequence of rows is insignificant.

6.       Each column must have a unique name.

Question: Explain the different types of lock-in SQL Server?

Answer: The following locks are available in SQL Server.

Shared (S)

Used for reading operations those do not change or update data, such as a SELECT statement.

Update (U)

Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking and potentially updating resources later.

Exclusive (X)

Used for data-modification Operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Intent

Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Schema

Used when an operation dependent on the schema of a table is executing. The types of schema locks are schema modification (Sch-M) and schema stability (Sch-S).

Bulk Update (BU)

Used when bulk copying data into a table, and the TABLOCK hint is specified.

Key-range

Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the questions were rerun.

Question: What is the DBCC command in SQL Server?

Answer: DBCC Stands for Database Consistency Checker. These commands are used to check the consistency of the database, like validation tasks, maintenance, and status checks.

For example –

DBCC CHECKALLOC – Responsible for checking that all pages are correctly allocated in the database.

DBCC CHECKDB – Responsible for checking the integrity & allocation of specific objects in a database.

DBCC SQLPERF- Responsible to show transaction log statistics.

DBCC SQLFILEGROUP – Responsible for checking all the tables file groups for any design.