Thursday, December 22, 2011

Inside SQL Server Optimizer Book

Guys,

Here is another book from Redgate, Inside SQL Server optimizer by Benjamin Nevarez.

Here's a quick overview of what the book covers:
  • Chapter 1, Execution Engine, Introduction to Query Optimization, starts with an overview on how the SQL Server Query Optimizer works, and introduces the concepts that will be covered in more detail in the rest of the book. A look into some of the challenges query optimizers still face today is covered next, along with a section on how to read and understand execution plans. The chapter closes with a discussion of join ordering, traditionally one of the most complex problems in query optimization.
  • Chapter 2 talks about the Execution Engine, describing it as a collection of physical operators that perform the functions of the query processor. It emphasizes how these operations, implemented by the Execution Engine, define the choices available to the Query Optimizer when building execution plans. This chapter includes sections on data access operations, the concepts of sorting and hashing, aggregations, and joins, to conclude with a brief introduction to parallelism.
  • Chapter 3, Statistics and Cost Estimation, shows how the quality of the execution plans generated by the Query Optimizer is directly related to the accuracy of its cardinality and cost estimations. The chapter describes Statistics objects in detail, and includes some sections on how statistics are created and maintained, as well as how they are used by the Query Optimizer. We'll also take a look at how to detect cardinality estimation errors, which may cause the Query Optimizer to choose inefficient plans, together with some recommendations on how to avoid and fix these problems. Just to round off the subject, the chapter ends with an introduction to cost estimation.
  • Chapter 4, Index Selection, shows how SQL Server can speed up your queries and dramatically improve the performance of your applications, just by using the right indexes. The chapter shows how SQL Server selects indexes, how you can provide better indexes, and how you can verify your execution plans to make sure these indexes are correctly used. We'll talk about the Database Engine Tuning Advisor and the Missing Indexes feature, which will show how the Query Optimizer itself can provide you with index tuning recommendations.
  • Chapter 5, The Optimization Process, goes right into the internals of the Query Optimizer and introduces the steps that it performs without you ever knowing. This covers everything, from the moment a query is submitted to SQL Server, until an execution plan is generated and ready to be executed, including steps like parsing, binding, simplification, trivial plan, and full optimization. Important components which are part of the Query Optimizer architecture, such as transformation rules and the memo structure, are also introduced.
  • Chapter 6, Additional Topics, includes a variety of subjects, starting with the basics of update operations, and how they also need to be optimized just like any other query, so that they can be performed as quickly as possible. We'll have an introduction to Data Warehousing and how SQL Server optimizes star queries, before launching into a detailed explanation of parameter sniffing, along with some recommendations on how to avoid some problems presented by this behavior. Continuing with the topic of parameters, the chapter concludes by discussing auto-parameterization and forced parameterization.
  • Chapter 7, hints, and warns that, although hints are a powerful tool which allow you to take explicit control over the execution plan of a query, they need to be used with caution, and only as a last resort when no other option is available. The chapter covers the most used hints, and ends with a couple of sections on plan guides and the USE PLAN query hint.
Its a very good book to understand a lot of features to enhance performance & also get a hands on what's happening in background.

Click here to download this book.

Happy Learning

Sudhir

Thursday, December 1, 2011

Microsoft's Virtual Tech Days 2011 from 14-16th DEC

Guys,

Microsoft is coming up with Virtual Tech Days from 14-16th Dec 2011.

Welcome to the next edition of the Virtual Tech Days event scheduled in the mid of December, we are very excited about the event and we are currently working out a highly interactive agenda where you will learn about future technologies from Microsoft and deep dive sessions across our platforms and solutions. There will be dedicated tracks for Developers, IT Pros and Architects in this edition of VTD. Stay tuned to this page to see the schedule and agenda coming up over the next few weeks

The focus will be on 3 tracks, Developer, IT Professionals & Architects. For Developers stress will be given on development technologies. For IT Professionals the focus will be on Administration, Sharepoint, Database etc.

Agenda for Architects is not yet published.

Click here to view Agenda. Note: Its different for each day.

Click here to Register for the event.

If you wish to view existing videos / presentations from old Virtual days then click here.

Happy Learning

Sudhir

Thursday, October 20, 2011

.Net, C#, C++ requirements for Mumbai (Credit Suisse) Bank

Guys,


My company Credit Suisse (department) is hiring people in Mumbai.

Here are details of the requirements and there are multiple positions:
  • .NET C# (4 - 7 years experience) with Winforms or WCF/WPF 
  • .NET C# (2 - 5 years experience) with experience working with 1 database technology (Sybase, SQL, Oracle) 
  • C++ (4 - 7 years experience) with strong Perl  
If interested then send your CV to me at sudhir.chawla@credit-suisse.com. Or for more information call me at +91-9920368156.

 
All the best.

Sudhir

Friday, September 30, 2011

Stored Procedure Vs Function in SQL Server

Guys,

Let's discuss some points about Stored Procedure & Functions in SQL Server.


Although both functions and sp's are prcomiled sql statements there exists some differences between them.

1. Functions must return a value(scalar,inline table or multi statement table) whereas stored proc may or may not return a value.


2.Functions can return a table whereas stored procs can create a table but can't return table.


3. Stored procs can be called independently using exec keyword whereas function are called using select statements.


4. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this


5. XML and output parameters can't be passed to functions whereas it can be with sp's.


6.transaction related statement can be handled in sp whereas it can't be in function.


7. stored procedures can call a funtion or another stored proc similarly a function can call another function and a stored proc. The catch with function is that no user defined stored proc can be called. Only extended/system defined procs can be called.


8. We can have extended stored procedures but not extended functions.


9. A function can be used in a Join whereas Stored proc can't be used in a Join.

Hope this is helpful.


Sudhir Chawla

Monday, August 29, 2011

24 Hours of PASS: Summit 2011 Preview

Guys,


24 Hours of PASS is back with more sessions on September 7th and 8th. Join knowledgeable SQL Server experts - slated to present in-depth sessions at PASS Summit 2011 - as they preview their SQL Server session topics over these 24 free technical webcasts.
When: 
Singapore 8 PM, 
India 5:30 PM



Detail of Sessions: 


  Session 01 Brian Knight
Brian Knight, SQL Server MVP, MCSE, MCDBA, is the co-founder of SQLServerCentral.com, BIDN.com and SQLShare.com. He works as President and lead architect for Pragmatic Works and has written thirteen books on SQL Server over his fifteen year database career.
Presenting: INTRODUCTION TO DATA MINING IN SQL SERVER ANALYSIS SERVICES. Learn how to create a data mining model to predict your best customers, use algorithms to ensure your marketing budget is spent wisely and use Time Series analysis for budgeting and forecasting.
 Session 02 Brent Ozar
Brent Ozar is a Microsoft Certified Master of SQL Server 2008, a Microsoft SQL Server MVP, and co-founder of Brent Ozar PLF. Brent has over a decade of experience with SQL Server, systems administration, SAN administration, virtualization administration, and project management. Brent blogs at http://www.BrentOzar.com like it's going out of style.
Presenting: SAN BASICS FOR DBAs. They keep telling you it's a SQL problem - but is it? Learn the basics about your storage options like RAID 5, RAID 10, solid state drives, and why your biggest bottleneck might be a $5 cable. No storage experience required.
 Session 03 Jonathan Kehayias
Jonathan Kehayias is a Senior Consultant with SQLskills, the youngest MCM for SQL Server 2008 and has been a SQL Server MVP for many years. Jonathan blogs on SQLskills.com, tweets as @SQLPoolBoy, and is an expert on Extended Events.
Presenting: DIVING INTO EXTENDED EVENTS. Extended Events provide information about the operations of SQL Server that have never before been available. Close the learning gap between Extended Events and SQL Trace.
 Session 04 Peter Myers
Peter Myers is a highly experienced consultant, trainer, mentor and presenter specializing in Microsoft Business Intelligence.
Presenting: EXPLORING POWERPIVOT FOR EXCEL IN SQL SERVER PROJECT "DENALI". Peter Myers explores the new design experience and modelling features available in PowerPivot for Excel in the upcoming release of SQL Server Project “Denali”.
 Session 05 Aaron Nelson
Aaron Nelson is a Senior SQL Server Architect who holds certifications for MCITP: Business Intelligence Developer, Database Administrator, Database Developer; as well as MCTS: Windows Server Virtualization, Configuration (meaning Hyper-V).
Presenting: WHY POWERSHELL? Windows PowerShell is rapidly becoming the go-to scripting choice of System Administrators, but what does that mean for SQL Server DBAs? We'll explore PowerShell and demonstrate how and why DBAs should use it.
 Session 06 Andy Leonard
Andy Leonard is a consultant and trainer with Linchpin People, LLC. He is active in the SQL Server Community, co-CSO of SQLPeople.net, and co-author of several books on SQL Server and Integration Services.
Presenting: SSIS IN THE ENTERPRISE. Learn key components of an SSIS Framework to facilitate SSIS development that performs and is manageable in the modern enterprise.
 Session 07 Gail Shaw
Gail Shaw is a SQL MVP from Johannesburg, South Africa, specializing in performance tuning and database optimization.
Presenting: EXTRACTING EXECUTION PLANS. This session will cover assorted ways of obtaining query execution plans from SQL Server.
 Session 08 Allan Hirt
Clustering MVP Allan Hirt is a consultant, published author, speaker, and trainer who has used SQL Server since 1992. Allan travels all over the world to work with and train clients. His most recent book is "Pro SQL Server 2008 Failover Clustering".
Presenting: MULTI-SITE FAILOVER CLUSTERING WITH SQL SERVER 2008, 2008 R2, and DENALI. Learn how to plan a multi-site cluster deployment for SQL Server 2008, 2008 R2, and Denali.
 Session 09 Stacia Misner + Denny Cherry
Stacia Misner and Denny Cherry are Microsoft MVPs with 25 years IT experience between them; Stacia focuses on the BI side and Denny focuses on the core engine side. Between them they have written 14 books and hundreds of articles.
Presenting: SO HOW DOES THE BI WORKLOAD IMPACT THE DB ENGINE? In this session, we review the architecture of SQL Server and its BI components and deployment options for optimal performance. We'll also discuss how day-to-day BI operations impact the database engine.
 Session 10 Maciej Pilecki
