Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Querying Microsoft SQL Server

I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.

My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.

When and why you should use 1=1 in WHERE clause?

One Interviewer ask this question during interview of my friend.It is easy to answer but need some specific and to the point answer.So I try to relate it with C#.
 If you don't know  the list of conditions at compile time and it will built at run time,…

Read more

0 comments, 637 reads

Posted in Querying Microsoft SQL Server on 24 August 2013

Remove duplicate records from a table in SQL server


Here I create a table and inserted some duplicate records for testing purpose.

create table temp(cust_id varchar(50))
insert into temp(cust_id)
values
('oms1'),
('oms2'),
('oms1'),
('oms2'),
('oms3')
select *from temp

Method 1:

Read more

0 comments, 1,050 reads

Posted in Querying Microsoft SQL Server on 19 March 2013

Querying Microsoft SQL Server : Basics of Triggers in SQL Server

Querying Microsoft SQL Server : Basics of Triggers in SQL Server: SQL Triggers:  A trigger is a procedural code like stored procedure that is automatically executed on certain actions (like insert, de...

Read more

0 comments, 771 reads

Posted in Querying Microsoft SQL Server on 13 March 2013

Querying Microsoft SQL Server : Basics of Indexes in SQL Server

Querying Microsoft SQL Server : Basics of Indexes in SQL Server: Indexes in SQL Server: If you see a book, in last of book an index is provided that has some titles and page numbers. You don’t need ...

Read more

0 comments, 584 reads

Posted in Querying Microsoft SQL Server on 13 March 2013

Querying Microsoft SQL Server : Control Statements in T-SQL

Querying Microsoft SQL Server : Control Statements in T-SQL: Control statements are used to control execution flow within a T-SQL.Here I provides some example on control statements. ·            ...

Read more

0 comments, 512 reads

Posted in Querying Microsoft SQL Server on 13 March 2013

Querying Microsoft SQL Server : Defining Variables

Querying Microsoft SQL Server : Defining Variables: Defining Variables in SQL Server: Like other programming languages T-SQL allows to defining your variables. A variable is known as lo...

Read more

0 comments, 549 reads

Posted in Querying Microsoft SQL Server on 13 March 2013

Find Transaction Log information

Transaction Log in SQL server records all operation on database.This recoring depends on Recovery Model selected for database.You can find Recovery Model for each database by following Query


SELECT
    name as DataBaseName,
    log_reuse_wait_desc,
    recovery_model_desc
    FROM sys.databases      

Output:
















There…

Read more

0 comments, 412 reads

Posted in Querying Microsoft SQL Server on 8 March 2013

Search all running SQL Server instances over LAN


Sometimes you need to know how many and what instances of SQL Server running in your organization over Network. You can easily see in simple steps
  •    Go to SSMS.
  •    Object Explorer
  •    Click on Connect




  •      Go to Server Name Dropdown and Select Browse…

Read more

0 comments, 1,071 reads

Posted in Querying Microsoft SQL Server on 22 February 2013

Generate Random Passwords using T-SQL



Use following script to generate random Passwords

DECLARE @id int,
@list varchar
SET @list = 'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789.,-_!$@#%^&*'
select replace(SUBSTRING(CONVERT(varchar(255), NEWID()),8, 8),'-','') as RANDOM



Example:

Suppose you have a table tblUser with columns

Read more

7 comments, 1,060 reads

Posted in Querying Microsoft SQL Server on 12 February 2013

Handling Null Values in SQL Server


What is NULL?

In SQL, A Null is an unknown or undefined value. Some interesting operations on NULL.

--query                 --result
select 3*NULL      --NULL
select 3+NULL      --NULL
select NULL/0      --NULL
select 'queryingsql' where null=null --Null never be equal to Null unless SET ANSI_NULLS OFF.




Now See…

Read more

0 comments, 332 reads

Posted in Querying Microsoft SQL Server on 5 February 2013

Frequently asked SQL Server Queries

Data to test:

CREATE TABLE [dbo].[emp](
      [emp_id] [nchar](10) NULL,
      [emp_name] [nchar](10) NULL,
      [salary] [int] NULL
) ON [PRIMARY]
 insert emp ( emp_id,emp_name,salary )  select '1','ABC',-20000
 insert emp (

Read more

2 comments, 386 reads

Posted in Querying Microsoft SQL Server on 4 February 2013

SQL Server



What is SQL Server?
Microsoft SQL Server is Relational database management system (RDBMS) developed and owned by Microsoft. It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another…

Read more

0 comments, 238 reads

Posted in Querying Microsoft SQL Server on 28 January 2013

DBCC Commands for SQL Server


DBCC Commands:
DBCC commands are most useful for performance and troubleshooting.
The DBCC Commands are used in Maintenance, Informational use, Validation on a database, index, or file group. Theses commands are helpful to check physical and logical consistency of database. Some DBCC statements fix errors automatically. Here I am showing…

Read more

0 comments, 745 reads

Posted in Querying Microsoft SQL Server on 28 January 2013

Frequently asked Scripts during Interviews


1. Select no of Months between two dates:


SELECT
    DATEDIFF(MONTH, '4/1/2011', '01/23/2013') +
    CASE
        WHEN DAY('4/1/2011') < DAY('01/23/2013')
        THEN 1
        ELSE 0
    END
Output:
----------------
22


2. What will be the output of…

Read more

1 comments, 268 reads

Posted in Querying Microsoft SQL Server on 24 January 2013

FOR XML Clause


FOR XML CLAUSE:  Sometimes we need to data in form of XML from Database.SQL Server provide FOR XML Clause. For XML clause returns result of query as XML.There are some options with FOR XML clause
  • Raw
  • Path
·         Auto
  • Elements(can be used with raw,auto,path)
  • Explicit

You will find…

Read more

0 comments, 447 reads

Posted in Querying Microsoft SQL Server on 26 December 2012

Find Nth highest salary using RANKING functions


Suppose you want to find nth highest salary from a table,Then you can use DENSE_RANK() function.Here i show some example that show results and i hope clear concept about ranking functions          

DENSE_RANK(),RANK(),ROW_NUMBER(),NTILE() in SQL Server:


CREATE TABLE [dbo].[Person1](
    …

Read more

2 comments, 10,980 reads

Posted in Querying Microsoft SQL Server on 19 December 2012

Partitioning Table in SQL Server:


Partitioning Table in SQL Server:
 When your database has large tables. Then partitioning will be beneficial in terms of performance, scalability and maintainability. Sql Server partitioning allows to spread data on various physical disks leveraging the concurrent performance of those disks to optimize query performance. 
To use partitioning we…

Read more

0 comments, 826 reads

Posted in Querying Microsoft SQL Server on 10 December 2012

Sending Querying Data on Mail as Attached File


Sending Querying Data on Mail:


Sometimes we need to send data fetched from query on mail automatically. You can 

create report and after running reports you can send mail data fetched from report.

Alternative is send query data on mail and schedule this job. Now you don’t need to 

run…

Read more

0 comments, 385 reads

Posted in Querying Microsoft SQL Server on 7 December 2012

Linked Server setup with Oracle


Linked Server Setup with Oracle in SQL Server:

Suppose you want to access data from oracle or other database products using T-SQL via SQL server database Engine then you need to configure Linked Server.
You can learn about Linked Server from MSDN .
Here I show an example to setup…

Read more

17 comments, 610 reads

Posted in Querying Microsoft SQL Server on 23 October 2012

Basics of Triggers in SQL Server

Querying Microsoft SQL Server : Basics of Triggers in SQL Server: SQL Triggers:  A trigger is a procedural code like stored procedure that is automatically executed on certain actions (like insert, de...

Read more

0 comments, 828 reads

Posted in Querying Microsoft SQL Server on 29 September 2012

Newer posts

Older posts