Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

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, 117 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, 142 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, 126 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, 155 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

6 comments, 230 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, 122 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, 115 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, 84 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, 151 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, 74 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, 137 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, 161 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, 270 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, 117 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

13 comments, 137 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, 208 reads

Posted in Querying Microsoft SQL Server on 29 September 2012

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, 145 reads

Posted in Querying Microsoft SQL Server on 29 September 2012

Connection String from Visual Studio

Querying Microsoft SQL Server : Connection String from Visual Studio: Connection String: A connection string contains connection information need to connect with data source.You can connect your project with ...

Read more

0 comments, 126 reads

Posted in Querying Microsoft SQL Server on 22 September 2012

Data Types in SQL Server

Querying Microsoft SQL Server : Data Types in SQL Server: Data Type Description Remarks BIGINT Used for large Integer data.(Range -2^63  to 2^63-1) It takes 8 B...

Read more

0 comments, 73 reads

Posted in Querying Microsoft SQL Server on 22 September 2012

Functions in 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...

Read more

0 comments, 105 reads

Posted in Querying Microsoft SQL Server on 22 September 2012

Newer posts

Older posts