Maciej Pilecki is a SQL Server MVP, Microsoft Certified Master in SQL Server 2008, Microsoft Certified Trainer and Senior Consultant with Project Botticelli. He is passionate about the internals of the SQL Server database engine, Storage Engine and Query Optimizer, and Performance Tuning.
Presenting: SECRETS OF THE SQLOS. This session will explore a mysterious component of Microsoft SQL Server: its own internal built-in operating system, or SQLOS.
 Session 11 Glenn Berry
Glenn works as a Database Architect at Avalara in Denver, CO. He is a SQL Server MVP, and Adjunct Faculty member at University College - University of Denver.
Presenting: HARDWARE 301: DIVING DEEPER INTO DATABASE HARDWARE. Making the right hardware selection decisions is very important for database scalability. Having properly sized and configured hardware can both increase application performance reduce capital expenses.
 Session 12 Grant Fritchey
Grant Fritchey: Red Gate Software Product Evangelist, Author, MVP, Consultant, Speaker, Blogger, a Scary DBA.
Presenting: ALL ABOUT EXECUTION PLANS: READING EXECUTION PLANS. This session introduces the basics of reading execution plans. Learn where to start and how to proceed when reading your own plans.
 Session 13 Adam Jorgensen
Adam Jorgensen, President of Pragmatic Works, SQL MVP and SSAS Maestro co-course director is focused on democratizing SQL Server technology. He mentors executive teams & drives towards this goal designing & delivering world-class solutions.
Presenting: ZERO TO CUBE - FAST TRACK TO SSAS DEVELOPMENT. Attendees will learn how to drive and build an Analysis Services cube and reports as they rotate through the podium as volunteers.
 Session 14 Rob Farley
Rob Farley (MVP and MCT) runs Australian consultancy LobsterPot Solutions. He is hosting a pre-conference seminar at the PASS Summit 2011, showing ways that a better understanding of Advanced T-SQL features can be used to fix queries.
Presenting: FIXING QUERIES WITH UNIQUENESS. In my pre-con seminar, Fixing Queries through Advanced T-SQL Techniques, I’ll be looking at ways you can make your queries run faster. In this 24HOP session I'll be looking at Uniqueness, and the impact that this can have.
 Session 15 Edwin Sarmiento
Edwin Sarmiento is a SQL Server DBA/Windows Engineer for Pythian, a global remote DBA services provider. He is very passionate about technology but has interests in music, professional and organizational development, leadership and management matters.
Presenting: DISASTER RECOVERY IS NOT JUST ABOUT TECHNOLOGY. This session will describe the principles that should drive disaster recovery strategies and how they need to become the foundation of the technology solutions that you need to implement.
 Session 16 Jes Borland
Jes Schultz Borland is a Database Administrator at Kimberly Clark. She holds an AAS - Programmer/Analyst degree. She has worked with SQL Server since 2007, focusing on Reporting Services and day-to-day administration.
Presenting: RERPORTING SERVICES 201: FROM BASIC TO WOW! You have solid, reliable reports that you and others depend on. Learn how to take them from basic to "wow", using features such as tablix, lists, images, and charts.
 Session 17 Karen Lopez
Karen Lopez has 20+ years of data architecture experience on large, multi-project programs. A new SQL Server MVP, she wants you to love your data.
Presenting: 5 PHYSICAL DATABASE DESIGN BLUNDERS & HOW TO AVOID THEM. Mistakes can cost you dearly: performance snags, development delays and bugs. Learn how to: find blunders, get tips on how to avoid them, talk about and defend design recommendations.
 Session 18 Adam Machanic
Adam Machanic is a Boston-based independent database consultant, writer, and speaker. He has written for numerous web sites, magazines, and books. He loves performance monitoring and shortening his bio for the sake of LiveMeeting.
Presenting: BASELINE BASICS OR WHO BROKE THE DATABASE. Does the server seem a bit sluggish today? Is a quick glance at performance counters not revealing any obvious culprit? Where should you go next?
 Session 19 Audrey Hammonds
Audrey Hammonds is a BI Consultant for Key2 Consulting and one half of the Datachix (www.datachix.com). She holds MCTS certifications in SQL Server 2008 Database Development and SQL Server 2008 Business Intelligence.
Presenting: T-SQL AWESOMENESS: 3 WAYS TO WRITE COOL SQL. There are some truly awesome ways to make your data do just what you need it to do, while improving performance and/or readability. Come learn new-school ways to expand your T-SQL repertoire.
 Session 20 Don Kiely
Don Kiely is a technology consultant and database application developer in Fairbanks, Alaska. donkiely@computer.org
Presenting: CODE-FIRST DEVELOPMENT WITH THE ENTITY FRAMEWORK. Code-first development in Entity Framework is a new feature of version 4.1, based on custom .NET classes. Define your objects then let EF create the database. This session explores this great new feature.
 Session 21 Simon Sabin
Simon Sabin is a principal consultant for SQLKnowHow who specialize in the high perf/big data SQL Server systems. His focus is troubleshooting and mentoring teams responsible for such systems. He is a SQL Server MVP and MCM and is the founder of SQLBits.
Presenting: ADVANCED REPORTING SERVICES. Slicers are an interactivity feature available in Excel and Crescent. Learn to use features such as multi valued parameters, expression formatting and custom code to show you how to add slicers to reporting services reports.
 Session 22 Victor Isakov
