SQL SERVER FUNCTION

--How much time attempt Login
 SELECT GETDATE() AS 'Today''s Date and Time',
@@CONNECTIONS/2 AS 'Login Attempts'
--@@DBTS returns the last-used timestamp value of the current database. A new timestamp value is generated when a row with a timestamp column is inserted or updated.
 SELECT @@DBTS;

--Returns the error number for the last Transact-SQL statement executed.
IF @@ERROR <>0
    PRINT N'A check constraint violation occurred.';
GO

@@OPTIONS

SET NOCOUNT on
IF @@OPTIONS & 512 > 0
RAISERROR ('Current user has SET NOCOUNT turned on.', 5, 5)

@@FETCH_STATUS
 --@@FETCH_STATUS using cursor using while loop
 --WHILE @@FETCH_STATUS = 0 -  The FETCH statement was successful.
 --WHILE @@FETCH_STATUS = -1 -     The FETCH statement failed or the row was beyond the result set.
 --WHILE @@FETCH_STATUS = -2 -  The row fetched is missing.

 @@IDENTITY  --are similar functions because they all return the last value inserted into the IDENTITY column of a table.
 --@@IDENTITY-- return the last identity value generated in any table in the current session.
 --SCOPE_IDENTITY--returns the value only within the current scope;
--  IDENT_CURRENTIs --the name of the table whose identity value is returned. table_name is varchar, with no default.

SELECT IDENT_CURRENT ('northwind.employees') AS Current_Identity;

EXEC sp_monitor
--To monitoring Current Database

@@LANGID
-- Current Language ID
SELECT @@LANGID AS 'Language ID'

@@LOCK_TIMEOUT
--default -1
SET LOCK_TIMEOUT 1800;

SELECT @@LOCK_TIMEOUT AS [Lock Timeout];


@@MAX_CONNECTIONS

SELECT @@MAX_CONNECTIONS AS 'Max Connections'

@@NESTLEVEL

alter PROCEDURE usp_InnerProc AS
    SELECT @@NESTLEVEL AS 'Inner Level';

GO
CREATE PROCEDURE usp_OuterProc AS
    SELECT @@NESTLEVEL AS 'Outer Level';
    EXEC usp_InnerProc;
GO

EXECUTE usp_OuterProc;
alter PROC usp_NestLevelValues AS
    SELECT @@NESTLEVEL AS 'Current Nest Level';
EXEC ('SELECT @@NESTLEVEL AS OneGreater');
EXEC sp_executesql N'SELECT @@NESTLEVEL as TwoGreater' ;
EXECUTE usp_OuterProc;
exec ('SELECT @@NESTLEVEL AS Third ');

GO


@@RowCount -- to detect if any rows were changed.
 update lego set delflag='N' where legoid=33780111
 if @@ROWCOUNT=0
 Print 'Warning '

 @@SERVERNAME --to display Servername
  SELECT @@SERVERNAME
  select @@SERVICENAME
  SELECT @@SPID
  SELECT @@TEXTSIZE
  SET TEXTSIZE 2048


@@TOTAL_ERRORS --shows the number of errors encountered by SQL Server as of the current date and time.
SELECT @@TOTAL_ERRORS AS 'Errors', GETDATE() AS 'As of'

@@TOTAL_READ --shows returning the total number of disk read and writes as of the current date and time.
SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As of'
---------
@@TRANCOUNT --The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

Print @@TRANCOUNT

BEGIN TRAN
    PRINT @@TRANCOUNT
    rollback
    BEGIN TRAN
        PRINT @@TRANCOUNT
--  The COMMIT statement will decrement the transaction count by 1.
    COMMIT
    PRINT @@TRANCOUNT
COMMIT

PRINT @@TRANCOUNT

------------------------------

Partition From --Only use in Enterprice Edition

CREATE PARTITION FUNCTION RangePF1 ( int )
AS RANGE FOR VALUES (10, 100, 1000) ;
GO
SELECT $PARTITION.RangePF1 (10) ;
GO

ABS--the absolute Value in number
ACOS--the specified number.
--
DECLARE @CurrentApp varchar(40)
SET @CurrentApp = APP_NAME()
IF @CurrentApp <> 'SQL Server Management Studio - Query'
PRINT 'This process was not started by a SQL Server Management Studio query session.';
GO

SELECT APP_NAME()
select Proc_name

