41.Explain Union, Union all, what is the difference between them?

Used to combine results of two or more SQL statements provided that the datatypes of respective columns in all the queries match exactly or are implicitly convertible. ‘Union’ removes duplicate rows but ‘Union all’ does not.

42.What alternatives do you have for Union?

You can use the temp table. You can use the ‘OR’ clause in the SQL statement.

43.Can u use select into statement in a Union?

44.I am having a table A with 500 rows, I write select * from A union select * from A, what will be the output? (i.e. How many rows will be displayed) ?

Same number of rows but if you use the union all command you will get the double number of records.

45.Can u change the data type of a column when there are records, without dropping the table?

In real scenario you can never update the data type of a column but in 11.9.x if you are having sa role, you can go to syscolumns and can update the type column without dropping the table or deleting the table but this is very dangerous because it may corrupt the Sybase storage files. Just think what will happen if you update the data type of char to int or vice-versa.
We can modify the datatype of a column in 12.5. You can only convert datatypes that are either implicitly or explicitly convertible to the new datatype, or if there is an explicit conversion function in Transact-SQL.

46.Can u drop a column if it is not the last column of the table?

Yes you can by same alter table.

47.If I need a few rows from the table, how can I best achieve it?

48.What is the diff between numeric and decimal data type?

Only numeric types with a scale of 0 can be used for the IDENTITY column.

49.What is the diff between char and varchar data type?

Char is fix length data type and varchar is variable length data type.

50.What is the diff between money and numeric?

Money can store a number up to 4 decimal places only. Numeric can save a number up to 37 decimal places.

51.What is the meaning of precision and scale of a data type?

Precision is maximum number of digit you can store in column. We can have 1-38 precision the default is 18
Scale is the number of digit we can have to the right of decimal point. We can have 0-38 scale. The default is 0.
E.g.: declare @var numeric(10,4)
Here 10 is precision and 4 is scale. We can have 10 digits and 4 decimal points in this variable

52.What is a cursor? Why should we avoid it? How should we avoid it?

By means of cursor, we can address a set of rows individually, one row at a time. A cursor is a pointer that identifies a specific working row within a set.
Cursors are very slow. So they can create performance issues and locking problems. We have to avoid these trying to put the logic in a set of queries itself.

53.Can I use temporary tables in cursors?

54.Why is @@sqlstatus used for?

To see the status of sql query executed.

55.Explain what are the diff values of @@ sqlstatus and what do they mean?

56.What is the diff between equi join and natural join?

Joins based on equality (=) are called equijoins. Equijoins compare the values in the columns being joined for equality and then include all the columns in the tables being joined in the results.
This query is an example of an equijoin:
select *
from authors, publishers
where authors.city = publishers.city
In the results of that statement, the city column appears twice. By definition, the results of an equijoin contain two identical columns. Because there is usually no point in repeating the same information, one of these columns can be eliminated by restating the query. The result is called a natural join.
The query that results in the natural join of publishers and authors on the city column is:
select publishers.pub_id, publishers.pub_name,
publishers.state, authors.*
from publishers, authors
where publishers.city = authors.city
The column publishers.city does not appear in the results.

57.What are constraints/rules/defaults? What is the use?

All are used to maintain domain integrity.

• Rule: – Rule provide a mechanism for enforcing domain constraints for columns or user-defined datatypes. The rules are applied before an insert or update statement is executed.
You have to create a rule and then bind it to a column. To drop a rule you have to unbind it from all the columns. If you bind a rule to a column and there is an existing rule bound to a column, it is replaced by the new rule. Rules are not applied when you bulk copy data into the system.
• Defaults: -provide a value for a column when it is not supplied at the time of insert.
  A default can be created as a database object and then bound to a column or it can be declared at the time of table creation. Only one default may be bound per column. If you try to bind a second default to a column, SQL server will give error. You can’t bind a default to a column that was created or altered to have a default value. The default is applied before a rule is checked. Defaults are applied during bulk copy.
• Constraints: – are used for defining data integrity requirements. There are 3 types

58.What is the diff between primary key and unique key?

Primary key does not allow null values. Unique key allows only one null value for the specified column in the table.

59.Can we create a non-clustered, non-unique index on the primary key?

alter table tt add constraint pk primary key nonclustered (c1)

60.What are constraints? Can I drop constraints? How?