SQL Server Fundamentals.

1. What is SQLCA, SQLSA, SQLDA?
They are built-in global objects that are predefined in all applications.
SQLCA - default PB Transaction Object, SQL communication area. Used to communicate with our Database.
SQLSA is Dynamic Staging Area. We use it when we need to use a Cursor dynamically.
SQLDA is Dynamic Description Area, used in dynamic SQL.

2. When and why do we need dynamic SQL?
Dynamic SQL is a structure of SQL statements which can be changed dynamically in your
program. Used when at design time it is undetermined what the user is going to input (ad hoc Query).

3. How many formats of Dynamic SQL do you know and what are they?
1. InPut, NoResultSet
2. InPut + ResultSet (through a dynamic Cursor or a dynamic Stored Procedure)
3. InPut and ResultSet unknown until runtime.

Consider the following code (it is the basis for Questions 1 - 8 ) :
CREATE table my_table( a int not NULL, b int default 10 NULL)
GO
CREATE rule b_rule as b_val between 0 and 100
GO
sp_bindrule b_rule, “my_table.b”
GO
CREATE view sample_view as SELECT * from my_table
GO
alter table my_table ADD c varchar(20) NULL
GO
alter table my_table ADD constraint b_check
check(b between -10 and 10)
GO.

4. What value will be inserted into the table for column b by the following insert statement : INSERT sample_tab(a, b) values (1, NULL)
Explicitly providing NULL is essentially a value (no value) and the default will not be used.

5. If there were 50 rows already in the table when the default was bound to column b and 20 of the rows had no values for column b, what value will those rows have for column b after the default is bound?
Defaults, rules apply only to future rows added to the table. Existing rows are unaffected.

6. If a file containing 20 rows is bulk copied into a table and 5 of the rows do not have values for column b, what value will those rows have for column b after the bcp is completed?
10 Defaults are recognized during bcp.

7. Will file containing rows that have negative values for column b be added during bulk copying?
Yes. Rules, triggers and constrains are not recognized during bulk copy operations.

8. What columns will the user see if he runs the following SELECT statement :
SELECT * from sample_view.
Columns a and b. Although the view executes a select *, * is expanded during view compilation. Column c does not exist at this point.

9. What values are allowed in column b for inserts/updates?
0 to 10. The rule is checked first (between 0 and 100), then the constraint will be checked (-10 to 10). The valid range is the intersection.

10. What methods can be used to enforce a business rule requiring a value in column b to be greater than a value in column a for an insert/update?
A table-level constraint or an insert/update trigger.

11. What command would you use to change the default value in column b to 5?
Alter my_table replace b default 5.

12. What system table contains information about objects, such as tables, defaults, and triggers within a Database?
Sysobjects.

13. How many pages are allocated when a table is created?
An extent of 8 pages.

14. How is clustered index different from a non-clustered index?
Clustered indexes dictate the physical order of data. The leaf level of the clustered index is the data. A non-clustered index has a row in the leaf level of the index for every row in the table.

15. Can a clustered index be placed on a separate device from a table?
No.

