Retrieve the view definition from a SQL Server

Is there a way to retrieve the view definition from a SQL Server?

YES!

Use this Query:

select definition
from sys.objects     o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = object_id( 'dbo.MyView')
  and o.type      = 'V'

Create table Periode

Kadang kita memerlukan table periode terutama untuk reporting. Pada case kali ini saya memerlukan table periode untuk populate data perbulan ke reporting table.

Cara otomatis membuat table periode:

;WITH cte

AS

(

SELECT DATEADD(M, DATEDIFF(M, 0, @StartDate), 0) AS Dt

UNION ALL

SELECT DATEADD(M, 1, Dt)

FROM cte

WHERE Dt BETWEEN @StartDate AND DATEADD(M, -1, @EndDate)

)

SELECT Dt

FROM cte

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

See here:
http://blogs.msdn.com/b/spike/archive/2008/07/31/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding.aspx

Create a clustered index

Using SQL Server Management Studio

To create a clustered index by using Object Explorer

1. In Object Explorer, expand the table on which you want to create a clustered index. 
2. Right-click the Indexes folder, point to New Index, and select Clustered Index…. 
3. In the New Index dialog box, on the General page, enter the name of the new index in the Index namebox. 
4. Under Index key columns, click Add…. 
5. In the Select Columns from table_name dialog box, select the check box of the table column to be added to the clustered index. 
6. Click OK. 
7. In the New Index dialog box, click OK. 

To create a clustered index by using the Table Designer

1. In Object Explorer, expand the database on which you want to create a table with a clustered index.
2. Right-click the Tables folder and click New Table…. 
3. Create a new table as you normally would. For more information, see Create Tables (Database Engine). 
4. Right-click the new table created above and click Design. 
5. On the Table Designer menu, click Indexes/Keys. 
6. In the Indexes/Keys dialog box, click Add. 
7. Select the new index in the Selected Primary/Unique Key or Index text box.
8. In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property. 
9. Click Close. 
10. On the File menu, click Save table_name. 

Using Transact-SQL

To create a clustered index

1. In Object Explorer, connect to an instance of Database Engine.
2. On the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute.

1. USE AdventureWorks2012;
   GO
   -- Create a new table with three columns.
   CREATE TABLE dbo.TestTable
       (TestCol1 int NOT NULL,
        TestCol2 nchar(10) NULL,
        TestCol3 nvarchar(50) NULL);
   GO
   -- Create a clustered index called IX_TestTable_TestCol1
   -- on the dbo.TestTable table using the TestCol1 column.
   CREATE CLUSTERED INDEX IX_TestTable_TestCol1 
       ON dbo.TestTable (TestCol1); 
   GO
   

Source: http://technet.microsoft.com/en-us/library/ms186342.aspx

Enabling remote access to a SQL Server

Configure the remote access option

1. In Object Explorer, right-click a server and select Properties.
2. Click the Connections node.
3. Under Remote server connections, select or clear the Allow remote connections to this server check box.

Configure 'SQL Server Configuration Manager'

1. On the SQL Server, open 'SQL Server Configuration Manager'. This can typically be found linked from the 'Start' menu. For example: Start | All Programs | Microsoft SQL Server 2008 R2 | Configuration Tools | SQL Server Configuration Manager.
2. Expand 'SQL Server Network Configuration' and highlight the 'Protocols for [InstanceName]' option.
3. In the right-hand window, if 'TCP/IP' currently has the 'status' of 'Disabled', right click on 'TCP/IP' and select 'Enable'.
   Note: You will be requested to restart the SQL Server service to complete the configuration change.
4. Enable access to port 1433 (the port used by SQL Server) on the db server via Windows Firewall and any network-level firewalls.
5. To restart the service, you can use the same Microsoft Management Console (MMC) window. To do so, highlight the 'SQL Server Services' option at the top of the tree. In the right-hand window, you can then right click on the 'SQL Server [Instance]' entry and choose 'Restart'.

