Pages

Google Analytics Alternative

Search This Blog

Friday, December 16, 2011

How to find a table when you don't know which database it belongs to?

In this very short tip I'd like to share my solution to a yesterday quesiton in MSDN forum - how to find the database the table belongs to. I use my favorite idea of dynamically constructing a query using INFORMATION_SCHEMA.TABLES view.

DECLARE @table sysname, @SQL NVARCHAR(MAX)

SET @table = 'Items'

SET @SQL = ''

SELECT @SQL = @SQL + ';

IF EXISTS (SELECT 1 from ' + QUOTENAME(name) + '.INFORMATION_SCHEMA.Tables WHERE Table_Name = @table

AND TABLE_TYPE = ''BASE TABLE'')

PRINT ''Table ' + @table + ' found in ' + name + ''''

FROM sys.databases

 
EXECUTE sp_executeSQL @SQL, N'@table sysname', @table
That's all. Try it with the table which you may have in multiple databases. I used a bit undocumented way of concatenating multiple rows in a string, so I can not actually use ORDER BY in this code. To be strict, I need to use XML PATH('') approach here instead.

Thursday, December 1, 2011

15 GREAT THOUGHTS BY CHANAKYA



1) "Learn from the mistakes of others... you can't live long enough to make them all yourselves!!"
- Chanakya

2)"A person should not be too honest. Straight trees are cut first and Honest people are screwed first."
- Chanakya

3)"Even if a snake is not poisonous, it should pretend to be venomous."
Chanakya

4)"There is some self-interest behind every friendship. There is no friendship without self-interests. This is a bitter truth."
- Chanakya
                                                            
5)" Before you start some work, always ask yourself three questions - Why am I doing it, What the results might be and Will I be successful. Only when you think deeply and find satisfactory answers to these questions, go ahead."
- Chanakya

6)"As soon as the fear approaches near, attack and destroy it."
- Chanakya

7)"The world's biggest power is the youth and beauty of a woman."
- Chanakya

8)"Once you start a working on something, don't be afraid of failure and don't abandon it. People who work sincerely are the happiest."
- Chanakya

9)"The fragrance of flowers spreads only in the direction of the wind. But the goodness of a person spreads in all direction."
- Chanakya

10)"God is not present in idols. Your feelings are your god. The soul is your temple."
- Chanakya

11) "A man is great by deeds, not by birth."
- Chanakya

12) "Never make friends with people who are above or below you in status. Such friendships will never give you any happiness."
- Chanakya

13) "Treat your kid like a darling for the first five years. For the next five years, scold them. By the time they turn sixteen, treat them like a friend. Your grown up children are your best friends."
- Chanakya

14) "Books are as useful to a stupid person as a mirror is useful to a blind person."
- Chanakya

15) "Education is the best friend. An educated person is respected everywhere. Education beats the beauty and the youth."
-Chanakya


Tuesday, November 29, 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, and that all integrity constraints have been satisfied.

Thursday, November 24, 2011

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 AddNumbers(int FirstNumber, int SecondNumber)
    {
        return FirstNumber + SecondNumber;
    }

    public int AddNumbers(int FirstNumber, int SecondNumber, int ThirdNumber)
    {
        return FirstNumber + SecondNumber + ThirdNumber;
    }
}


Method Overriding
-----------------

Method Overriding means having a different implementation of the same method in the inherited class. These two methods would have the same signature, but different implementation. One of these would exist in the base class and another in the derived class. These cannot exist in the same class.

Example of Overriding
---------------------

public partial class _Default : System.Web.UI.Page 
{
    protected void Button1_Click(object sender, EventArgs e)
    {
        MyBaseClass mbc = new MyBaseClass();
        Response.Write(mbc.Method1());
        MyBaseClass mdc = new MyDerivedClass();
        Response.Write("<br />" + mdc.Method1());
    }
}

public class MyBaseClass
{
    public virtual string Method1()
    {
        return "Base Class Method";
    }
}

public class MyDerivedClass : MyBaseClass
{
    public override string Method1()
    {
        return base.Method1() + "is called and in the Dereive Class Method, the base class method is reimplemented here.";
    }
}


Differences between Method Overloading and Method Overriding
------------------------------------------------------------

i) Overloading deals with multiple methods in the same class with the same name but different signatures. Where as, overriding deals with two methods, one in the base class and another in the child class, that have the same signature.
ii) Overloading lets you define a similar operation in different ways for different data. Overriding lets you define a similar operation in different ways of different object types.
iii) In Overloading methods should have different signatures. But, in Overriding methods should have same signatures.
iv) In Overloading, two or more methods may belong to the same class. But in Overriding, two methods must belong to two different classes.

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 = ]severity
Is the severity level of the error. severity is smallint with a default of NULL. Valid levels are from 1 through 25. For more information about severities, see Database Engine Error Severities.

[ @msgtext = ] 'msg'
Is the text of the error message. msg is nvarchar(255) with a default of NULL.

[ @lang = ] 'language'
Is the language for this message. language is sysname with a default of NULL. Because multiple languages can be installed on the same server, language specifies the language in which each message is written. When language is omitted, the language is the default language for the session.

[ @with_log = ] { 'TRUE' | 'FALSE' }
Is whether the message is to be written to the Windows application log when it occurs. @with_log is varchar(5) with a default of FALSE. If TRUE, the error is always written to the Windows application log. If FALSE, the error is not always written to the Windows application log but can be written, depending on how the error was raised. Only members of the sysadmin server role can use this option.

 Note
If a message is written to the Windows application log, it is also written to the Database Engine error log file.

[ @replace = ] 'replace'
If specified as the string replace, an existing error message is overwritten with new message text and severity level. replace is varchar(7) with a default of NULL. This option must be specified if msg_id already exists. If you replace a U.S. English message, the severity level is replaced for all messages in all other languages that have the same msg_id.

Examples :

A. Defining a custom message

The following example adds a custom message to sys.messages.
USE master
GO
EXEC sp_addmessage 50001, 16, 
   N'Percentage expects a value between 20 and 100. 
   Please reexecute with a more appropriate value.';
GO

B. Adding a message in two languages

The following example first adds a message in U.S. English and then adds the same message in French.
USE master;
GO
EXEC sp_addmessage @msgnum = 60000, @severity = 16, 
   @msgtext = N'The item named %s already exists in %s.', 
   @lang = 'us_english';

EXEC sp_addmessage @msgnum = 60000, @severity = 16, 
   @msgtext = N'L''élément nommé %1! existe déjà dans %2!', 
   @lang = 'French';
GO

C. Changing the order of parameters

The following example first adds a message in U.S. English, and then adds a localized message in which the parameter order is changed.
USE master;
GO

EXEC sp_addmessage 
    @msgnum = 60000, 
    @severity = 16,
    @msgtext = 
        N'This is a test message with one numeric
        parameter (%d), one string parameter (%s), 
        and another string parameter (%s).',
    @lang = 'us_english';

EXEC sp_addmessage 
    @msgnum = 60000, 
    @severity = 16,
    @msgtext = 
        -- In the localized version of the message,
        -- the parameter order has changed. The 
        -- string parameters are first and second
        -- place in the message, and the numeric 
        -- parameter is third place.
        N'Dies ist eine Testmeldung mit einem 
        Zeichenfolgenparameter (%3!),
        einem weiteren Zeichenfolgenparameter (%2!), 
        und einem numerischen Parameter (%1!).',
    @lang = 'German';
GO  

-- Changing the session language to use the U.S. English
-- version of the error message.
SET LANGUAGE us_english;
GO

RAISERROR(60000,1,1,15,'param1','param2') -- error, severity, state,
GO                                       -- parameters.

-- Changing the session language to use the German
-- version of the error message.
SET LANGUAGE German;
GO

RAISERROR(60000,1,1,15,'param1','param2') -- error, severity, state, 
GO


FellFree add your comment to make this blog more useful.

Thanks




Sunday, November 20, 2011

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. Memorising answers from hundrands of SQL blogs will not get you very far, because under the pressure of being interviewed by several people, your memory will start to suffer from impairment very soon.
Have you ever created primary keys for your SQL tables? I bet you have. But have you ever asked yourself why do we need to create primary keys? Or have you ever asked yourself a question in an even bigger scope: how do we efficiently organize data in a database?
Here are the simple answers to the above questions:
  1. Normalization is the process of efficiently organizing data in a database.
  2. There are two goals of the normalization process.
  3. One goal is to eliminate redundant data.
  4. Another goal is to ensure data dependencies make sense (only storing related data in a table).
  5. First normal form (1NF) happens to be the very basic rule for an organized database.
  6. The implementation of 1NF principle in DBMS is to create primary key for a SQL table.
  7. Now we know 1NF is implemented as primary key in DBMS. With the above answers, you’ve already received an A. With the additions below, you will for sure get a solid A+.
  8. When creating PK for a table, we want to accomplish two things.
  9. One is to eliminate duplicative columns from the same table.
  10. Another one is to create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Friday, November 11, 2011

get Identity column value from Table

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.



Thursday, November 10, 2011

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.

Tuesday, November 8, 2011

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
(
SELECT ProductID,ProductName,CategoryID,UnitPrice
FROM Products
)
SELECT * FROM ProductCTE

Here there are four fieds retrieves from the Products and the alias name have given in the arqument to the CTE result set name.

It also accepts like the following as it is in the normal select query.

WITH ProductCTE
AS
(
SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
FROM Products
)
SELECT * FROM ProductCTE

CTE 3: CTE joins with normal table

The result set of the CTE can be joined with any table and also can enforce the relationship with the CTE and other tables.

WITH OrderCustomer
AS
(
SELECT DISTINCT CustomerID FROM Orders
)
SELECT C.CustomerID,C.CompanyName,C.ContactName,C.Address+', '+C.City AS [Address] FROM Customers C INNER JOIN OrderCustomer OC ON OC.CustomerID = C.CustomerID

Here the Ordered Customers will be placed in the CTE result set and it will be joined with the Customers details.

CTE 4: Multiple resultsets in the CTE

WITH MyCTE1
AS
(
SELECT ProductID,SupplierID,CategoryID,UnitPrice,ProductName FROM Products
),

MyCTE2
AS
(
SELECT DISTINCT ProductID FROM "Order Details"
)
SELECT C1.ProductID,C1.ProductName,C1.SupplierID,C1.CategoryID FROM MyCTE1 C1 INNER JOIN MyCTE2 C2 ON C1.ProductID = C2.ProductID

Here, there are two result sets that will be filtered based on the join condition.

CTE 5: Union statements in the CTE

WITH PartProdCateSale
AS
(
SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Condiments')
UNION ALL
SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Seafood')
)
SELECT OD.ProductID,SUM(OD.UnitPrice*OD.Quantity) AS [Total Sale] FROM "Order Details" OD INNER JOIN PartProdCateSale PPCS ON PPCS.ProductID = OD.ProductID
GROUP BY OD.ProductID

Normally when we combine the many result sets we create table and then insert into that table. But see here, we have combined with the union all and instead of table, here CTE has used.

CTE 6: CTE with identity column

WITH MyCustomCTE
AS
(
SELECT CustomerID,row_number() OVER (ORDER BY CustomerID) AS iNo FROM
Customers
)
SELECT * FROM MyCustomCTE

Monday, November 7, 2011

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