16. Can a column be deleted from a table?
No. (Not officially, although a student once asked me this question. I answered, with authority “Never”. “I just did it!” he said. There is an undocumented System 10 feature : alter table t1 drop c1 from table t1. The implementation is interesting : the column is undefined, but all the data remains untouched until the rows are modified. As each row is modified, the column is nullified.

17. What command allows you to add columns to a table?
Alter table

18. How many columns with the identity property can be created in a table?
One or Zero.

19. What system datatype must be used for identity columns?
Numeric, with a definition of (n, 0) where n is the number of digits.

20. What global variable can you query to determine the last identity value inserted into a table?
@@identity

21. Explain the difference between char and varchar datatypes?
Char is fixed-length datatype whose trailing spaces are stored. Varchar is a variable-length datatype. Nullable char is stored like varchar because it is variable length – either no length or the declared length.

22. How is the string “Bob” in a char (10) not-null column stored differently than the same string in a varchar(10) not-null column?
This string would store 10 bytes in the char field (the string is padded with spaces) and 3 bytes in the varchar field (plus 1 byte overhead).

23. Define what is meant by NULL?
The absence of a value.

24. How are null values handled by aggregate functions?
NULLs are not considered in aggregates.

25. What Sybase function can substitute a value for NULL in a query?
IsNull (expression, value)

26. What is the restriction on updating base tables through a view, if the view is defined on multiple tables?
The columns being updated must exist in only one of the tables.

27. What is the restriction on inserting data into a base table through a view if the view contains only a subset of the columns in the base table?
The columns that are not part of the view must be Nullable or have a default.


28. What is the restriction on deleting rows through a multitable view?
Deletes are not allowed in multitable views.

29. What is the maximum triggers that can be created on a table?
Three triggers : insert, update, and delete triggers. (Note that there is a column called seltrig in Sysobjects, but it is not currently used.)

30. If update on table_A fires a trigger that updates table_B, and table_A has an update trigger defined on it, will the trigger on table_B be executed?
Yes, in the default server configuration. If the nested triggers configuration value is set to 0, the trigger on table_B would not fire.

31. If an update of a row in table_A fires a trigger that updates another row in table_A, will the trigger on table_A execute again?
No, by default. If nesting is allowed and set self_recursion on has been executed, the trigger would fire.

32. What are the names of the tables that are accessible only from within a trigger when a trigger is executed?
Inserted and deleted.

33. What is the structure of the tables referred to in the previous question?
The exact structure of the table on which the trigger is created.

34. Where are tables mentioned in the previous two questions located?
In memory. (Alternative answer : They actually are special views of syslogs)

35. When is the Update(column_name) function used, and when it is true?
Update(column_name) is used in a trigger to determine whether the value of a column has been modified. In an insert trigger, the function is true if a non-null value was inserted; in an update trigger, the function is true if a column was named in the update statement set clause.

36. An insert trigger is created on a table that validates whether one of its column values exists in another table before allowing the insert to succeed. A file that contains invalid values for that column is bulk copied into the table. Will the rows be inserted by the bulk copy?
Yes, bcp bypasses triggers, rules and constraints (but not defaults)

37. An update trigger exists on the titles table which contains 1,000,000 rows.
If you issue the following statement, how often does the trigger fire?
UPDATE titles
set price = price + $2
where type = “mod_cook”
One time. A trigger fires once per modification statement whether the statement modifies zero, one or many rows in a table.

38. If a table is created with a unique constraint on a column, will that column
allow NULL values?
Yes, but only once.

39. What if the column is created with a primary key constraint?
No. The primary key constraint requires the column to be declared as not NULL.

40. If a view is created on table “my_table” and that table is renamed to “new_table” will the view still work?
Yes. During compilation, the server converts an object name to an object ID. The rename changes only the name.

41. If a column is added to a table after a view has been defined on the table, what must be done to make the column accessible through the view?
Drop and re-create the view.

42. Under which of the following circumstances will a stored procedure be automatically recompiled:
a. Creating an index on a table referenced in the stored procedure?
b. Dropping an index on a table referenced in the stored procedure which is being used to satisfy the query?
c. Renaming a table referenced in the stored procedure?
d. Using the same table to drop and re-create a table referenced in a stored procedure?
b and d.

43. What three methods can be used to force a stored procedure to recompile a new query plan?
The following three methods :
sp_recompile table_referenced_in_proc
CREATE procedure proc_name ……. with recompile
EXEC proc_name with recompile

44. What system table contains information on system and user-defined datatypes?
Systypes

45. What stored procedure lists database user-defined types?
Sp_help

46. Describe how the @@trancount global variable is incremented and decremented.
Each begin tran(tran_name) increments by 1, commit tran decrements by one, and rollback tran rolls the transaction back and returns the value zero.

47. What type of the lock is held on a page during a select? A shared lock.

48. What type of the lock is held on a page during an insert? An exclusive lock.

49. How many page locks must be accessed by a single data modification statement before upgraded to a table lock? 200.

50. What global variable contains the number of rows affected by your last executed SQL statement? Which SQL statements affect that variable?
@@rowcount; all SQL statements except declare change the value of @@rowcount (many change its value to zero).

51. What is difference between server cursors and language cursors?
A server cursor is declared and processed in a stored procedure.
A language cursor is declared and executed using SQL, but not Open Client calls.

52. What is the proper sequence of statements to use and process a cursor?
The following sequence of statements :
DECLARE cursor_name cursor
OPEN cursor_name
FETCH cursor_name ….
CLOSE cursor_name
DEALLOCATE cursor cursor_name

53. What statement is used to delete the currently fetched row in an updatable cursor?
DELETE table_name where current of cursor_name

54. What type of lock does an updatable cursor obtain by default as it fetches rows? What type of lock does a read-only cursor obtain?
An updatable cursor obtains update locks. A read-only cursor obtains shared locks.

55. What is the difference between the following two select statements?
SELECT title, type, price from titles
ORDER BY type
COMPUTE avg(price) by type;

SELECT title, type, price from titles
ORDER BY type
COMPUTE avg(price);
The first select statement will report the average price for a type after listing its detail rows. The second select statement returns all detail rows followed by the average price for all non-null prices.

56. Assume the following five users have each executed the following set of statements in different Databases (assume all users are not running in chained mode) :
User1: begin tran User2: begin tran
delete my_tab delete my_tab
commit tran checkpoint
go go

User3: begin tran User4: begin tran
delete my_tab delete my_tab
commit tran go
go
checkpoint
go

User5: delete my_tab
go
If the SQL Server were to crash at this point, which of these transactions would be rolled back during recovery? Which would be rolled forward? Which would require no recovery?
Assuming an internal checkpoint was not activated by the server immediately prior to the crash, User1 and User5 are rolled forward; User2 is rolled back; User3 and User4 do not require recovery.

57. If a user executes the following set of statements, what rows will be displayed by a SELECT * from sample_tab statement?
CREATE table test_table (a int)
go
begin tran my_tran
INSERT sample_tab (8) values (1)
save tran my_savept
INSERT sample_tab (a) values (2)
rollback tran my_savept
INSERT sample_tab(a) values (3)
commit tran

The following will be displayed :
a
..
1
3
58. What global variable can you check to determine the current status of a transaction?
@@transtate

59. Describe a deadlock scenario.
User1 locks page A, User2 locks page B. User1 requests page B(process will be blocked waiting for the lock on page B to be released). If User2 requests page A, a deadlock has occurred.

60. What SQL statement is notorious for causing deadlock situations?
SELECT ….. holdlock

61. What steps can you take to avoid deadlocks?
You can access tables in the same order, decrease the length of transactions, avoid the use of holdlock, and spread data across data pages.

62. What is the definition of a transaction?
A transaction is a logical unit of work.

63. Describe the differences between chained transaction mode and unchained
transaction mode. When does a transaction begin and end in chained mode?
In unchained mode?
In chained mode, the server begins a transaction before the first SQL statement(insert, update, delete, or select) Only commit transaction must be specified. In unchained mode, begin and commit tran statements are required to define a logical unit of work; each SQL statement includes an implied begin tran and commit tran statement.

64. What global variable can you query to determine whether you are running in chained or unchained transaction mode?
@@tranchained

65. Based on following SQL statements : begin tran my_tran
print “Updating titles”
UPDATE titles set price = price * $1.25
if (SELECT avg(price) from titles ) > $50
print “Avg price exceeds $50”
rollback tran my_tran
Print “Update rolled back”
return
commit tran
print “Update successful”
go
a) What will be displayed if the average price after update is greater than $50?
“Updating titles”, “Avg price exceeds $50”, and “Update rolled back”.
b)What will be displayed if the average price after update is less than $50?
“Updating titles”, and “Update rolled back”.

66. What changes would you recommend for the code in Question 63?
Add begin after the if statement, and add end after the return statement.

67. How are local variables defined in SQL? How are global variables defined?
Local variables are defined by declare # @variable_name datatype.
Global variables are defined by system.

68. How are values assigned to local variables?
The select statement (SELECT @var_name = value)

69. How are values assigned to global variables?
Values for global variable are read-only : only the server may update globals.

70. What is the definition of local variables?
Local variables exist for the duration of a stored procedure or for the duration of a batch.

71. What are the two ways to change the displayed column heading for a select statement?
The two ways are as follows :
SELECT “Heading” = col_name ……
SELECT col_name Heading …….

72. What statement (other than CREATE TABLE) can be used to create a table in SQL Server?
SELECT into

73. What will be the effect using the following statement :
SELECT * into new_titles from titles where 1= 2
A new table will be created having the same structure with no rows

74. What data values would the following WHERE clause match :
WHERE name like “%[Cc]omputer”
Any string that ends in the word Computer or computer.

75. What function is used to display the value of a datetime column in the format dd/mm/yyyy?
Convert.

76. What security requirements must be met before a table can be referenced by a foreign key reference constraint?
Either both tables must be owned by the same user or the references permission must be granted.

77. Consider the following table definition :
CREATE table titles (title_id char(8) not NULL,
title varchar (68) not null,
pub_id char (4) not null
references publishers (pub_id)
total_sales int null,
pub_date datetime not null)
What happens if you attempt to delete a row from publishers, where there is an existing title in the titles table for that pub_id?
The delete fails, but processing continues. You must check @@error and rollback manually.