If you still cannot connect, turn off your firewall and try again

Source:
http://technet.microsoft.com/en-us/library/ms191464.aspx
http://www.sophos.com/en-us/support/knowledgebase/118473.aspx

http://stackoverflow.com/questions/13777680/sql-server-2012-remote-access-outside-domain

How to find the longest string in a varchar column in SQL Server

Given Table (SeiyuuNames)

DECLARE @SeiyuuNames TABLE
(
SeiyuuNamesID INT,
SeiyuuNames VARCHAR (100)
)

INSERT INTO @SeiyuuNames
(SeiyuuNamesID, SeiyuuNames)
VALUES
(1, 'Hirakawa Daisuke'),
(2, 'Yasumoto Hiroki'),
(3, 'Tachibana Shinnosuke'),
(4, 'Yonaga Tsubasa')

Query:

SELECT * FROM @SeiyuuNames

SELECT
SeiyuuNames AS Longest_Name
FROM
@SeiyuuNames
WHERE
LEN(SeiyuuNames) =
(
SELECT
MAX(LEN(SeiyuuNames))
FROM
@SeiyuuNames
)

Result:

SQL ROLLUP and COMPUTE BY

ROLLUP and COMPUTE BY are like doing running balannce. We can get the summary of each data.

Example :

CREATE TABLE tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
[Population (in Millions)] INT
)
GO
INSERT INTO tblPopulation VALUES('India', 'Delhi','East Delhi',9 )
INSERT INTO tblPopulation VALUES('India', 'Delhi','South Delhi',8 )
INSERT INTO tblPopulation VALUES('India', 'Delhi','North Delhi',5.5)
INSERT INTO tblPopulation VALUES('India', 'Delhi','West Delhi',7.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Bangalore',9.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Belur',2.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Manipal',1.5)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Mumbai',30)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Pune',20)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nagpur',11 )
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nashik',6.5)

GO
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)]
FROM tblPopulation
GROUP BY Country,[State],City
WITH ROLLUP
GO
SELECT Country,[State],City, [Population (in Millions)]
FROM tblPopulation
ORDER BY Country,[State],City
COMPUTE SUM([Population (in Millions)]) BY Country,[State]--,City
GO

source: Pinal Dave

Sort Varchar

combine string and number like 1.1


declare @t table(id int ,data varchar(50))
declare @t3 table(id int, data3 varchar(50))
insert into @t
select '1','Rak 10' union all
select '2','Rak 1.2' union all
select '3','Rak 122' union all
select '4','Rak 2' union all
select '5','Area 2'union all
select '6','Area Lantai 1' union all
select '7','Rak 1'
 
declare @xml xml,
        @max_len int
     

insert @t3 (id, data3)
select id, SubString(data,(LEN (data) - CharIndex (' ', REVERSE(data))+2),LEN (data)) as data2
from @t

set @xml =
(
select data3,
 cast('<i>' + replace(data3,'.','</i><i>') + '</i>' as xml)
from @t3
for xml path('id_root'),type
)

select @max_len = max(len(x.i.value('.','varchar(50)')))
from @xml.nodes('/id_root/i') x(i)

select T.data, T3.data3, srt.srtvalue
from @t3 T3
join @t T
ON T3.id = T.id
cross apply(
    select
case
when ISNUMERIC(x.i.value('.','varchar(50)')) = 1
then right(replicate('0',@max_len) + x.i.value('.','varchar(50)'),@max_len)
else x.i.value('.','varchar(50)')
end + '.'
    from @xml.nodes('/id_root/i') x(i)
    where x.i.value('../data3[1]','varchar(50)') = [T3].data3
    for xml path('')
) as srt(srtvalue)
order by SubString (data,1 ,CharIndex (' ', data)) ,srt.srtvalue

Result:
data
--------------------------------------------------
Area Lantai 1
Area 2
Rak 1
Rak 1.2
Rak 2
Rak 10
Rak 122


