Recompile in SQL Server

In next couple of posts i will explain about some very usefull stored procedures in SQL Server.

SP_RECOMPILE: SP_RECOMPILE

The sp_recompile stored procedure is used to recompile certain database objects. When you create a database object for the first time, the database object is compiled and a query plan is generated if all the dependencies are met. If you change the data in database objects, the stored procedures and triggers should be recompiled because the query plans generated for the database objects are no longer valid and new query plans should be generated.


The below example causes stored procedures that are using EMP table to be recompiled the next time they are run.

USE Pubs;
GO
EXEC sp_recompile N'HR.EMP'
GO

Primary Key in SQL Server

In next couple of posts i will explain about some very usefull stored procedures in SQL Server.

SP_PRIMARYKEY: SP_PRIMARYKEY TABLE,COLUMN1[, COLUMN2,....COLUMN(N)]

If you don’t know the syntax of primary key creation then this is the right procedure which you can use to create primary key. This stored procedure is used to apply the primary key constraint on a column or columns that is passed as a parameter.

The column1, column2, and column(n) parameters contain the column names on which you want to create the primary key constraint. A primary key can contain more than one column.

Use Pubs
GO
SP_PRIMARYKEY 'HR.EMP','empid
GO

Who are you

In next couple of posts i will explain about some very usefull stored procedures in SQL Server.

SP_WHO
The sp_who stored procedure is used to display all database users who are currently logged onto SQL Server. The stored procedure also displays the processes currently running on SQL Server.

Look at the below example to report all current users

USE master
GO
EXEC sp_who
GO

Change password in SQL Server

In next couple of posts i will explain about some very usefull stored procedures in SQL Server.

SP_PASSWORD: SP_PASSWORD OLD_PASS,NEW_PASS [, LOGIN_ID]

Your password is shared and wants to change the password then you need to use sp_password stored procedure. The sp_password stored procedure is used to modify the password of a login ID. You need to pass the old password of a login ID as a parameter to create a new password. You need to specify the old password as NULL when you modify the password of the SA login ID.

Microsoft is planning to remove this stored procedure usage in future versions and suggesting us to use “ALTER LOGIN” command.

Rename Objects in SQL Server

In next couple of posts i will explain about some very usefull stored procedures in SQL Server.

SP_RENAME: SP_RENAME OLD_NAME, NEW_NAME, TYPE_OF_OBJECT

The sp_rename stored procedure is used to modify the names of database objects or user-defined data types. In SQL Server each object is associated with Object ID. When you provide a new name for an existing database object, other database objects using the renamed object continue to refer to the renamed object.

old_name: parameter refers to the existing object name.
new_name: parameter refers to the name you want to assign.
type_of_object: parameter refers to the type of database object whose name you want to modify.

Renaming a table

USE Pubs
GO
EXEC sp_rename 'HR.Employees', 'EMP';
GO

Renaming a column

USE Pubs
GO
EXEC sp_rename 'HR.Employees.EmployeeID', 'EMPID', 'COLUMN';
GO

Constraint Information in SQL Server

In next couple of posts i will explain about some very usefull stored procedures in SQL Server.

SP_HELPCONSTRAINT: SP_HELPCONSTRAINTS [TABLE]

The sp_helpconstraint stored procedure is used to display information about the constraints created on the columns based on the input parameter. Constraint information contains type, name, delete action, update action, and status of constraints.


The below sample code displays the constraints information on Employee Table.

USE Pubs;
GO
EXEC sp_helpconstraint 'HR.Employees';
GO

Describe Index in SQL Server

In next couple of posts i will explain about some very usefull stored procedures in SQL Server.

SP_HELPINDEX: SP_HELPINDEX (TABLE)

The sp_helpindex stored procedure is used to display the indexes that are created on a table based on the input parameter. In addition to the indexes it will also display the columns which are part of the indexes.


USE Pubs;
GO
EXEC sp_helpindex N'HR.Employee';
GO

Columns in SQL Server

In next couple of posts i will explain about some very usefull stored procedures in SQL Server.

SP_COLUMNS: SP_COLUMNS OBJECT, [OWNER_NAME], [TABLE_QUALIFIER], [COLUMN_NAME], [ODBCVER]

