Posts

Showing posts from November, 2011

What is ACID properties and How SQL Server Comply to the same

What is ACID properties and How SQL Server Comply to the same Answer ACID  Propertied is Acronym for A- Atomicity-  Either all the operations/transactions are performed or None.Each transaction is said to be atomic if when one part of the transaction fails, the entire transaction fails and database state is left unchanged. C- Consistency-  This states that at a given moment of time the database is always in consistent. This guarantees that a transaction never leaves your database in a half-finished state. I- Isolation-  Isolation ensures that the transactions are separated(Isolated) from each other until they are finished. This means the transactions does not interfere with one another. D- Durability-  This ensures that once a transaction is commited it is available forever at any given moment of time. This guarantee that once the user has been notified of a transaction's success the transaction will not be lost, the transaction's data changes will survive system failure,

Polymorphism concept

Method Overloading ------------------ Method Overloading means having two or more methods with the same name but different signatures in the same scope. These two methods may exist in the same class or one in base class and another in the derived class. The signature of a function is determined by 3 factors: a) Number of arguments received by the function. b) Data types of the parameters/arguments. c) Position/order of the arguments. The signature of a function never depends upon the return type. Two functions differ only in their return type cannot be overloaded. Example of Method Overloading ----------------------------- public partial class _Default : System.Web.UI.Page { protected void Button1_Click(object sender, EventArgs e) { MyBaseClass mbc = new MyBaseClass(); Response.Write(mbc.AddNumbers(1,2).ToString()); Response.Write("<br />" + mbc.AddNumbers(1, 2, 3).ToString()); } } public class MyBaseClass { public int AddNumb

sp_addmessage can be used to create a User-Defined Error Message

hi friends, On Today Morning i go through with Custom Error Message in MSSql server. for that i work around below step and it's done man. i found one stored procedure "sp_addmessage" which used for adding my custome error message on server.. USE master GO EXEC sp_addmessage 50002, 10, N'The data %s already exists!' GO RAISERROR (50002, 10, 1, 'MyText') syntax for sp_addmessage is sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] 'msg'      [ , [ @lang= ] 'language' ]      [ , [ @with_log= ] { 'TRUE' | 'FALSE' } ]      [ , [ @replace= ] 'replace' ] [ @msgnum= ] msg_id Is the ID of the message. msg_id is int with a default of NULL. msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647. The combination of msg_id and language must be unique; an error is returned if the ID already exists for the specified language. [ @severity = ]se

What is First Normal Form (1NF)?

My blog has many practical tips and best pratices for SQL/BI developers,but I haven’t focused on interview questions for SQL/BI developers so far. This might change in the future. It’s been a challenge for many people to break into or stay competitive in the SQL/BI profession. I am very lucky to be able to stay in the profession and also stay in the financial industry. It’s been very rewarding for me to share my experience and knowledge through this blog. Many recruiters do not understand what exactly a SQL/BI Developer does. One thing they assume we don’t do is design. On the contrary, designing from simple table structure to the entire sub-system for staging and ETL is our daily job. In this blog, I’ll share with you one simple SQL design interview question and the answer that will set you apart from other candidates. Interview question: what is First Normal Form (1NF)? In order to give an answer that will earn you an A, we need to relate 1NF to what we do every day first. Memorisin

get Identity column value from Table

Image
hi friends, today i find one of the good feature to find Identity column from table.. go though with this example create Table T1(Col1 int identity(1,5),Col2 int) Go Insert into T1(Col2) Select 100 Go 100 Select $Identity,* From T1 so at here i am creating one table with having two columns one is my identity column and another is used to store value (100) now after creating table at here i am inserting record in it.. Insert into T1(Col2) Select 100 Go 100 Inserting Identity value starting from 1 and seeds to 5 element like 1,6,11,16,21,26 and so on.... and Go 100 is used for looping that insert statement 100 times.. and i am also selecting 100 value at here.. by using $Identity i can come to know about Identity column value.

SQL Function VS StoredProcedure

Question What is the difference between SQLStored Procedure and SQLFunction? Answer 1) A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value. 2) Stored procedure in Sql Server can not we executed within the DML statement.It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement.

CTE Common Table Expression

The CTE is one of the essential features in the sql server 2005.It just store the result as temp result set. It can be access like normal table or view. This is only up to that scope. The syntax of the CTE is the following. WITH name (Alias name of the retrieve result set fields) AS ( //Write the sql query here ) SELECT * FROM name Here the select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE. CTE 1: Simple CTE WITH ProductCTE AS ( SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price] FROM Products ) SELECT * FROM ProductCTE Here all the product details like ID, name, category ID and Unit Price will be retrieved and stored as temporary result set in the ProductCTE. This result set can be retrieved like table or view. CTE2:Simple CTE with alias WITH ProductCTE(ID,Name,Category,Price) AS ( SELE

Regular Expression for strong password

hi friend,  what is strong password?  Ans :: it contains at least  1 small letter character  1 capital letter character  1 numeric character  1 special symbol  and length of password is between 4 to 8 Character  that is called strong password..  regex for Strong password  ^(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%&?]).{4,8}$ how to work with asp.net regex library  bool IsMatch1 = Regex.IsMatch(passwordString, @"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%&?]).{4,8}$"); thanks  jayeshl  http://sqlassistant.blogspot.com  

Generate password dynamically

Generate Dyanmic Password using Asp.net protected void btnGenerate_Click(object sender, EventArgs e) { string allowedChars = ""; allowedChars = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,"; allowedChars += "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,"; allowedChars += "1,2,3,4,5,6,7,8,9,0,!,@,#,$,%,&,?"; char[] sep ={ ',' }; string[] arr = allowedChars.Split(sep); string passwordString = ""; string temp = ""; Random rand = new Random(); for (int i = 0; i < Convert.ToInt32(txtPassLength.Text); i++) { temp = arr[rand.Next(0, arr.Length)]; passwordString += temp; } txtPassword.Text = passwordString; }