(7 row(s) affected)

Sort Varchar (eq 1.1)

declare @temp table (id varchar(255))

insert into @temp (id) values
  ('1.1.a.1'),('1.1.aa.2'),
  ('1.1.b.3'),('1.1.a.4'),
  ('1.1.a.5'),('1.1.a.6'),
  ('1.1.a.7'),('1.1.a.8'),
  ('1.1.a.9'),('21.1.a.10'),
  ('1.1.a.11'),('1.1.b.1'),
  ('2.1.b.2'),('1.2.a.1'),
  ('1.10.a.1'),('1.11.a.1'),
  ('1.20.a.1'),('101.20.a.2'),
  ('1.20.a.150'),('1.1'),
  ('1.2'),('1')


declare @xml xml,
        @max_len int

select id as id, cast('<i>' + replace(id,'.','</i><i>') + '</i>' as xml)
from @temp
for xml path('id_root'),type
set @xml =
(
select id as id, cast('<i>' + replace(id,'.','</i><i>') + '</i>' as xml)
from @temp
for xml path('id_root'),type
)

select @max_len = max(len(x.i.value('.','varchar(10)')))
from @xml.nodes('/id_root/i') x(i)

select @max_len


select [id], srt.srtvalue
from @temp
cross apply(
    select case when ISNUMERIC(x.i.value('.','varchar(10)')) = 1 then right(replicate('0',@max_len) + x.i.value('.','varchar(10)'),@max_len) else x.i.value('.','varchar(10)') end + '.'
    from @xml.nodes('/id_root/i') x(i)
    where x.i.value('../id[1]','varchar(50)') = [@temp].id
    for xml path('')
) as srt(srtvalue)
order by srt.srtvalue

Result:
id
-------------------------
1
1.1
1.1.a.1
1.1.a.4
1.1.a.5
1.1.a.6
1.1.a.7
1.1.a.8
1.1.a.9
1.1.a.11
1.1.aa.2
1.1.b.1
1.1.b.3
1.2
1.2.a.1
1.10.a.1
1.11.a.1
1.20.a.1
1.20.a.150
2.1.b.2
21.1.a.10
101.20.a.2


(22 row(s) affected)

Sort VARCHAR on VARCHAR coloum (varchar + int)

SET NOCOUNT ON

Declare @Table table
(
    Id  INT Identity (1, 1),
    StringValue VarChar (30)
)

INSERT INTO @Table (StringValue) VALUES ('CAR 10')
INSERT INTO @Table (StringValue) VALUES ('CAR 20')
INSERT INTO @Table (StringValue) VALUES ('CAR 2')
INSERT INTO @Table (StringValue) VALUES ('CAR 3')
INSERT INTO @Table (StringValue) VALUES ('CAR 4')

INSERT INTO @Table (StringValue) VALUES ('SHIP 32')
INSERT INTO @Table (StringValue) VALUES ('SHIP 310')
INSERT INTO @Table (StringValue) VALUES ('SHIP 320')
INSERT INTO @Table (StringValue) VALUES ('SHIP 33')
INSERT INTO @Table (StringValue) VALUES ('SHIP 34')


SELECT Id,
    SubString (StringValue, 1, CharIndex (' ', StringValue)) ObjectName,
    CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue))) ObjectId
FROM @Table
ORDER BY 2, 3

SELECT Id, StringValue
FROM @Table
ORDER BY
    SubString (StringValue, 1, CharIndex (' ', StringValue)),
    CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue)))

Parse Coloumn into One Single String

There is 2 ways I know :
------------------------------------------------------------------------------------------
DECLARE @List VARCHAR(MAX)

SELECT
           @List = ISNULL(EmployeeID + ', ' , '') + @List
FROM
           Employee

SELECT @List
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