The sp_columns stored procedure is used to display information about table columns or view columns. The procedure accepts the table or view name as a parameter.

OBJECT: This is nothing but table or view.
OWNER_NAME: Is nothing but owner of object. This is optional Parameter.
TABLE_QUALIFIER: parameter refers to the database name in which the table or view exists. This is optional Parameter.
COLUMN_NAME: parameter specifies the name of a column that you pass as a parameter whose information is to be displayed.
ODBCVER: Parameter specifies the ODBC version used.

USE Pubs
GO
EXEC sp_columns @table_name = N'Employee', @table_owner = N'HR';

Describe in SQL Server

In next couple of posts i will explain about some very usefull stored procedures in SQL Server.

SP_HELP: SP_HELP [OBJECT | USER_DEFINED_DATATYPE]

The sp_help stored procedure is used to display information about a database object that is passed as a parameter.

The parameter is optional. If you want to display all the objects with in the current database then don’t pass any parameter. If you are looking for specific objects information then pass the object name.

USE master
EXEC sp_help

The above code is used to display information about each object in Sysobjects.

User Details in SQL Server

In next couple of posts i will explain about some very usefull stored procedures in SQL Server.

SP_HELPUSER: SP_HELPUSER [NAME_OF_USER]

The sp_helpuser stored procedure is used to display the user(s) and role(s) present in a database based on the input. Name of the user is optional filed; if you pass a specific user then it displays information, such as user name, login name, group name, default database, user ID, and sid of the user. The procedure displays information about the current database user if no parameter is passed.

EXEC sp_helpuser

The above stored procedure is used to display all the users in the current database.

Rename Database

In next couple of posts i will explain about some very usefull stored procedures in SQL Server.

This stored procedure is to rename the database from old names to new name. Microsoft is planning to remove this from future releases and also suggesting to use ALTER DATABASE command to implement the similar functionality.

Let’s look at the sample example

USE MASTER;
GO
CREATE DATABASE BANK_DB;
GO
EXEC SP_RENAMEDB N'BANK_DB', N'INVESTMENT_BANK_DB';
GO
SELECT NAME, DATABASE_ID, MODIFIED_DATE
FROM SYS.DATABASES
WHERE NAME = N'INVESTMENT_BANK_DB';
GO


We are creating a Database name called BANK_DB and then later we are modifying/renaming it to INVESTMENT_BANK_DB.

Query the SYS.DATABASES table to see whether it’s modified or not.

StringBuilder in .Net

There are small small things which can improve the performance of the code. When we are dynamically constructing SQL query in .Net application we declare a string variable and append the SQL to it.

Ex:

String sSQL = null;
sSQL += "SELECT Col1,Col2,Col3";
sSQL += " FROM Table_name ";
sSQL += " WHERE Col4 = 10 ";

This is not a good approach to deal with String because String is immutable. Therefore, every time when you concatenate the new value a new instance will be made and it will make a great performance loss if concatenation will be done many times.

Lets look at the best apporach

System.Text.StringBuilder sSQL = new System.Text.StringBuilder();
sSQL.Append(" SELECT Col1,Col2,Col3 ");
sSQL.Append(" FROM Table_name ");
sSQL.Append(" WHERE Col4 = 10 ");

So from now on try to use StringBuilder.

code Execution Time

There are so many procedures in this world which are running very slow, to find out the execution time of the stored procedure you need to put simple statement like CURRENT_TIMESTAMP at the begining and ending of the stored procedure.

DECLARE @start DATETIME, @End DATETIME
SET @start = CURRENT_TIMESTAMP

DECLARE @i INT
SET @i = 0
WHILE @i < 24000
BEGIN
SET @i = @i + 1
END
SET @End = CURRENT_TIMESTAMP
SELECT DATEDIFF(MS, @start, @End)

Credit Card validation

its pretty rare to imagine a person with out knowing the word "Credit Card", If some one is implementing a site which requires "Money Trasactions" then one should have this kind of validation in their code.

The below code is to validate the Input Credit card number.

//Namespace Referenceusing
System.Text.RegulsrExpressions
/// method to validate a credit card number

