ASP4UT: SQL Interview Questions

What are page splits in indexes ?
So does page split affect performance?
So how do we overcome the page split performance issue?
What exactly is fill factor?
What are “Table Scan’s” and “Index Scan’s”?
(Q) What are the two types of indexes and explain them in detail?
(DB) What is “FillFactor” concept in indexes?
(DB) What is the best value for “FillFactor”?
(Q) What are “Index statistics”?
(DB) How can we see statistics of an index?
(DB) How do you reorganize your index, once you find the problem?
(Q) What is Fragmentation?
(DB) How can we measure Fragmentation?
(DB) How can we remove the Fragmented spaces?
(Q) What are the criteria you will look in to while selecting an index?
(DB) What is “Index Tuning Wizard”?
(Q) How do you see the SQL plan in textual format?
(DB) What is Nested join, Hash join and Merge join in SQL Query plan?
(Q) What joins are good in what situations?
(DB) What is RAID and how does it work?
Chapter :- Stored procedures , Views Cursors , Functions and triggers
What are triggers and what are the different kinds of triggers ?
In what scenarios will you use instead of trigger and after trigger?
What are inserted and deleted tables?
What is a SQL Server view?
How do you create a view?
What are the benefits of using a view?
Are SQL Server views updatable?
Chapter :- SQL server Data types
Chapter :- Constraints (Primary keys, unique keys)
Is it possible to insert NULL value in to unique keys ?
Chapter :- MSBI ( SSIS , SSAS and SSRS)
Explain Business intelligence and ETL?
What is the difference between data warehouse and data mart?
What is the difference between OLTP and OLAP system?
What is the difference between star schema and snow flake design?
What are Facts, Dimension and Measures tables?
What are Cubes?
Can you explain ROLAP, MOLAP and HOLAP?
Where does SSIS, SSAS and SSRS fits in?
Chapter :- Business intelligence (SSIS)
What role does SSIS play in BI?
What is a package, control flow and data flow?
Can you explain architecture of SSIS (SQL Server integration services)?
What are the different locations of storing SSIS packages?
How can we execute SSIS packages?
What are the different types of variables in SSIS?
Explain difference between “For loop container” and “Foreach loop container”?
What are precedence constraints in SSIS?
What are sequence containers in SSIS and how do they benefit?
How can we consume web services in SSIS?
How to check quality of data using SSIS?
What kind of profile requests exists in SSIS?
What is the difference between Merge and Merge join transformation?
If we have data unsorted will merge and merge join work ?
How can you send a single data source output to multiple SSI controls?
You have millions of records in production, you want to sample some data to test a SSIS package ?
What is the use of SCD ?
Using SSIS how can we standardize “Indian”,”India” and “Ind” to “Ind”?
How can we convert “string” to “int” data type in SSIS ?
What is the use of “Audit” component ?
Chapter :- Business intelligence (SSAS)
How can we apply scale-out architecture for SQL Server Analysis Services?
How do you create cubes SSAS SQL Server Analysis Services?
You want your cube to support localization ?
What kind of tables will go in fact and dimension tables ?
In what kind of scenario will you use a KPI ?
How can you create a pre-calculated measure in SSAS ?
Chapter :- Business intelligence (SSRS)
Can you explain SSRS architecture?
Chapter :- SQL Server
What are the new features which are added in SQL Server ?
What are column store indexes?
(DB) Can we have a different collation for database and table?
Chapter : SQL
(Q) Revisiting basic syntax of SQL?
(Q) What are “GRANT” and “REVOKE’ statements?
(Q) What is Cascade and Restrict in DROP table SQL?
(Q) How to import table using “INSERT” statement?
(Q) What is a DDL, DML and DCL concept in RDBMS world?
(Q) What are different types of joins in SQL?
(Q) What is “CROSS JOIN”?
(Q) You want to select the first record in a given set of rows?
(Q) How do you sort in SQL?
(Q) How do you select unique rows using SQL? To get LIVE training, new topic release video updates install Telegram app &
(Q) Can you name some aggregate function is SQL Server?
(Q) What is the default “SORT” order for a SQL?
(Q) What is a self-join?
What is the difference between DELETE and TRUNCATE?
(Q) Select addresses which are between ‘//’ and ‘//’?
(Q) What are Wildcard operators in SQL Server?
(Q) What is the difference between “UNION” and “UNION ALL”?
(Q) What are cursors and what are the situations you will use them?
(Q) What are the steps to create a cursor?
(Q) What are the different Cursor Types?
(Q) What are “Global” and “Local” cursors?
(Q) What is “Group by” clause?
(Q) What is ROLLUP?
(Q) What is CUBE?
(Q) What is the difference between “HAVING” and “WHERE” clause?
(Q) What is “COMPUTE” clause in SQL?
(Q) What is “WITH TIES” clause in SQL?
(Q) What does “SET ROWCOUNT” syntax achieves?
What are Sub-Queries ?
What are co-related queries?
What is the difference between co-related query and sub query?
Can you explain Coalesce in SQL Server ?
What is CTE ( Common table expression)?
Can we use CTE multiple times in a single execution ?
Can you give some real time examples where CTE is useful ?
How to delete duplicate records which does not have primary key ?
Temp variables VS Temp tables
(Q) What is “ALL” and “ANY” operator?
(Q) What is a “CASE” statement in SQL?
(Q) What does COLLATE Keyword in SQL signify?
(Q) What is TRY/CATCH block in T-SQL?
(Q) What is PIVOT feature in SQL Server?
(Q) What is UNPIVOT?
(Q) What are RANKING functions?
(Q) What is ROW_NUMBER()?
(Q) What is RANK()?
(Q) What is DENSE_RANK()?
(Q) What is NTILE()?
(DB) What is SQl injection?
Chapter : NET Integration
(Q) What are steps to load a NET code in SQL SERVER ?
(Q) How can we drop an assembly from SQL SERVER?
(Q) Are changes made to assembly updated automatically in database? To get LIVE training, new topic release
(Q) Why do we need to drop assembly for updating changes?
(Q) How to see assemblies loaded in SQL Server?
(Q) I want to see which files are linked with which assemblies?
(Q) Does NET CLR and SQL SERVER run in different process?
(Q) Does NET controls SQL SERVER or is it vice-versa?
(Q) Is SQLCLR configured by default?
(Q) How to configure CLR for SQL SERVER?
(Q) Is NET feature loaded by default in SQL Server?
(Q) How does SQL Server control NET run-time?
(Q) In previous versions of NET it was done via COM interface “ICorRuntimeHost”
In NET it is done by “ICLRRuntimeHost”
(Q) What is a “SAND BOX” in SQL Server ?
(Q) What is an application domain?
(Q) How is NET Appdomain allocated in SQL SERVER ?
(Q) What is Syntax for creating a new assembly in SQL Server ?
(Q) Do Assemblies loaded in database need actual NET DLL?
(Q) You have an assembly, which is dependent on other assemblies; will SQL Server load the dependent assemblies?
(Q) Does SQL Server handle unmanaged resources?
(Q) What is Multi-tasking?
(Q) What is Multi-threading?
(Q) What is a Thread?
(Q) Can we have multiple threads in one App domain?
(Q) What is Non-preemptive threading?
(Q) What is pre-emptive threading?
(Q) Can you explain threading model in SQL Server?
(Q) How does NET and SQL Server thread work?
(Q) How is exception in SQLCLR code handled?
(Q) Are all NET libraries allowed in SQL Server?
(Q) What is “Hostprotectionattribute” in SQL Server ?
(Q) How many types of permission level are there for an assembly?
(Q) In order that an assembly gets loaded in SQL Server what type of checks are done?
(Q) Can you name system tables for NET assemblies?
(Q) Are two version of same assembly allowed in SQL Server?
(Q) How are changes made in assembly replicated?
(Q) In one of the projects following steps where done, will it work?
(Q) What does Alter assembly with unchecked data signify?
(Q) How do I drop an assembly?
(Q) Can we create SQLCLR using NET framework ?
(Q) While creating NET UDF what checks should be done
(Q) How do you define a function from the NET assembly? To get LIVE training, new topic release video updates install
(Q) Can you compare between T-SQL and SQLCLR?
(Q) With respect to NET is SQL SERVER case sensitive?
(Q) Does case sensitive rule apply for VBNET?
(Q) Can nested classes be accessed in T-SQL?
(Q) Can we have SQLCLR procedure input as array?
(Q) Can object data type be used in SQLCLR?
(Q) How is precision handled for decimal data types in NET?
(Q) How do we define INPUT and OUTPUT parameters in SQLCLR?
(Q) Is it good to use NET data types in SQLCLR?
(Q) How to move values from SQL to NET data types?
(Q) What is SQLContext?
(Q) Can you explain essential steps to deploy SQLCLR?
(Q) How do create function in SQL Server using NET?
(Q) How do we create trigger using NET?
(Q) How to create User Define Functions using NET?
(Q) How to create aggregates using NET?
(Q) What is Asynchronous support in ADONET?
(Q) What is MARS support in ADONET?
(Q) What is SQLbulkcopy object in ADONET?
(Q) How to select range of rows using ADONET?
(Q) If we have multiple AFTER Triggers on table how can we define the sequence of the triggers
(Q) How can you raise custom errors from stored procedure?
Chapter : Service Broker
(Q) What do we need Queues?
(Q) What is “Asynchronous” communication?
(Q) What is SQL Server Service broker?
(Q) What are the essential components of SQL Server Service broker?
(Q) What is the main purpose of having Conversation Group?
(Q) How to implement Service Broker?
(Q) How do we encrypt data between Dialogs?
(Q) What is XML?
(Q) What is the version information in XML?
(Q) What is ROOT element in XML?
(Q) If XML does not have closing tag will it work?
(Q) Is XML case sensitive?
(Q) What is the difference between XML and HTML?
(Q) Is XML meant to replace HTML?
(Q) Can you explain why your project needed XML?
(Q) What is DTD (Document Type definition)?
(Q) What is well formed XML?
(Q) What is a valid XML?
(Q) What is CDATA section in XML? To get LIVE training, new topic release video updates install Telegram app & join us using -
(Q) What is CSS?
(Q) What is XSL?
(Q) What is Element and attributes in XML?
(Q) Can we define a column as XML?
(Q) How do we specify the XML data type as typed or untyped?
(Q) How can we create the XSD schema?
(Q) How do I insert in to a table that has XSD schema attached to it?
(Q) What is maximum size for XML data type?
(Q) What is Xquery?
(Q) What are XML indexes?
(Q) What are secondary XML indexes?
(Q) What is FOR XML in SQL Server?
(Q) Can I use FOR XML to generate SCHEMA of a table and how?
(Q) What is the OPENXML statement in SQL Server?
(Q) I have huge XML file which we want to load in database?
(Q) How to call stored procedure using HTTP SOAP?
(Q) What is XMLA?
Chapter : Data Warehousing / Data Mining
(Q) What is “Data Warehousing”?
(Q) What are Data Marts?
(Q) What are Fact tables and Dimension Tables?
(DB)What is Snow Flake Schema design in database?
(DB) What is ETL process in Data warehousing?
(DB) How can we do ETL process in SQL Server?
(Q) What is “Data mining”?
(Q) Compare “Data mining” and “Data Warehousing”?
(Q) What is BCP?
(Q) How can we import and export using BCP utility?
(Q) During BCP we need to change the field position or eliminate some fields how can we achieve this?
(Q) What is Bulk Insert?
(Q) What is DTS?
(DB) Can you brief about the Data warehouse project you worked on?
(Q) What is an OLTP (Online Transaction Processing) System?
(Q) What is an OLAP (On-line Analytical processing) system?
(Q) What is Conceptual, Logical and Physical model?
(DB) What is Data purging?
(Q) What is Analysis Services?
(DB) What are CUBES?
(DB) What are the primary ways to store data in OLAP?
(DB) What is META DATA information in Data warehousing projects?
(DB) What is multi-dimensional analysis?
(DB) What is MDX? To get LIVE training, new topic release video updates install Telegram app & join us using -
(DB) How did you plan your Data warehouse project?
(Q) What are different deliverables according to phases?
(DB) Can you explain how analysis service works?
(Q) What are the different problems that “Data mining” can solve?
(Q) What are different stages of “Data mining”?
(DB) What is Discrete and Continuous data in Data mining world?
(DB) What is MODEL is Data mining world?
DB) How are models actually derived?
(DB) What is a Decision Tree Algorithm?
(DB) Can decision tree be implemented using SQL?
(DB) What is Naïve Bayes Algorithm?
(DB) Explain clustering algorithm?
(DB) Explain in detail Neural Networks?
(DB) What is Back propagation in Neural Networks?
(DB) What is Time Series algorithm in data mining?
(DB) Explain Association algorithm in Data mining?
(DB) What is Sequence clustering algorithm?
(DB) What are algorithms provided by Microsoft in SQL Server?
(DB) How does data mining and data warehousing work together?
(Q) What is XMLA?
(Q) What is Discover and Execute in XMLA?
Chapter : Integration Services / DTS
(Q) What is Integration Services import / export wizard?
(Q) What are prime components in Integration Services?
(Q) How can we develop a DTS project in Integration Services?
Chapter : Replication
(Q) Whats the best way to update data between SQL Servers?
(Q) What are the scenarios you will need multiple databases with schema?
(DB) How will you plan your replication?
(Q) What are publisher, distributor and subscriber in “Replication”?
(Q) What is “Push” and “Pull” subscription?
(DB) Can a publication support push and pull at one time?
(Q) What are different models / types of replication?
(Q) What is Snapshot replication?
(Q) What are the advantages and disadvantages of using Snapshot replication?
(Q) What type of data will qualify for “Snapshot replication”?
(Q) What is the actual location where the distributor runs?
(Q) Can you explain in detail how exactly “Snapshot Replication” works?
(Q) What is merge replication?
(Q) How does merge replication works?
(Q) What are advantages and disadvantages of Merge replication?
(Q) What is conflict resolution in Merge replication?
(Q) What is a transactional replication? To get LIVE training, new topic release video updates install Telegram app & join us using -
(Q) Can you explain in detail how transactional replication works?
(Q) What are data type concerns during replications?
Chapter : Reporting Services
(Q) Can you explain how can we make a simple report in reporting services?
(Q) How do I specify stored procedures in Reporting Services?
(Q) What is the architecture for “Reporting Services “?
Chapter : Transaction and Locks
(Q) What is a “Database Transactions “?
(Q) What is ACID?
(Q) What is “Begin Trans”, “Commit Tran”, “Rollback Tran” and “SaveTran”?
(DB) What are “Checkpoint’s” in SQL Server?
(DB) What are “Implicit Transactions”?
(DB) Is it good to use “Implicit Transactions”?
(Q) What is Concurrency?
(Q) How can we solve concurrency problems?
(Q) What kind of problems occurs if we do not implement proper locking strategy?
(Q) What are “Dirty reads”?
(Q) What are “Unrepeatable reads”?
(Q) What are “Phantom rows”?
(Q) What are “Lost Updates”?
(Q) What are different levels of granularity of locking resources?
(Q) What are different types of Locks in SQL Server?
(Q) What are different Isolation levels in SQL Server?
(Q) What are different types of Isolation levels in SQL Server?
(Q) If you are using COM+ what “Isolation” level is set by default?
(Q) What are “Lock” hints?
(Q) What is a “Deadlock”?
(Q) What are the steps you can take to avoid “Deadlocks”?
(DB) How can I know what locks are running on which resource?
What is the use of SQL Server governor?
How to combine table row in to a single column / variable ?
What is hashing?
What is CDC (Change data capture) in SQL Server?
How to enable CDC on SQL Server ?
How can we know in CDC what kind of operations have been done on a record?
Will CDC work if SQL Server Agent is not running ?

Saturday, 6 July 2024

SQL Interview Questions

No comments:

Post a Comment

Popular Articles

Follow me

Followers