SELECT
LEFT(CA.List, LEN(CA.List)-1)
FROM
(
SELECT
CONVERT(VARCHAR(100),EmployeeID) + ',' AS [text()]
FROM
 Employee 
FOR XML PATH('')
)CA(List)
-------------------------------------------------------------------------------------------

Sometimes way no 2 faster than no 1, but for some condition no 1 is faster.

Apply the APPLY Clause

The real magic happens when you use SQL Server 2005's new APPLY clause. The APPLY clause let's you join a table to a table-valued-function. That let's you write a query like this:

SELECT  C.CustomerID, 
	O.SalesOrderID,
	O.TotalDue
FROM 
	AdventureWorks.Sales.Customer AS C
CROSS APPLY
	AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O
ORDER BY 
	CustomerID ASC, TotalDue DESC

which results in this...

CustomerID  SalesOrderID TotalDue
----------- ------------ ---------------------
1           45283        37643.1378
1           46042        34722.9906
1           44501        26128.8674
2           46976        10184.0774
2           47997        5469.5941
2           57044        4537.8484
3           53616        92196.9738
3           47439        78578.9054
3           48378        56574.3871
4           47658        132199.8023
. . .

credit : http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

Javascript get text from dropdownlist

var ddlReport = document.getElementById("<%=DropDownListReports.ClientID%>");
var Text = ddlReport.options[ddlReport.selectedIndex].text;
var Value = ddlReport.options[ddlReport.selectedIndex].value;

SQL DATEADD Function

Returns a new datetime value based on adding an interval to the specified date.
SQL DATEADD SyntaxDATEADD ( datepart , number, date )


DECLARE @DateNow DATETIME
SET @DateNow='2007-06-04'
SELECT DATEADD(Year, 3, @DateNow) AS NewDate 
Return Value = 2010-06-04 00:00:00.000

SELECT DATEADD(quarter, 3, @DateNow) AS NewDate
Return Value = 2008-03-04 00:00:00.000

SELECT DATEADD(Month, 3, @DateNow) AS NewDate
Return Value = 2007-09-04 00:00:00.000

SELECT DATEADD(dayofyear,3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000

SELECT DATEADD(Day, 3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000

SELECT DATEADD(Week, 3, @DateNow) AS NewDateReturn Value = 2007-06-25 00:00:00.000


SELECT DATEADD(Hour, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 03:00:00.000

SELECT DATEADD(minute, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:03:00.000

SELECT DATEADD(second, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:03.000

SELECT DATEADD(millisecond, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:00.003




repost from : http://sqltutorials.blogspot.com/2007/06/sql-dateadd-function.html

Sort Number in Varchar Coloum

Here are some methods:
declare @t table(data varchar(15))
insert into @t
select '6134' union all
select '144' union all
select '7345' union all
select '109812' union all
select '100074'union all
select '1290' union all
select '45764'

--Method 1
select data from @t
order by cast(data as int)

--Method 2
select data from @t
order by data+0

--Method 3
select data from @t
order by len(data),data

--Method 4
select data from @t
order by replace(str(data),' ','0')

--Method 5
select data from @t
group by data
order by replicate('0',len(data)),data

--Method 6
select data from @t
order by replicate('0',(select max(len(data+0)) from @t)-len(data))+data

--Method 7
select data from @t
cross join
(
        select len(max(data+0)) as ln from @t
) as t
order by replicate('0',ln-len(data))+data

(credit: sqlblogcasts.com/blogs/madhivanan)


Those method only can be used if the coloum containing number only.
For varchar also number, you can use this:

DECLARE @TBL TABLE
(
 Name VARCHAR(100)
)

INSERT INTO @TBL VALUES('TKK1'),('TKK2'),('TKK11')

SELECT * FROM @TBL
ORDER BY LEN(Name),Name

This method only limited for fixed pattern (varchar value always same).

If you have data like this:

1
2
11
abc
you can use this method:
SELECT ... FROM table order by
CASE WHEN column < 'A' THEN LPAD(column, size, '0') ELSE column END;