Explore topic-wise InterviewSolutions in .

What do you think by Nested Triggers?
As we all knows triggers may implement by DML by use of insert, UPDATE and DELETE STATEMENTS. And these triggers which CONTAIN DML and find other triggers for data modification which are called Nested Triggers.

Where is the username and password stored in SQL SERVER?
In SQL Server usernames and passwords are stored in sys.server_principals and sys.sql_logins. BUt one SPECIAL note is that these passwords are ENCODED.

What is alternative of TOP keyword in SQL SERVER?
We can USE ROWCOUNT as alternate option for TOP keyword. But syntax is DIFFERENT from top keyword in SQL Server. Below is syntax for ROWCOUNT:-

Set rowcount 3
Select * from table_name order by id desc Set rowcount 0

Name all the standard trace templates by SQL Server Profiler?
To view all the standard trace templates in SQL Server first we need to open the SQL Server Profiler. And to open profiler we have many methods. As given in below picture:-



Below are the different predefined Templates available in SQL server:-



Now the information about the each of predefined templates:-

(1)SP_Counts:-This templates helps to captures stored procedure execution behavior over time.

(2)(Default)Standard:-This template is the generic STARTING point in SQL Profiler to create a trace. This will captures all stored procedures and all Transact SQL batches that are run. And this is basically used to monitor general database server activity.

(3)TSQL:-This will used to debug client APPLICATIONS. And this will captures all Transact-SQ: statements which are submitted to SQL Server by the clients and this will also captures the time issued.

(4)TSQL_Duration:-Basically this template is used to identify the slow queries. This will also used to capture all transact SQL statements submitted to SQL Server by clients and there execution time in milliseconds. And this also groups the data by duration.

(5)TSQL_Grouped:-This will basically used to investigate queries from a particular client or USER. This will captures all Transact SQL statements submitted to SQL Server and the time they were issued. We can also araange data in groups by user or client that submitted the statement.

(6)TSQL_Locks:-We generally used this templates to troubleshoot different aspects like lock timeout, lock escalation events and deadlocks. This will be used to capture all transact SQL statements submitted to SQL Server with the time they were used. We can also arranged data in groups by user or client that have submitted the statement.

(7)TSQL_Replay:-This will be basically used to perform iterative tuning just like some benchmark testing. This will also be used to capture information about the transact sql statements which are required if trace will be REPLAYED.

(8)TSQL_Sps:-This will basically used to analyze the components steps of stored procedure. Here we will add the SP:Recompile event if we suspect thet procedure which are being recompiled. This will used to capture the detailed information about all executing stored procedures.

(9)Tuning:-This template is used to produce trace output which Database Engine Tuning Advisor which is used as a workload to tuning database. This will captures information about stored procedures and transact SQL batch execution.

Can you PLEASE mention the different set operators AVAILABLE in SQL?
Below are the THREE main set operator available in SQL.
(1)UNION
(2)INTERSECT
(3)Minus

Name the case MANIPULATION functions in SQL?
There are 3 types of case manipulation functions in SQL.
(1)UPPER:-This will convert string in uppercase, And syntax is given below.
UPPER('crackyourinterview.com')
(2)Lower:-This will convert string in LOWERCASE, And syntax is given below.
LOWER('CRACKYOURINTERVIEW.COM')
(3)INITCAP:-This will convert first LETTER in uppercase and REST in lowercase.
INITCAP('crackyourinterview.com')

Can we LINKED SQL Server with other servers?
We can connect SQL Server with all the databases which have the LINK OLE-DB provider. For example we TAKE a example of Oracle that has OLE-DB provider which have a CONNECTION link with all SQL Servers.

Can we add CPU to SQL Server if yes then describe?
Yes we can add CPU to our SQL Server by either adding CPUs physically new hardware or by logically by ONLINE hardware partitioning or we can also add that by virtually through a virtualization layer. From Sql Sevre 2008 version sql server support CPU Hot Add means to add CPU minimum version is SQL Server 2008. Below are the some basic requirement to USE CPU Hot Add.
(1)Hardware must support the CPU hot add.
(2)We have SQL Server Enterprise version to support CPU Hot Add.
(3)And when we say about operating SYSTEM 64-bit edition of Windows Server 2008 Datacenter or Windows Server 2008 Enterprise Edition for Itanium-based system OS.
Kindly note once we add CPU we need to RUN RECONFIGURE and then SQL Server recongnizes the newly ADDED CPU.

