I started to think about this problem, when I saw this question asked by Sankar Reddy in SQL Server Quiz 2010. I have a fair bit of idea about Index fragmentation and defragmentation. I checked fragmentation of some of my table indexes previously and rebuilt the indexes when fragmentation percentage is too high. But I never thought about how exactly these will be calculated. But after looking at this question, I thought of finding how SQL server will calculate this. Let’s have a look at this example.

Over my years in (and before) IT, I’ve seen long projects, failed projects, confused projects, wildly successful projects, and even fun projects that ended far differently than we expected. The consistent take-away for me is that I am a big picture type of person, and that understanding that big, abstract picture cuts out a lot of wasted time sprinting down the wrong paths. Don't Sprint Blindly... (care of dmitriev.com) Creating a model forces me to refine a concept down to it’s simplest elements, forces me to face the unknowns that my mind has so casually been skipping over. When done well, a model communicates a clear idea and replaces not only the thousands words required to explain it, but the 9000 I would have wasted getting there.

Tomorrow night I will be talking about Execution Plans for the Wisconsin SQL Server User Group. This is a new session for me and I think the group and I will have a great time discussing how important it is to dig deep into plans. Execution plans are without a doubt a point in the tuning process in which we can either make a database server live or land face first on the floor.

The history of any product is important to fully understanding the product itself. With the knowledge of the foundation of a products birth, we can start to learn the depth of where it has come as it has aged over the years. SQL Server and all its features are no different. We may find ourselves disliking a product before taking full advantage of the abilities offered. SQL Server has had many levels of growth over the years as a relational database management system. SQL Server has taken on roughly twelve major releases; code names were adopted 10 major releases ago. Throughout these releases, Microsoft has enriched the features that ship with the SQL Server Engine itself. These features started with SQL Server 7.0 and the release of OLAP; code name, Plato. With Plato, came a direction to fulfill the goal of bringing SQL Server to an enterprise level. Also packaged with SQL Server 7.0 was Data Transformations Services (DTS). DTS was a milestone as it provided the ability to broaden the range of abilities to work natively with SQL Server and Bulk type operations. Job scheduling capabilities were also increased in the ability to work more freely with more complex tasks. These tasks were housed directly in SQL Server (with respect to outside binaries and data stores). SQL Server 7.0 provided a foundation for the years to come.

Earlier this week I decided that I wanted to generate a report that shows me how the security is set up on my databases. I’m mainly intersted in my database roles and how they’re set up, and logins assigned to the database. So I did some snooping around and generated 3 queries for myself. select dpr.name, dpr.principal_id, dpr.type, dpr.type_desc, dp.class, dp.class_desc, dp.major_id, dp.minor_id, dp.grantee_principal_id, dp.permission_name, case dp.state when 'g' then 'granted' when 'd' then 'denied' else 'n/a' end AS [state], ao.name as ObjectName, sc.name as SchemaName, case class_desc when 'OBJECT_OR_COLUMN' then ao.name when 'SCHEMA' then sc.name end AS PermissionObject from sys.database_principals dpr left outer join sys.database_permissions dp on dpr.principal_id=dp.grantee_principal_id left outer join sys.all_objects ao on dp.major_id=ao.object_id left outer join sys.schemas sc on dp.major_id=sc.schema_id where dpr.type='r' and class_desc>'' and dpr.principal_id>0 order by name This query displays all rights explicitly granted and denied in the database to database roles.

This blog post is a result of the currently active thread at MSDN T-SQL forum Parsing Address field to its components The question of parsing a row data with bad formatted addresses often comes in the SQL newsgroups. Unfortunately, there is no universal 100% bullet-proof solution that covers all possible scenarios. However, for a common scenario of City, State Zip the parsing can be done using the following technique: declare @t table (Address varchar(max)) insert into @t select 'ROSSIVILLE, GA' union all select 'LEESBURG, FL 34788' union all select 'COLUMBUS, OH 43221' union all select 'FORT BELVOIR, VA 22060' union all select 'MADISON HGTS, MI' union all select 'PALM BEACH, FL' union all select 'MCDONOUGH .GA 30352' select Address, F.City, F10.State, F4.Zip from @t cross apply (select charindex(', ', Address) AS CommaPos) F0 cross apply (select case when CommaPos = 0 then 'Bad Address' else SUBSTRING(Address,1,CommaPos - 1) end as City) F cross apply (select case when CommaPos = 0 then NULL else ltrim(substring(Address,CommaPos+1, len(Address))) end as Rest) F2 cross apply (select PATINDEX('%[0-9]%',Rest) as DigitPos) F3 cross apply (select case when DigitPos > 0 then substring(Rest, DigitPos, len(Rest)) end as Zip) F4 cross apply (select case when Zip Is not NULL then replace(Rest,Zip,'') else Rest end as State) F10 Please, note, that I used CROSS APPLY technique to divide the parsing job into several related steps by first taking the City, then the rest of the string, then parsing the remainder.

This blog was inspired by the following thread at MSDN Transact-SQL forum: Multiple values in field as parameter Given a string with values such as ‘201|203|301|400..600|725|800..900’ return records from the table where Code field will be any of the values passed with | as a delimiter and also within the passed ranges where range indication will be .. The first idea that comes to mind is to use various splitting techniques available (see, for example, this excellent blog by Aaron Bertrand Splitting List of Integers or follow up by Brad Schulz Integer List Split – a SQL fable or the old times classic by Erland Sommarskog Arrays and Lists in SQL Server) split the list first by | and then, if needed by .. to introduce the ranges of values.

The title of this article is one that is asked on many occasions around the forums and SQL Server community. SQL Server Integration Services (SSIS) is the Extract, Transform and Load (ETL) platform behind SQL Server. There aren’t many arguments against SSIS as a great tool, and it has the ability to get the job done as an ETL platform. With the added complexity of any product, pain is involved while becoming familiar with the intricacies of it. One pain that comes with any development practice that is performed within one set of environment and system variables and later moved to another set (Transport process): a process can execute successfully in one location and fail in another.

Introduction Well this is to be post number 250 and the final one from me on this site. Perhaps you like to know why I started blogging in the first place? More then 2 years ago this site started and the best way to get views is to add content and so I did that. I started blogging just to give us content. That’s how simple it is. The past It is kinda time for a “best of” I guess. These are some of my most viewed posts.

I have been wanting to write my last real blogpost on this site for a few days now and I have nothing to write about. I have thought of plenty of things to write about but they all seemed so trivial. I was gonna write about interview questions but it has been a while since I done any real interview myself and I never ever had to interview someone else. So the whole exercise would be pointless because it would just be theoretical.