Sudhir's SQL SERVER Blog

Inside SQL Server Optimizer Book

2011-12-22T14:47:00.000+05:30

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

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

2011-12-01T12:22:00.001+05:30

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

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

2011-10-20T17:29:00.002+05:30

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

Stored Procedure Vs Function in SQL Server

2011-09-30T16:05:00.000+05:30

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

24 Hours of PASS: Summit 2011 Preview

2011-08-29T09:17:00.001+05:30

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

Click here to Register for your choice of sessions.

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

SQL Server Questions Part IV

2011-08-11T12:02:00.000+05:30

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

2011-08-11T07:26:00.001+05:30

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

SQL Server Questions Part III

2011-08-08T10:00:00.000+05:30

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

SSIS Questions - Part 1

2011-08-01T10:04:00.001+05:30

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

Technical Videos of SQL Server Denali

2011-07-15T11:18:00.005+05:30

Hello Everyone,

Here are links of some of the videos that were recorded during Tech-Ed event in US.

Click on each to view the video.

What's New in Microsoft SQL Server Code-Named "Denali" for SQL Server Analysis Services and PowerPivot

What’s New in Microsoft SQL Server Code-Named “Denali” for SQL Server Integration Services

What’s New in Microsoft SQL Server Code-Named “Denali” for Reporting Services

Microsoft SQL Server Code-Named "Denali" AlwaysOn Series,Part 1

Microsoft SQL Server Code-Named "Denali" AlwaysOn Series,Part 2

What's New in Manageability for Microsoft SQL Server Code-Named Denali

Performance Tuning and Optimization in Microsoft SQL Server 2008 R2 and SQL Server Code-Named Denali

What's New in SQL Server "Denali" for Developers | DevDays 2011

What’s New in Security for Microsoft SQL Server Code-Named Denali

Microsoft SQL Server: The Data and BI Platform for Today and Tomorrow

What's Cool in Microsoft SQL Server 2008 R2 and New in SQL Server Code-Named Denali

Happy Learning

Sudhir

SQL Server Questions Part II

2011-07-05T05:39:00.000+05:30

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

Visual Studio 2010 and .NET Framework 4 Training Kit

2011-07-01T20:48:00.000+05:30

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

SQL Server Questions PART - 1

2011-06-27T12:34:00.001+05:30

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

SQL Server Analysis Services (SSAS) Tutorial by MSSQLTips.com

2011-05-25T05:53:00.000+05:30

Guys,

SQL Server Analysis Services (SSAS) is the technology from the Microsoft Business Intelligence stack, to develop Online Analytical Processing (OLAP) solutions. In other terms, we can use SSAS to create cubes using data from data marts / data warehouse for deeper and faster data analysis.

Cubes are multi-dimensional data sources which have dimensions and facts (also known as measures) as its basic constituents. From a relational perspective dimensions can be thought of as master tables and facts can be thought of as measureable details. These details are generally stored in a pre-aggregated proprietary format and users can analyze huge amounts of data and slice this data by dimensions very easily. Multi-dimensional expression (MDX) is the query language used to query a cube, similar to the way T-SQL is used to query a table in SQL Server.

Simple examples of dimensions can be product / geography / time / customer, and similar simple examples of facts can be orders / sales. A typical analysis could be to analyze sales in Asia-pacific geography during the past 5 years. Think of this data as a pivot table where geography is the column-axis and years is the row axis, and sales can be seen as the values. Geography can also have its own hierarchy like Country->City->State. Time can also have its own hierarchy like Year->Semester->Quarter. Sales could then be analyzed using any of these hierarchies for effective data analysis.

A typical higher level cube development process using SSAS involves the following steps:

1) Reading data from a dimensional model
2) Configuring a schema in BIDS (Business Intelligence Development Studio)
3) Creating dimensions, measures and cubes from this schema
4) Fine tuning the cube as per the requirements
5) Deploying the cube