How to disable overwrite instead of inserting text while typing in SQL?
Some time all of sudden SQL Server Management studio started behaving something different. Once we are writing any query this will overwrite the text in place of typing new words. And if you will try this on Notepad and other APPS all works fine. In SSMS we can actually DEFINE the behavior of the text editor, and alternate between Insert and Overwrite. To overcome this we need to check the status bar of SSMS as GIVEN below.



In below screen in status bar there is text "OVR" written which means this will overwrite the text. and double CLICK on this "OVR" this will be converted to "INS" which means it is now ready.

Name the different third party tools used in SQL Server and reason?
Below are the most common third party tools used in SQL Server.
(1)Idera(SQL Check):-This tool is used to monitor server activities and memory levels.
(2)Redgate(SQL Doc 2):-This tool is used to document different things in database.
(3)Redgate(SQL BACKUP 5):-This tool is also used to doing AUTOMATION of backup.
(4)Redgate(SQL Prompt):-This toll will provide INTELLISENSE feature for Sql Server 2000 or 2005.
(5)Quest(LiteSpeed 5.0):-We will use this tool to do backup and restore processes.

What are the virtual tables in TRIGGERS?
INSERT and DELETE are the only virtual tables PRESENT in triggers.

Can we HIDE instance of SQL Server if yes then how?
Yes, we can hise the instance of Sql Server instance. To hide the instance we will use below 4 steps:-
(1)In first step in Sql Server Configuration Manager we will expand SQL Server Network Configuration.
(2)In second step we will right click on PROTOCOLS for "Server Instance" and then select "Properties".
(3)AFter second step we will find a "HideInstance" box in which we need to on the "Flags" tab after thet we need to select "Yes".
(4)In last step we will click on "Ok".
Kindly note when we are hiding any named instance we need to provide a port number in connection string so that even the BROWSER is running it is possible to connect to hidden instance.

Do you knows the new INDEXES come in version 2005 of SQL Server?
The spatial and the XML indexes have first introduced in 2005 version as COMPARED to its PREVIOUS version SQL Server 2000.

How can we recompile a stored procedure at RUN time in SQL Server?
Below are the different METHODS to recompile a store procedure at runtime in SQL Server:-

(1)Below we have put parameter value as 10 and use keyword as RECOMPILE
EXEC dbo.Proc_name parameter1=10 WITH RECOMPILE;
GO

(2)By using sp_recompile system store procedure
EXEC sp_recompile 'Person.Address';
GO

(3)By using RECOMPILE HINTS keywords in stored procedure headers
ALTER PROCEDURE dbo.Proc_name
parameter1 int
WITH RECOMPILE
AS
SELECT statements
EXEC dbo.Proc2 parameter1;
GO

(4)By adding RECOMPILE with select STATEMENT in store procedure
ALTER PROCEDURE dbo.Proc_name
parameter1 int
AS
SELECT statement where clause OPTION (RECOMPILE);
EXEC dbo.Proc2 parameter1;
GO

How a dynamic SQL can be EXECUTED?
Below are the three methods to run dynamic SQL.
(1)By writing a query with specific parameters
(2)By using the EXEC
(3)By using the sp_executesql

What is "nolock" in SQL Server its benefits and drawback?
Usually we applied "nolock" in SQL Server on production servers. This is mainly used to MAKE records being shareable on the table when any QUERY is done from multiple users. Which means it will not PREVENT other queries from querying the same record parallel on the same table.
And the drawback of "nolock" is that there a some little risk that is it has TENDENCY to return some junk data some times. Because whenever user requesting some data from table while some other are inserting or updating commands are in PROGRESS. So may be missing or some extra rows are there.

