Pessimistic Locking?

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.
(Read more here http://bit.ly/sql-interview)

Which are the Important Points to Note when Multilanguage Data is Stored in a Table?

Which are the Important Points to Note when Multilanguage Data is Stored in a Table?

There are two things to keep in mind while storing unicode data. First, the column must be of unicode data type (nchar, nvarchar, ntext). Second, the value must be prefixed with N while insertion. For example,

INSERT INTO table (Hindi_col) values (N’hindi data’)

(Read more here http://bit.ly/sql-interview)

What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?

In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users. (Read more here http://bit.ly/sql-interview)

Explain Few of the New Features of SQL Server 2008 Management Studio

SQL Server 2008 Microsoft has upgraded SSMS with many new features as well as added tons of new functionalities requested by DBAs for long time.

A few of the important new features are as follows:

IntelliSense for Query Editing
Multi Server Query
Query Editor Regions
Object Explorer Enhancements
Activity Monitors

(http://bit.ly/sql-interview)






Explain Few of the New Features of SQL Server 2008 Management Studio

Create a SQL Login User in SQL Server 2008

This article will give a walkthrough on creating a SQL Login user. 

Step 1
Login to SQL Server 2008 instance using windows authentication

Step 2

Right click on server instance name and select properties.

Step 3

Property window will be open. In that select the Security tab.

In server authentication tab you can see your SQL Server in configured for 

1. Windows authentication mode
   
2. SQL Server and Windows Authentication mode

So if you want to enable SQL User login to SQL Server then select checked mixed mode here .

And then click ok. Now SQL Server is configured for mixed mode login. 

Step 4

Now to create SQL LOGIN, right click on Security tab and select New and then Login .

Step 5

When you click on Login you will get the below window

Give the Login Name

Select SQL Server Authentication and provide your desired password

Disable Enforce password policy

Select default database

Leave default language and click OK to create a new SQL Server login 

Now in object explorer you can see User1 login .

Now at time of connecting to SQL Server login User1 can be used. 

SQL SERVER – Retrieve SQL Server Installation Date Time

July 5, 2012 by pinaldave

I have been asked this question a number of times and my answer always has been “Search online and you will find the answer.” Every single time someone follows my answer, he finds the accurate answer in just a few clicks. However, this question is getting very popular nowadays, so I decided to answer this question through a blog post.

I usually prefer creating my own T-SQL script but in today’s case, I have taken the script from the Web. I have seen this script in so many places that I do not know who the original creator is, so I’m not sure who should get credit for the script.

Question: How do I retrieve SQL Server Installation date?

Answer: Run the following query and it will give you the date of SQL Server Installation.

SELECT create_date
FROM sys.server_principals
WHERE sid = 0x010100000000000512000000




Question: I have installed SQL Server Evaluation version. How do I know what is the expiry date for it?

Answer: SQL Server evaluation period lasts for 180 days. The expiration date is always 180 days from the initial installation. The following query will give the expiration date of evaluation version:

-- Evaluation Version Expire DateSELECT create_date AS InstallationDate,DATEADD(DD, 180, create_date) AS 'Expiry Date'FROM sys.server_principals
WHERE sid = 0x010100000000000512000000
GO




I believe there is a way to do this using registry, but I have not explored it personally. Now as what I’ve said earlier, there are many different blog posts on this subject. Let me list a few which I really enjoyed to read as they shared a few more insights about this subject:

Retrieving SQL Server 2012 Evaluation Period Expiry Date

How to find the Installation Date for an Evaluation Edition of SQL Server

Reference: Pinal Dave (http://blog.sqlauthority.com)

What is Optimistic Locking ?

Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn’t changed before you write the record back. If the record is dirty (i.e. different version to yours), then you abort the transaction and the user can re-start it.
(Read more here http://bit.ly/sql-interview)