Victor Isakov (MCA, MCM, MCT, MVP) is a Database Architect and Trainer who provides consulting and training services to various organizations in the public, private and NGO sectors globally. He regularly speaks at international conferences and has authored a number of books on SQL Server.
Presenting: IMPORTANT TRACE FLAGS THAT EVERY DBA SHOULD KNOW. In this session, Victor Isakov will present the important trace flags that all DBAs should know, what they do, when to implement them and how best to implement them in a production environment.
 Session 23 Jorge Segarra
Jorge Segarra is a DBA-turned-BI Consultant for Pragmatic Works consulting, SQL Server MVP, and a Regional Mentor for PASS. He is the founder of SQL University which can be found at his blog, http://sqlchicken.com.
Presenting: POLICY-BASED MANAGEMENT IN A NUTSHELL. Discover Policy-Based Management, a powerful and easy-to-configure feature added in SQL Server 2008. Learn how to set these up in your own shops, set up an elegant (and free) reporting solution, and much, much more!
 Session 24 Paul Turley
Paul Turley is a Mentor for SolidQ and a SQL Server MVP who has been architecting, managing and developing applications and business intelligence solutions for large and small businesses since 1992. He holds the current MCTS and MCITP certifications for SQL Server BI.
Presenting: VISUAL REPORT DESIGN: BRINGING SEXY BACK. BI is all the rage but you don’t have to invest in an entire BI platform to have fresh, usable reports. Learn design techniques applicable to enterprise analytic solutions and smaller-scale operational reporting.

Happy Learning

Sudhir


Thursday, August 11, 2011

SQL Server Questions Part IV

Guys,

Here are some more questions on SQL Server. PART - IV. Here are some more questions with answers on SQL Server:

Q. Define ACID property?

ANS:

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for while evaluating databases and application architectures. For a reliable database, all this four attributes should be achieved.

  • Atomicity is an all-or-none proposition.
  • Consistency guarantees that a transaction never leaves your database in a half-finished state.
  • Isolation keeps transactions separated from each other until they are finished.
  • Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

Q. What are the Different Normalization Forms?


Ans:

1st Normal Form: Eliminate Repeating Groups. Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2nd Normal Form: Eliminate Redundant Data. If an attribute depends on only part of a multi-valued key, then remove it to a separate table.

3rd Normal Form: Eliminate Columns Not Dependent On Key.
If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key.

BCNF: Boyce-Codd Normal Form. If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.

4th Normal Form: Isolate Independent Multiple Relationships. No table may contain two or more 1:n or n:m relationships that are not directly related.

5th Normal Form: Isolate Semantically Related Multiple Relationships. There may be practical constrains on information that justify separating logically related many-to-many relationships.

Q. What are the Different Types of Triggers?


Ans.
There are two types of Triggers.
1) DML Trigger
There are two types of DML Triggers
a.Instead of Trigger
Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.
b. After Trigger
After triggers execute following the triggering action, such as an insert, update, or delete.
2) DDL Trigger
This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers.

Q. What is a View?


Ans.
A simple view can be thought of as a subset of a table. It can be used for retrieving data as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does the data in the view as views are the way to look at parts of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

Q. What is a Linked Server?


Ans.
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server databases using T-SQL Statements. With a linked server, you can create very clean, easy–to-follow SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedures sp_addlinkedserver, sp_addlinkedsrvlogin will be used to add new Linked Server.

Q. What is Collation?


Ans.
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence with options for specifying case sensitivity, accent marks, Kana character types, and character width.

Will add more soon.

Happy Learning

Sudhir

Download SQL Server Code Name “Denali” CTP3 Product Guide

Guys,


I am sure everyone will be aware the new version of SQL SERVER DENALI is in its CTP3 phase. Now they have released Product Guide on the same. 

This product guide release represents a significant milestone for customers and partners who want to experience the extensive new value delivered by SQL Server Code Name “Denali.” As the foundation of the cloud-ready information platform, SQL Server Code Name “Denali” can help organizations unlock breakthrough insights across the organization as well quickly build solutions and extend data on-premises and across public clouds, backed by mission-critical confidence.

The SQL Server Code Name “Denali” CTP3 Product Guide includes useful resources and demos that will help you in your evaluation of CTP3.  This includes:

  • 14 Product Datasheets
  • 8 PowerPoint Presentations
  • 5 Technical White Papers
  • 13 Hands-On Lab Preview Documents
  • 6 Click-Through Demonstrations
  • 13 Self-Running Demonstrations
  • 26 Links to On-Line References
  • 44 Links to On-Line Videos including 26 Presentations from North America TechEd 2011
The Product Guide comes with a small application to help you navigate through the content. Once you download the ZIP file, your will want to extract all of the files to your local hard drive. To start the viewer, click in the STARTHERE.CMD file. The view works with IE8 and IE9. If you are using an alternative default browser, a simpler HTML version of the viewer will be launched.

Click here to download the guide.


Happy Learning


Sudhir

Monday, August 8, 2011

SQL Server Questions Part III

Guys,

Here are some more Questions with Answers.

Q. What is the difference between Delete and Truncate command and is it possible to rollback after Truncate command?

ANS. Delete command removes the rows from a table based on Where condition. Truncate will actually remove all the rows from a table.

