You might know that you are not allow to modify the Collation after creating a database.
So you use the scripting option for the database you will see that collation is missing in the script.
Following is the script database create with Danish_Norwegian_CI_AS collation.
However, you can see that collation is not mention in the above script.
If you want to script you need to explicitly mention the COLLATE as shown below.
You can create a database with a different collation to the server collation. This is one issue you will face in case database collation is different from the server collation.
Then let us create a table with few data,
Let me use temp table to use this data.
So you are using temp table and actual table. (This is a hypothetical scenario just to present this idea).
Last query will return following error.
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Danish_Norwegian_CI_AS" in the equal to operation.
This is due to the temp database has the server collation, so the workaround will be at the time of creating temp table, you have to explicitly mention the collation.
Microsoft Corp. today announced that the latest version of the world's most widely deployed data platform, Microsoft SQL Server 2012, has released to manufacturing. SQL Server 2012 helps address the challenges of increasing data volumes by rapidly turning data into actionable business insights. Expanding on Microsoft's commitment to help customers manage any data, regardless of size, both on-premises and in the cloud, the company today also disclosed additional details regarding its plans to release an Apache Hadoop-based service for Windows Azure.
LIGHTNING TALKS
After the successful feedback we got regarding the Whirlwind DBA Demos at the February 2012 meet-up, we are doing something equally special at the March 2012 meet-up as well...
WHAT IS IT? We have planned for Anything-SQL Lightning Talks.
HOW DOES IT WORK? Each presenter talks for just 2 minutes with or without slides. Simple, no?
WHO SPEAKS? We need a lot of speakers to make the session successful. We need YOU.
ABOUT WHAT? Anything related to SQL Server. Things you just learned, experiences, tips, stories, anything related to SQL Server. Beginner or Advanced - you decide.
WHEN? March 21, 2012 - 6:00PM
WHAT NEXT? If you are interested, please sign-up using this
[SIGNUP FORM] on or before March 13, 2012 - 12:00 PM (IST)
When I saw this question, I thought “NO WAY”. However, I gave it a try.
To my surprise, I was able to set READ ONLY to model database.
Then I created the database and it was a read only database which didn’t surprise me.
You can see, I was able to to set model to read only and when I created a new database it is read only.
However, after restarting SQL Server , tempdb will not be in READ ONLY.
This is the surprise.
If you check http://msdn.microsoft.com/en-us/library/ms186388.aspx#2 , it says you can’t set model database to read only mode.
This question was raised during our Whirlwind DBA Demos by Pearson Lanka DBAs at SSSLUG but we didn’t have a proper answer. So this faq tell you how you can set priorities for process during the deadlock.
In replication, the standard way of creating a publisher is to create a publication with a snapshot, this article shows how to create a Replication without using a snapshot but with using a backup option.
Dinesh Karunarathne has started a new blog and first post is on Partitions Views.
He finished with his post with,
If the partitioned column is part of the PRIMARY KEY, data can directly be inserted in to the view.
I want to elaborate on this point further with a practical example I had.
I had a Order table as I came up with a design to partition order table with Date. This means I have say, three order tables, Orders_2010, Orders_2011 and Order_2012.
So the tables are created and you will notice that you have CLUESTERED PRIMARY KEY for OrderID,OrderDate columns. Traditionally, you would create CLUSTERED PRIMARY KEY only on OrderID.
Next is creating the partition constraints as shown below.
If you use DATEPART function instead of BETWEEN, this will not be possible to use as a partition view.
Each base table has a partitioning column whose key values are enforced by CHECK constraints.
The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table. Any given value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, <=, >, >=, =.
Next is creating the View.
If you create PRIMARY KEY only on OrderID, you will not be able to insert into the view and you will get the following error.
Msg 4436, Level 16, State 12, Line 2
UNION ALL view 'LogicalPartitioning.dbo.vw_Orders' is not updatable because a partitioning column was not found.
Also, you cannot have IDENTITY property for OrderID. If you use it, you will get the following error.
Msg 4433, Level 16, State 4, Line 2
Cannot INSERT into partitioned view 'LogicalPartitioning.dbo.vw_Orders' because table '[LogicalPartitioning].[dbo].[Orders_2010]' has an IDENTITY constraint.
I got this issue while creating an SQL Server Agent Alert. When I want to select the database, it came to my notice that databases are not in order. I am not sure what the order is.
Same set of features are available in perfmon but databases are nicely ordered.
This does not apply only to log space used but for many other counters in alert.
The purpose of a Pareto chart is to highlight the most important amongst a set of factors. For example, in quality control for a manufacturer, a Pareto chart can highlight the most common sources of defects and the highest occurring type of defect.This faq tells you how to create a Pareto charts in SSRS
Do you know the history of the SQL Server? Here is a nice little video on the subject.
I have seen this bug for a while and in several SQL Server version. Seems like Microsoft doesn’t have plans to fix this,
If you have a database in restoring mode, right click the database just keep the mouse over at the Reports option (NO you don’t have to click it 
) you will be getting an error. Good thing is it won’t crash the SSMS.
Yes it is an unhandled exception.
Posts
All Comments
