-->
SQL Clone
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.

How to Split a string by delimited char in SQL Server..............

However I posted this one in one of my post.
Querying Microsoft SQL Server : Functions in SQL Server: Functions in SQL Server In SQL Server functions are subrotienes that encapsulate a group of T-SQL statements for reuse.SQL Server pro...

Here I separate ,Split function from that post.

CREATE…

Read more

13 comments, 450,478 reads

Posted in Querying Microsoft SQL Server on 19 September 2013

Connection Pool limit exceeds Error

Sometimes below error occurs on our web applications hosted on IIS connected with SQL Server.

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

Description: An unhandled exception…

Read more

1 comments, 23,513 reads

Posted in Querying Microsoft SQL Server on 13 September 2013

Insert data from Excel to SQL Server

Here I am discussing about data insertion from MS Excel into SQL Server table. SSMS provides  Export Import wizard by which you can achieve same easily. Follow below steps
Suppose I have a Excel file on server as c:/fis.xlsx.

1.       Go to database
2.       Right Click on…

Read more

5 comments, 6,145 reads

Posted in Querying Microsoft SQL Server on 11 September 2013

Sending Mail using SQL Server Express Edition

In SQL server standard and enterprise edition,A DataBase mail functionality in built to sent mail.But in SQL Server express edition,You need either use CLR integration or configure SQL Mail using MSDB system database.
Here I am discussing sending mail using MSDB system database.By default the MSDB database installed when…

Read more

7 comments, 37,139 reads

Posted in Querying Microsoft SQL Server on 2 September 2013

How to find SQL Server Version,Edition,Server Name?

How to find sql server version,edition,Server Name?



SQL Server provides a System Defined function SERVERPROPERTY(propertyname) .

By using this function you can find a number of things


Property Name
Description
syntax
Edition
Return SQL Server edition installed on machine.
select ServerProperty('edition')
EditionID
return Edition ID
select ServerProperty('editionid')
InstanceName

Read more

0 comments, 16,227 reads

Posted in Querying Microsoft SQL Server on 31 August 2013

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, 811 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,814 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, 1,054 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, 717 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, 620 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, 702 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, 485 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,547 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,948 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, 394 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, 507 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, 305 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, 890 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, 347 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, 574 reads

Posted in Querying Microsoft SQL Server on 26 December 2012

Newer posts

Older posts