Wednesday, December 8, 2010

Select Top 10% of rows - SQL Server 2008

Execute the following SQL Server T-SQL scripts in SSMS Query Editor to list the top 5 percent result set with ties and without ties:

USE AdventureWorks;



SELECT TOP ( 10 ) PERCENT WITH TIES Employee = FirstName + ' ' + Lastname,

e.Title,

Gender,

Rate

FROM HumanResources.Employee e

JOIN HumanResources.EmployeePayHistory eph

ON e.EmployeeID = eph.EmployeeID

JOIN Person.Contact c

ON c.ContactID = E.ContactID

ORDER BY Rate DESC;

GO

=====================================================================



SELECT TOP ( 10 ) PERCENT Employee = FirstName + ' ' + Lastname,

e.Title,

Gender,

Rate

FROM HumanResources.Employee e

JOIN HumanResources.EmployeePayHistory eph

ON e.EmployeeID = eph.EmployeeID

JOIN Person.Contact c

ON c.ContactID = E.ContactID

ORDER BY Rate DESC;

Tuesday, November 30, 2010

PrintDialog in C#

A PrintDialog control is used to open the Windows Print Dialog and let user select the printer, set printer and paper properties and print a file. A typical Open File Dialog where you select a printer from available printers, set printer properties, set print range, number of pages and copies and so on. Clicking on OK button sends the document to the printer.

Creating a PrintDialog
We can create a PrintDialog at design-time as well as at run-time

Design-time
To create a PrintDialog control at design-time, you simply drag and drop a PrintDialog control from Toolbox to a Form in Visual Studio.

Run-time
Creating a PrintDialog control at run-time is simple. First step is to create an instance of PrintDialog class and then call the ShowDialog method. The following code snippet creates a PrintDialog control.

PrintDialog PrintDialog1 = new PrintDialog();

PrintDialog1.ShowDialog();


Printing Documents


PrintDocument object represents a document to be printed. Once a PrintDocument is created, we can set the Document property of PrintDialog as this document. After that we can also set other properties. The following code snippet creates a PrintDialog and sends some text to a printer.

private void PrintButton_Click(object sender, EventArgs e)

{

PrintDialog printDlg = new PrintDialog();

PrintDocument printDoc = new PrintDocument();

printDoc.DocumentName = "Print Document";

printDlg.Document = printDoc;

printDlg.AllowSelection = true;

printDlg.AllowSomePages = true;

//Call ShowDialog

if (printDlg.ShowDialog() == DialogResult.OK)

printDoc.Print();



}

Wednesday, October 27, 2010

Use of Progress Bar control in C#

A Progress Bar control is used to display the progress of some activity.

The project is developed using C#, Windows Forms, and Visual Studio 2005.

As you can see below, we can add a timer control to the form and on the timer tick event handler, we can increment the value of the progress bar.

namespace Progress_Bar

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}



// Call the function of tick event of the timer:_...

// Set the timer Intervel at 60:_

// Set the timer enabled "True" by the timer properties:_

private void timer1_Tick(object sender, EventArgs e)

{

fn_prbar_();
}



//Create the function for progress bar:_
public void fn_prbar_()

{
progressBar1.Increment(1);

label1.Text = "Connecting to server_ " + progressBar1.Value.ToString() + "%";

if (progressBar1.Value == progressBar1.Maximum)

{
timer1.Stop();

MessageBox.Show("Server has been connected");
this.Close();
timer1.Stop();

}
}

}

}

Working with the Generic Dictionary Class In C#

This Example Demonstrates the use Of a Generic Dictionary Class Which is an Improved HashTable Class and performs much better than a HashTable.

Example :


using System;
using System.Collections.Generic;
class Program
{
static void Main(string[] args)
{
Dictionary< int, string > students = new Dictionary< int, string >();
students.Add(1, "Hefin");
students.Add(2, "Abbas");
students.Add(3, "Rinso");
students.Add(4, "Sachin");

Dictionary< string, string > Employees = new Dictionary< string, string >();
Employees.Add("E01", "Hefin Dsouza");
Employees.Add("E02", "Abbas Electriwala");
Employees.Add("E03", "Rinso Joseph");
Employees.Add("E04", "Sachin Gaikar");

Console.WriteLine("List of all Students");
foreach (KeyValuePair< int,string > n in students)
{
Console.WriteLine("|Roll Number : " + n.Key + "t| Name : " + n.Value);
}

Console.WriteLine("List of all Employees");
foreach (KeyValuePair< string, string > n in Employees)
{
Console.WriteLine("|EMP ID : " + n.Key + "tt| EMP Name : " + n.Value);
}
}
}

