Skip to main content

Posts

Showing posts from October, 2016

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

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

Select Table_CataLogasDB_NAME, Table_SchemaasTBL_SCHEMA, Table_NameasTBL_NAME from information_schema.tablesT where NotExists( Select          1 from information_Schema.Table_ConstraintsC where Constraint_Type='PRIMARY KEY' andC.Table_Name=T.Table_Name andC.Table_Schema=T.Table_Schema ) andTable_Type='BASE TABLE'

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

Some time we need to get all tables which have primary key constraints. There are many way to get this information.

Select ISTC.Table_CatalogasBD_Name, ISTC.Table_SchemaASTBL_SCHEMA, ISTC.Table_NameASTBL_NAME, ISCCU.Column_NameASCOL_NAME, ISTC.Constraint_NameASCONSTRAINT_NAME From information_Schema.Table_ConstraintsISTC INNERJOIN Information_Schema.constraint_column_usageISCCU onISTC.Constraint_Name=ISCCU.Constraint_Name ANDISTC.Table_Name=ISCCU.Table_Name where Constraint_Type='PRIMARY KEY'



This one is the best query to find out the tables which have 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 use to retrieve, store and manipulate data in 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: physically collection of data in systematic way is called database. Database gives permission to manage data like store, retrieve and manipulate. In one line Database is the collection data, schemas, tables, queries, reports, views, and other objects.
Question: What is table? Answer: Table is the collection of data which have 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

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 following query

CREATETABLE#TEMP ( FNAMEVARCHAR(100) ,LNAMEVARCHAR(100) ,CREATEDDATEDATETIME, )
INSERTINTO#TEMP SELECT'DILIP','SINGH',GETDATE() UNIONALL SELECT'ANIL','SINGH',GETDATE() UNIONALL SELECT'ASHISH','PRATAP',GETDATE()
-- In following query FNAME alias is FISRT_NAME. We can apply Order by caluse on alias as below SELECTFNAMEASFIRST_NAME,LNAMEASLAST_NAMEFROM#TEMP ORDERBYFIRST_NAMEASC
DROPTABLE#TEMP

Question:

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 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 Difference between @@CONNECTIONS and @@MAX_CONNECTIONS in SQL Server?
Answer: @@MAX_CONNECTIONS: @@MAX_CONNECTIONS returns maximum number of synchronous user connection allowed. SQL Server allowed by default maximum 32767 connections. To avoid many connections it can be configured at server level. Note: Maximum connections (32767) are depending on application and server hardware limits.
@@CONNECTIONS: @@CONNECTIONS returns number of connection attempt on SQL Ser…

Interview Questions and answers - SQL Server Day 8

Question: What is schema in SQL Server? How can we create schema in SQL Server?
Answer: Schema introduced in SQL Server 2005 which is used for separation, Management and Ownership of database object. Schema is distinct namespace which is used to store database object. It is remove the tight coupling if 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 CREATESCHEMA[MySchema]AUTHORIZATION[dilip]--dilip is the owner (database user) GO
Question: How to change schema of table in SQL Server?
Answer: Suppose we have a table Codefari which have default schema [dbo] and we have to transfer it in schema [blog], following query may help you. CREATESCHEMABlog GO
ALTERSCHEMABlog TRANSFERdbo.Codefari GO
Question: How to drop schema in SQL Server? Answer: If we have permission then using following query we can drop schema.
DROPSchemaBlog

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 database remains consistent state before the transaction and after the transaction is over.
Isolation Isolation assures that other operation 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 to notify 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…

Interview Questions and answers - SQL Server Day 6

Question: When we used UPDATE_STATISTICS commands in SQL server?
Answer: When bulk changes done in your data base 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: View is a virtual table which contains one or more than one fields of more than one physical table. View is used for de-normalization purpose. This is used to join multiple tables and get the data.
Question: What is the property of relational table in SQL Server? Answer: Values are atomic.Column values are of the same kind.Each row is unique.The sequence of columns is insignificant.The sequence of rows is insignificant.Each column must have a unique name.

Question: Explain different type of lock in SQL Server?
Answer: Following locks are available in SQL Server.
Shared (S) Used for read operations those do not change or update data, such as a SELECT statement.