SQL Server: SSMS 2008 Intellisense Stops Working after Installing VS 2010 SP1

After installing VS 2010 SP1, Intellisense stopped working in SQL Server Management Studio SSMS 2008 and R2 databases. After searching on the net, I found that this issue is being discussed on this thread and a MS support guy Weilin Qiao has given the following suggestion:

1. Go to Tools > Options > Text Editor > Transact-SQL > IntelliSense, and select Enable IntelliSense.

2. For each opening query window, please go to Query >> Intellisense Enabled.

3. To refresh IntelliSense local cache, go to Edit > IntelliSense > Refresh Local Cache or use the CTRL+Shift+R keyboard shortcut to refresh.

4. Enable statement completion: Go to Tools > Options > Text Editor > Transact-SQL > General, and check on Auto list members and Parameter information boxes.

5. Reboot SQL Server Management Studio.

I have tried the suggestions without much success and probably I will uninstall Sp1 and reinstall it again. I will update this post once I am able to figure this out! If you have a solution that works, please share it with the readers.

Update and a Workaround: Anonymous pointed me to a thread on MSConnect where a workaround shared by Richard Glidden is to install SQL Server 2008 R2 Cumulative Update 6 (http://support.microsoft.com/kb/2489376)

Repair SQL Server Database marked as Suspect or Corrupted

There can be many reasons for a SQL Server database to go in a suspect mode when you connect to it - such as the device going offline, unavailability of database files, improper shutdown etc. Consider that you have a database named ‘test’ which is in suspect mode

You can bring it online using the following steps:

1. Reset the suspect flag
2. Set the database to emergency mode so that it becomes read only and not accessible to others
3. Check the integrity among all the objects
4. Set the database to single user mode
5. Repair the errors
6. Set the database to multi user mode, so that it can now be accessed by others

Here is the code to do the above tasks:

Here’s the same code for you to try out

EXEC sp_resetstatus 'test'

ALTER DATABASE test SET EMERGENCY

DBCC CheckDB ('test')

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE test SET MULTI_USER

SQL Server: Insert Date and Time in Separate Columns

The DateTime datatype in SQL Server is used to store date values along with time. If there is a need to store date and times values in separate columns, you can store Date values in the Datetime column and Time values in either the char datatype or the time datatype (Sql Server 2008)

SQL Server 2005

Consider the following data which uses datetime datatype, to store date and char datatype,
to store the time values.Suppose you want to find out the date and time values that fall
in the year 2009. Here’s the query for the same:

Here’s the same query to try out:

declare @t table(date datetime, time char(8))
insert into @t
select '20101019','20:02:27' union all
select '20090122','12:19:20' union all
select '20110407','04:59:38'

select * from @t
where date+time>='20090101' and date+time<'20100101'

The condition date+time will make a datetime value and is checked in the range Jan 1st
of 2009 to Jan 1st 2010

SQL Server 2008

SQL server 2008 supports the Time datatype, which can be used to store the time values. Here’s the same query, but with the time datatype

declare @t table(date datetime, timecol time)
insert into @t
select '20101019','20:02:27' union all
select '20090122','12:19:20' union all
select '20110407','04:59:38'

select * from @t
where date+timecol>='20090101' and date+timecol<'20100101'

The query works same as the example code given above for version 2005

SQL Server: Combine Multiple Rows Into One Column with CSV output

In response to one of my posts on Combining Multiple Rows Into One Row, SQLServerCurry.com reader “Pramod Kasi” asked a question – How to Combine Multiple Rows Into One Column with CSV (Comma Separated) output . This is what he meant:

I found the question interesting and frequently asked, so I decided to write a post in response to Pramod’s question. There are three ways in my opinion using which this requirement can be achieved – Using FOR XML, a PIVOT operator or a CLR aggregation function. I will use the FOR XML method (originally shown by Tony Rogerson) in a sub-query to the SELECT, as shown below:

AWESOME isn’t it, making a complex query look so simple. I instant fell in love with this query when I learnt about it a couple of years ago and have used in a few reports as well. If you know a better approach, please share it in the comments section. Here’s the same query for you to try out:

SELECT DISTINCT
[Col1],
[Col2] = SUBSTRING(( SELECT ', ' + [Col2] as [text()]
FROM #Temp t2
WHERE t2.Col1 = t1.Col1
FOR XML path(''), elements
), 2, 100
)
FROM #Temp t1

Note: Adjust the SUBSTRING length as per your requirement. This query will work on SQL Server 2005 and above. If you are using SQL Server 2000, you will have to use a cursor and I honestly do not know how to code that.

OUTPUT

SQL Server: Search Non Round Numbers

Here’s a simple query that searches all non round numbers from a table. So the following are non-round numbers 20.00, 24.0 and the following are round numbers 20.20, 24.42 and so on.

Here’s the query:

Here’s the same query for you to try out:

DECLARE @TT TABLE (
id int,
salary float
);

INSERT INTO @TT VALUES (1, 23.44);
INSERT INTO @TT VALUES (2, 21.00);
INSERT INTO @TT VALUES (3, 20.00);
INSERT INTO @TT VALUES (4, 53.30);
INSERT INTO @TT VALUES (5, 11.00);

SELECT * FROM @TT
WHERE
ROUND(salary, 10) - FLOOR(ROUND(salary, 10)) = 0

OUTPUT

Note: Alternatively, you can also write the same query using WHERE (salary – floor(salary)) = 0, or WHERE salary != FLOOR(salary), but I use the query shown above (learnt this tip from Erland Sommarskog) which can also handle values like 20.00000000000097. If you are using the Money datatype, then the alternatives I suggested will work without an issue, since it is a fixed point type.

T-SQL and SQL Server Administration Articles Link List – February 2011

Here’s a quick wrap up of the articles published on http://www.sqlservercurry.com/ in the month of February 2011

SQL Server Administration Articles

Fastest Way to Update Rows in a Large Table in SQL Server - Many a times, you come across a requirement to update a large table in SQL Server that has millions of rows (say more than 5 millions) in it. In this article I will demonstrate a fast way to update rows in a large table

View Object Dependencies in SQL Server 2008 - Viewing object dependencies within a database as well as between databases and servers has become easier in SQL Server 2008. SQL Server 2008 introduces a catalog view (sys.sql_expression_dependencies) and dynamic management functions (sys.dm_sql_referenced_entities & sys.dm_sql_referencing_entities) that can help in dependency tracking.

SQL Server: Count Rows in Tables and its Size - In this post we will see how to count rows in all the tables of a database using SQL Server

T-SQL Articles

Calculate Median in SQL Server - Median is the numeric value that separates higher half of the list from the lower half. Let us see how to calculate Median in SQL Server

Sort Alphanumeric Data in SQL Server - This post shows how to sort alphanumeric data in SQL Server. You need to use a different approach to get an alphanumeric sort.

SQL Server: Return Multiple Values from a Function - A SQL Server function can return a single value or multiple values. To return multiple values, the return type of the the function should be a table.

SQL Server: Store and Retrieve IP Address - We can store IP addresses in SQL Server in a varchar column. However to retrieve IP address for a specific range, we need to split each part and compare it.

SQL Server: Group By Year, Month and Day - I have seen some confusion in developers as how to do a Group by Year, Month or Day, the right way. Well to start, here’s a thumb rule to follow.

SQL Server: First and Last Sunday of Each Month - This post shows you how to find the First and Last Sunday for each month of a given year, in SQL Server

SQL Server: Storing Images and Other BLOB types - In this post, we will see how to store BLOB (Binary Large Objects) such as files or images in SQL Server.

SQL Server: Highest and Lowest Values in a Row - Calculate both the highest and lowest values in a row without using an UNPIVOT operator.

SQL Server: First Day of Previous Month - A user asked me how to find the first day of the previous and next month in SQL Server

SQL Server: Convert to DateTime from other Datatypes - In this post, we will see how to convert data of different datatypes to a DateTime datatype, in SQL Server.

SQL Server: Convert to DateTime from other Datatypes

In this post, we will see how to convert data of different datatypes to a DateTime datatype, in SQL Server.

Sometimes date values that come from disparate sources may be of a different datatype other than DateTime such as int, varchar etc. In such cases we often need to convert the valuee back to DateTime. We will take two common scenarios of converting Int and Varchar datatype to DateTime

Consider the following examples:

Method 1 : Big Integer to Datetime

Assume that the date value along with time part is stored in Bigint datatype, use this query

Here’s the same query for you to try out:

declare @date bigint
set @date=20101219201119

select
cast(left(date,8)+' '+stuff(stuff(substring(date,9,6),5,0,':'),3,0,':') as datetime) from
(
select cast(@date as varchar(20)) as date
) as t

In the above example, the first 8 numbers denote a date and rest of numbers denote time values in the format HHMMSS. In the above code, left(date,8) extracts the date value. In order to have a proper date format we need to add ‘:’ in each of the time parts (hour, minute and second). The stuff function is used to add ‘:’ in the 3rd and 5th position and the entire string is converted to DateTime.

Method 2 : Varchar to Datetime

Assume that date value along with time part is stored in a Varchar datatype and a space seperates the date value from time values. Use this query:

Here’s the same query for you to try out:

declare @date varchar(20)
set @date='20101219 201119'

select
cast(left(@date,8)+ ' ' +
stuff(stuff(substring(@date,10,6),5,0,':'),3,0,':') as datetime)

OUTPUT

SQL Server: Count Rows in Tables and its Size

In this post we will see how to count rows in all the tables of a database using SQL Server. A couple of months ago I had written a similar query Count Rows in all the Tables of a SQL Server Database using DBCC UPDATEUSAGE and the undocumented stored procedure sp_msForEachTable. However the rows returned using this approach could be inaccurate at times.

I consulted my DBA friend Yogesh who told me about another approach which works accurately and counts both the rows as well as space taken by a table. The code shown here works well for SQL Server 2005 and above.

Here’s the same query for you to try out:

USE ADVENTUREWORKS
GO
-- Count All Rows and Size of Table by SQLServerCurry.com
SELECT
TableName = obj.name,
TotalRows = prt.rows,
[SpaceUsed(KB)] = SUM(alloc.used_pages)*8
FROM sys.objects obj
JOIN sys.indexes idx on obj.object_id = idx.object_id
JOIN sys.partitions prt on obj.object_id = prt.object_id
JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id
WHERE
obj.type = 'U' AND idx.index_id IN (0, 1)
GROUP BY obj.name, prt.rows
ORDER BY TableName

As you can see, we are using the sys.partitions catalog view which contains a row for each partition of all the tables and most types of indexes in the database. We are also using the sys.allocation_units catalog view to calculate the number of total pages actually in use.

Index_id 0 and 1 are for Heap and Clustered indexes respectively. Object Type ‘U’ is for User-defined Tables

OUTPUT (Partial)

SQL Server: First Day of Previous Month

A user asked me how to find the first day of the previous and next month in SQL Server. It’s quite simple as explained in Itzik’s excellent book Inside Microsoft SQL Server 2008: T-SQL Programming (Pro-Developer).

Here’s the query

Here’s the same query to try out:

-- To Get First Day of Previous Month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - 1, '19000101')
as [First Day Previous Month];
GO

