[code]
SELECT * FROM sys.columns WHERE NAME LIKE '%ColumnName%'
[/code]
Result Set will contain object_ids in which the "ColumnName" exists. Use following queries to get the corresponding object names.
[code]
SELECT * FROM sys.objects WHERE object_id in (39879409,
729261853,
2044026513
)
[/code]
More civilized code goes here;)
[code]
SELECT * FROM sys.objects O
INNER JOIN sys.columns C
ON O.object_id = C.object_id
WHERE C.NAME LIKE '%ColumnName%'
[/code]
Monday, January 30, 2012
Clustered and Non Clustered Indexes
Clustered Index
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows
Non-Clustered Index
A nonclustered index is analogous to an index in a textbook.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.
However, it’s important to keep in mind that non-clustered indexes slow down the data modification and insertion process, so indexes should be kept to a minimum
In following example column FileName is varchar(400), which will increase the size of the index key bigger than it is allowed. If we still want to include in our cover index to gain performance we can do it by using the Keyword INCLUDE.
Non-key columns can be included only in non-clustered indexes. Columns can’t be defined in both the key column and the INCLUDE list. Column names can’t be repeated in the INCLUDE list. Non-key columns can be dropped from a table only after the non-key index is dropped first. For Included Column Index to exist there must be at least one key column defined with a maximum of 16 key columns and 1023 included columns.
Avoid adding unnecessary columns. Adding too many index columns, key or non-key as they will affect negatively on performance. Fewer index rows will fit on a page. This could create I/O increases and reduced cache efficiency. More disk space will be required to store the index. Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.
Another example to test:
Create following Index on Database AdventureWorks in SQL SERVER 2005
Test the performance of following query before and after creating Index. The performance improvement is significant.
One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.
You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience excellent performance gains.
SQL Server provides a wonderful facility known as the Index Tuning Wizard which greatly enhances the index selection process. To use this tool, first use SQL Profiler to capture a trace of the activity for which you wish to optimize performance. You may wish to run the trace for an extended period of time to capture a wide range of activity. Then, using Enterprise Manager, start the Index Tuning Wizard and instruct it to recommend indexes based upon the captured trace. It will not only suggest appropriate columns for queries but also provide you with an estimate of the performance increase you’ll experience after making those changes!
When Non Clustered Indexes should be used:
http://www.sql-server-performance.com/2007/nonclustered-indexes/
Before you create nonclustered indexes, understand how your data will be accessed. Consider using nonclustered indexes for:
References: http://blog.sqlauthority.com/2007/04/23/sql-server-understanding-new-index-type-of-sql-server-2005-included-column-index-along-with-clustered-index-and-non-clustered-index/
Index tuning: http://www.brentozar.com/archive/2009/07/tuning-tip-identify-overlapping-indexes/
Covered Index: http://blog.sqlauthority.com/2010/03/09/sql-server-improve-performance-by-reducing-io-creating-covered-index/
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows
- Only 1 allowed per table
- Physically rearranges the data in the table to conform to the index constraints
- For use on columns that are frequently searched for ranges of data
- For use on columns with low selectivity
Non-Clustered Index
A nonclustered index is analogous to an index in a textbook.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.
However, it’s important to keep in mind that non-clustered indexes slow down the data modification and insertion process, so indexes should be kept to a minimum
- Up to 249 allowed per table in SQL 2000,2005 and upto 999 allowed in SQL 2008
- Creates a separate list of key values with pointers to the location of the data in the data pages
- For use on columns that are searched for single values
- For use on columns with high selectivity
In following example column FileName is varchar(400), which will increase the size of the index key bigger than it is allowed. If we still want to include in our cover index to gain performance we can do it by using the Keyword INCLUDE.
USE AdventureWorks
GO
CREATE INDEX IX_Document_Title
ON Production.Document (Title, Revision)
INCLUDE (FileName)Non-key columns can be included only in non-clustered indexes. Columns can’t be defined in both the key column and the INCLUDE list. Column names can’t be repeated in the INCLUDE list. Non-key columns can be dropped from a table only after the non-key index is dropped first. For Included Column Index to exist there must be at least one key column defined with a maximum of 16 key columns and 1023 included columns.
Avoid adding unnecessary columns. Adding too many index columns, key or non-key as they will affect negatively on performance. Fewer index rows will fit on a page. This could create I/O increases and reduced cache efficiency. More disk space will be required to store the index. Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.
Another example to test:
Create following Index on Database AdventureWorks in SQL SERVER 2005
USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)
GOTest the performance of following query before and after creating Index. The performance improvement is significant.
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN '98000'
AND '99999';
GOOne of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.
You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience excellent performance gains.
SQL Server provides a wonderful facility known as the Index Tuning Wizard which greatly enhances the index selection process. To use this tool, first use SQL Profiler to capture a trace of the activity for which you wish to optimize performance. You may wish to run the trace for an extended period of time to capture a wide range of activity. Then, using Enterprise Manager, start the Index Tuning Wizard and instruct it to recommend indexes based upon the captured trace. It will not only suggest appropriate columns for queries but also provide you with an estimate of the performance increase you’ll experience after making those changes!
When Non Clustered Indexes should be used:
http://www.sql-server-performance.com/2007/nonclustered-indexes/
Before you create nonclustered indexes, understand how your data will be accessed. Consider using nonclustered indexes for:
- Columns that contain a large number of distinct values, such as a combination of last name and first name (if a clustered index is used for other columns). If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is usually more efficient.
- Queries that do not return large result sets.
- Columns frequently involved in search conditions of a query (WHERE clause) that return exact matches.
- Decision-support-system applications for which joins and grouping are frequently required. Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.
- Covering all columns from one table in a given query. This eliminates accessing the table or clustered index altogether.
References: http://blog.sqlauthority.com/2007/04/23/sql-server-understanding-new-index-type-of-sql-server-2005-included-column-index-along-with-clustered-index-and-non-clustered-index/
Index tuning: http://www.brentozar.com/archive/2009/07/tuning-tip-identify-overlapping-indexes/
Covered Index: http://blog.sqlauthority.com/2010/03/09/sql-server-improve-performance-by-reducing-io-creating-covered-index/
Saturday, January 28, 2012
Interview question - Quick Links
SSRS
http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_1.aspx
http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_2.aspx
http://venkateswarlu.co.in/FAQ/CHAPTER_10.aspx
http://sqlserversolutions.blogspot.com/2011/06/ssrs-interview-questions.html
Running value function: http://mangalpardeshi.blogspot.in/2009/03/runningvalue-function-reporting.html
SSRS Formatting
http://sqlsafety.blogspot.in/2010/02/ssrs-formatting.html
SSRS Functions:
http://www.venkateswarlu.co.in/BI/ssrs/SSRS_Common_Functions__Text.aspx
SSRS Tips and Tricks
http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/67660/
Dataware housing
http://blog.sqlauthority.com/2007/07/25/sql-server-data-warehousing-interview-questions-and-answers-introduction/
SSIS
http://venkateswarlu.co.in/FAQ/Chapter_1.aspx -- --V Good
http://www.sql-server-business-intelligence.com/sql-server/interview-questions-and-answers/ssis-interview-questions-and-answers-pdf-download
http://www.aboutsql.in/
SSIS Event Handlers and Error Logging:
http://beyondrelational.com/modules/12/tutorials/24/tutorials/9686/getting-started-with-ssis-part-10-event-handling-and-logging.
Fuzzy Grouping:
http://www.bimonkey.com/2009/11/the-fuzzy-grouping-transformation/
http://f5debug.net/2011/04/25/sql-server-integration-services-ssis-%E2%80%93-part-45-%E2%80%93-fuzzy-grouping-transformation-in-ssis/
Fuzzy Look Up: http://www.bimonkey.com/2009/06/the-fuzzy-lookup-transformation/
SSIS Performance Tuning: http://technet.microsoft.com/en-us/library/cc966529.aspx
SSIS Check Points:
http://simonworth.wordpress.com/2009/11/08/ssis-package-properties-checkpoints/
Multicast Transformation vs Condition Split:
http://www.mssqltips.com/sqlservertip/2047/ssis-multicast-transformation-vs-conditional-split-transformation/
.Net: http://faq.programmerworld.net/programming/asp.net-interview-questions-answers.htm
Sql Server Performance tuning: http://blog.sqlauthority.com/2008/04/25/sql-server-optimization-rules-of-thumb-best-practices/ <--do Check the comments to the post
SDLC:
http://www.wiziq.com/tutorial/119305-Software-Engineering-What-Why-amp-How
SQL:
User Defined Datatypes: http://www.venkateswarlu.co.in/articles/SQL/UserDefined_Data_Types_in_sql_server.aspx
Performance Related: http://www.venkateswarlu.co.in/articles/SQL/SQL_performance.aspx
http://www.venkateswarlu.co.in/articles/SQL/Performance_Tuning.aspx
Rank, Dense_Rank, row_number: http://www.venkateswarlu.co.in/articles/SQL/RANK_RowNumber_DenseRank.aspx
Miscellaneous: http://venkateswarlu.co.in/sqlserver/articles.aspx
.Net:
http://www.dotnetfunda.com/interview/showcatquestion.aspx?category=33
Single tier – MS access, MS excel- Single file – direct acess ---usually single user application
2 tier – Client – Server application --multi user apps.
Multi Tier applications?
- 3 tier or N tier
- Presentation Layer , Business Logic and Database Layer
3 Tier Architecture Advantages:
- Easy to maintain
- Components are resusable
- Faster development by division of work
http://blog.simcrest.com/what-is-3-tier-architecture-and-why-do-you-need-it/
http://queens.db.toronto.edu/~papaggel/courses/csc309/docs/lectures/web-architectures.pdf
Design Patterns?
Solution to common problems defined by gang of four programmers.
data modeling?
SDLC Models?
Software development life cycle :
Waterfall model – requirement garthering, designing, implementation, verification\testing, maintainace
Incremental\Iterative Model This method doesn’t require to start with full requirement.
Proto-type Model In this model , a proto-type (an early approximation of a final system or product) is build for user acceptance and the improvements are done untill the users are convinced.
Spiral Model Combined features of proto-type and waterfall model.
Rapid application development Model Based on component integration techniques
http://www.slideshare.net/SivaprasanthRentala1975/sdlc-models
White box testing(tranasparent testing) – internal – logical\data flow testing
Black box testing – input and desired output testing, not concerened with the inner code.
Grey box à combination of white and black box
SSIS – Tasks?
http://venkateswarlu.co.in/FAQ/Chapter_1.aspx#.UPvFTB1vNAM
SSIS Auditing and Error Handling?
http://hardik-bhavsar.blogspot.in/2011/08/auditing-and-error-handling-in-ssis.html
SSIS deploying packages:
Sql Server
Normalisation – process of organising data in form of table and define relationship and hence form a realtional database.
RDBMS – ACID – atomicity , consistency, isolation, durability
1 NF – eliminating redundant groups
2 NF – eliminating redundant data
3NF - Eliminate Columns Not Dependent On Key
Trigers – DDL (drop table, create table, alter table or login events)
DML (insert update delete)
Instead off – fired instead off insert update delete
SSRS – variables – overview?
Delete duplicate rows
[code]
WITH CTE (COl1,Col2, DuplicateCount) AS
(SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) ASDuplicateCount
FROM DuplicateRcordTable)
DELETE FROM CTE WHERE DuplicateCount >1
[/code]
Master – contain information for all databases
Model – template for database --
MSDB –dts packages, jobs
Resource: read only – contain all system objects – sys.objects
Split Function: write a split function to get output of (1,2,3) in a table in different rows
Scope Identity and @@identity
It returns the last identity value generated for any table in the current session, across all scopes.
Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger.
It returns the last identity value generated for any table in the current session and the current scope.
Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.
It returns the last identity value generated for a specific table in any session and any scope.
In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.
Date , date part functions
http://shawpnendu.blogspot.in/2009/05/datepart-dateadd-datediff-sql-server.html
Case and if statement: difference , syntax
[code]
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
[/code]
‘If statement’ cannot be used inside select statement <--- v important
Table variable and #table difference – advantages and disadvantages:
http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table
major disadvantage of table variable
http://ssisdevelopers.wordpress.com/2013/03/04/temp-table-vs-table-variable-sql-server/
Power of CTE
http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example
http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/
Can two different SPs create same #table or ##table (temp tables)
Rank dense rank row number
Pivot and unpivot
Find the top third row from a dataset
Find the day(Monday, Tuesday etc) on the first day of the current month (lets say today is 25th feb)
Steps for optimisation
OOPS Concepts? Inheritance, polimorphism (operation overloding and overridding) , encapsulation,abstraction
http://www.c-sharpcorner.com/UploadFile/eecabral/OOPSand.NET211102005075520AM/OOPSand.NET2.aspx
http://manishagrahari.blogspot.in/2011/08/oops.html
http://dotnetstories.wordpress.com/2009/06/21/object-oriented-programming-concepts-with-c3-0/
Boxing – converting a value type to reference type
unboxing -- reverese i.e. converting a reference type to value type
http://stackoverflow.com/questions/2111857/why-do-we-need-boxing-and-unboxing-in-c
Delegates – function pointers
Cloud computing is the use of computing resources (hardware and software) that are delivered as a service over a network (typically the Internet). The name comes from the use of a cloud-shaped symbol as an abstraction for the complex infrastructure it contains in system diagrams. Cloud computing entrusts remote services with a user's data, software and computation. There are many types of public cloud computing:[1]
In the business model using software as a service, users are provided access to application software and databases. The cloud providers manage the infrastructure and platforms on which the applications run. SaaS is sometimes referred to as “on-demand software” and is usually priced on a pay-per-use basis. SaaS providers generally price applications using a subscription fee.
Advantages:
Disadvantages:
Software as a Service (SaaS)
Software as a service (SaaS) sometimes referred to as "on-demand software", is a software delivery model in which software and associated data are centrally hosted on the cloud. SaaS is typically accessed by users using a thin client via a web browser.e.g. Facebook.
http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_1.aspx
http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_2.aspx
http://venkateswarlu.co.in/FAQ/CHAPTER_10.aspx
http://sqlserversolutions.blogspot.com/2011/06/ssrs-interview-questions.html
Running value function: http://mangalpardeshi.blogspot.in/2009/03/runningvalue-function-reporting.html
SSRS Formatting
http://sqlsafety.blogspot.in/2010/02/ssrs-formatting.html
SSRS Functions:
http://www.venkateswarlu.co.in/BI/ssrs/SSRS_Common_Functions__Text.aspx
SSRS Tips and Tricks
http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/67660/
Dataware housing
http://blog.sqlauthority.com/2007/07/25/sql-server-data-warehousing-interview-questions-and-answers-introduction/
SSIS
http://venkateswarlu.co.in/FAQ/Chapter_1.aspx -- --V Good
http://www.sql-server-business-intelligence.com/sql-server/interview-questions-and-answers/ssis-interview-questions-and-answers-pdf-download
http://www.aboutsql.in/
SSIS Event Handlers and Error Logging:
http://beyondrelational.com/modules/12/tutorials/24/tutorials/9686/getting-started-with-ssis-part-10-event-handling-and-logging.
Fuzzy Grouping:
http://www.bimonkey.com/2009/11/the-fuzzy-grouping-transformation/
http://f5debug.net/2011/04/25/sql-server-integration-services-ssis-%E2%80%93-part-45-%E2%80%93-fuzzy-grouping-transformation-in-ssis/
Fuzzy Look Up: http://www.bimonkey.com/2009/06/the-fuzzy-lookup-transformation/
SSIS Performance Tuning: http://technet.microsoft.com/en-us/library/cc966529.aspx
SSIS Check Points:
http://simonworth.wordpress.com/2009/11/08/ssis-package-properties-checkpoints/
Multicast Transformation vs Condition Split:
http://www.mssqltips.com/sqlservertip/2047/ssis-multicast-transformation-vs-conditional-split-transformation/
.Net: http://faq.programmerworld.net/programming/asp.net-interview-questions-answers.htm
Sql Server Performance tuning: http://blog.sqlauthority.com/2008/04/25/sql-server-optimization-rules-of-thumb-best-practices/ <--do Check the comments to the post
SDLC:
http://www.wiziq.com/tutorial/119305-Software-Engineering-What-Why-amp-How
SQL:
User Defined Datatypes: http://www.venkateswarlu.co.in/articles/SQL/UserDefined_Data_Types_in_sql_server.aspx
Performance Related: http://www.venkateswarlu.co.in/articles/SQL/SQL_performance.aspx
http://www.venkateswarlu.co.in/articles/SQL/Performance_Tuning.aspx
Rank, Dense_Rank, row_number: http://www.venkateswarlu.co.in/articles/SQL/RANK_RowNumber_DenseRank.aspx
Miscellaneous: http://venkateswarlu.co.in/sqlserver/articles.aspx
.Net:
http://www.dotnetfunda.com/interview/showcatquestion.aspx?category=33
Single tier – MS access, MS excel- Single file – direct acess ---usually single user application
2 tier – Client – Server application --multi user apps.
Multi Tier applications?
- 3 tier or N tier
- Presentation Layer , Business Logic and Database Layer
- Presentation Layer – UI or Front end
- Handles User interactions – Screen validations
- Should not contain business logic
- Business Logic Layer – Middle ware or Back end
- Set of rules to process the information
- Should not contain presentation or data access code
- Data Layer
- Database , Datasets , RDBMS\DBMS
- Provide access to back-end i.e. database
3 Tier Architecture Advantages:
- Easy to maintain
- Components are resusable
- Faster development by division of work
- Web designer can do presentation
- Software engineer can do the logic part
- DB devloper and admin can handle the backend and data modeling
http://blog.simcrest.com/what-is-3-tier-architecture-and-why-do-you-need-it/
http://queens.db.toronto.edu/~papaggel/courses/csc309/docs/lectures/web-architectures.pdf
Design Patterns?
Solution to common problems defined by gang of four programmers.
- Factory - Creates an instance of several derived classes
- Abstract Factory - Creates an instance of several families of classes
- Singlton – single instance of a class can exists
- Builder - Separates object construction from its representation
data modeling?
- Process of defining and analysing data requirements to support business processes.
- ER diagrams are designed in VISIO to model the data requirements
SDLC Models?
Software development life cycle :
- Waterfall model
- Rapid application development Model
- Incremental\Iterative Model
- Proto-type Model
- Spiral Model
Waterfall model – requirement garthering, designing, implementation, verification\testing, maintainace
- Each phase has to be completed before the next starts
- A review takes place after a particular phase completes and the next starts. – review is with the aim to check if the project is on right path.
- No overlapping of phases
- Easy to use and implement
- Not good for projects where requirements changes frequently.
- Poor model for complex projects
Incremental\Iterative Model This method doesn’t require to start with full requirement.
- Also called Multi – waterfall model
- Development and delivery is broken down into increments
Proto-type Model In this model , a proto-type (an early approximation of a final system or product) is build for user acceptance and the improvements are done untill the users are convinced.
Spiral Model Combined features of proto-type and waterfall model.
- Similar to incremental model
- Planing, Risk analysis, engineering and evaluation
Rapid application development Model Based on component integration techniques
- Short development cycle and linear squential model
- Phases: business modeling , data modeling , process modeling, application modeling, testing.
http://www.slideshare.net/SivaprasanthRentala1975/sdlc-models
White box testing(tranasparent testing) – internal – logical\data flow testing
Black box testing – input and desired output testing, not concerened with the inner code.
Grey box à combination of white and black box
SSIS – Tasks?
- Data Flow task –extracts data from source, allow transformations, and the load data into target data destination
- File System task – copy/move/delete files and folder over a file system
- FTP task – copy/move/delete files and folder over a FTP
- Execute package
- Execute SQL
- Execute Script
- Send Mail
- Execute sql server agent job
- Notify operator
- Back up database
- Bulk insert
- For Loop: Repeat a task a fixed number of times
- Foreach Loop: Repeat a task by enumerating over a group of objects
http://venkateswarlu.co.in/FAQ/Chapter_1.aspx#.UPvFTB1vNAM
SSIS Auditing and Error Handling?
http://hardik-bhavsar.blogspot.in/2011/08/auditing-and-error-handling-in-ssis.html
SSIS deploying packages:
- Database – schedule using Jobs
- File system – schedule using control M
- Can be called via command prompt or execute the .dtsx package
Sql Server
Normalisation – process of organising data in form of table and define relationship and hence form a realtional database.
RDBMS – ACID – atomicity , consistency, isolation, durability
1 NF – eliminating redundant groups
2 NF – eliminating redundant data
3NF - Eliminate Columns Not Dependent On Key
Trigers – DDL (drop table, create table, alter table or login events)
DML (insert update delete)
Instead off – fired instead off insert update delete
SSRS – variables – overview?
Delete duplicate rows
[code]
WITH CTE (COl1,Col2, DuplicateCount) AS
(SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) ASDuplicateCount
FROM DuplicateRcordTable)
DELETE FROM CTE WHERE DuplicateCount >1
[/code]
Master – contain information for all databases
Model – template for database --
MSDB –dts packages, jobs
Resource: read only – contain all system objects – sys.objects
Split Function: write a split function to get output of (1,2,3) in a table in different rows
Scope Identity and @@identity
@@IDENTITY
It returns the last identity value generated for any table in the current session, across all scopes.
Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger.
SCOPE_IDENTITY
It returns the last identity value generated for any table in the current session and the current scope.
Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.
IDENT_CURRENT
It returns the last identity value generated for a specific table in any session and any scope.
In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.
Date , date part functions
http://shawpnendu.blogspot.in/2009/05/datepart-dateadd-datediff-sql-server.html
Case and if statement: difference , syntax
[code]
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
[/code]
‘If statement’ cannot be used inside select statement <--- v important
Table variable and #table difference – advantages and disadvantages:
http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table
major disadvantage of table variable
- Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.
- Temp tables behave same as normal tables and are bound by transactions
http://ssisdevelopers.wordpress.com/2013/03/04/temp-table-vs-table-variable-sql-server/
Power of CTE
http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example
http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/
Can two different SPs create same #table or ##table (temp tables)
Rank dense rank row number
Pivot and unpivot
Find the top third row from a dataset
Find the day(Monday, Tuesday etc) on the first day of the current month (lets say today is 25th feb)
Steps for optimisation
OOPS Concepts? Inheritance, polimorphism (operation overloding and overridding) , encapsulation,abstraction
http://www.c-sharpcorner.com/UploadFile/eecabral/OOPSand.NET211102005075520AM/OOPSand.NET2.aspx
http://manishagrahari.blogspot.in/2011/08/oops.html
http://dotnetstories.wordpress.com/2009/06/21/object-oriented-programming-concepts-with-c3-0/
Boxing – converting a value type to reference type
unboxing -- reverese i.e. converting a reference type to value type
http://stackoverflow.com/questions/2111857/why-do-we-need-boxing-and-unboxing-in-c
Delegates – function pointers
Cloud computing
Cloud computing is the use of computing resources (hardware and software) that are delivered as a service over a network (typically the Internet). The name comes from the use of a cloud-shaped symbol as an abstraction for the complex infrastructure it contains in system diagrams. Cloud computing entrusts remote services with a user's data, software and computation. There are many types of public cloud computing:[1]
- Infrastructure as a service (IaaS)
- Platform as a service (PaaS)
- Software as a service (SaaS)
- Network as a service (NaaS)
- Storage as a service (STaaS)
- Security as a service (SECaaS)
In the business model using software as a service, users are provided access to application software and databases. The cloud providers manage the infrastructure and platforms on which the applications run. SaaS is sometimes referred to as “on-demand software” and is usually priced on a pay-per-use basis. SaaS providers generally price applications using a subscription fee.
Advantages:
- SaaS allows a business the potential to reduce IT operational costs by outsourcing hardware and software maintenance and support to the cloud provider.
- This enables the business to reallocate IT operations costs away from hardware/software spending and personnel expenses, towards meeting other IT goals.
- In addition, with applications hosted centrally, updates can be released without the need for users to install new software.
Disadvantages:
- One drawback of SaaS is that the users' data are stored on the cloud provider’s server. As a result, there could be unauthorized access to the data.
- Basically from the security point of view it is not the safest as compared to others.
Software as a Service (SaaS)
Software as a service (SaaS) sometimes referred to as "on-demand software", is a software delivery model in which software and associated data are centrally hosted on the cloud. SaaS is typically accessed by users using a thin client via a web browser.e.g. Facebook.
SSRS
http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_1.aspx
http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_2.aspx
http://venkateswarlu.co.in/FAQ/CHAPTER_10.aspx
http://sqlserversolutions.blogspot.com/2011/06/ssrs-interview-questions.html
SSRS Formatting
http://sqlsafety.blogspot.in/2010/02/ssrs-formatting.html
SSRS Functions:
http://www.venkateswarlu.co.in/BI/ssrs/SSRS_Common_Functions__Text.aspx
SSRS Tips and Tricks
http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/67660/
Dataware housing
http://blog.sqlauthority.com/2007/07/25/sql-server-data-warehousing-interview-questions-and-answers-introduction/
SSIS
http://venkateswarlu.co.in/FAQ/Chapter_1.aspx -- --V Good
http://www.sql-server-business-intelligence.com/sql-server/interview-questions-and-answers/ssis-interview-questions-and-answers-pdf-download
SSIS Event Handlers and Error Logging:
http://beyondrelational.com/modules/12/tutorials/24/tutorials/9686/getting-started-with-ssis-part-10-event-handling-and-logging.
Fuzzy Grouping:
http://www.bimonkey.com/2009/11/the-fuzzy-grouping-transformation/
http://f5debug.net/2011/04/25/sql-server-integration-services-ssis-%E2%80%93-part-45-%E2%80%93-fuzzy-grouping-transformation-in-ssis/
Fuzzy Look Up: http://www.bimonkey.com/2009/06/the-fuzzy-lookup-transformation/
SSIS Performance Tuning: http://technet.microsoft.com/en-us/library/cc966529.aspx
SSIS Check Points:
http://simonworth.wordpress.com/2009/11/08/ssis-package-properties-checkpoints/
Multicast Transformation vs Condition Split:
http://www.mssqltips.com/sqlservertip/2047/ssis-multicast-transformation-vs-conditional-split-transformation/
.Net: http://faq.programmerworld.net/programming/asp.net-interview-questions-answers.htm
Sql Server Performance tuning: http://blog.sqlauthority.com/2008/04/25/sql-server-optimization-rules-of-thumb-best-practices/ <--do Check the comments to the post
SDLC:
http://www.wiziq.com/tutorial/119305-Software-Engineering-What-Why-amp-How
SQL:
User Defined Datatypes: http://www.venkateswarlu.co.in/articles/SQL/UserDefined_Data_Types_in_sql_server.aspx
Performance Related: http://www.venkateswarlu.co.in/articles/SQL/SQL_performance.aspx
http://www.venkateswarlu.co.in/articles/SQL/Performance_Tuning.aspx
Rank, Dense_Rank, row_number: http://www.venkateswarlu.co.in/articles/SQL/RANK_RowNumber_DenseRank.aspx
.Net:
http://www.dotnetfunda.com/interview/showcatquestion.aspx?category=33
http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_1.aspx
http://www.venkateswarlu.co.in/FAQ/SSRS_Interview_Questions_2.aspx
http://venkateswarlu.co.in/FAQ/CHAPTER_10.aspx
http://sqlserversolutions.blogspot.com/2011/06/ssrs-interview-questions.html
SSRS Formatting
http://sqlsafety.blogspot.in/2010/02/ssrs-formatting.html
SSRS Functions:
http://www.venkateswarlu.co.in/BI/ssrs/SSRS_Common_Functions__Text.aspx
SSRS Tips and Tricks
http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/67660/
Dataware housing
http://blog.sqlauthority.com/2007/07/25/sql-server-data-warehousing-interview-questions-and-answers-introduction/
SSIS
http://venkateswarlu.co.in/FAQ/Chapter_1.aspx -- --V Good
http://www.sql-server-business-intelligence.com/sql-server/interview-questions-and-answers/ssis-interview-questions-and-answers-pdf-download
SSIS Event Handlers and Error Logging:
http://beyondrelational.com/modules/12/tutorials/24/tutorials/9686/getting-started-with-ssis-part-10-event-handling-and-logging.
Fuzzy Grouping:
http://www.bimonkey.com/2009/11/the-fuzzy-grouping-transformation/
http://f5debug.net/2011/04/25/sql-server-integration-services-ssis-%E2%80%93-part-45-%E2%80%93-fuzzy-grouping-transformation-in-ssis/
Fuzzy Look Up: http://www.bimonkey.com/2009/06/the-fuzzy-lookup-transformation/
SSIS Performance Tuning: http://technet.microsoft.com/en-us/library/cc966529.aspx
SSIS Check Points:
http://simonworth.wordpress.com/2009/11/08/ssis-package-properties-checkpoints/
Multicast Transformation vs Condition Split:
http://www.mssqltips.com/sqlservertip/2047/ssis-multicast-transformation-vs-conditional-split-transformation/
.Net: http://faq.programmerworld.net/programming/asp.net-interview-questions-answers.htm
Sql Server Performance tuning: http://blog.sqlauthority.com/2008/04/25/sql-server-optimization-rules-of-thumb-best-practices/ <--do Check the comments to the post
SDLC:
http://www.wiziq.com/tutorial/119305-Software-Engineering-What-Why-amp-How
SQL:
User Defined Datatypes: http://www.venkateswarlu.co.in/articles/SQL/UserDefined_Data_Types_in_sql_server.aspx
Performance Related: http://www.venkateswarlu.co.in/articles/SQL/SQL_performance.aspx
http://www.venkateswarlu.co.in/articles/SQL/Performance_Tuning.aspx
Rank, Dense_Rank, row_number: http://www.venkateswarlu.co.in/articles/SQL/RANK_RowNumber_DenseRank.aspx
.Net:
http://www.dotnetfunda.com/interview/showcatquestion.aspx?category=33
Wednesday, January 25, 2012
Microsoft Azure - An Intro
Microsoft Azure
Advantages:
Manageability, High Availability :
Higher availability with less management required. (less labor required to maintain network security , maintenance of servers etc ).No Physical servers to buy, install, patch, maintain or secure.
Scalability:
1 GB to 50 GB support and scalable with partitioning to larger DBs.
Global Scalability:
If you want to target a specific region, you can deploy your database at the closest data center
Familiar Development Model : Built on T-Sql
Shortcomings:
Doesn’t support XML datatypes and many Sql commands.
No support for backup and restore. Need to use following third party components.
- For Restore: SQL Azure Migration Wizard (SSMS 2008 R2 should be installed) To Migrate database from Sql Server to Windows Azure. Other way out is to generate Script from SSMS 2008 R2 for windows azure and then deploy the script on Azure server. But not a robust solution.
- For DB Sync: Microsoft Sync Framework Power Pack for SQL Azure to synchronize data between a data source and a SQL Azure installation. But if the source DB is more than 50 GB, the client needs to manage the partitioning manually.
- For Partitioning: Enzo SQL Shard Third party component for partitioning.
- For Backup: Found following third party components\scripts. We need to drill down to these to check the best suitable for us. Used to backup from Sql Azure to Sql Blob Storage. Please note: Microsoft charges ~$0.10 per GB for Blob Storage.
- Red Gate Backup tool: Considered as best but presently available as beta version. Probably would be paid software once it comes out of Beta.
- Script to automate back up to sql blob storage
- SQL Azure Migration Wizard: The SQL Azure Migration Wizard gives you the options to analyzes, generates scripts, and migrate data (via BCP) from:
- SQL Server to SQL Azure
- SQL Azure to SQL Server
- SQL Azure to SQL Azure
Other options not available when working with a SQL Azure database include: (one in red would affect us)
- Full-text indexing
- CLR custom types (however, the built-in Geometry and Geography CLR types are supported)
- RowGUIDs (use the uniqueidentifier type with the NEWID function instead)
- XML column indices
- Filestream datatype
- Sparse columns
- SQL Azure Doesn't Support Database Mirroring or Failover Clustering
Pricing:
As per the example below Pricing is quite simple:
Database + Data transfer based per day charging
Costing Examples: http://www.microsoft.com/en-us/showcase/details.aspx?uuid=09aa4f10-333a-4c98-aed1-4cb300de63ec&WT.mc_id=otc-f-corp-jtc-DPR-MVA_INTROSQLAZURE
Following page makes the pricing a bit confusing as it states that we would be charged on Bandwidth usage, catching etc in addition to the Database and Data transfer.
Costing Calculator: https://www.windowsazure.com/en-us/pricing/calculator/
Pricing can also be based on Computation Power, Db storage, Blob Storage, Bandwidth
- Compute: For front end application hosting. You can have 0 to 10 Instances of VM. à Required to consider in cast we plan to host our front end also.
- Database :2 plans.
Database
Standard pay-as-you-go pricing
Web Edition (up to 5 GB)
$9.99 per 1 GB of database per month
Business Edition (up to 150 GB)
$99.99 per 10 GB of database per month
(Maximum charge of $499.95 per database)*
- Data transfers: All inbound data transfers, i.e. data going into the Window Azure platform datacenters, are free. Price for outbound data transfers, i.e. data going out of the Windows Azure platform datacenters, is shown below.
Pricing details for outbound data transfers North America and Europe regions: $0.12 Asia Pacific Region: $0.19 - Bandwidth: 0 to 2000 Gb. Charged as per bandwidth used.
http://msdn.microsoft.com/en-us/library/windowsazure/ee730903.aspx
Bandwidth used between SQL Azure and Windows Azure or Windows Azure AppFabric is free within the same sub-region or data center. When deploying a Windows Azure application, locate the application and the SQL Azure database in the same sub-region to avoid bandwidth costs. For more information, see Accounts and Billing in SQL Azure. - Catching services, Storage Services etc are also provided and charged.
Some important tools that you can use with SQL Azure for Tuning and Monitoring include:
- SSMS Query Optimizer to view estimated or actual query execution plan details and client statistics
- Select Dynamic Management views to monitor health and status:
http://msdn.microsoft.com/en-us/library/windowsazure/ff394114.aspx
Some Depreciated features in Sql Azure
- ‘ANSI_NULLS’ is not a recognized SET option.
- Deprecated feature ‘SET ANSI_PADDING OFF’ is not supported in this version of SQL Server.
- Deprecated feature ‘More than two-part column name’ is not supported in this version of SQL Server. This a significant change if your using Schemas.
- Deprecated feature ‘Data types: text ntext or image’ is not supported in this version of SQL Server.
- Deprecated feature ‘Table hint without WITH’ is not supported in this version of SQL Server.
- Global temp Tables are not supported in this version of SQL Server.
- A full list id found at Deprecated Database Engine Features in SQL Server 2008 – MSDN.
Important Links:
Video tutorial for scripting out DB Schema for migration to SQL Azure using SSMS R2
https://www.microsoftvirtualacademy.com : Free course to learn basics of SQL Azure by Microsoft.
http://www.geeksco.com/blog/?p=36 : sql azure errors and trouble shooting
http://sqlazuretutorials.com/wordpress/sql-statements-not-supported-for-sql-azure/ : SQL Statements not supported for sql azure
Grant Execute to Given User - Dynamic Query
declare @username varchar(255)
set @username = 'YourUser'
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ROUTINE_TYPE='PROCEDURE'
The above query with result in all the User Defined Stored Procedures with "Grant Execute Permissions.
Run the resulting commands and 'YourUser' has the Execute Rights :)
set @username = 'YourUser'
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ROUTINE_TYPE='PROCEDURE'
The above query with result in all the User Defined Stored Procedures with "Grant Execute Permissions.
Run the resulting commands and 'YourUser' has the Execute Rights :)
Monday, January 23, 2012
SSIS Expression To Append Time and Date with File Name Dynamically
Use Following expression to append date and time at the time of package execution with the file name.
[code]
"D:\output\LogFile"
+ (DT_WSTR, 50)(DT_DBDATE)GETDATE()+"#"
+(DT_STR,2,1252)DATEPART("hh",GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("mi" ,GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("ss",GETDATE())
+".txt"
[/code]
Evaluated Expression would look some thing like this:
D:\output\LogFile2012-01-17#12-11-54.txt
Using Variable:
@[User::OutputFileLocation] : Variable to hold file location
@[User::OutputFileName] : Variable to hold file name
Expression:
[code]
@[User::OutputFileLocation]
+ @[User::OutputFileName]
+ (DT_WSTR, 50)(DT_DBDATE)GETDATE()+"#"
+(DT_STR,2,1252)DATEPART("hh",GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("mi" ,GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("ss",GETDATE())
+".txt"
[/code]
Hope it Helps :)
[code]
"D:\output\LogFile"
+ (DT_WSTR, 50)(DT_DBDATE)GETDATE()+"#"
+(DT_STR,2,1252)DATEPART("hh",GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("mi" ,GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("ss",GETDATE())
+".txt"
[/code]
Evaluated Expression would look some thing like this:
D:\output\LogFile2012-01-17#12-11-54.txt
Using Variable:
@[User::OutputFileLocation] : Variable to hold file location
@[User::OutputFileName] : Variable to hold file name
Expression:
[code]
@[User::OutputFileLocation]
+ @[User::OutputFileName]
+ (DT_WSTR, 50)(DT_DBDATE)GETDATE()+"#"
+(DT_STR,2,1252)DATEPART("hh",GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("mi" ,GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("ss",GETDATE())
+".txt"
[/code]
Hope it Helps :)
BIDS 2008 does not support XML datatype in OLE DB Command
Senario Overview:
We have some SP which expect some XML Type input parameters and also return XML Type parameter.
Having a requirement to insert data in bulk to Database we planned to use SSIS.
Working on the package, I found that SSIS 2008 doesn't supports XML datatypes in OLE DB Command.
Error Details:
Operand type clash: int is incompatible with xml
Googled.. and found that the issue has been solved in latest releases .. :)
But, No luck after installing the latest patch.
Posted the ISSUE on MSDN Form:
Found the following work around:
1. Create a new SP and use NVARCHAR(MAX) instead of Input XML Datatypes and call this SP from SSIS ( We could have modified the original though, but preferred avoiding dependencies issues)
2. Call the main SP from the new SP after type casting to XML
Now new issue:
As we have one XML Output datatype . It still went on giving error even after casting to NVARCHAR(MAX)
Finally, NVARCHAR(4000) worked fine.
Not a robust solution but good enuf to full fill our requirements :)
Wednesday, January 11, 2012
Combining Multiple Rows into One Row - Sql Server
In this example i am going to describe how to combine multiple rows in one column in MS SQL.
Here is the scenario
uSER_id and respective Hobbies are listed in the table.
i want to combine all the hobbies of a user
declare @User table
(
UserId int,
Hobbies VARCHAR(100)
)
INSERT INTO @User (UserId,Hobbies) values
(1, 'gaming') , (1, 'cricket'),
(2, 'movies') , (2, 'cricket'), (2, 'gardening') , (2, 'football'),
(3, 'gaming') , (3, 'football')
SELECT * FROM @User
SELECT DISTINCT
UserId,
Allhobbies = substring( ( SELECT ', ' + Hobbies
FROM @User T1
WHERE T1.UserId = T2.UserId FOR XML path(''), elements
),2,500)
FROM @User T2
Here is the scenario
uSER_id and respective Hobbies are listed in the table.
i want to combine all the hobbies of a user
declare @User table
(
UserId int,
Hobbies VARCHAR(100)
)
INSERT INTO @User (UserId,Hobbies) values
(1, 'gaming') , (1, 'cricket'),
(2, 'movies') , (2, 'cricket'), (2, 'gardening') , (2, 'football'),
(3, 'gaming') , (3, 'football')
SELECT * FROM @User
SELECT DISTINCT
UserId,
Allhobbies = substring( ( SELECT ', ' + Hobbies
FROM @User T1
WHERE T1.UserId = T2.UserId FOR XML path(''), elements
),2,500)
FROM @User T2
Subscribe to:
Posts (Atom)