I have gathered this information through different blogs and demos
1.
Sequences: Sequences have been
requested by the SQL Server community for years, and it's included in this
release. Sequence is a user defined object that generates a sequence of a
number. Here is an example using Sequence.
/****** Create Sequence Object ******/
CREATE SEQUENCE MySequence
START WITH 1
INCREMENT BY 1;
CREATE SEQUENCE MySequence
START WITH 1
INCREMENT BY 1;
/****** Create Temp Table ******/
DECLARE @Person TABLE
(
ID int NOT NULL PRIMARY KEY,
FullName nvarchar(100) NOT NULL
);
DECLARE @Person TABLE
(
ID int NOT NULL PRIMARY KEY,
FullName nvarchar(100) NOT NULL
);
/****** Insert Data ******/
INSERT @Person (ID, FullName)
VALUES (NEXT VALUE FOR MySequence, 'Shamas Saeed'),
(NEXT VALUE FOR MySequence, 'Qamar Saeed');
INSERT @Person (ID, FullName)
VALUES (NEXT VALUE FOR MySequence, 'Shamas Saeed'),
(NEXT VALUE FOR MySequence, 'Qamar Saeed');
/****** Display results ******/
SELECT * FROM @Person;
The results would look like this:
ID FullName
1 Shamas Saeed
2 Qamar Saeed
1 Shamas Saeed
2 Qamar Saeed
2.
New Paging through Query: Paging results in SQL Server has been
discussed for years. The Order By option in the SQL SELECT statement has been
enhanced in SQL Server 2012. Using a combination of OFFSET and FETCH along with
ORDER BY gives you control of paging through a result set. Using this technique
can really help performance by bring back only the results you want to show to
your users when they are needed. Following is the example of paging though
query. The OFFSET command provides a starting point for the SELECT statement in
terms of paging, and the FETCH command provides how many records to return at a
time.
SELECT *
FROM TableSample
ORDER BY ID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
FROM TableSample
ORDER BY ID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
3.
Full Text Search: The Full Text Search in SQL Server 2012 has
been enhanced by allowing you to search and index data stored in extended
properties or metadata. Consider a PDF document that has "properties"
filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest
release of SQL Server, this data could be indexes and searched along with the
data in the document itself. The data does have to be exposed to work, but its
possible now.
4 comments:
Sqeuence and paging Queries are awesome, Can u tell me if we can select return columns from a stored proc like the way we do from table / function.
i.e.
Select colA,colB from MyStoredProc
No, We cannot use store procedure as select statement but there are other ways to return values from a store proc.
1- Using Select statement to return data set.
2- Using OUTPUT parameter to return variable values.
There is a way you can do this...
look at this example
First I have created 2 procedures and then result of first procedure returns into another table and return that data into second procedure.
if exists (select * from sys.objects where name like 'up_returnDataSet')
drop procedure up_returnDataSet
go
if exists (select * from sys.objects where name like 'up_returnDataSet2')
drop procedure up_returnDataSet2
go
Create procedure up_returnDataSet
as
begin
Declare @vTable Table(ID int,Value varchar(10))
Insert into @vTable values (1,'Shamas'),(2,'Shams'),(3,'Qamar'),(4,'Usman'),(5,'Atif'),(6,'Salman')
Select * from @vTable
end
go
Create procedure up_returnDataSet2
as
begin
Declare @vTableInsert Table(ID int,Value varchar(10))
Insert into @vTableInsert
exec up_returnDataSet
select * from @vTableInsert
end
exec up_returnDataSet
exec up_returnDataSet2
this might help.
http://shamas-saeed.blogspot.com/2012/02/calling-procedure-result-set-out-put-in.html
this might also help
Post a Comment