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 for More

There are two options Local Servers and Network Servers.

•           Local Servers: It lists all instances installed on your local machine.
•       Network Servers: It lists all instances installed on your Network.

Further,There are various utilities with more functionality to achieve the same.

I find some utility as

·       Quest Discovery Wizard for SQL Server 

Quest Discovery Wizard for SQL Server makes it simple to detect all the SQL Server instances in your organization and monitor your network for changes. It even helps you find SQL Server instances that native tools cannot detect.

With Discovery Wizard, you can:

Create an inventory of the databases on your network and the amount of data they contain

Select databases for consolidation

Review database history, including installation and creation dates

Find out which SQL Server versions and service packs are deployed

Learn server, instance, and operating system names

See who your end users are.

                         http://www.quest.com/discovery-wizard-for-sql-server/

·        The Microsoft Assessment and Planning Toolkit

The Microsoft Assessment and Planning Toolkit make it easy to assess your current IT infrastructure for a variety of technology migration projects. This Solution Accelerator provides a powerful inventory, assessment, and reporting tool to simplify the migration planning process.

      http://blogs.msdn.com/b/petersad/archive/2012/11/09/using-the-map-toolkit-8-0-beta-to-discover-sql-server-instances.aspx

Generate Random Passwords using T-SQL

Use following script to generate random Passwords

select replace(SUBSTRING(CONVERT(varchar(255), NEWID()),8, 8),'-','') as RANDOM

Example:

Suppose you have a table tblUser with columns

[id],[userid],[password].

CREATE TABLE [dbo].[tblUser](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [userid] [varchar](250) NULL,

      [password] [varchar](350) NULL,

      )

Now Create a trigger on table tblUser and update password column with random password

CREATE TRIGGER [dbo].[insUser]

   ON  [dbo].[tblUser]

   AFTER Insert

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

  DECLARE @id int

  SET @id = (SELECT max(id) FROM tblUser)

UPDATE tblUser set password =replace(SUBSTRING(CONVERT(varchar(255), NEWID()),8, 8),'-','') WHERE id=@id

END

this trigger create random password.

---

You can also create a function and can use any where.
To create function to generate random password, first you need to create   a view as

CREATE  VIEW getNewID as SELECT newid() as new_id

Because NEWID() does not work in function.Error occurred as

Msg 443, Level 16, State 1, Procedure fn_random, Line 7

Invalid use of a side-effecting operator 'newid' within a function.

After creating view,You create function like

CREATE function fn_random()

RETURNS varchar(16)

with encryption

begin

declare @pass varchar(16)

declare @n varchar(max)

select @n=new_id from getNewID

Select @pass=replace(SUBSTRING(CONVERT(varchar(255),CAST(@n as varchar(max)) ),8, 8),'-','')

return @pass

end

     

Now you can query function like

select dbo.fn_random()

Output:

A56B53349

again execute the same

select dbo.fn_random()

Output:

0E87A21C4

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 an Example

create table tbltest

(

id int identity(1,1),

name varchar(12),

city varchar(200)

)

insert into tbltest(name,city)values

('a',NULL)

,('b','Meerut')

,('c','Rampur')

,('d',NULL)

,('e',NULL)

,('f','NOIDA')

select *from tbltest

Output:

id            name    city

1              a              NULL

2              b             Meerut

3              c              Rampur

4              d             NULL

5              e             NULL

6              f              NOIDA

Find rows that does not have city Meerut.You make a query like

select *from tbltest where city<>'merrut'

Output:

id            name    city

3              c              Rampur

6              f              NOIDA

But this result is wrong. Result should have a, c, d, e, and f.  Where are a, d, and e.as

id            name    city

1              a              NULL

3              c              Rampur

4              d             NULL

5              e             NULL

6              f              NOIDA

It was due to NULL value. To resolve this Problem either use ISNULL() as

select *from tbltest where isnull(city,'')<>'merrut'