Below are some points about TRUNCATE:

  • TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. 
  • TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
  • TRUNCATE removes all the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. The counter used by an identity for new rows is reset to the seed for the column.
  • You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
  • TRUNCATE cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
  • TRUNCATE is a DDL Command.
  • TRUNCATE resets the identity of the table.

Below are some points about DELETE:

  • DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
  • DELETE does not reset Identity property of the table.
  • DELETE can be used with or without a WHERE clause.
  • DELETE activates Triggers if defined on table.
  • DELETE can be rolled back.
  • DELETE is DML Command.
  • DELETE does not reset the identity of the table.

Q. Why we should give preference to stored procedure?

ANS. A stored procedure is a group of SQL statements that have been created and stored in a database. A procedured can accept input parameters. When the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

Procedure Cache is the place where Execution Plan of Stored Procedure is stored. An execution plan states the efficient way in which the query(s) is executed. So whenever a normal query is executed its Execution Plan is created but when a Stored Procedure is executed, Execution plan is created and stored in Procedure Cache. Whenever the same procedure is executed and its execution plan exists in Procedure cache then it uses that execution plan rather than creating a new plan.

Q. What is an extent?

ANS. Extent is a basic unit of storage to provide space for tables. Every extent has a number of data pages. As new records are inserted new data, pages are allocated. There are eight data pages in an extent. So as soon as the eight pages are consumed, it allocates a new extent with data pages.

While extent is basic unit storage from a database point of view, page is a unit of allocation within extent.

Q. What is the advantage of SET NOCOUNT ON?

ANS. SET NOCOUNT ON gives a performance boost to action queries by suppressing the "(n row(s) affected) message that results from running a query.

Qualifying an object with it's owner boosts performance because SQL does not have to work out where if there is a user specific version of the same object. It also gives benefits in the caching of execution plans.

The performance boost is due to the few bytes of information that make up the "(1 row(s) affected)" message not being transmitted to the client application.

With this in mind I should consider the following points:

Communication between the database and the client application on a stand-alone machine will be as fast as it is possible to get. If your front end application had it's own clock and you recorded the time from submitting the query to the time when the client finally received the full results I would expect that transmitting results across a network to be slower.

In this experiment we are carrying out a single simple insert statement. If your procedure carries out multiple operations the performance boost will be more pronounced.

For queries that retrieve data the performance boost will be less simply because the size of the "(1 row(s) affected)" message is small compared to the volume of data being returned.

In .NET applications an ExecuteNonQuery command returns the number of records affected by the operation. Set NOCOUNT ON means that the value that this call returns is always zero.

Q. Suppose I have a table that has ID as primary key with IDENTITY. Whenever a new row is inserted in table, Identity will auto incremement the value by 1.

I don't want to insert an auto incremented value but I want to give it my own value. How can I do this?

Ans. We need to use SET IDENTITY_INSERT [TABLE NAME]. This allows explicit values to be inserted into the identity column of a table.

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

Example:


SET IDENTITY_INSERT products ON
GO


INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO


SET IDENTITY_INSERT products OFF
GO

Q. What are IMPLICIT TRANSACTIONS?

ANS. Implicit Transactions are those that requires a COMMIT or ROLLBACK for every transaction.

When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode.

For example if we turn it on & update a table, the changes will not be committed until COMMIT command is executed.


Happy Learning

Sudhir

Monday, August 1, 2011

SSIS Questions - Part 1

Guys,
Let's start a series of Q&A's on SSIS also. As these days its essential to learn SSIS or any other BI tool.
I felt SSIS is one of the best tools to start with. Let's see some of the basic questions asked on SSIS.


Q. What is the control flow in SSIS? 
ANS:
As we have MAIN in .Net, same way we have Control Flow in SSIS. 
Containers, Data flow tasks, Administration tasks, Precedence constraints, and Variables are elements of the control flow. 
The control flow is the highest-level of control process. It allows to orchestrate and manage the run-time process activities of data flow and other processes within a package. We can design a control flow by using an Execute Package task to manage the sequence of processing for a set of existing packages in a Master Package concept. This capability allows to combine individual packages into a highly manageable workflow process. Use precedence constraints to set the process rules and to specify sequence within the control flow. An SSIS package consists of a control flow and one or more objects. Data flow and event handler process control components are optional.

Following are some key points of Control Flow:

  • Process is the key: precedence constraints control the project flow based on task completion, success or failure.
  • Task 1 needs to complete before task 2 can begin.
  • Smallest unit of the control flow is a task.
  • Control flow does not move data from task to task.
  • Tasks are run in series if connected with precedence or in parallel.
  • Package control flow is made up of containers and tasks connected with precedence constraints to control package 


Q. what is a data flow in SSIS?

ANS.

The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved. Addition of a Data Flow task to a package, control flow makes it possible for the package to extract, transform, and load data.

A data flow consists of at least one data flow component, but it is typically a set of connected data flow components: sources that extract data; transformations that modify, route, or summarize data; and destinations that load data. Components are connected in the data flow by paths. Each path specifies the two components that are the start and the end of the path.

At run time, the Data Flow task builds an execution plan from the data flow, and the data flow engine executes the plan. You can create a Data Flow task that has no data flow, but the task executes only if it includes at least one data flow.