How to check its active or passive NODES in SQL?
To check this cluster administrator will CHECKS the SQL SERVER group where we can SEE the current owner. Here current owner is active node and other nodes are passive nodes.

Is it POSSIBLE to create a Primary Key without a clustered index?
When we create a primary key in any of table a clustered index is automatically created UPON the columns. HENCE it is not possible to create a Primary Key without clustered index.

Do you knows the size of Null value in SQL Server?
There are two main reason which will impacts the size of "Null" VALUES in SQL Server.
(1)IF the size of COLUMN is FIXED width then storing NULL takes the same space as any other value in the column will take which is the width of the field.
(2)If we have the variable width in SQL Column then the NULL value takes up no space.

Define the main steps in DATA MODELING?
Data Modeling have TWO steps which is further define in many PARTS
(1)Logical:-
(i)Planning
(II)Analysis
(iii)Design
(2)Physical:-
(i)Design
(ii)Implementation
(iii)Maintenance

Is it possible to take backup of RESOURCE DB in SQL and how?
No we cannot take a backup of Resource DB. The only way is by using Windows backup for OPTION resource MDF and IDF files only.

Write down a SQL query to get all TRIGGERS in DATABASE?
Below is the query to get all the triggers in database. Here we will USE sys.triggers to get all the triggers.

SELECT name,is_instead_of_trigger
FROM sys.triggers WHERE type = 'TR';

What is INFORMATION_SCHEMA Database and why use this?
Before going to anything we will first discuss the DEFINITION of INFORMATION_SCHEMA Database.
First Definition
(1)"This is the database about databases"
Second Definition
(2)In RDBMS information_schema is an ANSI_standard set of read only views that will provide information about tables, views, COLUMNS and PROCEDURES in database.

we can use information_schema to get information about all the tables of particular database. Below is the example to get complete information about database.


use database_name

select * from INFORMATION_SCHEMA.TABLES;

Select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;


As we have use the use KEYWORD with database name the query only will be fire on the given database. Above query we will get all the information about the different tables and in second table we will get all constraint with table name.

Some facts about the SQL All operator?
Below are the 3 most useful facts about the SQL ALL operator
(1)This will RETURNS a BOOLEAN VALUE as a OUTPUT result
(2)This will return the TRUE if all the subquery will meet the condition
(3)We can used this with Select, Where and Having statements

Name the different TYPES of SQL Server backups?
Below are the name of 8 different types of backups available in SQL Server:-
(1)Full backups
(2)File backups
(3)DIFFERENTIAL backups
(4)FILEGROUP backups
(5)Partial backups
(6)Copy-only backups
(7)Mirror backups
(8)Transaction LOG backups

Process to run SQLCMD in a Docker CONTAINER?
When we are in context of our container and we can run the tools bundled with the GIVEN SQL Server image. Now we will run below COMMAND and this command will start an instance of SQLCMD. And we can run QUERY into the command-line session.





Once we run above command and on success of above CMD we will get below screen which means everything works fine.

(1)The major difference between procedure and function is that function return value that should be only single and function can pass any no of parameter but come out only ONE value only USING return statement.
procedure can pass any parameter and came out any no of VALUES there no any RETURN word.
(2)Other difference between procedure and function is that procedure is to be compile only one time. Execution of procedure is faster then function.
(3)Fuction can return a single value only.STORED procedure return more than one value.
(4)Function can be used in SELECT Queries statement but in stored procedure it's not possible.

How to START a SQL Server Container in Docker?
To start a SQL Server container in Docker we use only one line command which helps you start SQL. And Docker Hub has placed official SQL Server docker image. Here i have CLARIFY you that i HIGHLY recommend naming your running instances using the --name flag. Below is the SINGLE command.



In above command it is call our container sqlserver and i have initialize "sa" user account. And to EXPOSE SQL Server we use port 1433 to access database remotely. And to start our container we use detached (-d). And this will free the terminal from other user inputs. You can also change this value as per your requirements.

(1)The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a FUNCTIONING master database, you must administer this database with care.

