Friday 22 February 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 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





Tuesday 12 February 2013

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

Tuesday 5 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 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

Monday 4 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 ( 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)