A package can include multiple Data Flow tasks, and complex packages frequently do. For example, if a package requires that data flows be run in a specified sequence, or that other tasks be performed between the data flows, you must use a separate Data Flow task for each data flow.

The Data Flow task also manages error flows. At run time, row-level errors may occur when data flow components convert data, perform a lookup, or evaluate expressions. For example, a data column with a string value cannot be converted to an integer, or an expression tries to divide by zero. Both operations cause errors, and the rows that contain the errors can be processed separately using an error flow.

There are many types of transformations as the data moves from the data source to the description. Here are some of the transformations:

Copy Column - This transformation copies the data in a column and creates a new column with it.
Script Component - This uses a VB script to transform the data somehow. You can use programmatic means to access the data, and set this script up as a data source, destination, or a transformation. When you open up the script component, there is a button stated as Design Script, which invokes a Visual Studio Editor where you can code the script with intellisense support.
Sort - This transformation sorts the data based on one or more columns setup to sort in a specified order.
Pivot and Unpivot - New to SQL Server 2005 is the way to pivot/unpivot data rather easily and they are supported as transformations. Pivoting data means you can make the columns of the result set based on a distinct result from the data.
Merge/Union All – Merging allows you to veer two inputs into one output. You can specify the input/output parameters that the transformation will map to. The Merge transformation is a little more restrictive than the Union All transformation.
Conditional Split – Conditional Split works the opposite way; based on values within the data, you can setup statements to split the data if a condition matches. For instance, you can split one result based on the expression “ListPrice > 100” and any data that matches that result is returned via a specific data flow path.
OLE DB Command – This transformation executes a SQL statement for every row in the input source.
Lookup – This transformation looks up the value of a field in a lookup data source/table.
Derived Column - A way to add an additional column in the dataset.
Aggregate - This allow to group data.
Fuzzy Logic - This allows to perform text manpulation like upper case, lower case operations on string data.
etc

Tuesday, July 5, 2011

SQL Server Questions Part II

Guys,

Today we will see some more generic questions on SQL Server.

Here are more questions:


Q. How can you ensure that a table named TableB with a field named Fld1 will only have those values in the Fld1 field that are also in the table named TableA with a field named Fld1?

Ans: Use referential integrity / Foreign Key Constraint

Q. How can you reset Identity key value of a table?

Ans: Truncate Table will reset the Identity value of table.

Q. What can be used to ensure that a field in a table only accepts a certain range of values?

Ans: We can use various constraints that can be used to achieve this.

There are various constraints available in SQL Server.

Primary Key Constraint => to uniquely identify a row without using NULL

Foreign Key Constraint => to maintain referential integrity between 1 or more tables

Unique Key Constraint => to uniquely identify a row

CHECK Constraint => the value entered is validated and if it fails condition then don't allow entry of data

Default Constraint => If we don't supply any value for field then set value of field with specified value

Null Constraint => Whether field can accept NULL value or not.

Q. Our company recently introduced a policy where all names of objects must follow naming convention. For ex. Table must start with tbl_, procedure should start with prc_ etc. How can I ensure all new objects must adher to these policies and if not followed object should not be created?

Ans: 1 way to use DDL triggers. From 2008 onwards we can also refer to Policy Based Management to ensure this.

Q. Is it possible to drop multiple objects using single DROP statement?

Ans:
Drop Table a,b,c
Drop proc a,b,c

Q. If you have a stored procedure, you want to just check the syntax of the same rather than executing it, what will u do?

Ans: Press CONTROL + F5 to check for its syntax. Or click on button next to EXECUTE button.

Q. Which event (CHECK constraint, Foreign Key, Rule, Trigger, Primary Key check) will be performed last for an integrity check?

Ans: Trigger because everything is executed before data is inserted in the table & as soon as data is entered in Table, trigger is fired.

Q. When a new parameter is added to an SP, what steps to take to ensure existing code in an application does not break?

ANS: Give a default value to the newly added parameter. This will ensure that all existing code will not fail.

Q. When we drop a variable length column using ALTER TABLE DROP COLUMN statement, the space used by the column is not automatically claimed. What will u do to claim the space?

Ans: DBCC CLEANTABLE (database_name, table_name)

Q. I have a table Employee with following definition:


Employee (
EmpID int Not Null Identity (1,1),
EmpName varchar(100) default 'test',
Age int default 2)

What query you will write to insert default values in EMPLOYEE table?

Ans: Insert into Employee Default Values

Q.How to retrieve a single table from database backup?

Ans: It is not possible to directly restore 1 table from backup. Only option is to restore the database somewhere else & then load data of required table and transfer to destination.

Q. How do you flip rows to columns and vice-versa?

Ans: We can use PIVOT, UnPivot to flip rows to columns & vice-versa.

Q. What is WITH ENCRYPTION clause and where it is used?

Ans: This will encrypt the definition of object and it will not be visible whenever we use sp_helptext. This can be done with View, Stored Procedure, Function etc

Q. What will happen if I write following statement:
Select getdate()
go 10

Ans: Select Getdate() is executed 10 times because GO 10 acts as a loop.

Q. What is the purpose of the USE command?

Ans: The USE command helps select any available database, so that every query is executed on selected database else default database is used.

Do let me know if you have any comments or more questions.
 
Thanks
 
Sudhir

Friday, July 1, 2011