(2)The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some REPLICATION information such as for log SHIPPING.

(3)The tempdb holds temporary objects such as global and local temporary tables and stored procedures. The model is essentially a template database used in the CREATION of any new user database created in the instance.

(4)The model is essentially a template database used in the creation of any new user database created in the instance.

(5)The resoure Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

Define N in nvarchar and in nchar?
Here n in nvarchar and nchar stands for the WORLD NATIONAL. ISO synonyms for nchar are national CHAR and for nvarchar are national char varying.

Truncate Command removes all ROWS from a table, but the table structure, all RELATIONSHIPS, CONSTRAINTS remain in the Database.
But Drop Command removes the entire table, all relationships associated with that table, all constraints in that table.
We can insert new records in a TRUNCATED table. But it is not possible when a table is dropped as it doesn't EXIST.

Best practice while doing SQL Maintenance plan?
Below are the 10 most usefull point which need to take care beofore doing any maintenance for SQL and this may cover backup and restore process in CASE of any crash.
(1)Full Daily Backup(Daily):-This one of the most important aspects of maintenance which contains full backup. But you must check the storage and number of days to kept the backup must be define. Some company will take care 30 days backup and some or doing only for 3 days. It is depend on storage avaialble and type of your business.
(2)Differential Backup(Hourly):-There are certain business which REQUIRED this type of backup which include some particular tables and procs.
(3)Transaction Log Backups(15 Min):-You can take this of full database or some parts let suppose for some specific tables.
(4)Check DB Integrity:-I will advice you to avaiod this in peak hours of your business as this process will scan all of your database which may chop your bandwidth. SO try to sechdule this in offline hours.
(5)Reorganize Indexing:-This need to be done on demand.I advice not to do this on daily basis if not required. Move all index and statistics operations on a single weekly task.
(6)Update statistics:- This should be also done on demand not on daily basis my ANSWER is same as PREVIOUS.
(7)Shrink Database(Weekly):- PLEASE avoid such thinks if you can.
(8)Rebuild Index:-Same as Reorganize Indexing
(9)Maintenance Cleanup(Daily):-My answer is yes for this.
(10)Restore verification:-Always try and test restore process as this may helps you in case of any issue persist or failure of system.

When Exclusive Lock is on any processes no other lock can be placed on that row or table. Every other process have to wait till Exclusive Lock is complete its tasks.

When two TRANSACTIONS doing SHARED mode locks on same (row or table) and then trying to update data concurrently when first transaction attempts the lock conversion to an exclusive lock. The shared mode to exclusive lock conversion must wait because the exclusive lock for first transaction is not compatible with the shared mode lock of the other transaction a lock wait occurs. The second transaction attempts to acquire an exclusive lock for its update. Now both transactions are converting to exclusive locks and they are each waiting for the other transaction to release its shared mode lock then DEADLOCK occurs.To AVOID this deadlock problem update locks are used. Only ONE transaction can obtain an update lock to a (row or table) at a time. If a transaction trying to modifies a row or table the update lock is converted to an exclusive lock else the lock is converted to a shared mode lock.

What are new function to be ADD in security in new upcoming version of SQL SERVER 2022?
SQL Server 2022 introduces some of the new functions or we can say some new security related ENHANCEMENTS. Below are the LIST of new security functions:-
(1)SQL Server Ledger that brings blockchain technology to SQL Server 2022.
(2)New enhancements relates to always encrypted policy.
(3)Added a new function to secure encrypted connections.
(4)Some ADDITIONAL granular permissions and roles added in new SQL Server 2022.

Some important information about Docker and SQL?
Now a days in market containerize all things are in progress, and many of vendors are trying to do remote development as a viable development OPTION. So may be in future some RATIO of development stack will be remote enable.
That also means lots of command-line developers are USEFUL there. But we have both options here some are GUI and some are using command line.
To start SQL Server container instance we use different commands. And will connect to SHELL instance within container and EXECUTE some SQL commands.
But it is very important that Docker must be running in development environment and there must be command line access to you which should either be from SSH or from some editor.