Or use  COALESCE()

select *from tbltest where COALESCE(city,'')<>'merrut'

Prev--->For XML Clause in SQL Server                                                          Next--->Cursor in T-SQL

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 ( emp_id,emp_name,salary )  select '2','XYZ',-5000

 insert emp ( emp_id,emp_name,salary )  select '3','PQR',-7800

 insert emp ( emp_id,emp_name,salary )  select '4','AK ',10000

 insert emp ( emp_id,emp_name,salary )  select '5','MP ',19000

 insert emp ( emp_id,emp_name,salary )  select '6','KHJ',11000

 insert emp ( emp_id,emp_name,salary )  select '7','LTR',13000

 insert emp ( emp_id,emp_name,salary )  select '8','MAN',14000

select *from emp.

emp_id                emp_name        salary

1              ABC                     -20000

2              XYZ                         -5000

3              PQR                       -7800

4              AK                          10000

5              MP                         19000

6              KHJ                         11000

7              LTR                         13000

8              MAN                     14000

·        How to get nth Highest Salary from Emp Table

SELECT * FROM  emp a WHERE 3 = (SELECT COUNT(*) FROM  emp b WHERE a.salary <= b.salary) ;

emp_id      emp_name    salary

7           LTR         13000

for nth salary

SELECT * FROM  emp a WHERE nth= (SELECT COUNT(*) FROM  emp b WHERE a.salary <= b.salary) ;

·        How to delete duplicate rows from a table

--insert auto increment id if not in table

ALTER TABLE emp ADD autoid INT IDENTITY(1,1)

--then query  returns autoid for unique rows

SELECT MAX(autoid)

FROM emp

GROUP BY emp_id, emp_name, salary

--delete duplicate rows

DELETE

FROM emp

WHERE autoid NOT IN

(

SELECT MAX(autoid)

FROM emp

GROUP BY emp_id, emp_name, salary)

·        Select alternate rows from Emp table

--Using CTE

WITH CTE AS

(

SELECT ROW_NUMBER()OVER (ORDER BY emp_id)AS ROW,* FROM emp

)

SELECT * FROM CTE WHERE ROW%2=0

--Using SubQuery

select a.* from (SELECT ROW_NUMBER()OVER (ORDER BY emp_id)AS ROW,* FROM emp) a where ROW%2=0

Output:

ROW   emp_id      emp_name    salary      autoid

2     2           XYZ         -5000       10

4     4           AK          10000       4

6     6           KHJ         11000       6

8     8           MAN         14000       8

·        Select nth record from Emp table

select a.* from (SELECT ROW_NUMBER()OVER (ORDER BY emp_id)AS ROW,* FROM emp) a where ROW=3

Output:

ROW      emp_id                emp_name        salary         autoid

3              3              PQR                                       -7800                     3

·        Suppose a column has some -Ve values and +Ve  values.Find the sum of -Ve numbers and the sum of the +Ve numbers.

data:

SELECT

SUM(CASE WHEN salary < 0 THEN salary ELSE 0 END) Sum_of_negative_amount,

SUM(CASE WHEN salary > 0 THEN salary ELSE 0 END) Sum_of_positive_amount

FROM emp;

Sum_of_negative_amount  Sum_of_positive_amount

-32800                  6000

• Suppose You have a table

   create table tbltests(id int ,id1 int)

  insert into tbltests(id,id1) values

  (1,2),(2,1),(3,null),(4,5),(5,4)

  
  

  Table Data:

   1  2

   2  1

   3  NULL

   4  5

   5  4

   Fetch data like

  id            id1
  
  1              2
  
  3              NULL
  
  4              5
  
  Query:
  
  
  
  SELECT t1.id,t1.id1
  FROM tbltests t1
  WHERE NOT EXISTS(SELECT * FROM tbltests t2
                   WHERE t2.id = t1.id1
                     AND t2.id1 = t1.id
                     AND t2.id < t2.id1)