This tutorial will step through a number of topics that we need to understand in order to successfully create a basic cube. Gigh level outline is as follows:

Design and develop a star-schema
Create dimensions, hierarchies, and cubes
Process and deploy a cube
Develop calculated measures and named sets using MDX
Browse the cube data using Excel as the client tool

In this tutorial, we will also try to develop an understanding of OLAP development from the eyes of an OLTP practitioner.

Click here to read tutorial.

Happy Learning

Sudhir

SQL Server Integration Services Tutorial by MSSQLTIPS.com

2011-05-24T13:06:00.000+05:30

Guys,

SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations; i.e. Extract, Transform and Load data. While ETL processing is common in data warehousing (DW) applications, SSIS can do much more than that e.g. when we create a Maintenance Plan using SQL Server Management Studio (SSMS) an SSIS package is created, when we export or import data, an SSIS package is created.

At a high level, SSIS provides the ability to:

Retrieve data from just about any source
Perform various transformations on the data; e.g. convert from one type to another, convert to uppercase or lowercase, perform calculations, etc.
Load data into just about any source
Define a workflow

In this tutorial we will step through a number of topics that we need to understand in order to successfully build an SSIS package. Our high level outline is as follows:

Creating SSIS packages with SQL Server Management Studio (SSMS)
Business Intelligence Development Studio (BIDS)
Creating a simple SSIS package in BIDS
Deploying SSIS packages
Executing SSIS packages

Click here to read tutorial.

Happy learning

Sudhir

SQL Server Reporting Services Tutorial by MSSQLTips.com

2011-05-24T12:47:00.001+05:30

Guys,

SQL Server Reporting Services 2008 (SSRS) is a feature included in the SQL Server 2008 product. We use SSRS to design, develop, test, and deploy reports. SSRS leverages Business Intelligence Development Studio (BIDS) developer tool for all aspects of authoring and deploying reports.

In this tutorial we will step through a number of topics that we need to understand to successfully build a report. The tutorial will run through following topics:

Reporting Services Components
Install Reporting Services
Business Intelligence Development Studio (BIDS)
Install Sample Database
Create a Simple Report with the Wizard
Create a Simple Report with the Report Designer
Deploy Reports
Configure Report Manager Security

Click here to read tutorial.

Happy Learning

Sudhir

SSWUG Free Expo Event: Understanding SQL Server Analysis Services

2011-05-16T19:01:00.002+05:30

Guys,

SSWUG.ORG’s virtual expo will review various aspects of SQL Server Analysis Services (SSAS), which enables the server to be used for analytical processing and data mining. Through in-depth sessions with four of the leading experts in the information technology (IT) and business intelligence (BI) fields, we will see many demonstrations and examples on designing, creating and managing data from multiple sources. By the end of our event, we should have the tools and understanding needed to bring added functionality, automation and insight into your data.

Please register now to learn from four acclaimed SSAS experts for free on Friday, May 20! Click here to Register.

Friday, May 20, 2011
9 a.m. - 1 p.m. PDT

Sessions will cover the following topics:

Common Mistakes with SSAS Cube Designs Sudhir
SSAS Partitioning and Aggregation Strategies
Properly Using the SSAS Query Cache
Overcoming SSAS Implementation Issues
Building a Scalable SSAS Solution
Best Practices for Performance and Tuning Techniques

Introduction to MDX video & slides by Business Intelligence architect and MDX expert Bill Pearson

2011-05-12T19:53:00.000+05:30

Guys,

To explore the basic functions of MDX and view many practical examples on using the query language in SSWUG.ORG’s "Introduction to MDX" webcast series. In three, in-depth sessions, Business Intelligence architect and MDX expert Bill Pearson will focus on the basic components of MDX, as well as provide information on crafting simple MDX expressions and queries that generate result sets. By the end of this three-part series, we should have the information needed to meet many real-world business needs.