Visual Studio 2010 and .NET Framework 4 Training Kit

Guys,


Visual Studio 2010 and .NET Framework 4 Training Kit includes presentations, hands-on labs, and demos. This content is designed to help you learn how to utilize the Visual Studio 2010 features and a variety of framework technologies including:
  • C# 4
  • Visual Basic 10
  • F#
  • Parallel Extensions
  • Windows Communication Foundation
  • Windows Workflow
  • Windows Presentation Foundation
  • Silverlight 4
  • ASP.NET 4
  • Windows 7
  • Entity Framework
  • ADO.NET Data Services
  • Managed Extensibility Framework
  • Application Lifecycle Management
  • Windows Azure
This version of the Training Kit works with Visual Studio 2010 and .NET Framework 4.


Download and launch the self-extracting package. The Training Kit will uncompress to the selected folder and launch a HTML browser for the content.


Click here to download the kit.


Happy Learning


Sudhir

Monday, June 27, 2011

SQL Server Questions PART - 1

Guys,


Let's discuss some of the basic questions that we might face anywhere. I will try to come up with as many as I can. It will be great to receive comments/feedback on the same.


Here we go:


Q. How many types of Indexes are there in SQL Server? What is the difference between them?


Ans: There are 2 type of indexes. Clustered and Non-Clustered. There can be only 1 Clustered index per table and 249 Non-Clustered Indexes per table. The index at the end of the book is a perfect example of Clustered Index.


In Clustered index, data is sorted & saved. Suppose there is a table Employee with Clustered Index created on Employee Name field. We have data like Abrahim, Jacob etc. Now whenever it tries to save new data with Colin, it will be inserted between Abrahim and Jacob. This is not applicable for Non-Clustered index, the data is not saved in sorted way.


Visit here for Clustered Indexes
msdn.microsoft.com/en-us/library/aa933131(v=SQL.80).aspx
Visit here for Non-Clustered Indexes
msdn.microsoft.com/en-us/library/aa933130(v=SQL.80).aspx


Q. What is the difference between UNION and UNION ALL?


Ans: There is a very small difference between UNION and UNION ALL. When we join 2 resultsets using UNION, it will internally add Distinct clause before returning final result. When we use UNION ALL, full set of data is retrieved.

To see this, go to SSMS and Enable Execution Plan, it will show the difference.


Visit here for more information:
msdn.microsoft.com/en-us/library/ms180026.aspx


Q. What is the difference between Primary key and Unique key?


Ans: We have tables & Rows and to identify each row we need Primary key. Someone can say that a Unique key can also help to identify each row. That’s true but there is a difference. Primary Key value can’t contain NULL, it must have a value whereas Unique Key can have 1 NULL value as its unique and because NULL is unknown its hard to identify/represent a row with NULL. That’s why Primary key doesn’t allow NULL.


Q. How Page Split occurs and why?


Ans. I am sure we all are aware of Indexes, especially Clustered Index because data is sorted before it is written to disk. As we have a 8K page size and further it depends on Fill factor and row size (length of each field) how much that page will be filled.


Suppose one page can accommodate maximum 2 rows in 1 page. We have same Employee table with Employee Name as primary Key. First page has 2 rows where Employee Name in 1st Row is Amar and 2nd Row is Carol. Now when we try to add a new row with Employee Name as Boris. As Boris comes before Carol so data will be inserted after Amar, but a page can hold only 2 rows so what will happen to Carol.


SQL Server pick handles/Link list end points for next page from 1st page. Create a new page, move Boris to this new page & store Boris on 1st page. Now reset the next page after 1st Page to New Page and New Page will point to the original 2nd page so that the Link list is updated.


sqlblogcasts.com/blogs/tonyrogerson/arch...appen-why-worry.aspx


Q. What are the disadvantages of having Indexes?


Ans. Insertion or saving become slow because data is sorted before it is saved and while doing so some page splits will occur and it will take its own time.


There should not be too many indexes else things will slow down heavily.


If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.


Q. What is a covering Index?


Ans. covering index, which is a form of a composite index, includes all of the columns referenced in the SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn’t have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.


On the other hand, if the covering index gets too big (has too many columns), this could actually increase I/O and degrade performance. Generally, when creating covering indexes, follow these guidelines:
• If the query or queries you run using the covering index are seldom run, then the overhead of the covering index may outweigh the benefits it provides.
• The covering index should not add significantly to the size of the key. If it does, then it its use may outweigh the benefits it provides.
• The covering index must include all columns found in the SELECT list, the JOIN clause, and the WHERE clause.


www.simple-talk.com/sql/learn-sql-server...e-query-performance/


Q. Is it possible to use Order by clause in View? If yes, how?


Ans. Generally Order by clause is not allowed in a view. In case we need to use it then we need to use TOP clause.


Select top 10 * from ViewName
Order by Field

Or

Select top 100 Percent from ViewName
Order by Field

Q. Write a query that return Nth highest salary of employees.


SELECT TOP 1 UnitPrice
FROM (
SELECT TOP 12 UnitPrice
FROM Products
ORDER BY UnitPrice DESC) a
ORDER BY UnitPrice


OR