78. Considering the code in Question 77, what if you attempt to update a pub_id for a publisher who has related rows in the titles table?
The update fails.

79. Considering the code in Question 77, what if you attempt to insert a new publisher table? The row is inserted.

80. Considering the code in question 77, what happens if you attempt to insert a row into the titles table? It will check the pub_id value to see if it exists in the publishers table. If not, the insert fails.

81. Considering the code in Question 77, what happens if you update a pub_id in the titles table?
It checks to see if the new value is in the publisher table. If not, the update fails.

82. Considering the code in Question 75, what if you delete a row the titles table?
The row is deleted.

83. Describe the difference between a table-level and a column-level constraint.
A constraint is considered table level if it is defined after the column definitions in the CREATE table statement or is defined using alter table.

84. When must you define a constraint as a table-level constraint?
When the constraint references two different columns.

85. What value will be displayed for @var when the following statements are executed:
CREATE proc my_proc (@parm1 int output)
as
SELECT @parm1 = @parm1 + 50
go

DECLARE @var int
SELECT @var = 50
EXEC my_proc @var output
SELECT @var
go
100.

86. What data values can be returned by the return statement from within a stored procedure? Any integer value.

87. What data values are reserved for use by Sybase?
-1 through -99

88. What statement is used to generate system -like error messages?
RaisError

89. What is the minimum error number that can be used?
20 001

90. If a rule is bound to a user-defined datatype, what happens to any existing columns that are defined with that user-defined datatype?
The rule will also be bound to those columns.

91. What if those columns in the previous question already had a rule that was bound explicitly to the column?
An explicit bind to a column overrides a bind to a datatype. The bind to the datatype would have no effect.

92. If a column has a rule bound to it and you attempt to bind another rule to that column, what happens?
The new rule will replace the old one (the existing rule does not have to be unbound).

93. How are database objects fully qualified?
dbname.username.objectname

94. What is the purpose of the syskeys table?
The syskeys table contains a row for every primary, foreign or common key in a Database.(These rows are added through sp_primarykey, sp_foreignkey, or sp_coomonkey). Keys are stored for documentation and reference only; the server does not enforce keys as part of referential integrity. Use constraints instead. SQL Server Administration.

95. What command do use to configure the SQL Server?
sp_configure

96. What command do you use to start SQL Server?
StartServer

97. How are remote procedures invoked?
By fully qualifying the procedure name to include the server name .The format is
servername.dbname.owner.procedure_name.

98. What needs to be set up or configured on the local server to implement remote procedure calls?
Remote access needs to be installed and the names of the local and remote servers needs to be added with sp_addserver

99. What needs to be set up or configured on the remote server to implement remote procedure calls?
Remote access needs to be installed; a remote login method needs to be selected and remote logins added; and the names of the local and remote servers need to be added with sp_addserver

100. What is normalization? What do you mean by Under- and Overnormalization?
Normalization means that you eliminated redundancy. Normalization is a theory of a logical Database design which refer to minimum data redundancy and maximum data integrity.
1. Eliminate arrays.
2. Eliminate repeating data across the rows.
3. Make sure all columns within a row depend on the primary key column.
4. Eliminate situation when two or more columns have mutual exclusive values.
Undernormalization contains a lot of redundant data
Overnormalization contains a lot of small tables which call a lot of joins.

101. Why its not such a good idea to use joining more then certain number of tables?
Because joining more then five tables in Sybase database will decrease performance.

102. What does OPEN CURSOR mean?
OPEN CURSOR causes the SELECT specified when the cursor was declared to be executed. The USING Transaction Object clause is not allowed with OPEN; the transaction object was specified in the statement that declared the cursor.

103. What is the purpose of Fetch statement?
We use Fetch to retrieve data from the buffer, line by line, and place it into a variable.