-- To Get First Day of Next Month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 1, '19000101')
as [First Day Next Month];
GO

To understand what we just did, first execute the following query:

SELECT DATEDIFF(MONTH, '19000101', GETDATE())

This query returns 1333 (as of this writing) which is the number of months since 1/1/1900. Since we want to calculate a day of the previous month, subtract 1. To calculate a day of the next month, add 1. That’s it, now use the DATEADD function to return a specified date with the number interval (1333) subtracted or added to the datepart (month) of 1/1/1900.

OUTPUT

You may also want to check

SQL Server: First and Last Sunday of Each Month

Find First and Last Day of the Current Quarter in SQL Server

SQL Server: Highest and Lowest Values in a Row

Some time back, I had written a query to find Find Maximum Value in each Row – SQL Server where I used UNPIVOT to find the highest value in a row or across multiple columns. A SQLServerCurry.com reader D. Taylor wrote back asking if the same example could be written without using an UNPIVOT operator, to calculate both the highest and lowest values in a row. Well here’s another way to do it.

First create a sample table with some values

Now write the following query to use CROSS APPLY and get the highest and lowest value in a row

SELECT t.id, tt.maxValue, tt.minValue
FROM @t as t
CROSS APPLY
(
SELECT
MAX(col) as maxValue, MIN(col) as minValue
FROM
(
SELECT col1 UNION ALL
SELECT col2 UNION ALL
SELECT col3
) as temp(col)
) as tt

If you are wondering why did I use a CROSS APPLY instead of a simple correlated sub-query, then the reason is that I can work with multiple rows here. Moreover CROSS APPLY can return multiple columns too (like a derived table). At the end, we are referencing these values in our outer SELECT statement and the output is as shown below:

OUTPUT