SELECT MIN(UnitPrice) FROM Products
WHERE UnitPrice IN
(SELECT TOP 12 UnitPrice FROM Products ORDER BY UnitPrice DESC)


 
Q. There are 2 tables Customers and Orders and CustomerID is primary key in Customers table and OrderID is primary key in Orders table. CustomerID is foreign key in Orders table.


Write a query to list all Customers who have ordered at least once.

Write a query to list all Customers who are have no orders.


ANS:

--Customers with at least 1 order

Select c.CustomerId, c.ContactName, Count(OrderID)
from Customers c inner join Orders d
on c.CustomerID = d.CustomerId
group by c.CustomerID, c.ContactName
having Count(*) >= 1


--Customers with no orders

select c.CustomerId, c.ContactName, Count(OrderID) NoOfOrders
from Customers c left join Orders d
on c.CustomerID = d.CustomerId
group by c.CustomerID, c.ContactName
having Count(OrderID) = 0

or

Select c.* from Customers c left join
Orders b on c.CustomerID = b.CustomerID
where b.OrderID is null


Q. There is a table Employees with fields like EmployeeID, ReportsTo and CityId. EmployeeID is Primary Key and ReportsTo as foreign Key in Employees table.


Write a query to retrieve all Employees who are Managers.

Write a query to retrieve all employees who live in same city as their managers


Ans.

--All Employees as Managers
Select a.employeeid, a.lastName from employees a inner join employees b
on a.Employeeid = b.Reportsto
group by a.EmployeeID, a.lastname
having count(*) >0

--All Employees who live in same city as their Manager
select a.EmployeeId, b.EmployeeId, a.LastName + ',' + a.FirstName, b.LastName + ',' + b.FirstName, a.City, b.City

From Employees a inner join Employees b
on a.EmployeeID = b.ReportsTo
Where a.City = b.City


Q. Write a query to identify duplicate records in a table.
2 CASES: If we have identity field and another when we don't have identity field



ANS:


create table testWithIdentity
(
ID int Identity(1,1),
Project varchar(10),
hours int,
Activity varchar(10))
Go

Insert into testWithIdentity
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST1'
go




Select * from testWithIdentity
go


Select project, hours, activity from test
group by project, hours, activity
having count(*) > 1


--This statement will work but what about ID field, let’s add ID in select statement.


Select id,project, hours, activity from test
group by id,project, hours, activity
having count(*) > 1


--This shows 0 records as duplicate.


Let’s write our query differently:


Select a.* from test a,
test b
where a.project = b.project and a.hours = b.hours
and a.activity = b.activity
and a.id < b.id


CASE 2:


create table testWithNoIdentity
(
Project varchar(10),
hours int,
Activity varchar(10))
go


Insert into testWithNoIdentity
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST1'
go


Select * from testWithNoIdentity
go


Select * from testWithNoIdentity
group by project, hours, activity
having count(*) > 1



Q. What do we call a table that has no index?


ANS: HEAP

Q. What is the data Page size (actual and available)?


ANS:

8KB = 8196 = TOTAL ROW SIZE


8060 = AVAILABLE ROW SIZE



Q. What are magic tables (hint: Triggers / OUTPUT clause)


ANS: INSERTED and DELETED also known as Special Tables



Q. What will be the output of following snippet:

Declare @val1 varchar(10)
Declare @val2 varchar(10)
Declare @val3 varchar(10)


set @val3=null
set @val2='adadasd'
set @val1=null


Select Coalsec(@val3, @val1, @val1)

The output will be NULL


If we rewrite our statement as Select Coalsec(@val3, @val1, @val2)


It will return


‘adadasd’




Q. What does NULL mean?


ANS: UNKNOWN






Q. If I have a view with definition as Select * from table and table has 3 columns. When I execute Select * from view I see 3 columns.


Now if I alter my table and add 1 more column & execute Select * from view how many columns will be returned & why?

Ans: As View is also an object and it has definition with columns retrieved. As soon as view is created, it creates entry for all fetched columns, so even if we add another field in base table and execute query as Select * from view, it will pick only those fields which are available in its metadata / sys.Columns or syscolumns table.


To update the view schema, execute


sp_refreshview ‘ViewName’


This will refresh the schema definition of the view.



Q. There are 5 records in a table with two columns i.e name and age.


Data in Name field can be anything but age are 20,30,null,40 and null.

What will be the output of following statements:

select avg(age) from table
select count(1) from table

select count(age) from table






Ans:


create table test
(name varchar(100),
age int null)
go


insert into test
select 'ax', 20
union all
select 'bw',30
union all
select 'bw1',null
union all
select 'abw',40
union all
select 'bw1a',null
go






select avg(age) from test – 30 because 20+30+40 = 90 and null columns are not counted for calculating avg, which is a numeric value


go


select count(1) from test –- 5 total records in table are 5


go


select count(age) from test –- 3 because null is excluded, so only 3 rows with number are returned.


Go


Q. How to pass Rows to stored procedure as parameter?


ANS: Use Table Value Parameter


Q. What is the difference in DEALLOCATE and CLOSE CURSOR?


Ans: Close Cursor will close the Cursor but it can be accessed again using Open Cursor. When Deallocate cursor is given, the cursor will be removed from memory. In case that cursor is required again, then we need to create fresh cursor with DECLARE statement again.


Appreciate your feedback. Will come up with more soon.
 
Thanks
 
Sudhir