Questions based on Collation settings

If you right click a database and view the properties, you can look for the collation setting as shown in the figure. The highlighted ‘CI’ represents the database is case-insensitive. Suppose if it is ‘CS’ then your database is case sensitive.






For example, the following query returns different results in different collation settings.

SELECT * FROM TestTab WHERE CharCol LIKE N'abc'


Following query creates a column with ‘Case In-sensitive’

CREATE TABLE TestTab
(PrimaryKey int PRIMARY KEY,
CharCol char(10) COLLATE French_CI_AS
)


1. At what level you can specify the COLLATE clause. Choose all correct answers.

a. Creating or altering a database.
b. Creating or altering a table column.
c. Casting the collation of an expression.
d. All of the above.



2. What function is used to retrieve a list of all the valid collation names for Windows collations and SQL collations

a. fn_sqlvarbasetostr()
b. fn_my_permissions()
c. fn_helpcollations()
d. fn_dblog()


3. Which of the following datatypes are used with COLLATE clause.

a. char, varchar, text
b. nchar, nvarchar, and ntext
c. int, smallint, tinyint
d. datetime, money


Show Answers:

1. (d)
2. (c)
3. (a), (b)



Or Hide Answers (X).

Reference: Books on line: COLLATE (Transact-SQL)

Basic questions on SELECT statement

1. Which of the following data types cannot be used with ORDER BY clause.

(a) Text
(b) ntext
(c) Image
(d) xml


2. Which of the following can’t be used in an indexed view?

(a) Getdate()
(b) DateDiff()
(c) HOUR()
(d) MINUTE()

3. To get cartesian product from two tables, what type of join you will use.

(a) Outer Join
(b) Full Join
(c) Cross Join
(d) Self-Join

4. What data type allows you to use upto 2 GB and allow you to use operators and functions.

(a) Binary
(b) Varbinary
(c) Varchar
(d) Varchar(max)

5. Select EmployeeID,
convert (Varchar, DateOfJoining, 101) AS DateOfJoining
FROM Employee
ORDER BY DateOfJoining

When executing the above query, if dates considered were 07/01/2010 and 08/01/2009, which date will appear first.

(a) 08/01/2009
(b) 07/01/2010

6. Select EmployeeID,
convert (Varchar, DateOfJoining, 101) AS [Date Of Joining]
FROM Employee
ORDER BY DateOfJoining

When executing the above query, if dates considered were 07/01/2010 and 08/01/2009, which date will appear first.

(a) 08/01/2009
(b) 07/01/2010

Show Answers:

3. (C) The result set returned by a cross join is known as a cartesian product. A cross

join joins each row from the first table with each row from the second table.

4. (D)
5. (B)
6. (A)


Or Close (X).