This is an Improved HashTable which restricts what data type will the Key be and What Data Type the value should be.So it makes this class much Type Safe.

Get number of rows in file in C#

This small one line of code will give you total number of lines or rows in a normal text file in c#.

Just save result of this in normal int variable and you ready to go..

int lines = System.IO.File.ReadAllLines("path").Length

Hope this helps in File read operations

Tuesday, October 26, 2010

Use of Row Constructor in Sql Server 2008!!

There is a new feature of "Row Constructors" in SQL server 2008 , where I can load temp table from stored procedure directly.


Look at the following SQL where I have to use OpenQuery from server to itself creating loopback server and execute stored procedure and insert into temp table.

INSERT INTO #TempTable
SELECT *
FROM OPENQUERY(ServerName, 'exec StoredProc')

Above mentioned same query can be now written with simpler statement as described here.

INSERT INTO #TempTable
EXEC StoredProc

Note that this does not work with real tables or any other objects. This feature is only available to load temp tables.

Exact use of COALESCE() function in sql server

COALESCE(arg1,arg2,...) is a pretty useful function in SQL. Suppose we have a table A having 3 columns FullName, CompleteName and DisplayName. Any of these columns can contain null values. Now we want to select the DisplayName from this table, but if it is null, then return FullName, if that is also null then return CompleteName. We can easily perform the same in one select statement as:

SELECT COALESCE(DisplayName, FullName, CompleteName) From A

This will return the first non-null value. And best thing is that COALESCE is an ANSI standard.

To get random records from a table in Sql Server

With the help of newid() function, each time different records will be genertaed .
This is inbuilt function in sql Server.
Ex:

SELECT Top 5 [name], [salary]FROM [dbo].[dupemp] ORDER BY NEWID()

EXCEPT Key Word in SQL Server

Microsoft introduced the EXCEPT operator in SQL Server 2005, which returns all of the distinct rows from the left side of the EXCEPT operator. It also removes all of the rows from the result set that match the rows that are on the right side of the EXCEPT operator.


To return all rows in table1 that do not match exactly the rows in table2, we can just use EXCEPT like this:

select * from table1 except select * from table2

Difference between Varchar(MAX) and TEXT Datatype in Sql Server

The TEXT data type is used to store large amount of text data like text file contents.
The VARCHAR is suitable used for small quantity of data.

In previous versions of Sql Server we need to use READTEXT and WRITETEXT functions to operate on TEXT
datatypes. Now TEXT data types can be dealt like a normal data type using standard DML queries.

The scenarios where TEXT could be advisable is while storing resume contents, html contents etc.
VARCHAR is useful when we are sure the maximum data won't exceed a particular number.

Create directory using Sql Server...

Please use the follwing Command.


EXEC master.sys.xp_create_subdir 'C:\Test\'

Load the xml data into the DataSet using c#