select * from App_Links


 ASSEMBLYPROPERTY --
 SELECT ASSEMBLYPROPERTY ('HelloWorld' , 'PublicKey');

 CREATE ASSEMBLY munish
FROM 'D:\209.210.25.64\PS\PS\PS\Bin\SMTPAuthentication.dll'
WITH PERMISSION_SET = SAFE

--Certficate id  to select
SELECT Cert_ID('ABerglundCert3');

use Northwind
select * from employees
SELECT COLUMNPROPERTY(OBJECT_ID('Employees'),'LastName','PRECISION')AS 'Column Length';



use Northwind

create table Tran1(ino int,name varchar(20))
DECLARE @TransactionName varchar(20) = 'Transaction1';
ROLLBACK TRAN @TransactionName;
Commit


DECLARE @TransactionName varchar(20) = 'Transaction1';
 BEGIN TRAN @TransactionName
       INSERT INTO Tran1 VALUES(12,'fff');
  BEGIN TRAN @TransactionName
  delete from   Tran1  

DECLARE @TransactionName0 varchar(20) = 'Transaction1';
rollback TRAN @TransactionName0
DECLARE @TransactionName1 varchar(20) = 'Transaction2';
DECLARE @TransactionName2 varchar(20) = 'Transaction3';
 begin transaction @TransactionName0
 create table TransactionName0(ino int)

 begin   transaction @TransactionName1
 create table  TransactionName1(ino int)

 begin   transaction @TransactionName2
 create table  TransactionName2(ino int)


  rollback

 select * from   TransactionName2




 --Exception Handling  in try cacth
 begin try
 declare @a as int
 set @a='sss'
 print 'will block to access'
 end try
begin catch
print 'Catch section'
end catch
print 'End'

Begin try
declare @x as int

end try
begin catch
declare @enum as nvarchar(200)
declare @errormessage as nvarchar(200)
declare @erroline as nvarchar(200)
select
  @enum=ERROR_MESSAGE() ,@errormessage=ERROR_STATE() ,@erroline=ERROR_SEVERITY()
  
select ERROR_NUMBER() as errornuum,ERROR_LINE() as errorline,
ERROR_MESSAGE() as errormessage,
ERROR_SEVERITY()as errservity,
ERROR_STATE() as errorstate
 
 RAISERROR(@errormessage,@enum,@erroline)
  
 
end Catch


--DBCC Comments
--http://www.novicksoftware.com/tipsandtricks/tip-sql-dbcc-help.htm

 dbcc traceon(2520)
      dbcc help ('?')
      GO

use Northwind


create table table1(col1 varchar(100))
insert into table1(col1)
select '0001'
union ALl
select '000100'
union ALL
Select '100100'
union ALL
Select '000 0001'
union ALL
Select '00.001'
union ALL
Select '01.001'
select * from table1
select SUBSTRING(col1,PATINDEX ('%[^0]%',col1+''),LEN(col1)) from table1-- the pattern is a literal string that can contain wildcard character
select SUBSTRING(col1,CHARINDEX ('%[^0]%',col1+''),LEN(col1)) from table1--  CHARINDEX, the pattern is a literal string without wildcard characters.

Newid()--Creates a unique value of type uniqueidentifier.

 DECLARE @myid1 uniqueidentifier
SET @myid1 = 'A972C577-DFB0-064E-1189-0154C99310DAAC12'
GO
CREATE TABLE cust
(
 CustomerID uniqueidentifier NOT NULL
   DEFAULT newid()
   )
  
   insert  cust (CustomerID)  values   (NEWID())
  
   select   * from cust
  
select * from Customers 
 select * from Customers order by CHECKSUM(NEWID())
  select 
  ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 10000 as column3
from Customers
  
 Rand-
  
SELECT Rand() as RandomNumber, *
FROM Northwind..Customers
 SELECT RAND(2)

 create view tview
 as
 select rand() 

 create function Rrand()
 returns decimal(18,18)
 as
 begin
 declare @rndvalue decimal(18,18)
 select @rndvalue=result from tview
 return @rndvalue
 end

 --select dbo.Rrand()
 --select rand()

 -- this way does not use
 create function radfn()
 returns decimal(18,18)
 as begin
 declare @rndva decimal(18,18)
 set @rndva=rand()
 return @rndva
 end
 --end

 --group by
 CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH CUBE


 

Browse More Articles

Tweet

Written by Test

We are Creative Blogger Theme Wavers which provides user friendly, effective and easy to use themes. Each support has free and providing HD support screen casting.