The first session will concentrate on crafting simple MDX expressions and queries whose purposes, for the most part, are to return a set of data. Bill will review the structure of a cube, using the sample "Adventure Works" cube as an example and is available to anyone installing SQL Server Analysis Services 2008 R2. He will also outline the components of simple MDX syntax, explain how to begin writing basic expressions and queries, expose basic member functions and introduce filters (or “slicers”). Finally, we will have the chance to explore core MDX functionality, including calculated members and named sets.

Click here to register for the video and presentation slides.

All the best & happy learning.

Sudhir

Singapore SQL Server User Group on 28th April

2011-04-24T11:45:00.000+05:30

Hi,

Welcome to SQL Server User Group Singapore. Following are details of this months meeting on 28th April at Microsoft.

Topic:

Introduction to Business Intelligence – Renaud/Michael will deliver the details of Decision support process , needs and BI From Business needs to Business Intelligence Solutions, The "Boss"questions, Applied Example, Real life project demo

What’s new in SSRS 2008 R2?– SSRS 2008 R2 is a minor version upgrade from SSRS 2008, however, there are many features which are very useful to aid in the development of reports using SSRS. Come and join me about a whirlwind tour about the various features in SSRS 2008 R2.

Date: April 28th, 2011
Time: 7:00PM – 9:00 PM
Registration starts at 6:30 PM
Venue: NTUC Building 22CF-12, Microsoft Office One Marina Boulevard

Speaker profile:
Renaud HARDUIN is ESSILOR European IT Singapore Developement Service Manager, in Singapore (ESSILOR ITSS / 38 people / 6 teams). He is in charge of Europe IT Development Team and support for various Business Support. Renaud is is MVP on SQL/BI since 2005

Michael LABASTIDA is ESSILOR BI Technical Leader & BI Expert In charge of one of the major BI ESSILOR Project in ESSILOR ITSS.

Alvin Lau is your average Joe in the application space and is a large advocate of .NET technologies since 2005. In his spare time, he runs SGDOTNET, a Singapore-based .NET user group, and loves to share his experience and knowledge. His area of interests includes ASP.Net, Agile development, SharePoint and Microsoft BI.

Our website of sql user group : http://sgsql.org

Please confirm your attendance by sending email to SGSQL@LIVE.COM

See you there on 28th April, 2010

Enjoy

Sudhir

Designing Cubes for Performance by Stacia Misner

2011-04-21T11:32:00.000+05:30

Guys,

If you are a SQL Server BI developer, who wants to enhance his/her skills on Cube performances then please click on below links to view presentation & recordings of session given by MVP Stacia Misner.

I am sure this will be really helpful.

Enjoy & Happy Learning

Sudhir
Follow this link for the details:

Click here to download Slides

Stairway to SSRS Part 1-> SSRS in Flash

2011-04-15T12:48:00.001+05:30

Guys,

If you are interested in learning about SSRS (SQL Server Reporting Services) then have a look at this article. This article from Jessica Moss (SQL Server MVP) shows how we can get started in SSRS.

We can learn following from this article:

What is SQL Server Reporting Services?
What can we do with SSRS reports?
What components does SSRS use?

To read complete article, click here.

Happy Learning

Sudhir

The SSIS Data Pump - Step 2 of the Stairway to Integration Services

2011-03-31T07:31:00.000+05:30

Guys,

As we all know that SQL Server Integration Services is one of the most popular subsystems in SQL Server. In allows us to Extract, Transform, and Load (ETL) data between a variety of data sources and programmatically change data in any manner you can think of and script in C#.

In our previous Part 1 – BIDS we learned our way around Business Intelligence Development Studio and we are now itching to build an SSIS package that moves real data!

This article explains:

The relationship between Connection Managers and adapters (OLE DB Source and OLE DB Destination)
Role and some features of Data Flow Paths that connect Data Flow Task components to each and represent the "data flow pipeline"
Design-time validation and how to view Errors and Warnings.
Some cool time-saving features of the Adapter Editors - we will fall in love with these New buttons!
Formatting components on the Data Flow canvas so they're aligned.
Take a peek under the hood of the Data Flow Task, viewing the Data Flow Path metadata.
How this metadata can be used by the OLE DB Destination to build a CREATE TABLE statement.

Click here to read complete article.

Happy Learning

Sudhir

What is SSIS? Step 1 of the Stairway to Integration Services

2011-03-31T07:23:00.000+05:30

Guys,

As we know that SQL Server Integration Services is one of the most popular subsystems in SQL Server. In allows us to Extract, Transform, and Load (ETL) data between a variety of data sources and programmatically change data in any manner you can think of and script in C#.

We can use SSIS to:

Perform ETL (Extract, Transform, and Load) operations
Transfer data
Copy database objects
Perform FTP operations
Work with files

This article from SQLServerCentral provides an introduction to SQL Server Integration Services, and an overview of its architecture.

To read this article, click here

Happy learning

Sudhir

Resources for migrating from Oracle/Sybase/MySQL/Access to SQL Server and SQL Azure

2011-03-21T10:51:00.001+05:30

Guys,

Everyone faces issues when migrating data from various sources like Oracle, MySQL, Sybase or Access to SQL Server.

We might have come across issues like:

How to correct schema issues?
Data type conversion cases
Should comments be saved or no?

and many more.

To get an answer to these questions, refer to Microsoft SQL Server Migration Assistant (SSMA) Team's Blog

There are many examples, which are very helpful.

Happy Learning.

Sudhir

Stairway to SQL Server Agent: Step 1: Setup and Overview

2011-03-10T06:21:00.000+05:30

Hi,

We all come across a situation where we need to schedule something & run the process in background automatically in database itself.

The tool that we can use to achieve this task is SQL SERVER AGENT.

SQL Server Agent is at the heart of any live database system. The Agent has a number of uses which aren't always obvious, and so a knowledge of the system is always useful, to developers as well as DBAs. Richard Waymire provides a simple explanation of its many uses.

In this article Richard explains following things:

SQL Server Agent Setup and Overview
Getting SQL Server Agent Running
Setting the Agent to Auto-start with SQL Server
Creating your first Job

Click here to read full article.

Happy Learning

Sudhir

Sudhir's SQL SERVER Blog

Inside SQL Server Optimizer Book

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

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

Stored Procedure Vs Function in SQL Server

24 Hours of PASS: Summit 2011 Preview

SQL Server Questions Part IV

Download SQL Server Code Name “Denali” CTP3 Product Guide

SQL Server Questions Part III

SSIS Questions - Part 1

Technical Videos of SQL Server Denali

SQL Server Questions Part II

Visual Studio 2010 and .NET Framework 4 Training Kit

SQL Server Questions PART - 1

SQL Server Analysis Services (SSAS) Tutorial by MSSQLTips.com

SQL Server Integration Services Tutorial by MSSQLTIPS.com

SQL Server Reporting Services Tutorial by MSSQLTips.com

SSWUG Free Expo Event: Understanding SQL Server Analysis Services

Friday, May 20, 2011 9 a.m. - 1 p.m. PDT Sessions will cover the following topics:

Introduction to MDX video & slides by Business Intelligence architect and MDX expert Bill Pearson

Singapore SQL Server User Group on 28th April

Designing Cubes for Performance by Stacia Misner

Stairway to SSRS Part 1-> SSRS in Flash

The SSIS Data Pump - Step 2 of the Stairway to Integration Services

What is SSIS? Step 1 of the Stairway to Integration Services

Resources for migrating from Oracle/Sybase/MySQL/Access to SQL Server and SQL Azure

Stairway to SQL Server Agent: Step 1: Setup and Overview

Friday, May 20, 2011
9 a.m. - 1 p.m. PDT

Sessions will cover the following topics: