Here are links of some of the videos that were recorded during Tech-Ed event in US.
Click on each to view the video.
Performance Tuning and Optimization in Microsoft SQL Server 2008 R2 and SQL Server Code-Named Denali
Happy Learning
Sudhir
Friday, July 15, 2011
Technical Videos of SQL Server Denali
Hello Everyone,
Here are links of some of the videos that were recorded during Tech-Ed event in US.
Click on each to view the video.
Here are links of some of the videos that were recorded during Tech-Ed event in US.
Click on each to view the video.
Tuesday, July 5, 2011
SQL Server Questions Part II
Guys,
Today we will see some more generic questions on SQL Server.
Here are more questions:
Q. How can you ensure that a table named TableB with a field named Fld1 will only have those values in the Fld1 field that are also in the table named TableA with a field named Fld1?
Ans: Use referential integrity / Foreign Key Constraint
Q. How can you reset Identity key value of a table?
Ans: Truncate Table will reset the Identity value of table.
Q. What can be used to ensure that a field in a table only accepts a certain range of values?
Ans: We can use various constraints that can be used to achieve this.
There are various constraints available in SQL Server.
Primary Key Constraint => to uniquely identify a row without using NULL
Foreign Key Constraint => to maintain referential integrity between 1 or more tables
Unique Key Constraint => to uniquely identify a row
CHECK Constraint => the value entered is validated and if it fails condition then don't allow entry of data
Default Constraint => If we don't supply any value for field then set value of field with specified value
Null Constraint => Whether field can accept NULL value or not.
Q. Our company recently introduced a policy where all names of objects must follow naming convention. For ex. Table must start with tbl_, procedure should start with prc_ etc. How can I ensure all new objects must adher to these policies and if not followed object should not be created?
Ans: 1 way to use DDL triggers. From 2008 onwards we can also refer to Policy Based Management to ensure this.
Q. Is it possible to drop multiple objects using single DROP statement?
Ans:
Drop Table a,b,c
Drop proc a,b,c
Q. If you have a stored procedure, you want to just check the syntax of the same rather than executing it, what will u do?
Ans: Press CONTROL + F5 to check for its syntax. Or click on button next to EXECUTE button.
Q. Which event (CHECK constraint, Foreign Key, Rule, Trigger, Primary Key check) will be performed last for an integrity check?
Ans: Trigger because everything is executed before data is inserted in the table & as soon as data is entered in Table, trigger is fired.
Q. When a new parameter is added to an SP, what steps to take to ensure existing code in an application does not break?
ANS: Give a default value to the newly added parameter. This will ensure that all existing code will not fail.
Q. When we drop a variable length column using ALTER TABLE DROP COLUMN statement, the space used by the column is not automatically claimed. What will u do to claim the space?
Ans: DBCC CLEANTABLE (database_name, table_name)
Q. I have a table Employee with following definition:
Employee (
EmpID int Not Null Identity (1,1),
EmpName varchar(100) default 'test',
Age int default 2)
What query you will write to insert default values in EMPLOYEE table?
Ans: Insert into Employee Default Values
Q.How to retrieve a single table from database backup?
Ans: It is not possible to directly restore 1 table from backup. Only option is to restore the database somewhere else & then load data of required table and transfer to destination.
Q. How do you flip rows to columns and vice-versa?
Ans: We can use PIVOT, UnPivot to flip rows to columns & vice-versa.
Q. What is WITH ENCRYPTION clause and where it is used?
Ans: This will encrypt the definition of object and it will not be visible whenever we use sp_helptext. This can be done with View, Stored Procedure, Function etc
Q. What will happen if I write following statement:
Select getdate()
go 10
Ans: Select Getdate() is executed 10 times because GO 10 acts as a loop.
Q. What is the purpose of the USE command?
Ans: The USE command helps select any available database, so that every query is executed on selected database else default database is used.
Do let me know if you have any comments or more questions.
Thanks
Sudhir
Today we will see some more generic questions on SQL Server.
Here are more questions:
Q. How can you ensure that a table named TableB with a field named Fld1 will only have those values in the Fld1 field that are also in the table named TableA with a field named Fld1?
Ans: Use referential integrity / Foreign Key Constraint
Q. How can you reset Identity key value of a table?
Ans: Truncate Table will reset the Identity value of table.
Q. What can be used to ensure that a field in a table only accepts a certain range of values?
Ans: We can use various constraints that can be used to achieve this.
There are various constraints available in SQL Server.
Primary Key Constraint => to uniquely identify a row without using NULL
Foreign Key Constraint => to maintain referential integrity between 1 or more tables
Unique Key Constraint => to uniquely identify a row
CHECK Constraint => the value entered is validated and if it fails condition then don't allow entry of data
Default Constraint => If we don't supply any value for field then set value of field with specified value
Null Constraint => Whether field can accept NULL value or not.
Q. Our company recently introduced a policy where all names of objects must follow naming convention. For ex. Table must start with tbl_, procedure should start with prc_ etc. How can I ensure all new objects must adher to these policies and if not followed object should not be created?
Ans: 1 way to use DDL triggers. From 2008 onwards we can also refer to Policy Based Management to ensure this.
Q. Is it possible to drop multiple objects using single DROP statement?
Ans:
Drop Table a,b,c
Drop proc a,b,c
Q. If you have a stored procedure, you want to just check the syntax of the same rather than executing it, what will u do?
Ans: Press CONTROL + F5 to check for its syntax. Or click on button next to EXECUTE button.
Q. Which event (CHECK constraint, Foreign Key, Rule, Trigger, Primary Key check) will be performed last for an integrity check?
Ans: Trigger because everything is executed before data is inserted in the table & as soon as data is entered in Table, trigger is fired.
Q. When a new parameter is added to an SP, what steps to take to ensure existing code in an application does not break?
ANS: Give a default value to the newly added parameter. This will ensure that all existing code will not fail.
Q. When we drop a variable length column using ALTER TABLE DROP COLUMN statement, the space used by the column is not automatically claimed. What will u do to claim the space?
Ans: DBCC CLEANTABLE (database_name, table_name)
Q. I have a table Employee with following definition:
Employee (
EmpID int Not Null Identity (1,1),
EmpName varchar(100) default 'test',
Age int default 2)
What query you will write to insert default values in EMPLOYEE table?
Ans: Insert into Employee Default Values
Q.How to retrieve a single table from database backup?
Ans: It is not possible to directly restore 1 table from backup. Only option is to restore the database somewhere else & then load data of required table and transfer to destination.
Q. How do you flip rows to columns and vice-versa?
Ans: We can use PIVOT, UnPivot to flip rows to columns & vice-versa.
Q. What is WITH ENCRYPTION clause and where it is used?
Ans: This will encrypt the definition of object and it will not be visible whenever we use sp_helptext. This can be done with View, Stored Procedure, Function etc
Q. What will happen if I write following statement:
Select getdate()
go 10
Ans: Select Getdate() is executed 10 times because GO 10 acts as a loop.
Q. What is the purpose of the USE command?
Ans: The USE command helps select any available database, so that every query is executed on selected database else default database is used.
Do let me know if you have any comments or more questions.
Thanks
Sudhir
Friday, July 1, 2011
Visual Studio 2010 and .NET Framework 4 Training Kit
Guys,
Download and launch the self-extracting package. The Training Kit will uncompress to the selected folder and launch a HTML browser for the content.
Click here to download the kit.
Happy Learning
Sudhir
Visual Studio 2010 and .NET Framework 4 Training Kit includes presentations, hands-on labs, and demos. This content is designed to help you learn how to utilize the Visual Studio 2010 features and a variety of framework technologies including:
- C# 4
- Visual Basic 10
- F#
- Parallel Extensions
- Windows Communication Foundation
- Windows Workflow
- Windows Presentation Foundation
- Silverlight 4
- ASP.NET 4
- Windows 7
- Entity Framework
- ADO.NET Data Services
- Managed Extensibility Framework
- Application Lifecycle Management
- Windows Azure
Download and launch the self-extracting package. The Training Kit will uncompress to the selected folder and launch a HTML browser for the content.
Click here to download the kit.
Happy Learning
Sudhir
Subscribe to:
Posts (Atom)