public static bool validCCNum(string num)
{
// This expression is looking for a series of numbers, which follow the pattern
// for Visa, MC, Discover and American Express. It also allows for dashes between sets of numbers

string pattern = @"^((4\d{3})|(5[1-5]\d{2})|(6011))-?\d{4}-?\d{4}-?\d{4}|3[4,7][\d\s-]{15}$";
Regex match = new Regex(pattern);
return match.IsMatch(num);

}

Nth Salary

One of the common questions in interviews is how to find out 2nd best salary or find 5th best salary etc...

There are somany ways to write this kind of query.

Let me show you some of them....

--Find 3rd highest salary

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary
FROM tblSalary
ORDER BY salary DESC) S
ORDER BY salary

Here in the Sub query we are trying to get the TOP 3 salaries based on the order by. In the outer query order it once again but this time it is ASC order to reverse order so that we can use "TOP 1" to get the 3rd best salary.

If we go and generilize this query to work for any kind of result then


--Find Nth highest salary
SELECT TOP 1 salary FROM (
SELECT DISTINCT TOP N salary FROM tblSalary ORDER BY salary DESC) S
ORDER BY salary

Just replace the replace the Number with "N" in the sub query. Now if you want 5th best salary just replace N with 5. Simple...

Dont try to remember...understand the logic/concept....

Find index on a table

Normally when there is an issue on the performance one really want to see how many indexes are defined on a table.

the simpest command to get this information is sp_helpindex where just pass the tablename as the input parameter to the procedure then it will list out all the indexes defined on that table.

Differences between ISNULL and COALESCE

I came across a question in the various SQL Server forums, about ISNULL and COALESCE usage. ISNULL and COALESCE can be used to get the same results but there are some differences.

they are
Data type determination of the resulting expression - ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence

Validations for ISNULL and COALESCE is also different. For example, NULL value for ISNULL is converted to int whereas for COAELSCE you have to provide a type. Ex:

ISNULL(NULL, NULL) -- is int

COALESCE(NULL, NULL) -- Will throw an error

COALESCE(CAST(NULL as int), NULL) -- it valid and returns int

and the other obvious difference is
ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters

Large Objects in SQL Server

A very common issue for database developers in the past was how to store and utilize large binary objects such as documents and media files. The method typically used was to store the files outside of the database and just store a pointer in the database to the external file. With this method, however, when you move the file, you must also remember to update the pointer.

SQL Server 2008 handles this issue with the new FILESTREAM data type. With this data type, files can still be stored outside of the database, but the data is considered part of the database for transactional consistency. This allows for the use of common file operations while still maintaining the performance and security benefits of the database.

Framework 3.5 Service Pack 1

Microsoft .NET Framework 3.5 Service Pack 1 is a full cumulative update that contains many new features building incrementally upon .NET Framework 2.0, 3.0, 3.5, and includes cumulative servicing updates to the .NET Framework 2.0 and .NET Framework 3.0 subcomponents.


Some of the new features and improvements:

Core improvements to the CLR (common language runtime) that include better layout of .NET Framework native images, opting out of strong-name verification for fully trusted assemblies, improved application startup performance, better generated code that improves end-to-end application execution time, and opting managed code to run in ASLR (Address Space Layout Randomization) mode if supported by the operating system. Additionally, managed applications that are opened from network shares have the same behavior as native applications by running with full trust.

Performance improvements to WPF (Windows Presentation Foundation), including a faster startup time and improved performance for Bitmap effects. Additional functionality for WPF includes better support for line of business applications, native splash screen support, DirectX pixel shader support, and the new WebBrowser control.

Windows Communication Foundation now makes the DataContract Serializer easier to use by providing improved interoperability support, enhancing the debugging experience in partial trust scenarios, and extending syndication protocol support for wider usage in Web 2.0 applications.

The .NET Framework Data Provider for SQL Server (SqlClient) adds new support for file stream and sparse column capabilities in SQL Server 2008.

upgrade yourself from below link

http://www.microsoft.com/downloads/details.aspx?familyid=ab99342f-5d1a-413d-8319-81da479ab0d7&displaylang=en

C# Interview Questions

Does C# support multiple-inheritance?
No.