function Loadxml()
{
XmlReader Xmlfile;
Xmlfile= XmlReader.Create(",new XmlReaderSettings());
Dataset oDs=new Dataset();
oDS.ReadXml();
}


replace "xmlfilepath" WITH YOUR FILE PATH

replace "xmlfile" WITH YOUR FILE NAME

Can we have a Stored Procedure and a table with a same name in SQL Server?

Not possible. Under the db level all are of objects with name as unique.
(table, stored procedure, views etc.)

Try select * from sysobjects

Difference between Soundex and Difference() function in Sql Server

SoundEX will return 4 letters code, this is used to compare two strings. for example
SELECT SOUNDEX ('cat'), SOUNDEX ('dog')
will return
C300 D200

DIFFERENCE finction will return 0 to 4, 4 indicates the best match.
SELECT DIFFERENCE('principal', 'principle') is 4, because those strings are almost similar, But a query looking at the difference between the words cat and dog shows a different result:
SELECT DIFFERENCE('cat', 'dog')is 2, not a best match

Differences between Crystal and SQL reporting services...

Ease of hosting reports: - Using the URL technology in Reprting Services we can host reports more easily than in crystal report where we need to make a UI for the same.

Supporting platforms: - Crystal can run on windows, IBM and sun while RS (reporting services) can run only on windows environment.

Client tools: - In reporting services we have Business intelligence ( BI ) and development studio while in crystal its Report designer.

Caching: - This achieved in crystal by using cache server while in reporting services it's stored as snapshots in Reportserver database.

Export formats:- In crystal we have HTML,PDF,Excel,XML,Word , PDF , RTF , CSV, text files while in Reprting Services we have all the formats above it also gives capability to extend custom reporting formats.

Data sources:- Crystal support more data sources while RS only supports Microsoft and oracle data sources. Crystal supports ADO, COM, Database excel Access, Exchange, NT, Xbase, JDBC, File system and Paradox. Reprting Services supports only SQL Server, Oracle, ODBC, OLEDB and you can also extend additional data sources which does not exists in crystal reports.

Version issues: - One of the main issues faced in crystal is it have different versions which makes it difficult to use, while Reprting Services comes with SQL Server minimizing the version issue.

Web server support: - Crystal can run on IIS 5/6, Apache, lotus etc while Reprting Services S works only with IIS 5.0 and above.

Saturday, October 23, 2010

What happens when you click on a .net exe?

A .NET exe is a standard Portable Executable (PE) file which contains data in its header which identifies it as such and which enables the CLR to be loaded. There are also some additional sections in the header (the CLR header and CLR data).

When you build an EXE assembly, the compiler/linker emits some special information into the resulting assembly’s PE File header and the file’s .text section. When the EXE file is invoked, this special information causes the CLR to load and initialize. Then the CLR locates the entry point method for the application and lets the application start executing.

Make an SQL query sleep for some time

Use these simple statement.

WAITFOR DELAY '00:00:10'

SELECT * FROM My_Table

Thursday, October 21, 2010

Convert Date to String in SQL Server

DECLARE @Dt as DateTime
SET @Dt = '2010-04-27 11:30:17'
SELECT CONVERT(CHAR(8), @Dt, 112) +
REPLACE(CONVERT(CHAR(8), @Dt, 114), ':', '')

Dynamically Pass a Table Name to TRUNCATE TABLE

GO
DECLARE @tbl varchar(30) = 'YourTableName'
DECLARE @dynSQL varchar(50)
SET @dynSQL = 'TRUNCATE TABLE ' + @tbl
EXEC (@dynSQL)

Find rows with lower case characters in SQL Server

Table - Test
Column - col1

SELECT *
FROM Test
WHERE col1
LIKE '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS

Find the duplicates and count of a column value in SQL Server

We have a table 'Test' with a column col1
Find the duplicate values & count of duplicates for the column col1


SELECT col1, COUNT(col1) AS NumOfOccurrences
FROM dbo.Test
GROUP BY col1
HAVING (COUNT(col1) > 1)

SQL Server - Update columns of a table by taking values from another table

Update columns of Table 1 with corresponding values from Table 2 based on the values of a third common column.


UPDATE Table_Name1
SET
col1 = Table_Name2.col1,
col2 = Table_Name2.col2,
FROM Table_Name1, Table_Name2
WHERE Table_Name1.col3 = Table_Name2.col3

GO

Duplicate a column of a table in SQL Server

To duplicate an existing column in a table.

Assume a we have table 'Test' with columns col1( varchar(10)) and col2(varchar(10))
Assume we have values present in col1.

Use the following simple query to duplicate col2 by taking values from col1

UPDATE Test
SET col2= col1

Wednesday, October 20, 2010

List all the Weekends of the Current Year using SQL Server

DECLARE @StrtDate datetime
DECLARE @EndDate datetime
SELECT @StrtDate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
SELECT @EndDate = DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))
;WITH CTE (weekends)AS(SELECT @StrtDateUNION ALLSELECT DATEADD(d,1,weekends)
FROM CTEWHERE weekends < @EndDate)
SELECT weekends from CTEWHERE DATENAME(dw,weekends)In ('Saturday', 'Sunday')

Compare Temp Table & Table Variable in SQL Server

Temp Table
--------------
1) Temp table is valid for a session.For eg: when you run the following code
create table #temp(i int)
insert into #temp select 345
Go
create table #temp(i int)
insert into #temp select 345
Go
you will get an error
2) It is possible to alter the temp table to add columns, idexes,etc
3) It is possible to truncate a temp table
4) SELECT INTO method can be used for temp table
SELECT * INTO #temp from tablename
5) Temp table can be useful when you have a large amount of data
Table Variable
--------------

1) Table variable has a statement-level scope. ie as soon as you execute the statement the scope is lost For eg: when you run the following code
declare @t table(i int)
insert into @t select 45
GO
declare @t table(i int)
insert into @t select 45
GO
you will not get an error
2) It is not possible to alter a table variable
3) It is not possible to truncate a table variable
4) SELECT INTO method cannot be used for table variable. You get error for the following
SELECT * INTO @t from your_table
5) For small set of data, table variables can be useful