SQL server

--how to use @table variable in sql server?
begin
declare @alldatabases table(name varchar(100));
Insert into @alldatabases(name) select name from sysdatabases  
select * from @alldatabases
end


--How to user  normal transaction in sql server?
BEGIN TRANSACTION trans
BEGIN TRY
--multiple insert updates command
IF @@TRANCOUNT > 0
COMMIT TRANSACTION trans
END TRY
BEGIN CATCH
print 'Error Occured'
IF @@TRANCOUNT > 0
 ROLLBACK TRANSACTION trans
END CATCH


--what is @@trancount?
--Auto increment as "1" with COMMIT and decrement as "0" with rollback


--what is @@rowcount?
--It return no of rows affected after each select statement
select * from sys.tables
select @@rowcount
select * from sys.databases
select @@rowcount


-- What is set noncount on|off in sql server?
--If we don’t want to return @@rowcount value after select statement then we use set nocount on.
Go
set NOCOUNT  ON
select * from sys.databases
set nocount OFF
select @@rowcount
Go


--What is @@options in sql server?
-- Returns information about the current SET options.
select @@options
SET NOCOUNT ON
select @@options
SET NOCOUNT OFF


--How to raise error in sql server?
RAISERROR ('Current user has SET NOCOUNT turned on.', 1, 1)


--how to insert identity value explicitly in sql server?
-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('book')
INSERT INTO products (product) VALUES ('pen')
SET IDENTITY_INSERT products ON
INSERT INTO products (id, product) VALUES(5, 'ink')
SET IDENTITY_INSERT products OFF
GO

--Get server name in sql server?
select @@servername


--Get current service instance name in sql server?
select @@SERVICENAME


--How to know sql server version information in sql server?
select @@version


--How to get current sql session id of the current user process?
select @@SPID


--How many maximum simultaneous user connections are possible in an instance of sql server?
--Ans:- about 32767
select @@MAX_CONNECTIONS


--Which default language is enable for the current sql server?
select @@language


--Get records from each group.
select * from (
select name, len(name) as nlength , rank() over (partition by len(name) order by name asc) as nrank  from sys.tables
)t1 where nrank=1


-- How to use with keyword as nested query?
with names as (
select * from sys.tables
)
select name from names where name like('%track%')