Who is a protected class-level variable available to?
It is available to any sub-class (a class inheriting this class).

Are private class-level variables inherited?
Yes, but they are not accessible. Although they are not visible or accessible via the class interface, they are inherited.

Describe the accessibility modifier “protected internal”.
It is available to classes that are within the same assembly and derived from the specified base class.

What’s the top .NET class that everything is derived from?
System.Object.

What does the term immutable mean?
The data value may not be changed. Note: The variable value may be changed, but the original immutable data value was discarded and a new data value was created in memory.

What’s the difference between System.String and System.Text.StringBuilder classes?
System.String is immutable. System.StringBuilder was designed with the purpose of having a mutable string where a variety of operations can be performed.

What’s the advantage of using System.Text.StringBuilder over System.String?
StringBuilder is more efficient in cases where there is a large amount of string manipulation. Strings are immutable, so each time a string is changed, a new instance in memory is created.

Can you store multiple data types in System.Array?
No.

What’s the difference between the System.Array.CopyTo() and System.Array.Clone()?
The Clone() method returns a new array (a shallow copy) object containing all the elements in the original array. The CopyTo() method copies the elements into another existing array. Both perform a shallow copy. A shallow copy means the contents (each array element) contains references to the same object as the elements in the original array. A deep copy (which neither of these methods performs) would create a new instance of each element's object, resulting in a different, yet identacle object.

How can you sort the elements of the array in descending order?
By calling Sort() and then Reverse() methods.

What’s the .NET collection class that allows an element to be accessed using a unique key?
HashTable.

What class is underneath the SortedList class?
A sorted HashTable.

Will the finally block get executed if an exception has not occurred?­
Yes.

What’s the C# syntax to catch any possible exception?
A catch block that catches the exception of type System.Exception. You can also omit the parameter data type in this case and just write catch {}.

Can multiple catch blocks be executed for a single try statement?
No. Once the proper catch block processed, control is transferred to the finally block (if there are any).

Explain the three services model commonly know as a three-tier application.
Presentation (UI), Business (logic and underlying code) and Data (from storage or other sources).

DotNetNuke

DotNetNuke is an open source web application framework ideal for creating, deploying and managing interactive web, intranet and extranet sites. DotNetNuke can support multiple portals or sites off of one install. DotNetNuke includes a multi-language localization feature which allows administrators to easily translate their projects and portals into any language.

some of the Silent Features

User-Friendly – DotNetNuke is designed to make it easy for administrators, content editors, developers, and designers to manage all aspects of their web assets. Wizards, content-sensitive help, and a well-researched user interface provide a superior user experience.

Powerful – DotNetNuke can support multiple websites from a single application installation. In dividing administrative options between host level and individual site level, DotNetNuke allows administrators to manage an unlimited number of websites – each with its own unique appearance and content.

Feature-Rich – DotNetNuke comes loaded with a set of built-in features that provide exceptional functionality. Site hosting, web design, content management, security, and membership options are all easily managed and customized through simple, browser-based tools.


Check out the below link

http://www.dotnetnuke.com/

umbraco

umbraco is an open source project with roots back to year 2000 even though it wasn't released as open source until 2004. Prior to that it was founder Niels Hartvig's home-grown weapon of choise for working as a freelancer.
Today it has grown to be among the top fifteen most popular open source .NET applications and in May 2008 we hit a milestone at 50.000 installed and active websites (double up from 25.000 in April 2007).

Check out the below link for more information..
http://www.umbraco.org/

Virtual TechDays

Microsoft products and tools give you the productivity boost to quickly address your most time-consuming development, deployment and management challenges, which helps you better connect with life outside work. Don't take our word for it, though. Join your fellow colleagues and Tech Gurus at this online event and explore Microsoft products online, from the cosy confines of your workstations.

During the event, the Technical Experts from Microsoft will deliver 40+ free, in-depth sessions on latest Microsoft technologies that help you boost your productivity. Get set to access these online interactive sessions and provide instant feedback.


Join us at Virtual TechDays and discover how Microsoft can help you get more out of life.




Visit the below link and register your-self.
http://www.connectwithlife.co.in/vtd/default.aspx

C u there....