declare
@dtVariable as datetime;
set
@dtVariable =
getdate();
-- Truncated to the second
select
@dtVariable as Original,
DATEADD(MILLISECOND,
-DATEPART(MILLISECOND, @dtVariable),
@dtVariable) as
TruncatedToSecond;
-- Rounding to the second
select
@dtVariable as Original,
DATEADD(ms, 500 -
DATEPART(ms, @dtVariable +
'00:00:00.500'),
@dtVariable) as
RoundedToSecond;
-- Truncated to the minute
select
@dtVariable as Original,
DATEADD(minute,
DATEDIFF(minute, 0, @dtVariable), 0) as
TruncatedToMinute;
-- Rounded to minute
select
@dtVariable as Original,DATEADD(minute,
DATEDIFF(minute, 0,DATEADD(second, 30 -
DATEPART(second,
@dtVariable +
'00:00:30.000'),@dtVariable)), 0)
as RoundedToMinute;
-- Truncated to the hour
select
@dtVariable as Original,DATEADD(hour,
DATEDIFF(hour, 0, @dtVariable), 0) as
TruncatedToHour;
-- Rounded to hour
select
@dtVariable as Original,DATEADD(hour,
DATEDIFF(hour, 0,DATEADD(minute, 30 -
DATEPART(minute,
@dtVariable +
'00:30:00.000'), @dtVariable)), 0) as
RoundedToHour;
-- Truncated to the day
select
@dtVariable as Original,DATEADD(Day,
DATEDIFF(Day, 0, @dtVariable), 0) as
TruncatedToDay;
-- Rounded to day
select
@dtVariable as Original,DATEADD(day,
DATEDIFF(day, 0,DATEADD(hour, 12 -
DATEPART(hour,
@dtVariable +
'12:00:00.000'), @dtVariable)), 0) as
RoundedToDay;
XVI. Error Message
XVII. Other
XVII.1 Other
XVIII. Srini Query & Other
1. We can RUN the below Queries on SQL Server "sql3.network80.com"
and Database "cBizSoft"
SELECT *
FROM dbo.AllHostedDB
ORDER BY SQLServerName
SELECT *
FROM dbo.ContactsInfoOnServerName('sql3.network80.com,sql.network80.com')
ORDER
BY SQLServerName
SELECT *
FROM dbo.HostedDBNotInLogins
ORDER BY SQLServerName
SELECT *
FROM dbo.HostedDBNotInLiveButInLogins
ORDER BYLoginsSQLServer
XVIII.1. Functions, Procedures and Jobs On (License Database cBizSoft)
XVIII.2. Functions, Procedures and Jobs On (Ticketing Database cBizSoft)
XIX.0 T-SQL Query & Database Optimization
XIX. T-SQL Query & Other
1. How to determine which version, SQL Bit and More... of SQL Server (Service pack):
SELECT
SERVERPROPERTY('Edition')
EXEC master.dbo.xp_msver
SELECT
@@version
SELECT
SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'), SERVERPROPERTY
('edition')
1.1. Attention Using SET QUOTED_IDENTIFIER OFF (Use
Both Off and On Or else it will effect the Procedure):
SET
QUOTED_IDENTIFIER OFF
... Procedure/Function...
SET
QUOTED_IDENTIFIER ON
SELECT
SCHEMA_NAME(s.schema_id) +
'.' + s.name AS name,s.create_date,s.modify_date,
OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') AS
IsQuotedIdentOn
FROM
sys.objects
s
WHERE
s.type
IN ('P','TR','V','IF','FN','TF')
AND
OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') =
0
ORDER
BY SCHEMA_NAME(s.schema_id)
+ '.' + s.name
DESC
1.2. How to check if a function exists on a SQL database:
IF
EXISTS
(SELECT *
FROM
sys.objects
WHERE
object_id = OBJECT_ID(N'[dbo].[foo]')
AND type
IN
( N'FN',
N'IF',
N'TF',
N'FS',
N'FT' ))
2.1. Re-Indexing one Table Column:
DBCC
DBREINDEX ("Companies", aaaaaCompanies_PK,80)
2.2. Re-Indexing all the main Tables:
DBCC
DBREINDEX ('Companies' ,
' ', 80)
DBCC
DBREINDEX ('Consultants' ,
' ', 80)
DBCC
DBREINDEX ('Contacts' ,
' ', 80)
DBCC
DBREINDEX ('Documents' ,
' ', 80)
DBCC
DBREINDEX ('EMailsLinked' ,
' ', 80)
DBCC
DBREINDEX ('EMailsSent' ,
' ', 80)
DBCC
DBREINDEX ('Links' ,
' ', 80)
DBCC
DBREINDEX ('Lookup' ,
' ', 80)
DBCC
DBREINDEX ('MLinkInfo' ,
' ', 80)
DBCC
DBREINDEX ('Reminders' ,
' ', 80)
DBCC
DBREINDEX ('Requirements' ,
' ', 80)
DBCC
DBREINDEX ('SaveSetInfo' ,
' ', 80)
DBCC
DBREINDEX ('SaveSets' ,
' ', 80)
Procedure
2.3. Create Index On a Column:
CREATE INDEX
[UserField10] ON [Consultants]([UserField10]) ON [PRIMARY]
3. How to register Dll and Exe in command-line:
To un/register Dll:
c:\>regsvr32 "C:\Windows\System32\My.dll"
c:\>regsvr32 /u "C:\Windows\System32\My.dll"
To un/register Exe:
c:\>"C:\Program Files\MyAppLocation\My.exe" /REGSERVER
c:\>"C:\Program Files\MyAppLocation\My.exe" /UNREGSERVER
4. Returns the sequential number of a row within a partition of a result set, starting
at 1 for the first row in each partition:
SELECT
ReqIntID, ROW_NUMBER()
OVER
(ORDER BY ReqIntID
)
AS 'RowNumber'
FROM
Requirements
5. Shrink Log File Script :
BACKUP
LOG cBizOne WITH
TRUNCATE_ONLY
DBCC
Shrinkfile ('cBizOne_log',2)
For SQL SERVER 2008:
USE
AdventureWorks;
GO
-- Truncate the log by changing the database recovery
model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY
SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE
(AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
6. How to Change The Logical File Names of SQL Server Databases:
You can not change the database file name using the GUI (Graphical User Interface)
of the SQL Management Studio in SQL 2005 or the Enterprise Manager in SQL 2000.
But you can use the ALTER DATABASE sql command as shown below :
ALTER
DATABASEProduct
MODIFY
FILE (NAME = ProductTest_Data,
NEWNAME = Product_Data)
This command returns the below message if it is successfull
The file name 'Product_Data' has been set.
ALTER
DATABASEProduct
MODIFY
FILE (NAME = ProductTest_Log,
NEWNAME = Product_Log)
This command returns the below message if it is successfull
The file name 'Product_Log' has been set.
7. How to SRINIK SQL Server Databases: The file size will not decrease when
you delete data. In order to decrease the file size, you must run DBCC SHRINKFILE.
exec
sp_spaceused
USE
VishnuTest1
DBCC
SHRINKFILE ('VishnuTest1')
GO
DBCC
SHRINKFILE ('VishnuTest1_log')
GO
8. How to copy data from one table to other table:
INSERT
INTO cBizOne_ProductMarketing.
DBO.CONTACTS
SELECT
*
FROM
cBizOneIndia.DBO.CONTACTS
WHERE
(COMPANYNAME
LIKE 'CBIZ%')
AND
(COUNTRY LIKE
'US%') AND
(CONTACTINTID!='CNZ0001169')
9. Executes a Transact-SQL statement:
EXECUTE
sp_executesql
N'SELECT * FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level',
N'@level tinyint',
@level = 109
;
10 ALTER TABLE and Add Column and Constraint:
--Kris said to use this
ALTER
TABLE SaveSetInfo ADD
SaveSetType nvarchar(20) NOT
NULL DEFAULT
''
ALTER
TABLE SaveSetInfo ALTER
COLUMN SaveSetType
nvarchar(20)
NULL
OR
ALTER
TABLE Requirements ADD
uuRunJob BIT NOT NULLDEFAULT
(0)
10.1 Delete column with Constraint and ALTER Column name:
DECLARE @SQL_Text
NVARCHAR(MAX)
= ''
IF
((SELECT COUNT(*) FROM
INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME =
'Requirements' AND (COLUMN_NAME
= 'ClientRate'
OR COLUMN_NAME =
'ClientRate_OLD_DB'))
= 2)
IF
(((SELECT COUNT(1) FROM
Requirements WHERE
ISNULL(ClientRate,0.00)
!= 0.00)
= 0)
AND
((SELECT
COUNT(1) FROM
Requirements WHERE
LEN(ISNULL(ClientRate_OLD_DB,''))
> 0)
> 0))BEGIN
SELECT @SQL_Text += '
ALTER TABLE ' + t.name +
' DROP CONSTRAINT ' +
df.name +
'
ALTER TABLE ' + t.name +
' DROP COLUMN ' +
c.NAME +
'
EXEC sp_rename ''Requirements.ClientRate_OLD_DB'', ''ClientRate'', ''COLUMN'';'
FROM sys.default_constraints
df
INNER JOIN sys.tables t ON df.parent_object_id =
t.object_id
INNER JOIN sys.columns c ON df.parent_object_id =
c.object_id
AND df.parent_column_id
= c.column_id
WHERE t.name = 'Requirements'
AND c.NAME
= 'ClientRate'
END
IF
((SELECT COUNT(*) FROM
INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME =
'Consultants' AND (COLUMN_NAME
= 'Rate'
OR COLUMN_NAME =
'Rate_OLD_DB'))
= 2)
IF
(((SELECT COUNT(1) FROM
Consultants WHERE
ISNULL(Rate,0.00) != 0.00) = 0) AND ((SELECT COUNT(1)
FROM Consultants WHERE
LEN(ISNULL(Rate_OLD_DB,'')) > 0) > 0))
BEGIN
SELECT @SQL_Text += '
ALTER TABLE ' + t.name +
' DROP CONSTRAINT ' +
df.name +
'
ALTER TABLE ' + t.name +
' DROP COLUMN ' +
c.NAME +
'
EXEC sp_rename ''Consultants.Rate_OLD_DB'', ''Rate'', ''COLUMN'';'
FROM sys.default_constraints
df
INNER JOIN sys.tables t ON df.parent_object_id =
t.object_id
INNER JOIN sys.columns c ON df.parent_object_id =
c.object_id
AND df.parent_column_id
= c.column_id
WHERE t.name = 'Consultants'
AND c.NAME
= 'Rate'
END
IF(LEN(@SQL_Text)
> 0)
BEGIN
--PRINT @SQL_Text
EXECUTE(@SQL_Text)
PRINT 'Done.'
END
11. Changing the field size:
ALTERR
TABLE Consultants
ALTER COLUMN
UserField5 NVARCHAR
(200)
NULL
ALTER TABLE
EMailsSent ADD
UUBody1 NTEXT NULL
11.1. Change field DataType:
DECLARE @Requirements_Rate_Constraint NVARCHAR(100),
@SQLQuery NVARCHAR(MAX)
SELECT @Requirements_Rate_Constraint
= OBJECT_NAME(O.OBJECT_ID)
FROM
sys.objects
O, sys.columns C
WHERE (O.parent_object_id
= C.object_id) AND
OBJECT_NAME(O.parent_object_id) =
'Requirements' AND
O.type_desc LIKE
'%CONSTRAINT'
AND
(C.default_object_id
= O.object_id) AND
(C.name
= 'Rate')
SELECT @SQLQuery = '
ALTER TABLE [dbo].Requirements DROP
CONSTRAINT ' + @Requirements_Rate_Constraint
+ '
ALTER TABLE [dbo].Requirements ALTER
COLUMN Rate NVARCHAR(80) NULL
ALTER TABLE [dbo].Requirements ADD
CONSTRAINT ' + @Requirements_Rate_Constraint
+ ' DEFAULT ('''') FOR Rate'
EXEC(@SQLQuery)
=====
DECLARE @EMailsLinked_Info_Constraint
NVARCHAR(128),
@SQLQuery NVARCHAR(MAX)
SELECT
@EMailsLinked_Info_Constraint =
OBJECT_NAME(O.OBJECT_ID)
FROM sys.objects O,
sys.columns
C
WHERE (O.parent_object_id =
C.object_id) AND
OBJECT_NAME(O.parent_object_id)
=
'EMailsLinked' AND
O.type_desc LIKE
'%CONSTRAINT'
AND
(C.default_object_id =
O.object_id) AND
(C.name
= 'Info')
SELECT
@SQLQuery = '
ALTER TABLE [dbo].EMailsLinked
DROP CONSTRAINT ' + @EMailsLinked_Info_Constraint + '
ALTER TABLE [dbo].EMailsLinked ALTER
COLUMN Info VARCHAR(max) NULL
ALTER TABLE [dbo].EMailsLinked ADD
CONSTRAINT '
+ @EMailsLinked_Info_Constraint
+ ' DEFAULT ('''')
FOR Info'
EXEC(@SQLQuery)
Note: Which completes the conversion by moving
the data from the lob structure to the table (if the length in less than 8k), which
improves performance / keeps things proper.
UPDATE
[dbo].EMailsLinked SET
Info = Info
WHERE
1 = 1
11.2. Get Column Constraint Name:
SELECT OBJECT_NAME(O.OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(O.schema_id)
AS SchemaName,
OBJECT_NAME(O.parent_object_id) AS TableName,
O.type_desc AS
ConstraintType, C.name
as ColumnName
FROM
sys.objects
O, sys.columns C
WHERE (O.parent_object_id
= C.object_id) AND
OBJECT_NAME(O.parent_object_id) =
'Requirements' AND
O.type_desc LIKE
'%CONSTRAINT'
AND
(C.default_object_id
= O.object_id) AND
(C.name
= 'Rate')
12. Calculate Percentage Under Reports (VB Script):
If RTPeople = 0 Then If RTPeople = 0 Then
Persent.Value = 0
Else
Persent.Value = FormatPercent(RTLast30Days/RTPeople,0)
End If
IF (LEN(txtUserID10)>0) THEN
Line13.Visible=True
txtUserID10.Visible=True
txtTotal.Left=txtTotal.Left+895
Line.Width=Line.Width+895
IF ((StrComp(Theme,"Table Style 2")=0) or (StrComp(Theme,"Table Style
3")=0) or (StrComp(Theme,"Table Style 4")=0)) THEN
Line.Visible=False
END IF
END IF IF (StrComp(Theme,"Table Style 1")=0) THEN
RTCTotal="ffffff"
txtTotal1.ForeColor = "&H000000"
txtUserID1.ForeColor = "&H000000"
txtUserID2.ForeColor = "&H000000"
txtUserID3.ForeColor = "&H000000"
txtUserID4.ForeColor = "&H000000"
txtUserID5.ForeColor = "&H000000"
txtUserID6.ForeColor = "&H000000"
txtUserID7.ForeColor = "&H000000"
txtUserID8.ForeColor = "&H000000"
txtUserID9.ForeColor = "&H000000"
txtUserID10.ForeColor = "&H000000"
txtTotal.ForeColor = "&H000000"
END
<Name>txt1</Name>
<Section>0</Section>
<Text>PeakRank</Text>
<Calculated>-1</Calculated>
<Left>30</Left>
<Top>30</Top>
<Width>1345</Width>
<Height>270</Height>
<Align>7</Align>
<WordWrap>0</WordWrap>
<Font> <Name>Tahoma</Name>
<Size>8.25</Size>
<Bold>-1</Bold>
</Font>
</Field>
12.1. Remove the Default date Under Reports (VB Script):
If DateValue(SubmittedDate) = DateValue("1/1/1900") Then
Consultant1.Value = ""
End If
12.2.How to make SQL single User:
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [DatabaseName] SET SINGLE_USER
GO
Use Master
GO
ALTER DATABASE Database Name SET MULTI_USER
GO
12.3. How to Kill the "session ID" It is used to convert
database from multiuser to single user
SELECT
SPID FROM master..
sysprocesses
where dbid
=db_id
('DemoDataBase'
) and
spid <>
@@spid
KILL
69
AND
use master
SELECT request_session_id
FROM
sys.dm_tran_locks
WHERE
resource_database_id =
DB_ID('cbiz_vgroup')
kill 295
Set Database to Multi_User/Single_User
ALTER
DATABASE [Works] SET
MULTI_USER WITH
NO_WAIT
ALTER
DATABASE [Works] SET
SINGLE_USER WITH
NO_WAIT
USE [master];
ECLARE
@kill varchar(8000) =
'';
SELECT
@kill = @kill +
'kill ' +
CONVERT(varchar(5), session_id)
+ ';'
FROM
sys.dm_exec_sessions
WHERE
database_id
= db_id('Exelare2019')
EXEC(@kill);
ALTER
DATABASE [Exelare2019]
SET MULTI_USER
WITH NO_WAIT
12.4. The database principal owns a schema in the database, and cannot be dropped
SELECT
name FROM sys.schemas WHERE principal_id =
USER_ID('1Demodatabase')
ALTER
AUTHORIZATION ON
SCHEMA::SchemaName
TO dbo
GO
DROP
USER myUser
13.Repaire SQL Database:
dbcc checkdb('cBizOne',Repair_rebuild)
13.1 Repaire SQL Database with Data Loss:
USE Exl_VishnuTest1XYZ
ALTER
DATABASE Exl_VishnuTest1XYZ
SET SINGLE_USER
WITH ROLLBACK
IMMEDIATE;
BEGIN
TRAN T1;
DBCC
CHECKDB ('Exl_VishnuTest1XYZ',
REPAIR_ALLOW_DATA_LOSS);
COMMIT
TRAN T1;
ALTER
DATABASE Exl_VishnuTest1XYZ
SET MULTI_USER;
13.2 Client complaints on Exelare TimeOut:
ALTER DATABASE
[Exl_eProCorp] SET
SINGLE_USER WITH
ROLLBACK IMMEDIATE;
BEGIN
TRAN T1;
DBCC
CHECKDB ('Exl_eProCorp',
Repair_rebuild);
COMMIT
TRAN T1;
ALTER
DATABASE [Exl_eProCorp]
SET MULTI_USER;
Srini Ran following Successfully.
1. DBCC CHECKDB - All Good
2. Shrinked .MDF - Done
3. ReIndexing - Done
4. Catalog Drop and Recreated - Done
5. Incresed the AutoFileGrouth to
64MB - Done
14. CATALOG Script (SQL 2005):
Start Optimize Catalog Population on cbiz_Ashton.CBizOneCatalog
cbiz_Ashton Full text indexing
ALTER FULLTEXT CATALOG [CBizOneCatalog] REORGANIZE
exec sp_fulltext_catalog N'CBizOneCatalog', N'start_full'
14.1. Delete a full-text catalog from a Table
DROP FULLTEXT
INDEX ON [dbo].[Consultants]
GO
15. DATALENGTH()
This function
can be used on
all data types (Text,
Ntext, Image)
in your table.
16. Blanking the Admin password:
UPDATE
Users SET Password=
'' WHERE
ID='Admin'
AND RecType=
'20'
17. Set the default admin permissions
UPDATE Users
SET Field1 = '2047,963,963,963,963,963,963,963'
WHERE
(RecType = '20') AND
(ID = 'Admin') AND
(RPointer = '0,0') AND (IsAdmin
= 1)
18. Split path and file name
SELECT
'C:\Users\aiacullo\Documents\Resumes\Comp
Analyst-Acctg\Figueroa, Rebecca.doc'
SELECT
REVERSE(SUBSTRING(REVERSE('C:\Users\aiacullo\Documents\Resumes\Comp
Analyst-Acctg\Figueroa, Rebecca.doc'),PATINDEX('%\%',REVERSE('C:\Users\aiacullo\Documents\Resumes\Comp Analyst-Acctg\Figueroa,
Rebecca.doc')),1000))
SELECT
REVERSE(SUBSTRING(REVERSE('C:\Users\aiacullo\Documents\Resumes\Comp
Analyst-Acctg\Figueroa, Rebecca.doc'),0,PATINDEX('%\%',REVERSE('C:\Users\aiacullo\Documents\Resumes\Comp
Analyst-Acctg\Figueroa, Rebecca.doc'))))
Example:
UPDATE Consultants
SET ResumeDir = REVERSE(SUBSTRING(REVERSE(ResumeFile),PATINDEX('%\%',REVERSE(ResumeFile)),1000)),
ResumeFile = REVERSE(SUBSTRING(REVERSE(ResumeFile),0,PATINDEX('%\%',REVERSE(ResumeFile))))
WHERE
(ResumeFile like
'%:%')
Example1: Remove incomplet data =
SELECT
SUBSTRING('Cromwell,
Arletta, E',1,LEN('Cromwell, Arletta, E')
- PATINDEX('%,%',REVERSE('Cromwell, Arletta, E'))) = O/P Cromwell,
Arletta
19.1 Adding columns in 2000 replication database
sp_repladdcolumn @source_object = 'Users'
, @column = 'AppPermissions'
, @typetext = 'ntext NULL'
, @publication_to_add = 'cBiz_rjt'
19.2 Adding/Deleting the columns in 2005 replication database
sp_repladdcolumn
[ @source_object = ] 'source_object', [ @column = ] 'column'
]
[ , [ @typetext = ] 'typetext'
]
[ , [ @publication_to_add = ] 'publication_to_add'
]
[ , [ @from_agent = ] from_agent ]
[ , [ @schema_change_script = ] 'schema_change_script'
]
[ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]
[ , [ @force_reinit_subscription = ] force_reinit_subscription ]
exec
sp_repladdcolumn
'Consultants'
,
'uuData5',
'BIT NOT NULL DEFAULT ((0))',
'all',
null,
0,
0,
0
exec
sp_repldropcolumn
'Consultants', 'uuData5
Note: DDL commands can only be executed at the root Publisher, not at any
of the republishing Subscribers.
This procedure should not be used on columns with data types that were introduced
in SQL Server 2005 or SQL Server 2008.
20. IF LookUp values are Not Deleting, Then Execute the below Query
on the Database
DELETE Lookup
WHERE
(((LookupFieldName+
DisplayOrder+LTRIM(RTRIM(LookupValue
)))
In (
SELECT LookupFieldName
+DisplayOrder+LTRIM(RTRIM(LookupValue))
FROM
Lookup As Tmp
GROUP BY
LookupFieldName+DisplayOrder+LTRIM(RTRIM(LookupValue
)) HAVING Count
(*)
> 1
)))
AND (LookupValue
LIKE ' %')
GO
UPDATE Lookup
SET LookupValue
= LTRIM(RTRIM(
LookupValue)) WHERE (
LookupValue
LIKE ' %')
GO
21. Find the duplicate Email1 and order by DESC
SELECT Email1
FROM Consultants
GROUP BY
Email1
HAVING COUNT
(*) >
1
GO
ORDER BY CAST(SUBSTRING(RemIntID, 4, 10) AS INT)
DESC
GO
21.1. No Primary Key in Reminders and Duplicate RemintIntID - Delete
Duplicate Records
1.
Create a Column VishnuID
and set it as Identity Column and Mark as Primary Key.
ALTER
TABLE [dbo].[Reminders] ADD
VishnuID int not
null identity(1,1)
primary key
DELETE Reminders
WHERE VishnuID IN
(
SELECT VishnuID
FROM
(
SELECT
TOP (200)
max(VishnuID) as VishnuID,
RemIntID, ItemIntID,
CreateDate, EditDate
FROM
Reminders
WHERE (RemIntID
IN
(SELECT RemIntID
FROM Reminders AS
Reminders_1
GROUP
BY RemIntID
HAVING
(COUNT(*) > 1)))
GROUP
BY RemIntID, ItemIntID,
CreateDate, EditDate
) TEMP
)
21.2. Easy way of DELETING complex query (RAM)
WITH cte as
(
SELECT
ROW_NUMBER() OVER(Partition
By ApptType,ApptSubType,
StartDateTime, EndDateTime,ItemIntID,Subject,UserIDs
ORDER BY CreateDate
ASC)
as Dup,Reminders.RemIntID,Reminders.ApptType,Reminders.ApptSubType,Reminders.StartDateTime,Reminders.EndDateTime,Reminders.ItemIntID,dbo.XCBO_GetName(Reminders.ItemIntID) as
Name,Reminders.Subject,dbo.XCBO_TrimDelimiter(Reminders.UserIDs) as UserIDs,
Reminders.CreateDate
FROM Reminders
WHERE (Reminders.RemType='Appointment') AND
(Reminders.StartDateTime
< DateAdd(d,1,GETDATE())
OR IsDate(Reminders.StartDateTime)=0)
AND
((3=3)) AND
((Reminders.UserIDs
LIKE '%,jjinright,%'))
AND Reminders.Done=0 AND LEN(ApptSubType) > 0
)
SELECT
* FROM cte
WHERE Dup
= 1
22. when we update the date field from out side the cBiz, then convert
it to Small Date Time and Update it.
CAST(
GETDATE()
AS
SMALLDATETIME)
22.1. Getting Date From DateTime.
SELECT
DATEADD
(dd,
0,
DATEDIFF(
dd, 0
,
GETDATE()))
22.2. Delete the CnadidateFetch setting from the database.
DELETE
Documents
WHERE ItemIntID =
'JC00000003'
AND DocType =
'Config' AND
DocSubType =
'JCConfig' AND DocName = 'cf_settings.xml'
23. cBizOne Date Stamp.
SELECT '*** ' + ISNULL('Admin', '') + ' *** '
+ CONVERT(NVARCHAR(20),GETDATE(),100) + CHAR(13) + CHAR(10)
24. RaisError from Reminders Table.
CREATE
TRIGGER
[dbo].[CBO_Reminders]
ON [dbo]
.[Reminders]
FOR
update
AS
BEGIN
RAISERROR
('HI FROM REMINDERS'
, 16,
1)
END
24.1. Not Include the custom trigger for replication.
CREATE
TRIGGER
[dbo].[Trig_RecruiterAssigned]
ON [cBizOne]
.[dbo].[Requirements] FOR INSERT
NOT FOR REPLICATION
AS ........
25. Unable to delete Maintenance plane and Job.
SELECT * FROM
sysmaintplan_subplans
SELECT * FROM
[msdb].[dbo].[sysmaintplan_log]
WHERE subplan_id
=
'FBD49A45-8A46-4932-9A4B-AAB25BDBFB11'
DELETE [msdb].[dbo].[sysmaintplan_log]
WHERE subplan_id
= 'FBD49A45-8A46-4932-9A4B-AAB25BDBFB11'
DELETE [msdb].[dbo].[sysmaintplan_subplans]
WHERE subplan_id
= 'FBD49A45-8A46-4932-9A4B-AAB25BDBFB11'
26. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
|
SET @sql1 = 'ASDASDASDASD.....'
You have declared this as nvarchar(max) which allows 2GB of data so it will store
2GB.
What is happening:
- The datatype is not yet nvarchar(max) until assignment to @sql1
- Before that, it's a collection of strings, each less than 4000 (constants)
- You are concatenating short constants with short variables (short = < 4000)
- So you have 4000 characters put into @sql1
So, you have make sure you have nvarchar(max) on the right hand side.
One idea. The 2nd line concatenates nvarchar(max) with a constant = nvarchar(max)
SET @SQL1 = ''
SET @SQL1 =
@SQL1 + 'SELECT DISTINCT Venue... ....
It's no different to the integer division that happens in every language.
HELP:
http://stackoverflow.com/questions/1371383/for-nvarcharmax-i-am-only-getting-4000-characters-in-tsql
|
27. Date Diff (dd:hh:mm:ss)
(trim(convert(char(10),
(DATEDIFF(
SECOND,
Requirements.CreateDate
,MLinkInfo.DateTime)
/ 86400))) +
':'
+ right('0'
+ trim(convert(char(2),
((DATEDIFF(
SECOND,
Requirements.CreateDate
,MLinkInfo.DateTime)
% 86400) /
3600))), 2
)
+ ':'
+
right('0'
+ trim
(convert
(char
(2),
(((
DATEDIFF(
SECOND,
Requirements.CreateDate
,MLinkInfo.DateTime)
% 86400) %
3600)
/ 60) %
60)), 2
)
+ ':'
+
right('0'
+ trim
(convert
(char
(2),
(((
DATEDIFF(
SECOND,
Requirements.CreateDate
,MLinkInfo.DateTime)
% 86400) %
3600)
% 60) %
60)),2
))
AS DDIFF
28. IMP IMP IMP Identity Column with
Select statement and Building the Temp table with Select statement
SELECT *,
IDENTITY( int )
AS idcol INTO #newtable
FROM Consultants
SELECT *
FROM #newtable
OR
CREATE TABLE #OpenReq(RowNumber INT IDENTITY, ReqIntID_T NVARCHAR(10))
INSERT INTO #OpenReq(ReqIntID_T)
SELECT Requirements.ReqIntID
FROM Requirements
WHERE Requirements.Archived = 0
AND
(Requirements.ClosedDate >
GETDATE() OR
IsDate(Requirements.ClosedDate)=0)
SELECT *
FROM #OpenReq
DROP TABLE #OpenReq
29. When Use Top Use Order By
(Delete Duplicate Candidates based on Email1.)
USE
[1Demodatabase]
GO
-- Keep Old Record and Delete all Other Duplicate.
WITH
DuplicateRows_Table
AS
(
SELECT
EMail1 AS Duplicate_Row_ID
FROM
dbo.Consultants
WHERE
(LEN(ISNULL(EMail1, ''))
> 0)
GROUP
BY EMail1
HAVING
COUNT(1) > 1
),
RowsToDelete_Table
AS
(
SELECT ROW_NUMBER() OVER(Partition By
DRT.Duplicate_Row_ID ORDER
BY Temp.CreateDate
ASC)
AS 'RowNumber', Temp.ConsIntID, Temp.CreateDate
FROM Consultants Temp,
DuplicateRows_Table DRT
WHERE (Temp.EMail1
= DRT.Duplicate_Row_ID)
)
SELECT
*
--DELETE TOP (10)
FROM
Consultants
WHERE
ConsIntID IN
(
SELECT ConsIntID
FROM RowsToDelete_Table
WHERE (RowNumber
!= 1)
)
29.1. DELETE Duplicate LookUp entries
(Keep first one duplicate entry and delete other)
WITH
DuplicateRows_Table
AS
(
SELECT
LookupFieldName +DisplayOrder+LTRIM(RTRIM(LookupValue))
as ID
FROM
Lookup As Tmp
GROUP
BY LookupFieldName+DisplayOrder+LTRIM(RTRIM(LookupValue
))
HAVING
Count (*) > 1
), RowsToDelete_Table
AS
(
SELECT ROW_NUMBER() OVER(Partition By LookupFieldName
+DisplayOrder+LTRIM(RTRIM(LookupValue))
ORDER By LookupFieldName) AS
'RowNumber',
LookupFieldName + DisplayOrder+LTRIM(RTRIM(LookupValue))
as ID
FROM Lookup
WHERE
(LookupFieldName +DisplayOrder+LTRIM(RTRIM(LookupValue))) IN
(SELECT
ID FROM DuplicateRows_Table)
)
DELETE
RowsToDelete_Table
WHERE
RowNumber != 1
30. To Know the number of Triggers on the Databaseebasee
(DB All Triggers)
SELECT
s2.
[name] tablename,
s1.[name] triggername
,
CASE
WHEN s2
.deltrig
= s1.
id
THEN
'Delete'
WHEN s2.instrig
= s1.
id
THEN
'Insert'
WHEN s2.updtrig
= s1.
id
THEN
'Update'
END 'TriggerType', 'S1',
s1.*,
'S2',
s2.*
FROM sysobjects
s1 JOIN
sysobjects s2
ON s1.
parent_obj = s2
.[id]
WHERE s1.
xtype =
'TR'
OR
SELECT
* FROM
sys./span>triggers
WHERE is_disabled =
0
30.1. Run following query to find the jobs which are currently running.
exec
msdb..sp_get_composite_job_info
@execution_status=1
30.2. Currently Jobs on a Database.
SELECT
database_name, name as
JobName
FROM
msdb.dbo.sysjobs
job INNER JOIN
msdb.dbo.sysjobsteps
steps
ON
job.job_id = steps.job_id
WHERE
job.enabled = 1 AND
database_name = 'Exl_VishnuTest1'
ORDER
BY database_name
31. COALESCEE
DECLARE
@Values VARCHAR(1000)
SELECT @Values
= COALESCE(@Values
+ ', ',
'') + Consultants.DisplayName
FROM Consultants
SELECT
@Values
--RESULT: A AhamedHajaShareef, A Arun, A Asharaf Ali, A B Saravanan, A Babu
32. Change the DataBase Name and Logical Name
ALTER
DATABASE cBizOne1 MODIFY
NAME = cBizOne
GO
ALTER
DATABASE
[cBizOne] MODIFY
FILE (
NAME=N'cBizOne1'
, NEWNAME
=N'cBizOne_Data'
)
GO
ALTER
DATABASE [cBizOne] MODIFY
FILE (
NAME=N'cBizOne1_Log'
,
NEWNAME=N'cBizOne_Log'
)
GO
32. Free up memory for SQL Server by cleaning up its
cache
DBCC
FREESESSIONCACHE
DBCC FREEPROCCACHE
33. Time consuming query (you can control this query
with Companies.UserField input as ‘YES’)
SELECT
COUNT(*)
FROM
(
SELECT DISTINCT
(ReqIntID+CompanyIntID+
ConsIntID+RemIntID
+MLinkIntID)
AS ID
FROM dbo.Companies,dbo
.Consultants,dbo.
Reminders,dbo
.MLinkInfo,dbo.
Requirements
WHERE Companies.
UserField1 =
'YES'
) T
WHERE ID LIKE '%VISHNU%'
34. Important Logic Please Go Through (Rows
to Columns)
SELECT
Month1,
ISNULL(SUM(case
when What='Sourced' then XCount
end),0) as Sourced,
ISNULL(SUM(case
when What='InitialInterviews' then XCount
end),0) as InitialInterviews,
ISNULL(SUM(case
when What='AdvancedInterviews' then XCount
end),0) as AdvancedInterviews,
ISNULL(SUM(case
when What='Submitted' then XCount
end),0) as Submitted
FROM
(
SELECT (Datename(MONTH, Consultants.CreateDate
)
+ Datename
(YEAR
, Consultants.CreateDate))
AS Month1
,
COUNT(*) AS XCount,
max('Sourced'
)
as What
....
)
TempTable
GROUP BY Month1
34.1. Important Logic Please Go Through (Rows
to Columns for StringData)
SELECT
MAX(CASE WHEN
ColumnName
=
'_1'
THEN
Data
END
)
_1,
MAX(CASE WHEN
ColumnName
=
'_2'
THEN
Data
END
)
_2
FROM
(
SELECT 'First' as
Data
,
'_1' as ColumnName
UNION ALL
SELECT 'Second' as
Data
,
'_2' as ColumnName
)TempTable
35. DISABLE/ENABLE ALL TRIGGER IN A DATABASE
Example1:
-- DISABLE ALL TRIGGER IN A DATABASE
DECLARE @SQLMessage NVARCHAR(MAX)
SET @SQLMessage = ''
SELECT @SQLMessage = @SQLMessage + 'DISABLE TRIGGER ALL ON ' + TABLE_SCHEMA + '.'
+ TABLE_NAME + ';' + CHAR(10) + CHAR(13) from INFORMATION_SCHEMA.TABLES
EXEC(@SQLMessage)
-- ENABLE ALL TRIGGER IN A DATABASE
DECLARE @SQLMessage NVARCHAR(MAX)
SET @SQLMessage = ''
SELECT @SQLMessage = @SQLMessage + 'ENABLE TRIGGER ALL ON ' + TABLE_SCHEMA
+ '.' + TABLE_NAME + ';' + CHAR(10) + CHAR(13) from INFORMATION_SCHEMA.TABLES
EXEC(@SQLMessage)
Example2:
-- DISABLE/ENABLE ALL TRIGGER ON One Table
DISABLE TRIGGER ALL ON dbo.Reminders;
ENABLE TRIGGER ALL ON dbo.Reminders;
Example3:
-- DISABLE/ENABLE ALL TRIGGER ON Database At Once
EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"
EXEC sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER ALL"
35.1. To copy customizations from one database to another(Same Server)
-- Deleting Customization and Stylesheets
DELETE [exl_geckosmith].[dbo].[Documents]
WHERE ItemIntID = 'CF00000000'
DELETE [exl_geckosmith].[dbo].[Documents]
WHERE ItemIntID = 'CF00000001'
--- Customize.zip
INSERT INTO [exl_geckosmith].[dbo].[Documents] (ItemIntID, DocType, DocSubType,
DocName, DocContent, DocText, UserIDs, Private, CreateDate, EditDate, MergeDate,
Field1, Field2, Field3)
SELECT ItemIntID, DocType, DocSubType, DocName, DocContent, DocText, UserIDs, Private,
CreateDate, EditDate, MergeDate, Field1, Field2, Field3
FROM [exl_geckoscott].[dbo].[Documents]
WHERE ItemIntID = 'CF00000000'
--- Stylesheets.zip
INSERT INTO [exl_geckosmith].[dbo].[Documents] (ItemIntID, DocType, DocSubType,
DocName, DocContent, DocText, UserIDs, Private, CreateDate, EditDate, MergeDate,
Field1, Field2, Field3)
SELECT ItemIntID, DocType, DocSubType, DocName, DocContent, DocText, UserIDs, Private,
CreateDate, EditDate, MergeDate, Field1, Field2, Field3
FROM [exl_geckoscott].[dbo].[Documents]
WHERE ItemIntID = 'CF00000001'
35.1.1 To copy customizations from one database to another(Auto Create Table)(Kris
Ask this)
SELECT * INTO Documents_Temp FROM
Documents WHERE ItemIntID
LIKE 'CF%'
Now Generate Script and Select Data Only and QueryWindow
Replace the Documents_Temp to Documents in the Query and Execute at target
Database.
35.1.2 Build Temp Table form SELECT Command(Transwar data from one server to another server)(Kris
gives this work)
SELECT 'SELECT
* INTO #VishnuTest1 FROM ('
UNION
ALL
SELECT
'SELECT ''' +
CAST(UserField10
AS NVARCHAR(MAX)) +
''' as UserField10, '''
+ UserIDs + '''
as UserIDs, ''' +
CAST(Child_Person_Number
AS NVARCHAR(MAX)) +
''' as Child_Person_Number, '''
+ CandFirstName +
''' as CandFirstName, '''
+ CandLastName +
''' as CandLastName, '''
+ RecruiterFirstName + ''' as RecruiterFirstName, ''' + RecruiterLastName + ''' as RecruiterLastName UNION ALL '
FROM
CandidateUserIDs
UNION
ALL
SELECT
') Temp'
35.2. Issues with Recurring annual events
1. Delete all the existing annual events
DELETE Reminders
WHERE SUBJECT like
'Date of Birth%' OR RemField = 'DOB'
2. Now once again go to cBizOne Tools – Options – Calendar unset and save and close
cBizOne and reset the Annual/Onetime Events (Since this Events uses computer regestry
to create them).
OR
1. Keep Old Record and Delete all Other Duplicate
WITH
DuplicateRows_Table
AS
(
SELECT ItemIntID
+
UserIDs as Duplicate_Row_ID
FROM Reminders
WHERE
(SUBJECT like
'Date of Birth%' OR
RemField = 'DOB') AND Done
= 0
GROUP
BY
ItemIntID + UserIDs
HAVING
(COUNT(*)
> 1)
), RowsToDelete_Table
AS
(
SELECT
ROW_NUMBER()
OVER(Partition By DRT.Duplicate_Row_ID ORDER
BY Temp.CreateDate
ASC) AS
'RowNumber', DRT.Duplicate_Row_ID,
Temp.RemField,Temp.RemIntID, Temp.CreateDate
FROM Reminders
Temp, DuplicateRows_Table DRT
WHERE
(Temp.ItemIntID
+ Temp.UserIDs
= DRT.Duplicate_Row_ID) AND
(Temp.SUBJECT
like 'Date of Birth%'
OR Temp.RemField
= 'DOB') AND Temp.Done = 0
)
SELECT *
--DELETE TOP (10)
FROM Reminders
WHERE RemIntID
IN
(
SELECT RemIntID
FROM
RowsToDelete_Table
WHERE
(RowNumber != 1)
)
35.3. Complete the Old Reminders (Done = 1)
select
distinct(datepart(year,RemDateTime)),count(*)
from Reminders
where Remtype =
'Appointment'
and Done
= 0
group by datepart(
year,
RemDateTime)
Go
-- First Test the above and give the appropriate old year in the
-- below code and
execute
update Reminders set Done = 1,
RemAlarm = 0
where Remtype = 'Appointment'
and Done
= 0 and
datepart(
year,
RemDateTime)
= '1800' --<---
Give the correct value here
35.3.1 On ‘My Daily Call/Task List’ Complete all items on or before 10/26/15
SELECT
RemType, ApptType,
Done, RemAlarm,
StartDateTime, RemDateTime,
UserIDs
FROM
Reminders
WHERE (Reminders.RemType =
'Appointment')
AND
(Reminders.ApptType='Call'
OR Reminders.ApptType='Task')
AND
(Done
= 0)
AND (StartDateTime
< '2015-10-26') AND
(UserIDs =
',Keith Fall,')
UPDATE
Reminders SET Done =
1, RemAlarm =
0
WHERE (Reminders.RemType =
'Appointment')
AND
(Reminders.ApptType='Call'
OR Reminders.ApptType='Task')
AND
(Done
= 0)
AND (StartDateTime
< '2015-10-26') AND
(UserIDs =
',Keith Fall,')
35.4. Update Companies WebPage from Contact WebPage
UPDATE
Companies SET Companies.WebPage
= Contacts.WebPage
FROM Contacts,
Links
WHERE (Companies
.CompanyIntID
= Contacts.LinkToIntID) AND
(LEN(ISNULL(Companies.
WebPage,''))=0
) AND
(LEN(
Contacts.WebPage
)>0) AND
(LEN
(Contacts.LinkToIntID)>
0)
AND
(Links.FromID
= Contacts.ContactIntID AND Links.
ToID = Contacts
.LinkToIntID
AND Links.Type = 1
AND Links.SubType
= 1)
36. (TSQL) Escape Underscore in Like (IMP)
SELECT * FROM Table WHERE Column LIKE '_a_'
The above code will generate results that has any one character before and after
'a' such as:
mac
cat
sad
SELECT * FROM Table WHERE Column LIKE '%[_]a[_]%'
The above code will generate results such as:
in_a_house
she_is_a_mother
make_a_living
36.1 (TSQL) Escape Underscore in Like (IMP)
DECLARE
@command nvarchar
(2000)
SELECT @command =
'use [?]
IF(''?'' like ''cbiz[_]%'')
BEGIN
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''Users''
AND COLUMN_NAME = ''AppPermissions'') = 0
SELECT ''?''
END '
EXEC
sp_MSforeachdb
@command
37. LookUp Value for a field in a Dynamic View (Create
a Candidate with display name (Consultants.PrimarySkills))
<View>
<ID>uuLookup</ID>
<Caption>Lookup</Caption>
<SQL>SELECT Consultants.ConsIntID,Consultants.DisplayName,Lookup.LookupValue,Lookup.LookupDesc
FROM Consultants,Lookup WHERE (Consultants.DisplayName = Lookup.LookupFieldName)
AND (Consultants.Private=False OR Consultants.UserIDs LIKE [LoggedInUser])</SQL>
<Properties>54269</Properties>
</View>
38. Format MONEY DataType (Remove .00)
DECLARE
@MONEY MONEY
SET
@MONEY = 222
SELECT
@MONEY
-- OUT PUT 222.00
SELECT
REPLACE(CONVERT(VARCHAR(20), @MONEY, 1),'.00','')
-- OUT PUT 222
39. Make Mandatory field in cBizOne
<Usage>JobTitle,,,,?*,"E: Please enter Job Title.",C0C0FF</Usage>
40. cBizOne Command Arguments
cBizOne /d "c:\Program Files\cBiz\cBizData" /db DSN=cBizOne;UID=cbiz_test;PWD=cbiz_test;
/lod yes /f Contacts /hosteddb no /u username /p password
41. Find the Existing SiteID
SELECT
DISTINCT(SiteID) AS SiteID
FROM
(
SELECT DISTINCT(SUBSTRING(ConsIntID,3,1))
AS SiteID
FROM dbo.Consultants
UNION
ALL
SELECT DISTINCT(SUBSTRING(CompanyIntID,3,1))
AS SiteID
FROM dbo.Companies
UNION ALL
SELECT DISTINCT(SUBSTRING(ContactIntID,3,1))
AS SiteID
FROM dbo.Contacts
UNION ALL
SELECT DISTINCT(SUBSTRING(RemIntID,3,1))
AS SiteID
FROM dbo.Reminders
UNION ALL
SELECT DISTINCT(SUBSTRING(ReqIntID,3,1))
AS SiteID
FROM dbo.Requirements
UNION ALL
SELECT DISTINCT(SUBSTRING(FROMID,3,1))
AS SiteID
FROM dbo.links
UNION ALL
SELECT DISTINCT(SUBSTRING(TOID,3,1))
AS SiteID
FROM dbo.links
) TEMP
42. You can access the web2.0 Exelare app by doing
the following:
1. http://cbiz-srv3.network80.com/Exelare
2. Enter the following details:
CompanyID: cbiz_test2
cBiz username:Test
Password:test
Note: Also, you can have both cBizOne and Exelare working simultaneously.
Any changes made in Exelare will be reflected in cBizOne and vice-versa.
43. How To Obtain The Size Of All Tables In A SQL Server
Database
(DB
All Row Count)
--Reports and corrects pages and row count inaccuracies in the catalog views.
--These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused
system stored procedure.
--DBCC UPDATEUSAGE('Exl_VishnuTest1', 'dbo.Documents') WITH COUNT_ROWS ;
SET NOCOUNT
ON
DBCC UPDATEUSAGE(0)
-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
CREATE
TABLE #t
(
[name] NVARCHAR(128),
[rows]
CHAR(11),
reserved VARCHAR(18),
data
VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT *
FROM #t
Order by Name
-- # of rows.
SELECT
SUM(CAST([rows]
AS
int)) AS [rows]
FROM #t
DROP
TABLE #t
43.-0. Get Each Column size in MD and GB.
-- Specify the table name
DECLARE @TableName NVARCHAR(128) = 'Consultants';
DECLARE @ColumnName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName;
OPEN column_cursor;
FETCH NEXT FROM column_cursor INTO @ColumnName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = @SQL +
'SELECT ''' + @ColumnName + ''' AS ColumnName, ' +
' SUM(CAST(DATALENGTH([' + @ColumnName + ']) AS BIGINT)) / 1048576.0 AS Size_MB ' +
'FROM ' + @TableName + ' UNION ALL ';
FETCH NEXT FROM column_cursor INTO @ColumnName;
END
CLOSE column_cursor;
DEALLOCATE column_cursor;
-- Remove the trailing "UNION ALL" from the SQL
SET @SQL = LEFT(@SQL, LEN(@SQL) - LEN(' UNION ALL '));
--print @SQL
CREATE TABLE #All_ColumnSize
(
ColumnName NVARCHAR(128),
Size_MB REAL
)
-- Execute the dynamic SQL
INSERT #All_ColumnSize EXEC sp_executesql @SQL;
SELECT ColumnName, Size_MB, Size_MB/1024 AS Size_GB
FROM
(
SELECT ColumnName, CONVERT(decimal(18, 1), ROUND(ISNULL(Size_MB, '0'), 1)) AS Size_MB FROM #All_ColumnSize
) TT
WHERE Size_MB > 0
ORDER BY Size_MB DESC
DROP TABLE #All_ColumnSize
43.-1. Export Database Check All Table Count (Good
use of Exec() command).
SET
QUOTED_IDENTIFIER OFF
DECLARE @SQL_Message
NVARCHAR(MAX),
@OriginalDatabase NVARCHAR(100),
@ExportDatabase NVARCHAR(100)
SET
@OriginalDatabase
=
'[cBiz_redseal]'
SET
@ExportDatabase =
'[cBiz_redseal_Export]'
SET
@SQL_Message = "
SELECT '
SELECT ''' + name + ''' as TableName,
(
SELECT COUNT(1) FROM "
+ @OriginalDatabase + ".dbo.'
+ name + '
) as " +
@OriginalDatabase + ",
(SELECT COUNT(1) FROM "
+
@ExportDatabase + ".dbo.'
+ name + '
) as " + @ExportDatabase
+
",
ISNULL(
CAST((
SELECT ''True''
WHERE ( (SELECT COUNT(1) FROM " + @OriginalDatabase + ".dbo.'
+ name + ') =
(SELECT COUNT(1) FROM "
+ @ExportDatabase + ".dbo.'
+ name + ')
)
) AS CHAR(5))
,''FALSE'') as Result'
FROM " + @OriginalDatabase
+
".dbo.sysobjects
WHERE xtype='U'
UNION ALL
SELECT '
SELECT ''' + name + ''' as TableName,
0 as " + @OriginalDatabase
+ ",
(
SELECT COUNT(1)
FROM " + @ExportDatabase
+ ".dbo.' + name
+ '
) as " + @ExportDatabase
+ ", ''New'' as Result'
FROM " + @ExportDatabase
+ ".dbo.sysobjects
WHERE xtype='U' AND (name IN ('ContactInfo','RMLinks'))
"
--print @SQL_Message
CREATE TABLE #WorkingData(
col1
NVARCHAR(MAX
))
INSERT INTO #WorkingData
EXEC(@SQL_Message
)
SELECT @SQL_Message =
STUFF(
(SELECT ' UNION ALL '
+
[dbo].[CBO_RemoveControlCharacters_Which_Are_Not_Allowed_In_XML](Col1)
FROM #WorkingData
ORDER
BY col1 FOR
XML
PATH
(''), TYPE).value('.',
'NVARCHAR(MAX)')
,1,
11,
'')
CREATE TABLE #ResultData(TableName
NVARCHAR(250),
cBiz_redseal BIGINT,
cBiz_redseal_Export BIGINT,
Result NVARCHAR(10))
INSERT INTO #ResultData
EXEC(@SQL_Message)
SELECT * FROM
#ResultData WHERE (Result
!=
'New'
)
UNION ALL
SELECT 'Total: ',
SUM
(cBiz_redseal),
SUM(cBiz_redseal_Export
),
'' FROM #ResultData
WHERE
(Result
!= 'New')
UNION ALL
SELECT *
FROM #ResultData WHERE
(Result
= 'New')
DROP TABLE #WorkingData
DROP TABLE
#ResultData
SET
QUOTED_IDENTIFIER ON
43.0. Find Database .mdf and .ldf Size with file physical
location.
SELECT
DB_NAME(
database_id)
AS DatabaseName,Name
AS
Logical_Name
,Physical_Name,
(size*
8)/
1024
SizeMB
FROM
sys.master_files
WHERE DB_NAME(
database_id)
= 'Exl_VishnuTest1'
43.1. Query 1 can be used to find out count of memory
pages loaded for each database.
SELECT
COUNT(*)AS cached_pages_count
,CASE database_id
WHEN 32767
THEN
'ResourceDb'
ELSE db_name(database_id)
END
AS database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id)
,database_id
ORDER BY cached_pages_count DESC;
43.2. How To Pass the EXEC value to Variable (IMP IMP)
DECLARE
@Rank INT
CREATE
TABLE #Value
( Column1 INT
)
INSERT
#Value EXEC('SELECT 3')
SELECT
@Rank =
Column1 FROM #Value
SELECT
@Rank
DROP
TABLE
#Value
44. cBizOne Dashboard with Groups as UsetID's
Give the following in the EntitiesCustom:
<AddTab Caption="Custom Dashboard" URL="customdashboard.htm"
Groups="Departments:Sales"/>
45. Remove the Space before and after from an Email1
--Consultants
UPDATE Consultants SET
EMail1 =
LTRIM(RTRIM(EMail1))
WHERE (LEN
(EMail1)>0)
AND
((EMail1 LIKE
' %') OR (
EMail1
LIKE '% '))
GO
--Contacts
UPDATE Contacts SET
EMail1 =
LTRIM(RTRIM(EMail1))
WHERE (LEN
(EMail1)>0)
AND
((EMail1 LIKE ' %')
OR
(EMail1
LIKE '% '))
GO
46. One More Contact to Requirement
ALTER
TABLE Requirements ADD
uuContact NVARCHAR(
60)
Add the below text in RequirementsTLA.xml:
<LinkToID>
<ID>RQCN1002</ID>
<Link>Requirement</Link>
<To>Contact</To>
<Type>1002</Type>
<SubType>1</SubType>
<MenuCaption>Link [[Link]] to Selected [[To]] (Source Name)</MenuCaption>
<FormCaption>Link [[SelectedName]] to a [[To]] (Source Name)</FormCaption>
<Description>Description</Description>
<SearchFields>DisplayName,CompanyName,EMail1</SearchFields>
<ViewSQL>SELECT ContactIntID,DisplayName,CompanyName,EMail1 FROM Contacts
WHERE Archived=False AND (Private=False OR UserIDs LIKE [LoggedInUser])
ORDER BY DisplayName,CompanyName
</ViewSQL>
<LinkedItemDisplayFields>DisplayName,CompanyName</LinkedItemDisplayFields>
</LinkToID>
Add the below text in RequirementsFields.xml:
<XField><ID>uuContact</ID>
<Properties>292733</Properties>
<Caption>Contact:</Caption>
<ListCaption>Contact</ListCaption>
<DefaultValue></DefaultValue>
<Usage>ContactName,LinkToIDRQCN1002</Usage>
NOTE:
1. This New contact will create a link in Links Table only.
2. We have to adjust the xTabs and Reports code by using Type = 1002 and SubType
= 1 or else it will reflect the submissions twice as we have 2 contact to a requirement
3. This feature will remove the "Merge Duplication" option.
46.1. Only Caption on the cBizForm
<XField>
<ID>uuFieldServices</ID>
<Properties>893</Properties>
<Control> <ID>uuFieldServices</ID>
<CType>TextBox</CType>
<TabNumber>6</TabNumber>
<Left>600</Left>
<Top>250</Top>
<Width>4255</Width>
<Height>195</Height>
<Locked>True</Locked>
<BackColor>-2147483633</BackColor>
<MultiLine>False</MultiLine>
<ScrollBars>0</ScrollBars>
<BorderStyle>0</BorderStyle>
<FontBold>True</FontBold>
<Text>Field Services:</Text>
</Control>
</XField>
46.2. Trigger Is Not Firing For Bulk Records Update
(IMP)
http://www.dbforums.com/microsoft-sql-server/857888-triggers-dont-fire-mass-deletes-updates.html
Most people don't write triggers to handle multi record updates. A trigger acts
on a batch which could contain one or more records.
Note: If you are using any update command then don't use any variables in it.
Eg:
IF @isPrimary = 1
BEGIN
UPDATE ContactInfo
SET isPrimary = 0
WHERE ContactID = @ContactID
AND ContactInfoID <> @ContactInfoID
END
Update ContactInfo
set ContactInfo.isPrimary = 0
from ContactInfo
join insrted on ContactInfo.ContactID = inserted.ContactID
where ContactInfo.ContactInfoID <> inserted.ContactInfoID
and inserted.isPrimary
= 1
47. "Today", "This Week", "This Month" Date and time
at 00:00:00
-- Today
select
DATEADD(d,
DATEDIFF(d,
0,
GETDATE()), 0
)
select DATEADD
(d,
DATEDIFF(d,
0,
GETDATE()),
1)
--This Week
SELECT DATEADD(WK, (DATEDIFF(DAY, 0, GETDATE())/7), 0)
SELECT DATEADD(WK, (DATEDIFF(DAY, 0, GETDATE())/7)+1, 0)
--This Month
select
DATEADD(month,
DATEDIFF(month
, 0,
GETDATE()), 0)
select DATEADD
(month
, 1,
DATEADD(month, DATEDIFF
(month
, 0,
GETDATE()),
0))
OR
--This Month
select
DATEADD(month,
DATEDIFF(month, 0, GETDATE()), 0)
select DATEADD(SECOND, -1, DATEADD(month, 1, DATEADD(month, DATEDIFF(month,
0, GETDATE()), 0)))
48. T-SQL: Joins Explained
-- INNER JOIN (Just matches)
-- so total rows will be number of matched records
-- LEFT JOIN (All records from
left table, NULLs in the right table if no match)
-- so total rows will be number of records in the left table (tblA)
-- RIGHT JOIN (All records
from the right table, NULLs in the left table if no match)
-- so total rows will be number of records in the right table (tblB)
-- FULL JOIN (all records from
both tables with matches or not)
-- so total rows will be number of rows in left table + number of rows in right
table - number of exact matches)
-- CROSS JOIN (cartesian product
of both tables)
-- total rows of left table * right table
Help Link
48.0.
Excellent Query Optimization
Through having JOINS in the FROM block
We can Improve the Query Performance by having Joins in the from Block.
Ram Hint: From Block will Execute first.
Below Query Performance is Ammazing, I used the help of above Image and developed
the Query.
Example:
SELECT Requirements.ReqIntID,Requirements.ReqID
as Requirement,Requirements.JobTitle,Requirements.Location,
Contacts.ContactIntID,Contacts.DisplayName
as Contact,Contacts.LinkToIntID,Contacts.CompanyName as
Company,
''
as ConsIntID,'' as Consultant,'No'
as Type,'' as SubType,
Requirements.CreateDate
AS DateAndTime,Requirements.UserIDs
FROM Requirements LEFT JOIN Contacts ON Requirements.LinkToIntID=Contacts.ContactIntID
WHERE (Requirements.Archived
= 0)
AND
(Requirements.ClosedDate > GETDATE()
OR IsDate(Requirements.ClosedDate)=0)
AND
((SELECT COUNT(1)
FROM dbo.[XCBO_PermReqsPipeLineDetailed]
P WHERE (P.ReqIntID_T = Requirements.ReqIntID))
= 0)
SELECT Requirements.ReqIntID,Requirements.ReqID
as Requirement,Requirements.JobTitle,Requirements.Location,
Contacts.ContactIntID,Contacts.DisplayName
as Contact,Contacts.LinkToIntID,Contacts.CompanyName as
Company,
''
as ConsIntID,'' as Consultant,'No'
as Type,'' as SubType,
Requirements.CreateDate
AS DateAndTime,Requirements.UserIDs
FROM dbo.[XCBO_PermReqsPipeLineDetailed]
P
RIGHT
JOIN
(Requirements LEFT
JOIN Contacts ON Requirements.LinkToIntID=Contacts.ContactIntID) ON
(P.ReqIntID_T
= Requirements.ReqIntID)
WHERE
(Requirements.Archived
= 0)
AND
(Requirements.ClosedDate > GETDATE()
OR IsDate(Requirements.ClosedDate)=0)
AND
(P.ReqIntID_T
IS
NULL)
Function LInk:
XCBO_PermReqsPipeLineDetailed.sql.
48.1.
Cross Apply and Outer Apply
SQL Server 2005 introduced the Apply operator like a join clause, which allows joining
between two table expressions. The difference between Join and Apply operator becomes
evident when we have a table-valued expression on the right side and want this table-valued
expression to be evaluated for each row from the left table expression.
Example:
DROP TABLE
#newtable
SELECT
* INTO #newtable
FROM
(
SELECT 10
AS
empId, 'AL' AS empState,
'1/1/2012'
AS
empStDate, '12/1/2012' AS empEndDate
UNION
ALL
SELECT 10
AS
empId, 'FL' AS empState,
'2/1/2012'
AS
empStDate,
'2/1/2013'
AS
empEndDate
UNION
ALL
SELECT 15
AS
empId, 'FL' AS empState,
'3/20/2012'
AS
empStDate, '1/1/2099' AS empEndDate
) TT
SELECT
* FROM #newtable
select empid,
col,
value from #newtable
cross
apply
(
select
'empstate', empstate
union all
select
'empstdate',
convert(varchar(10), empstdate, 120)
union all
select
'empenddate',
convert(varchar(10), empenddate, 120)
) c
(col, value)
Help Link
48.2.
Table Value Function with Cross Apply
SELECT
IDENTITY( int
) AS RowNo, A.EntityName, A.StatusName, A.ProcessSinceDate, G.RemReqIntID, G.UserIDs
INTO #AutoScheduleReminders
FROM
#AutoScheduleInfo A Cross
apply dbo.CBO_GetEntityLatestStatus(A.EntityName,A.StatusName,A.ProcessSinceDate) G
49. Concatenate row values T-SQL
Attention:
FOR XML
PATH(''), TYPE).value('.',
'NVARCHAR(MAX)')
- Will Not Work with SET QUOTED_IDENTIFIER OFF
FOR XML
PATH('') - Will Work with SET QUOTED_IDENTIFIER
OFF, but NewLine(CHAR(13) + CHAR(10)) will be generated to strange symbols, Replace it with ('')
Example 1.0:
SELECT
STUFF(
(SELECT ', '
+ [dbo].[CBO_RemoveControlCharacters_Which_Are_Not_Allowed_In_XML](ID)
FROM Users
WHERE RecType = '20'
ORDER
BY Field2 FOR XML
PATH(''), TYPE).value('.',
'NVARCHAR(MAX)')
,1,2,'')
Example 2.0:Replace the New line with Comma and remove
Unnecessary Commas
UPDATE Requirements SET Location =
(
STUFF(
(SELECT ', ' + [dbo].[CBO_RemoveControlCharacters_Which_Are_Not_Allowed_In_XML](Data)
FROM dbo.XCBO_fn_ArrayTable(replace(location,char(13)+char(10),', '),',')
WHERE (LEN(Data)>0)
ORDER BY IDX FOR XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)')
,1,2,'')
)
where datalength(Location)>0 and
(location like '%'+char(13)+char(10)+'%')
Note: By adding ,TYPE you instruct FOR XML PATH to generate the
data with the xml data type. Then you can use the type method value to extract the
value in the desired data type.
Complete Example:
SELECT Requirements.ReqID,Requirements.JobTitle,Requirements.UserField6,PrimarySkills,
Description,Location, UserIDs,
( STUFF((SELECT ', ' + [dbo].[CBO_RemoveControlCharacters_Which_Are_Not_Allowed_In_XML](Field2)
FROM Users WHERE RecType = '20' AND Requirements.UserIDs LIKE '%,'+ID+',%'
ORDER BY Field2 FOR XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
) AS EmailIDs
FROM Requirements
WHERE Requirements.Archived = 0
ORDER BY Requirements.ReqID,Requirements.JobTitle
Example 1:
SELECT
CAST(((SELECT [dbo].[CBO_RemoveControlCharacters_Which_Are_Not_Allowed_In_XML](Subject) +
', '
FROM Reminders
WHERE (RemType='Note' AND ApptSubType='PrePriority') AND
((Reminders.RemIntID)
In (SELECT FromID FROM Links
WHERE ToID='RQ00000019' AND
Type=1 AND SubType
=1))
ORDER
BY Subject FOR
XML PATH(''),
TYPE).value('.',
'NVARCHAR(MAX)'))
AS NVARCHAR(4000))
OutPut:
Client, Partner, Sub Contractor,
Example 2: (Update the QNotes of candidate for othere table with
duplicate EmailID's)
UPDATE
dbo.Consultants SET
Consultants.Notes
=
(
SELECT
CAST((
SELECT ISNULL(E.
[Qnote for cBiz],'')
+
' '
FROM dbo.EmailVishnu E
WHERE (E.EMAIL
= V.EMAIL
)
ORDER BY
E.[Qnote for cBiz]
FOR
XML PATH('')
) AS
NVARCHAR(
MAX))
)+ Char
(13)
+
Char(10) +
Char(13) +
Char(
10)
+
ISNULL(CAST
(Consultants.Notes AS
NVARCHAR(MAX)),
'')
FROM dbo.
EmailVishnu V
where (LTRIM
(RTRIM
(Consultants.EMail1))
=
LTRIM(RTRIM(V.Email)))
49.0.
Use GROUP BY to Concatenate Strings (IMP IMP)
SELECT *
INTO #YourTable
FROM
(
SELECT
'CS00000106' AS
id, 'WE'
AS VALUE
UNION
ALL
SELECT 'CS00000106'
AS id,
'EE' AS VALUE
UNION ALL
SELECT 'CS00000106'
AS id,
'DD' AS VALUE
UNION ALL
SELECT 'CS00000105'
AS id,
'SS' AS VALUE
) T
SELECT Results.ID,
STUFF((
SELECT
', ' +
[dbo].[CBO_RemoveControlCharacters_Which_Are_Not_Allowed_In_XML](T.Value)
FROM
#YourTable T
WHERE
(T.ID = Results.ID)
FOR
XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS
NameValues
FROM #YourTable Results
GROUP
BY Results.ID
OUT
PUT:
ID NameValues
CS00000105 SS
CS00000106 WE,
EE, DD
49.1. Querying XML (IMP IMP)
Sample Example 1:
DECLARE
@XML XML;
SELECT @XML =
'<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>';
SELECT --R.Node.query('.'),
R.Node.query('.').value('(/Customers/CustomerId/.)[1]','varchar(100)') AS
CustomerID,
R.Node.query(
'.').
value('(/Customers/CompanyName/.)[1]',
'varchar(100)')
ASCompanyName
FROM @XML.nodes('/ROOT/Customers') R(Node);
Sample Example
1.1:
DROP TABLE
#AutoScheduleInfo
DECLARE
@XML XML;
SELECT
@XML =
'<SalesAutomation>
<Schedule StatusName="05 - Demo Complete" ReminderDuration="1" StartTime="06:00
AM" EndTime="08:30 AM" AddSubject="test" />
<Schedule StatusName="CompaniesStatus" ReminderDuration="1" StartTime="06:00 AM"
EndTime="08:30 AM" AddSubject="test55313131131" />
<Schedule StatusName="Interview" ReminderDuration="1" StartTime="08:00 AM"
EndTime="08:30 AM" AddSubject="" />
</SalesAutomation>';
SELECT
--R.Node.query('.'),
IDENTITY( int ) AS RowNo,
R.Node.value('@StatusName','varchar(100)') AS StatusName,
R.Node.value('@ReminderDuration', 'varchar(100)') ReminderDuration,
R.Node.value('@StartTime', 'varchar(100)') StartTime,
R.Node.value('@EndTime', 'varchar(100)') EndTime,
R.Node.value('@AddSubject', 'varchar(100)') AddSubject INTO
#AutoScheduleInfo
FROM
@XML.nodes('/SalesAutomation/Schedule') R(Node);
SELECT
* FROM
#AutoScheduleInfo
Sample Example 2:
DECLARE
@XML XML, @UserIDs
NVARCHAR(MAX
)
SET @UserIDs = 'Kris|Admin|Ahmed|Alex|Bob|Bryan|Test'
IF (LEN(@UserIDs) > 0)
SET @XML = '<ROOT><Item>' +
REPLACE(@UserIDs,'|','</Item><Item>')
+ '</Item></ROOT>'
SELECT TempTable.TempColumn.query('.').value('(/Item/.)[1]','NVARCHAR(512)') AS Data
FROM @XML.nodes('/ROOT/Item') TempTable(TempColumn);
OutPut:
Kris
Admin
Ahmed
Alex
Bob
Bryan
Test
49.2 Delete and Update XML (IMP IMP)
Sample Example 1:
DECLARE
@XML XML
SET
@XML =
'<SalesAutomation>
<Schedule EntityName="Contacts" StatusName="05 - Demo Complete" ScheduleAfterDays="1" ProcessSinceDate="11/06/1990" StartTime="08:00 AM" EndTime="08:30 AM" AddSubject=""
OnceProcessed="No" />
<Schedule EntityName="Contacts" StatusName="Interview" ScheduleAfterDays="1" ProcessSinceDate="11/06/1990" StartTime="08:00 AM" EndTime="08:30 AM" AddSubject="" OnceProcessed="No" />
<Schedule EntityName="Contacts" StatusName="Placed" ScheduleAfterDays="2" ProcessSinceDate="11/06/1990" StartTime="08:00 AM" EndTime="08:30 AM" AddSubject="" OnceProcessed="No" />
<Schedule EntityName="Consultants" StatusName="Placed" ScheduleAfterDays="2" ProcessSinceDate="11/06/1990" StartTime="08:00 AM" EndTime="08:30 AM" AddSubject="" OnceProcessed="No" />
<Schedule EntityName="Companies" StatusName="Test1" ScheduleAfterDays="2" ProcessSinceDate="11/06/1990" StartTime="08:00 AM" EndTime="08:30 AM" AddSubject="" OnceProcessed="No" />
<Schedule EntityName="Consultants" StatusName="Interview" ScheduleAfterDays="2" ProcessSinceDate="11/06/1990" StartTime="08:00 AM" EndTime="08:30 AM" AddSubject="" OnceProcessed="No"
/>
</SalesAutomation>'
SELECT
@XML
DECLARE
@DeleteEntityName NVARCHAR(100)
= 'Contacts', @DeleteStatusName
NVARCHAR(100)
= 'Interview', @UpdateEntityName
NVARCHAR(100)
= 'Consultants', @UpdateStatusName
NVARCHAR(100)
= 'Placed', @NewOnceProcessed
NVARCHAR(100)
= 'Yes'
IF(@XML.exist('SalesAutomation/Schedule[(@EntityName eq
sql:variable("@DeleteEntityName")) and (@StatusName eq
sql:variable("@DeleteStatusName"))]')
= 1)
SET
@XML.modify('delete
SalesAutomation/Schedule[(@EntityName eq sql:variable("@DeleteEntityName")) and
(@StatusName eq sql:variable("@DeleteStatusName"))]')
IF(@XML.exist('SalesAutomation/Schedule[(@EntityName eq
sql:variable("@UpdateEntityName")) and (@StatusName eq
sql:variable("@UpdateStatusName"))]')
= 1)
SET
@XML.modify('replace value of
(SalesAutomation/Schedule[(@EntityName eq sql:variable("@UpdateEntityName")) and
(@StatusName eq sql:variable("@UpdateStatusName"))]/@OnceProcessed)[1] with
sql:variable("@NewOnceProcessed")')
SELECT
@XML
Sample Example 2: Update all attriutes values
-- DECLARE @nodeCount int =
@xml.value('count(SalesAutomation/Schedule/@OnceProcessed)','int')
DECLARE
@Node int = 1, @UpdateCount int
= @xml.value('count(SalesAutomation/Schedule/@OnceProcessed[.="No"])','int')
WHILE
(@Node <= @UpdateCount)
BEGIN
SET @XML.modify('replace value of
(SalesAutomation/Schedule/@OnceProcessed[.="No"])[1] with "Yes"')
SET @Node = @Node + 1
END
SELECT
@XML
IF(@UpdateCount >= 1)
SELECT
'Do some activity' as
Action
50. Search and Replace in a TEXT(NTEXT) column (IMP IMP)
Sample Example:
DECLARE @FindString NVARCHAR
(100),
@ReplaceString NVARCHAR(100),
@ConsIntID NVARCHAR(
10),
@TextPointer VARBINARY
(16),
@DeleteLength INT
,
@OffSet INT
SET @FindString = 'Vishnu'
SET @ReplaceString = 'Vishnu Murthy'
SET @ConsIntID = 'CS00000095'
SELECT @TextPointer = Textptr(uuResumeText)
FROM Consultants
WHERE ( ConsIntID
= @ConsIntID )
SET @DeleteLength = Len(@FindString)
SET @OffSet = 0
SET @FindString = '%'
+ @FindString +
'%'
SELECT @OffSet = Patindex(@FindString, uuResumeText)
- 1
FROM Consultants
WHERE Patindex(@FindString, uuResumeText) <> 0
AND
( ConsIntID = @ConsIntID )
PRINT @TextPointer
PRINT @OffSet
PRINT @DeleteLength
PRINT @ReplaceString
UPDATETEXT Consultants.uuResumeText
@TextPointer
@OffSet
@DeleteLength
@ReplaceString
51. Password Reset
select * from
Users
where RecType
=
'20' and ID =
'Admin'
update Users set Password='“–‘˜“šÍÏÎÏ'
where RecType
=
'20' and ID =
'Admin'
update Users set Password=''
where RecType
=
'20' and ID =
'Admin'
51.1. Creating Test UserID
INSERT INTO dbo.Users
VALUES('20','Test', '0,0','', 1,'26623,1011,1011,1011,1011,1019,1011,1011'
,'','' ,'','' )
51.2.
Creating the constraints to ZipCodes table
ALTER TABLE ZipCodes ALTER
COLUMN
ZIPCode NVARCHAR(
5)
NOT NULL
GO
ALTER TABLE
dbo.ZipCodes
ADD
CONSTRAINT [aaaaaZIPCode_PK]
PRIMARY KEY
NONCLUSTERED
([ZIPCode]
)
ON [PRIMARY] GO
52. Update blank CompanyPhone
with Top 1 Linked Contact CompanyPhone (IMP IMP)
xupdate Companies
set CompanyPhone
= NULL
from Contacts
where (len(isnull(contacts.LinkToIntID,''))>0)
and
(len(isnull(contacts.companyphone,''))>0) and
(companies.CompanyIntID =
contacts.LinkToIntID)
and
(len(isnull(companies.CompanyPhone,''))=0)
Go
xupdate Companies set CompanyPhone
= NULL
from Contacts
where (len(isnull(contacts.LinkToIntID,''))>0)
and
(len(isnull(contacts.DirectPhone,''))>0) and
(companies.CompanyIntID =
contacts.LinkToIntID)
and
(len(isnull(companies.CompanyPhone,''))=0)
Go
xupdate companies set companies.CompanyPhone = Contacts.CompanyPhone
from Contacts
where (len(isnull(contacts.LinkToIntID,''))>0)
and
(len(isnull(contacts.companyphone,''))>0) and
(companies.CompanyIntID =
contacts.LinkToIntID)
and
(companies.CompanyPhone is
null)
Go
xupdate companies set companies.CompanyPhone = Contacts.DirectPhone
from Contacts
where (len(isnull(contacts.LinkToIntID,''))>0)
and
(len(isnull(contacts.DirectPhone,''))>0) and
(companies.CompanyIntID =
contacts.LinkToIntID)
and
(companies.CompanyPhone is
null)
Go
53. cBizOne RegEdit Path
Sample Example:
Type "RegEdit" in Run and navigate to the following path
HKEY_CURRENT_USER
- Software
- VB and VBA Program
Settings
- xBar
54. SQL New Functions
COALESCE
SELECT
COALESCE(NULL,'VISHNU','MURTHY')
-- First non Nullable column
VISHNU --O/P
QUOTENAME
SELECT QUOTENAME('SQL Server Rider')
SELECT
QUOTENAME('SQL
Server Rider',
'''')
SELECT
QUOTENAME('SQL
Server Rider',
'"')
SELECT
QUOTENAME('SQL
Server Rider',
'<')
Out Put:
[SQL Server Rider]
'SQL Server Rider'
"SQL Server Rider"
<SQL Server
Rider>
------------------------------------------------------------
IF (SELECT COUNT(*) FROM Links, Consultants, Contacts
)
> 0
SELECT 'SUCCESS'
GO
-- Query cost 50%
IF EXISTS (SELECT * FROM Links, Consultants, Contacts
)
SELECT 'SUCCESS'
GO
-- Query cost 50%
------------------------------------------------------------
-- 10 MIN TO RUN THIS QUERY
-----------------------------
DECLARE @aa INT
SELECT @aa =
1
FROM Consultants,
links
SELECT @aa
-----------------------------
-----------------------------
--0 MIN TO RUN THIS QUERY
-----------------------------
DECLARE @aa int
SELECT @aa = 1
WHERE EXISTS (SELECT 1
from Consultants,
links)
SELECT @aa
-----------------------------
------------------------------------------------------------
SET
QUOTED_IDENTIFIER OFF
GO
EXECUTE("SELECT
'Success' WHERE (1=1)")
SET QUOTED_IDENTIFIER ON
GO ------------------------------------------------------------
How to use "SET QUOTED_IDENTIFIER
OFF" in a
Procedure
SET QUOTED_IDENTIFIER OFF
GO CREATE PROCEDURE [dbo].[CBO_BulkMergeCandidatesOnPhone]
@NumberOfMergeRecords INT, @TestPhoneNumber NVARCHAR(200) = '' AS
BEGIN SET NOCOUNT ON DECLARE @MasterConsIntID NVARCHAR(10),
@SlaveConsIntID NVARCHAR(10) ....... END
SET QUOTED_IDENTIFIER ON
GO
------------------------------------------------------------
-- Alter Table Schema
ALTER
SCHEMA dbo TRANSFER cBiz_millstream.SaveSetInfo
go
------------------------------------------------------------
The CHARINDEX and PATINDEX functions return the starting position of a pattern
you specify. PATINDEX can use wildcard characters, but CHARINDEX cannot.
Example
:
SELECT CHARINDEX('arm', 'vishnuarmmurthy') AS POSITION
SELECT PATINDEX('%reflector%', 'vishnu murthy reflector') AS
POSITION
------------------------------------------------------------
55.Error while running
the cBizOneHostingScript
ALTER
TABLE
Consultants ALTER
COLUMN
Summary [NTEXT];
-- Out Put
-- Error
SELECT COUNT
(*)
FROM Consultants
WHERE
LEN(
ISNULL(
Summary,''))
=
0
UPDATE Consultants SET
Summary =
NULL
WHERE LEN
(ISNULL
(Summary,''))
=
0
ALTER TABLE
Consultants ALTER
COLUMN
Summary [NTEXT];
-- Out Put
-- Command(s) completed successfully.
56. LookUp is
Pointing to wrong lookUp Values
Eg: Consultants "Status...", "Sub-Type" Lookup Point to "Reminders.ApptSubType,Status,Status,Contacts,Status,Status,Consultants"
but it should point to "Reminders.ApptSubType,Status,Status,Consultants".
Even we change it to correct value still it is pointing to same.
Solution:
Replace the RemindersFields.xml from Sys Files.
57.
Bug in the cBizOne Users Table
- When we delete the users table group it will not delete the previously assigned
users
DELETE
Users
WHERE (RecType
= '1002')
AND
(SELECT
COUNT(*)
FROM Users U
WHERE U.
RecType =
'1001'
AND U.
ID = Users
.RPointer
) =
0
58.
Execute on all "cBiz_" Databases
--------------------------------------------------------------------------------------------------------------------
-- List All the Database's on the Server
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
DECLARE @SQL NVARCHAR(max)
SELECT @SQL =
"
use [?]
IF(('?' like 'cbiz[_]%') OR ('?' like 'exl[_]%'))
BEGIN
PRINT '?'
END
"
EXEC sp_MSforeachdb
@SQL
SET QUOTED_IDENTIFIER
ON
SET NOCOUNT
OFF
--------------------------------------------------------------------------------------------------------------------
-- Triggers on the Server
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
DECLARE @SQL NVARCHAR(max)
SELECT @SQL = "
use [?]
IF(('?' like 'cbiz[_]%') OR ('?' like 'exl[_]%'))
BEGIN
IF (
SELECT COUNT(*)
FROM sysobjects s1 JOIN sysobjects s2 ON s1.parent_obj = s2.[id]
WHERE s1.xtype = 'TR'
) > 0
PRINT '?'
END
"
EXEC sp_MSforeachdb @SQL
SET QUOTED_IDENTIFIER ON
SET NOCOUNT OFF
--------------------------------------------------------------------------------------------------------------------
-- Audit Triggers on the Server
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
DECLARE @SQL NVARCHAR(max)
SELECT @SQL = "
use [?]
IF(('?' like 'cbiz[_]%') OR ('?' like 'exl[_]%'))
BEGIN
IF (
SELECT COUNT(*)
FROM sysobjects s1 JOIN sysobjects s2 ON s1.parent_obj = s2.[id]
WHERE s1.xtype = 'TR' AND
(s1.[name] LIKE '%Audit%')
) > 0
PRINT '?'
END
"
EXEC sp_MSforeachdb @SQL
SET QUOTED_IDENTIFIER ON
SET NOCOUNT OFF
--------------------------------------------------------------------------------------------------------------------
-- Exemple 1
DECLARE @Value1 NVARCHAR(200), @Value2 NVARCHAR(20), @SQL NVARCHAR(max)
SET @Value1 = '"linkedin|'
SET @Value2 = '",'
SELECT @SQL = '
use [?]
IF((''?'' like ''cbiz[_]%'') OR (''?'' like ''exl[_]%''))
BEGIN
IF (
SELECT TOP 1 COUNT(*)
FROM Consultants
where (UserField5 LIKE ''linkedin,'')
) > 0
BEGIN
PRINT ''?''
update consultants set JobBoardIDs = '''+ @Value1 +
'''+userfield6+'''
+ @Value2 +''', uuSourceWebSites = UserField5
where DATALENGTH(isnull(userfield5,''''))>0 AND
DATALENGTH(isnull(userfield6,''''))>0 AND
(userfield5 LIKE ''linkedin,'')
END
END
'
EXEC sp_MSforeachdb
@SQL
59.
Find which requirement have more Submissions
SELECT
DISTINCT(Req) AS Req,
SUM(
XCount)
as XCount
FROM
(
SELECT
DISTINCT(ToID)
AS Req
, COUNT
(*)
as XCount
FROM
MLinkInfo
WHERE
(MLinkInfo.Type=1
AND MLinkInfo
.SubType=1)
AND
(ToID
like 'RQ%'
)
GROUP
BYToID
UNION ALL
SELECT DISTINCT(SentToIntID)
AS Req,
COUNT(*) as XCount
FROM EMailsSent
WHERE EMailsSent.MailType
=
'Resume' AND
(SentToIntID
like
'RQ%')
GROUP
BY SentToIntID
) TEMP
GROUP BY
Req
ORDER
BY XCount DESC
-------------------------------------------------
SELECT [ItemIntID], XCount from
( SELECT [ItemIntID], Count(*) as XCount FROM [dbo].[Reminders]
where [ItemIntID] like 'RQ%' GROUP BY [ItemIntID] ) t
ORDER BY XCount DESC
60. Query will not build
STATISTICS (IMP, Use this on Live Server)
DECLARE
@Sql_Query NVARCHAR(MAX)
SET
@Sql_Query ='SET QUOTED_IDENTIFIER
OFF'
SELECT @Sql_Query
+='
EXEC ("DROP STATISTICS ['
+
su.name + '].[' + so.name + '].[' + si.name
+
']") '
FROM sysindexes si
JOIN
sysobjects so ON si.id
= so.id JOIN sysusers su on
su.uid =
so.uid
WHERE
INDEXPROPERTY(si.id,si.name,
'IsStatistics')
= 1
--AND si.name not like '_WA_Sys%'
AND
OBJECTPROPERTY(so.id,
'IsUserTable') = 1
ORDER BY
so.name,
si.name
SET
@Sql_Query +='
SET QUOTED_IDENTIFIER ON '
--Print @Sql_Query
EXEC(@Sql_Query)
Print 'Query Name: ABC'
ALTER DATABASE [Exl_VishnuTest1]
SET AUTO_CREATE_STATISTICS OFF
GO
DECLARE @StartDateTime
DATETIME
SET
@StartDateTime =
GETDATE()
----------------------------------------------------------------
-- Put your Query here
----------------------------------------------------------------
PRINT
'Time Took:' +
CAST
(DATEDIFF(
MS,@StartDateTime
,GETDATE())
AS
CHAR
(100
))
ALTER DATABASE
[Exl_VishnuTest1] SET AUTO_CREATE_STATISTICS ON
GO
60.1. Delete all User Table
Statistics for a database
DECLARE
@Sql_Query
NVARCHAR(MAX
)
SET
@Sql_Query
='SET QUOTED_IDENTIFIER
OFF
'
SELECT
@Sql_Query +='EXEC ("DROP STATISTICS [' +
su
.name +
'].[' + so
.name +
'].[' + si
.name +
']")
'
FROM
sysindexes
si
JOIN
sysobjects so
ON
si.
id
=
so
.id
JOIN
sysusers su
on
su.
uid
=
so
.uid
WHERE
INDEXPROPERTY(si
.id,
si.name
,
'IsStatistics'
)
=
1
--AND si.name not like '_WA_Sys%'
AND
OBJECTPROPERTY(
so.id
,
'IsUserTable'
)
= 1
ORDER
BY
so.name
,
si
.name
SET
@Sql_Query
+='SET QUOTED_IDENTIFIER
ON
'
Print
@Sql_Query
EXEC(
@Sql_Query)
60.2. Analyzing & Optimizing T-SQL Query Performance
--DO NOT RUN THIS ON PRODUCTION!!!
DBCC
DROPCLEANBUFFERS;
DBCC
FREEPROCCACHE;
DBCC
FREESESSIONCACHE;
GO
SET
STATISTICS IO
ON;
SET STATISTICS TIME
ON;
ALTER DATABASE [cBiz_test2] SET AUTO_CREATE_STATISTICS OFF;
GO
SELECT COUNT(*)
FROM Consultants
GO
ALTER DATABASE [cBiz_test2] SET AUTO_CREATE_STATISTICS ON;
SET STATISTICS TIME
OFF;
SET STATISTICS IO
OFF;
GO
Note: Use NOCOUNT ON Returns the single biggest performance boosts when coding
stored procedures, triggers, and functions. Even casual scripting can experience
a significant boost!
SET NOCOUNT ON < code > SET NOCOUNT OFF
60.2.1. SQL Server is
not very good in optimizing the OR condition
predicates.
Even though columns in the WHERE clauses are covered by indexes, SQL Server is unable
to use these indexes. This raises the question as to whether anything is “blocking”
the use of the indexes. The answer to this question is yes -- the culprits are the
parameters and the “OR” condition.
Example: Performance Issue
with OR Operator
Select key3
From
Table
Where (@key1 =0
OR Key1 =@Key1)
Solution: Use
UNION ALL
SELECT
key3
FROM
Table
WHERE
@key1 = 0
UNION
ALL
SELECT
key3
FROM
Table
WHERE
@key1 <> 0 AND
key1 = @key1
60.3. Clearing Cache for SQL Server Performance
Testing (IMP Don't Use This on Live Server)
USE
[1DemoDataBase]
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
SELECT *
FROM Contacts, Links, Requirements
Example:
----------------------------------------------------
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
SET
STATISTICS IO
ON
GO
SET STATISTICS
TIME
ON
GO
ALTER DATABASE [cBiz_test2] SET AUTO_CREATE_STATISTICS OFF
GO
EXEC dbo
.XCBO_UserActivitiesCountsReport
'3/6/2000'
,'3/6/2013'
,'Kris|Admin|<Avg-Sel>|Ahmed|Alex|<Avg-All>|Bob|Bryan|Test'
,'Test'
ALTER DATABASE [cBiz_test2] SET AUTO_CREATE_STATISTICS
ON
GO
SET
STATISTICS TIME
OFF
GO
SET STATISTICS
IO
OFF
GO
----------------------------------------------------
Link:
http://www.mssqltips.com/sqlservertip/1360/clearing-cache-for-sql-server-performance-testing/
60.4. Using with clause(IMP)
The query against sys.dm_exec_query_stats is an efficient way to determine
which query is using the most cumulative CPU.
select
DB_NAME(q
.dbid)
as
DB_NAME
,
OBJECT_NAME(q
.objectid,
q.dbid
)
as
OBJECT_NAME
,
highest_cpu_queries
.plan_handle,
highest_cpu_queries
.total_worker_time,
q.
number, q.
encrypted,
highest_cpu_queries
.total_rows,
highest_cpu_queries
.creation_time,
highest_cpu_queries
.last_execution_time,
q.[text]
from
(
select top 500 qs
.plan_handle,
qs
.total_worker_time,
qs
.creation_time,
qs
.last_execution_time,
qs
.total_rows
from
sys.dm_exec_query_stats
qs
order
by
qs
.total_worker_time
desc)
as
highest_cpu_queries
cross
apply
sys
.dm_exec_sql_text(
plan_handle)
as
q
order
by
highest_cpu_queries.total_worker_time
desc
60.5. Obtaining information about the top five
queries by average CPU time(IMP)
SELECT
TOP 5 total_worker_time/execution_count AS [Avg CPU Time],SUBSTRING(st.text,
(qs.statement_start_offset/2)+1,
((CASE
qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text) ELSE
qs.statement_end_offset
END - qs.statement_start_offset)/2)
+ 1) AS statement_text FROM
sys.dm_exec_query_stats AS qs CROSS
APPLY sys.dm_exec_sql_text(qs.sql_handle)
AS st
ORDER BY total_worker_time/execution_count DESC;
60.6. Obtaining top 100 Query
shoted to a pertical database(V.V.V.IMP)
SELECT TOP
100 DB_NAME(q.dbid)
as DB_NAME, OBJECT_NAME(q.objectid,q.dbid)
as OBJECT_NAME, qs.plan_handle, qs.total_worker_time,
q.number,
q.encrypted, qs.total_rows, qs.creation_time,
qs.last_execution_time,
qs.total_worker_time,
q.objectid, q.[text]
FROM
sys.dm_exec_query_stats
qs cross apply
sys.dm_exec_sql_text(qs.plan_handle) as q
WHERE (DB_NAME(q.dbid) =
'cBizSoft')
ORDER
BY qs.last_execution_time
DESC
61. Using with clause
The WITH keyword is used to create a temporary named result set, that is defined
within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE
VIEW statement.
WITH
Administrators
(
Name, Surname
)
AS
(
SELECT
Name, Surname
FROM Users
WHERE AccessRights =
'Admin'
)
SELECT
*
FROM Administrators
61.1 How Recursive Common Table Expression (CTE)
Works ?
Example1:
declare
@StartDate smalldatetime, @Wk
int, @pSchoolEndDate
smalldatetime
set @StartDate = '1/1/1900'
SET @Wk = 1
set @pSchoolEndDate =
'1/1/2040'
;with cte ([Date],Wk,DOW)
as (
select @StartDate,@Wk,datepart(dw,@StartDate)
union all
select [Date]+1,case when DOW=1
then Wk+1 else Wk end,datepart(dw,[Date]+1)
from cte
where [Date]<@pSchoolEndDate
)
select *
from cte
OPTION (MAXRECURSION 0);
Note:
100 is the default limit for CTE, one can set it upto
32767 with 'maxrecursion'
Example2:
DECLARE
@SplitList
NVARCHAR(
4000);
SELECT @SplitList
= 'Count Howmany Characters InString'
;
WITH Listings
(
Position, CharacterSymbol
) AS
(
SELECT
1,
SUBSTRING
(@SplitList, 1,
1)
UNION
ALL
SELECT
Position + 1
, SUBSTRING
(@SplitList,Position
+
1, 1
)
FROM
Listings
WHERE
Position
<=
LEN(
@SplitList)
- 1
)
SELECT
CASE WHEN
CharacterSymbol
=
''
THEN
'Empty
Space'
ELSE CharacterSymbol
END
AS
WCharacter
,
COUNT(CharacterSymbol)
FROM Listings
GROUP
BY CharacterSymbol
OPTION
(MAXRECURSION 0);
Example3:
WITH
Number
AS
(SELECT
1
AS n
UNION
ALL
SELECT
n + 1
FROM
Number
WHERE
n < 32767
)
SELECT n
FROM Number
OPTION (
MAXRECURSION 32767
);
Note:
The CTE usage has some disadvantages, one of them being the fact that CTE’s members
cannot use the following clauses or keywords: DISTINCT, GROUP BY, HAVING, TOP, LEFT/RIGHT/OUTER
JOIN, limiting by this the types of the queries that can be created and reducing
their complexity. Other disadvantage can be the fact that the recursion is limited
as the recursive member can refer to the CTE only once.
Attention:
Note: ContactsReceived is a With Table
Fast Query:
SELECT Consultants.ConsIntID,Consultants.DisplayName as Candidate,Consultants.JobTitle
AS CandJobTitle,Requirements.ReqIntID,Requirements.ReqID,Requirements.JobTitle as
ReqJobTitle,Requirements.Location, Contacts.ContactIntID,Contacts.DisplayName as
Contact,Contacts.CompanyName as Company,Reminders.ApptSubType AS Stage, Reminders.Subject,Reminders.StartDateTime
AS DateAndTime,dbo.XCBO_TrimDelimiter(Reminders.UserIDs) as UserIDs FROM dbo.GetReminderLinks('Status','Status',NULL)
AS GetReminderLinks, RemindFROM dbo.GetReminderLinks('Status','Status',NULL)
AS GetReminderLinks, Reminders, Consultants, Contacts, ContactsReceived,
Requirements, RequirementsReceived
WHERE (Consultants.ConsIntID = GetReminderLinks.ConsIntID) AND (Contacts.ContactIntID
= GetReminderLinks.ContactIntID) AND (Requirements.ReqIntID = GetReminderLinks.ReqIntID)
AND (GetReminderLinks.ConsIntID = ContactsReceived.ConsIntID) AND (Reminders.RemIntID
= GetReminderLinks.RemIntID) AND (ContactsReceived.ContactIntID = GetReminderLinks.ContactIntID)
AND (RequirementsReceived.ReqIntID = GetReminderLinks.ReqIntID)
Time Taking Query:
SELECT Consultants.ConsIntID,Consultants.DisplayName as Candidate,Consultants.JobTitle
AS CandJobTitle,Requirements.ReqIntID,Requirements.ReqID,Requirements.JobTitle as
ReqJobTitle,Requirements.Location, Contacts.ContactIntID,Contacts.DisplayName as
Contact,Contacts.CompanyName as Company,Reminders.ApptSubType AS Stage, Reminders.Subject,Reminders.StartDateTime
AS DateAndTime,dbo.XCBO_TrimDelimiter(Reminders.UserIDs) as UserIDs Reminders, Consultants,
Contacts, ContactsReceived, Requirements, RequirementsReceived FROM dbo.GetReminderLinks('Status','Status',NULL)
AS GetReminderLinks, Reminders, Consultants, Contacts, ContactsReceived,
Requirements, RequirementsReceived
WHERE (Consultants.ConsIntID = ContactsReceived.ConsIntID) AND (Contacts.ContactIntID
= ContactsReceived.ContactIntID) AND (Requirements.ReqIntID = RequirementsReceived.ReqIntID)
AND (GetReminderLinks.ConsIntID = ContactsReceived.ConsIntID) AND (Reminders.RemIntID
= GetReminderLinks.RemIntID) AND (ContactsReceived.ContactIntID = GetReminderLinks.ContactIntID)
AND (RequirementsReceived.ReqIntID = GetReminderLinks.ReqIntID) Fix for Exelare Bug in xTabs, views... (Eg. xTab is working in cBizOne
but not working in Exelare)
UNION ALL
SELECT
'','','',''
,'','',''
WHERE
(1=
2)
62. How to split a comma-separated value to columns
DECLARE @stringToSplit
VARCHAR(MAX),
@Split_Name NVARCHAR(255), @Split_Pos INT
DECLARE
@Comma_Split_List TABLE(Value
VARCHAR(MAX))
SET
@stringToSplit =
'Vishnu, Ram, Water, Boost'
WHILE
CHARINDEX(',', @stringToSplit) > 0
BEGIN
SET @Split_Pos =
CHARINDEX(',', @stringToSplit)
SET @Split_Name = SUBSTRING(@stringToSplit, 1, @Split_Pos-1)
INSERT INTO @Comma_Split_List SELECT
LTRIM(RTRIM(@Split_Name))
SET @stringToSplit = SUBSTRING(@stringToSplit,
@Split_Pos+1,
LEN(@stringToSplit)-@Split_Pos)
END
INSERT
INTO @Comma_Split_List
SELECT LTRIM(RTRIM(@stringToSplit))
SELECT
* FROM
@Comma_Split_List
63. Recently add Duplicate Candidates on Email1
View
WITH DuplicateConsultants
AS
(
SELECT ConsIntID, EMail1, CREATEDATE,
ROW_NUMBER()
OVER (PARTITION
BY EMail1 ORDER BY CREATEDATE
ASC) as
DuplicateCount
FROM Consultants
WHERE LEN(ISNULL(EMAIL1,''))>0
)
SELECT *
FROM DuplicateConsultants
WHERE DuplicateCount > 1
ORDER BY EMail1, DuplicateCount
63.1. PARTITION Example:
SELECT DisplayName, CompanyName, ROW_NUMBER() OVER (PARTITION BY CompanyName ORDER
BY DisplayName) AS theRow
FROM Contacts
64. Last Time a Table was Accessed
WITH
LastActivity
(TableName, DateAndTime, LastAction) AS
(
SELECT OBJECT_NAME(object_id)
AS TableName,
last_user_seek as DateAndTime,
'seek' AS LastAction
FROM
sys.dm_db_index_usage_stats u
WHERE database_id
= db_id(db_name())
UNION
SELECT
OBJECT_NAME(object_id)
AS
TableName,
last_user_scan
as DateAndTime,
'scan'
AS LastAction
FROM
sys
.dm_db_index_usage_stats
u
WHERE
database_id
=
db_id(db_name())
UNION
SELECT
OBJECT_NAME(object_id)
AS
TableName,
last_user_lookup
as DateAndTime,
'lookup'
AS LastAction
FROM
sys
.dm_db_index_usage_stats
u
WHERE
database_id
=
db_id(db_name())
)
SELECT
TableName,
MAX
(DateAndTime)
AS
DateAndTime, LastAction
FROM
LastActivity
GROUP
BY TableName
, LastAction
ORDER
BYTableName
65. IMP
1. You can not compare 'NULL' Values Like This
SELECT
'asdasd'
WHERE (null
=
null)
66. Delete large amount of data in sql server
DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1
WHILE (@RowsDeleted
> 0)
BEGIN
DELETE TOP
(10000)
FROM MyTable [WHERE .....]
-- WHERE is optional
SET @RowsDeleted =
@@ROWCOUNT
END
67. Issue in Trigger Using IF
UPDATE()
When we have AuditTrigger and Insert, Update Trigger the IF
UPDATE() will give issues.
IF UPDATE(City)
BEGIN
.....
END
Solution:
IF (
SELECT COUNT(*)
FROM INSERTED LEFT JOIN DELETED ON (INSERTED.[ConsIntID] = DELETED.[ConsIntID])
WHERE (ISNULL(INSERTED.City,'') != ISNULL(DELETED.City,''))
) > 0
BEGIN
....
END
67.1. Issue in Trigger Using IF UPDATE() IN
EXELARE (Found for xUsers in cBizSoft DB)
--NOTE: For Some reason, if updating
raising even data not changed (May be Date and Smalldatetime issues)
DECLARE
@ExpiryDate_Changed
INT
,
@Status_Changed
INT
SELECT
@ExpiryDate_Changed
=
COUNT
(1
)
FROM
INSERTED LEFT
JOIN
DELETED
ON
(
INSERTED.[CompanyID]
=
DELETED
.[CompanyID] AND
INSERTED.[UserID]
=
DELETED
.[UserID])
WHERE
(
ISNULL(INSERTED
.ExpiryDate,
'1/1/1900')
!=
ISNULL
(DELETED.
ExpiryDate,'1/1/1900'
))
SELECT @Status_Changed = COUNT(1)
FROM INSERTED LEFT
JOIN DELETED ON
(INSERTED.[CompanyID] = DELETED.[CompanyID] AND INSERTED.[UserID] = DELETED.[UserID])
WHERE
(ISNULL(INSERTED.Status,'')
!= ISNULL(DELETED.Status,''))
IF (@ExpiryDate_Changed>0
OR @Status_Changed>0)
BEGIN
.........
END
68. Sample Query to find the
Duplicate records on Requirement - Status On Requirement (IMP)
DECLARE @Count INT, @TotalCount INT, @ItemIntID_T NVARCHAR(10), @Subject_T NVARCHAR(300)
SELECT [ItemIntID], [Subject], IDENTITY( int ) AS idcol INTO #newtable from [Reminders]
where subject like 'Job Status:%' group by [ItemIntID], [Subject] having
count(*)>1
SELECT @TotalCount = COUNT(*) FROM #newtable
SET @Count = 1
WHILE(@Count <= @TotalCount)
BEGIN
SELECT @ItemIntID_T = ItemIntID, @Subject_T = SubjecT FROM #newtable WHERE idcol
= @Count
SELECT * FROM reminders WHERE (ItemIntID = @ItemIntID_T) AND (Subject = @Subject_T)
and reminders.RemIntID != ( select top 1 r.RemIntID from reminders r WHERE (r.ItemIntID
= @ItemIntID_T) AND (r.Subject = @Subject_T) order by r.CreateDate asc )
SET @Count = @Count + 1
END
DROP TABLE #newtable
69
. Explanation of Regular Expressions:
Regular expressions are used to search specified in the source string.
Examples:
Pattern#1 Regex objNotNaturalPattern=new Regex("[^0-9]");
Pattern#2 Regex objNaturalPattern=new Regex("0*[1-9][0-9]*");
Pattern#1 will match for strings other than 0 to 9.^ symbol is used for Specifying
not condition.[] brackets if we are to give range values such as 0 - 9 or a-z or
A-Z eg. abc will return true 123 will return false.
Pattern#2 will match for string which are Natural Numbers.Natural numbers Are numbers
which are always greater than 0.The pattern 0* tells that a natural Number can be
prefixed with any number of zero's or no zero's.the next [1-9] tells that
it should contain atleast one number from 1 to 9 followed by any numbers of 0-9's
Eg. 0007 returns true whereas 00 will return false.
Basic things to be understood in RegEx: "*" matches 0 or more patterns
"?" matches single character "^" for ignoring matches. "[]"
for searching range patterns.
Help Like's:
http://www.mikesdotnetting.com/Article/46/CSharp-Regular-Expressions-Cheat-Sheet
http://www.c-sharpcorner.com/UploadFile/prasad_1/RegExpPSD12062005021717AM/RegExpPSD.aspx
70. How To Copy SQL Server 2005 Jobs From One Server
To Another:
Right-click on the job that you want to copy over to a second server.
Select Script Job as –> Create To –> New Query Editor Window.
Link:
http://robbamforth.wordpress.com/2009/05/01/how-to-copy-sql-server-2005-jobs-from-one-server-to-another/
71. Convert Rows to Columns:
SELECT ColumnName, DataValue
FROM
(
SELECT Exl_Licenses, cBiz_Licenses, Digits16_Licenses, ThinClient FROM [dbo].[PadmacBizSoftLicenseCount]
WHERE (LicenseCountDate > DATEADD(DAY,-2,GETDATE()))
) MainTable
UNPIVOT
(DataValue FOR ColumnName IN (Exl_Licenses, cBiz_Licenses, Digits16_Licenses, ThinClient)
) AS TempTable
72. Differences between ISNULL and COALESCE:
Both ISNULL and COALESCE can be used to get the
same results but there are some differences.
1. Data type determination of the resulting expression - ISNULL uses the first parameter
type, COALESCE follows the CASE expression rules and returns type of value with
highest precedence
2. ISNULL(NULL, NULL) -- is int
COALESCE(NULL, NULL) -- Will throw an error
COALESCE(CAST(NULL as int), NULL) -- it valid and returns int
3. ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters
select COALESCE('SSS',NULL,'DD', 'eee')
select COALESCE(NULL,NULL,NULL, 'eee')
4.This will make a performance difference and queries with COALESCE often fare worse
here.
73. Round to one Decimal Point:
SELECT CONVERT(decimal(18, 1), ROUND(0.857142895460129, 1)) as GoalValue
74. No More Multiple UserID's (Single UserID):
SELECT XCount,What,UserIDsSingle as UserIDs
FROM
(
SELECT count(*) as XCount,UserIDs,max('Companies') as What
FROM Companies
GROUP BY UserIDs
) MultipeUsers LEFT JOIN
(SELECT UserIDsSingle FROM (SELECT ','+ID+',' AS UserIDsSingle,0 Private FROM Users
WHERE RecType='20') SingleUsers1
) SingleUsers
ON MultipeUsers.UserIDs LIKE '%'+SingleUsers.UserIDsSingle+'%'
74. Convert Requirements.NumberOfOpenings
to INT
DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('Requirements') AND [name] = 'NumberOfOpenings';
EXEC('ALTER TABLE Requirements DROP CONSTRAINT ' + @ObjectName)
ALTER TABLE Requirements
ALTER COLUMN NumberOfOpenings INT
75. Avoid SQL
Server functions
in the WHERE clause for Performance
Execute Slow:
SELECT EmailAddress
FROM person.contact
WHERE left(EmailAddress,2) = 'As'
Execute
Fast: (SQL Server can do an index seek which is much
more efficient then an index scan)
SELECT EmailAddress
FROM person.contact
WHERE EmailAddress like 'As%'
Help Link:
http://www.databasejournal.com/features/mssql/article.php/3845381/T-SQL-Best-Practices-150-Don146t-Use-Scalar-Value-Functions-in-Column-List-or-WHERE-Clauses.htm
http://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/
http://www.blog.namwarrizvi.com/?p=101
76. The SQL LIKE operator
very often causes unexpected performance behaviour
Tip Avoid LIKE expressions with leading wildcards
(e.g., '%TERM').
SELECT ES.UserIDs, COUNT(*) AS NoContacted
FROM [EMailsSent] ES
WHERE (ES.EMail
= CTDBECM.EMail)
GROUP BY ES.UserIDs
Out Put: 3 Min
SELECT ES.UserIDs, COUNT(*) AS NoContacted
FROM [EMailsSent] ES
WHERE (ES.EMail like '%' +
CTDBECM.EMailDomain)
GROUP BY ES.UserIDs
Out Put: 2 Hr
Solution:
Use the FullText Catalog
SELECT ES.UserIDs, COUNT(*) AS NoContacted
FROM [EMailsSent] ES
WHERE (CONTAINS(ES.EMail,
@EMailDomain))
GROUP BY ES.UserIDs
Out Put: 18 Min
Example Function:
CBO_GetEmailSendUserIDsandCounts
(From the Email Domain get the EmailSend UserID's and Counts)(Insted
of Using Like Operater using the Fulltext Catalog)
77. Use sp_settriggerorder stored procedure to change
trigger firing order.
Examples:
exec sp_settriggerorder @triggername = 'CBO_AppendDoller',
@order = 'LAST',
@stmttype = 'insert'
exec sp_settriggerorder @triggername = 'CBO_TriggerOrderDemo',
@order = 'first',
@stmttype = 'insert'
exec sp_settriggerorder @triggername = 'CBO_AppendDoller',
@order = 'none',
@stmttype = 'insert'
exec sp_settriggerorder @triggername = 'CBO_TriggerOrderDemo',
@order = 'none',
@stmttype = 'insert'
78. Debug SQL Query in Management Studio(F11).
Examples:
CREATE PROCEDURE Perfect (@Input INT)
AS
DECLARE
@temp INT = 1,
@counter INT = 2;
BEGIN
IF(@Input < 1)
BEGIN
RAISERROR('Enter a positive number',16,1)
return;
END
Start Debug Mode:
WHILE(@counter <= @Input/2)
BEGIN
IF(@Input % @counter = 0)
BEGIN
SET @temp = @temp + @counter
END
SET @counter = @counter + 1
END
IF(@temp = @Input)
BEGIN
PRINT 'Perfect Number ' + CAST( @Input AS NVARCHAR
)
END
ELSE
BEGIN
PRINT 'Not Perfect ' + CAST( @Input AS NVARCHAR
)
END
END
EXEC Perfect 6
79. Insert Large Amount of Data in to Database(Bulk
Insert).
Examples 1:
BULK INSERT ZipCodes
FROM 'C:\Users\Vishnu.Vishnu\Desktop\NewDatabaseScript\ZipCodes.CSV'
WITH
(
FIELDTERMINATOR =
',',
ROWTERMINATOR=
'\n'
)
Examples 2:
CREATE TABLE
[dbo].[Stickers]
(
[name] varchar(10)
,
[category] varchar(10)
,
[gender] varchar(1)
,
[imageData] varchar(max)
)
INSERT
INTO
[dbo].[Stickers]
([name],
[category], [gender],
[imageData])
SELECT
'Red dress'
, 'Dress'
, 'F'
, photo.*
FROM
OPENROWSET(BULK
'D:\20150707_150530.jpg',
SINGLE_BLOB)
[photo]
Note: LogInID - Server Roles - Select bulkadmin to be able to use
bulk commands or sysadmin to be able to use any commands to your database.
80. Create New Table With Select Command/With Existing
Tables.
SELECT
*
INTO Companies1
FROM
Companies
WHERE (1=
1)
81.
Generating Integer Random Number in given Limits
.
---- Create the variables for the random number generation
DECLARE
@Random
INT;
DECLARE @Upper
INT;
DECLARE @Lower
INT
SET
@Lower = 1
SET
@Upper = 999
SELECT @Random
= ROUND
(((@Upper -
@Lower -1)
*
RAND
()
+ @Lower
), 0
)
SELECT @Random
82.
SQL Server update trigger is not working(GET SINGLE RECORD
AT A TIME)(IMP).
SET
NOCOUNT ON;
DECLARE @TotalCount
BIGINT, @Count
BIGINT
SELECT ReqIntID,
VisaStatus
,
IDENTITY( int
)
AS idcol
INTO
#newtable
FROM [dbo].
[RemoveVISHNU]
SELECT
@TotalCount
=
COUNT(*)
FROM #newtable
SET
@Count =1
WHILE
(@Count
<= @TotalCount
)
BEGIN
UPDATE Requirements
SET
Requirements
.VisaStatus =
#newtable.VisaStatus
FROM
#newtable
WHERE
(#newtable.
idcol
= @Count
)
AND (Requirements.
ReqIntID
= #newtable
.ReqIntID)
SET
@Count
+=
1
END
PRINT
'(' + CAST
(@Count AS
CHAR(5))
+ ' row(s) affected)'
DROP TABLE #newtable
82.1. Get Single Record and Update One After Other in a Loop(With Out Using Cursor)
SET
NOCOUNT ON;
DECLARE
@Count INT, @TotalCount
INT, @Domain_T
NVARCHAR(MAX)
SELECT
Domain, IDENTITY( int
) AS idcol INTO
#EmailDomain
FROM
(
SELECT
DISTINCT(SUBSTRING(Email1, PATINDEX('%@%',Email1)+1,LEN(Email1)))
as Domain
FROM
[dbo].[Contacts]
WHERE
LEN(ISNULL(Email1,''))>0 and
Email1 like '%@%'
and Email1 not
like '@%'
)
TEMP
SELECT
@TotalCount = COUNT(*) FROM #EmailDomain
SET
@Count = 1
WHILE(@Count
<= @TotalCount)
BEGIN
SELECT
@Domain_T = Domain FROM
#EmailDomain WHERE idcol
= @Count
IF((SELECT
COUNT(1) FROM [dbo].[ContactsVerify]
WHERE [Website] =
@Domain_T) = 1)
BEGIN
UPDATE [dbo].[Contacts]
SET uuEmailDomainExist =
1 WHERE Email1 LIKE
'%@'+@Domain_T
DELETE [dbo].[ContactsVerify]
WHERE [Website] =
@Domain_T
END
SET
@Count = @Count +
1
END
DROP
TABLE #EmailDomain
OR
SET
NOCOUNT
ON;
DECLARE @Count
INT, @TotalCount
INT, @ID
NVARCHAR(512)
DECLARE
@All_UserIDs
TABLE
( IDx
INT
IDENTITY(1,1),
Data
NVARCHAR(512)
)
INSERT
INTO @All_UserIDs
(Data)
SELECT ID
FROM Users
WHERE RecType
=
'20';
SELECT @TotalCount
=
COUNT(*)
FROM @All_UserIDs
SET @Count
= 1
WHILE(@Count
<= @TotalCount)
BEGIN
SELECT @ID
=
Data
FROM @All_UserIDs
WHERE IDx
= @Count
--Allow Sending SMS Messages
EXEC dbo.XCBO_SaveUsersPermissions
@ID,0,21,1,',<Self>,'
SET @Count
= @Count
+ 1
END
83="2">. TRUNCATE TABLE
Less transaction log space is used, Fewer locks are typically used and Without exception,
zero pages are left in the table.
TRUNCATE
TABLE [dbo].
[ZipCodes]
83.1. TRUNCATE Documents TABLE leave Customizations
SELECT
* INTO Documents_Temp
FROM Documents WHERE
ItemIntID LIKE 'CF%'
TRUNCATE
TABLE Documents
INSERT
INTO Documents SELECT
* FROM Documents_Temp
DROP
TABLE Documents_Temp
84. Copy DataFrom XML
to TSQL Table
Sample XML
File
Copy the above Sample file to D:\ Drive
DECLARE
@xmlData XML
SET
@xmlData =
(
SELECT * FROM
OPENROWSET
(
BULK 'D:\VishnuSampleXMP.xml'
,
SINGLE_CLOB
) AS xmlData
)
SELECT
ref
.value(
'FilterID[1]',
'int') AS
FilterID ,
ref.value
('Name[1]',
'NVARCHAR (10)')
AS Name ,
ref.value
('Code[1]',
'NVARCHAR (10)')
AS Code ,
ref.value
('Department[1]',
'NVARCHAR (3)')
AS Department
,
ref.value
('Number[1]',
'int') AS
Number
FROM @xmlData.
nodes('/ArrayOfSpangemansFilter/SpangemansFilter'
)
xmlData( ref
)
85. Display Distinct values
with minimum date (We Use this for First Submissions of a Candidate for a Requirement) (V.V.V.IMP)
select
distinct dd,
min
(Date1)
as Date1
from
(select 'A'
as
dd
, 1
as Date1
union all
select 'A'
as
dd, 2 as
Date1
union all
select 'B'
as
dd, 1 as
Date1
union all
select 'A'
as
dd, 1 as
Date1|
union all
select 'B'
as
dd, 2 as
Date1
)
tt
group by
dd
86. Display all the Values
in the Order of minimum date as 1 and so on (E.g.: Numbering the Duplicate
Submissions) (V.V.V.IMP)
SELECT
dd,
Date1
,
ROW_NUMBER() OVER
(PARTITION
BY
dd
ORDER
BY
dd
,
Date1) AS
'RowNumber'
from
(select 'A'
as
dd
, 1
as Date1
union all
select 'A'
as
dd, 2 as
Date1
union all
select 'B'
as
dd, 1 as
Date1
union all
select 'A'
as
dd, 1 as
Date1
union all
select 'B'
as
dd, 2 as
Date1
)
tt
OutPut:
Value1 Date1 RowNumber
A 1
1
A 1
2
A 2
3
B 1
1
B 2
2
86.1 Difference between
ROW_NUMBER, RANK, DENSE_RANK, NTILE (V.V.V.IMP)
-- create table
CREATE TABLE dbo.
Grades
(Names
VARCHAR
(1
),Grade
INT)
GO
-- insert data
INSERT INTO
dbo.Grades
VALUES ('A',
100),(
'B',90),('C'
,80
),('D',
70),(
'E',70),('F'
,60
)
GO
-- Test the data
SELECT Names,
Grade,
ROW_NUMBER
() OVER
(
ORDER
BY Grade
DESC
)
as ROW_NUMBER
,
RANK ()
OVER
(ORDER
BY Grade
DESC
)
as RANK,
DENSE_RANK ()
OVER
(ORDER
BY Grade
DESC
)
as DENSE_RANK
,
NTILE(3
)
OVER(ORDER
BY
Grade
desc
)
AS NTILE
FROM dbo.
Grades
Names Grade ROW_NUMBER RANK
DENSE_RANK NTILE
----- ----------- -------------------- -------------------- --------------------
A 100
1
1
1
1
B 90
2
2
2
1
C 80
3
3
3
2
D 70
4
4
4
2
E 70
5
4
4
3
F 60
6
6
5
3
Note: You can use "PARTITION BY"
if you want to Group them.
86.2 SQL
Find difference between previous and current row (V.V.V.IMP)
Status
columnOfNumbers
Vishnu
1
Vishnu
2
Vishnu
3
Vishnu
5
Vishnu
6
Vishnu
9
Ram
1
Ram
5
Ram
7
WITH CTE AS
(SELECT ROW_NUMBER() OVER (PARTITION
BY Status ORDER BY
Status, columnOfNumbers)
as ROW,Status, columnOfNumbers
FROM vishnu_tableName)
SELECT a.Status, a.columnOfNumbers, (a.columnOfNumbers - b.columnOfNumbers)
as Diff
FROM CTE a
LEFT JOIN CTE b ON
(a.ROW = b.ROW + 1
AND a.Status
= b.Status)
OutPut:
Status
columnOfNumbers
Diff
Ram
1
NULL
Ram
5
4
Ram
7
2
Vishnu
1
NULL
Vishnu
2
1
Vishnu
3
1
Vishnu
5
2
Vishnu
6
1
Vishnu
9
3
Example:
WITH
CTE
AS
(
SELECT
ROW_NUMBER()
OVER (ORDER
BY DateAndTime)
as ROW,
ApptSubType, DateAndTime
FROM
(
SELECT 'Filled'
as ApptSubType,
CAST('2016-05-04'
AS DATETIME) as DateAndTime
UNION ALL
SELECT 'Hold'
as ApptSubType,
'2016-04-04' as
DateAndTime
UNION ALL
SELECT 'DDD'
as ApptSubType,
'2016-03-04' as
DateAndTime
UNION ALL
SELECT 'Active'
as ApptSubType,
'2016-02-04' as
DateAndTime
UNION ALL
SELECT 'Filled'
as ApptSubType,
'2016-01-04' as
DateAndTime
UNION ALL
SELECT 'Hold'
as ApptSubType,
'2015-12-04' as
DateAndTime
UNION ALL
SELECT 'CCCC'
as ApptSubType,
'2015-11-04' as
DateAndTime
UNION ALL
SELECT 'Hold'
as ApptSubType,
'2015-10-04' as
DateAndTime
UNION ALL
SELECT 'Active'
as ApptSubType,
'2015-09-04' as
DateAndTime
UNION ALL
SELECT 'RRRR'
as ApptSubType,
'2015-08-04' as
DateAndTime
UNION ALL
SELECT 'Active'
as ApptSubType,
'2015-07-04' as
DateAndTime
UNION ALL
SELECT 'BBBB'
as ApptSubType,
'2015-06-04' as
DateAndTime
UNION ALL
SELECT 'Hold'
as ApptSubType,
'2015-05-14' as
DateAndTime
UNION ALL
SELECT 'Hold'
as ApptSubType,
'2015-05-04' as
DateAndTime
UNION ALL
SELECT 'Hold'
as ApptSubType,
'2015-04-04' as
DateAndTime
UNION ALL
SELECT 'Active'
as ApptSubType,
'2015-03-04' as
DateAndTime
UNION ALL
SELECT 'Active'
as ApptSubType,
'2015-02-04' as
DateAndTime
UNION ALL
SELECT 'AAAA'
as ApptSubType,
'2015-01-04' as
DateAndTime
)
TEMP
WHERE ApptSubType IN ('Active','Hold','Filled')
)
SELECT
ApptSubType, DateAndTime
FROM
(
SELECT
a.ApptSubType,
a.DateAndTime, (a.DateAndTime
- b.DateAndTime) as Diff
FROM
CTE a LEFT JOIN
CTE b ON (a.ROW
= b.ROW
+ 1 AND a.ApptSubType = b.ApptSubType)
)
TEMP
WHERE
Diff IS NULL
7. Percentage Calculation(RAM)
CREATE
FUNCTION CBO_Percent(
@Input1
int
,
@Input2 int)
returns decimal
(10
,2
)
AS
BEGIN
RETURN CAST((
ISNULL((@Input1
*1.0
)/NULLIF(
@Input2,0),0
)
* 100) as
decimal
(10
,2
))
END
88. cBizOne Customizations,
StylSheets, Exelare Customizations and JobPages Info laded in Documents table
CF00000000 - cBiz Customize.zip
CF00000001 - Stylesheets.zip
CF90000000 - Exelare Customize.zip
JC00000003 - Job Pages
AS00000003 - Auto Schedule
89. Giving the permission
to read the TSQL System Tables
Eg:
GRANT VIEW SERVER
STATE TO [cBiz_bentley]
;
Help
Link:
http:
//dba.
stackexchange.com
/questions/
34612/
give-permission
-to-
select-from
-system-
tables
89.1. Create LogIn User and give Permission to some Tables and all Procedure and Functions
USE [Exl_JDISearch]
GO
CREATE
LOGIN [Exl_JDISearch_User1]
WITH PASSWORD='2led_45965'
CREATE USER
[Exl_JDISearch_User1] FOR
LOGIN [Exl_JDISearch_User1]
GRANT
SELECT ON [dbo].[Companies] TO
[Exl_JDISearch_User1]
GRANT
INSERT ON [dbo].[Companies] TO
[Exl_JDISearch_User1]
GRANT
UPDATE ON
[dbo].[Companies] TO
[Exl_JDISearch_User1]
GRANT
SELECT ON [dbo].[Contacts] TO
[Exl_JDISearch_User1]
GRANT
INSERT ON [dbo].[Contacts] TO
[Exl_JDISearch_User1]
GRANT
UPDATE ON
[dbo].[Contacts] TO
[Exl_JDISearch_User1]
GRANT
SELECT ON [dbo].[Consultants] TO
[Exl_JDISearch_User1]
GRANT
INSERT ON [dbo].[Consultants] TO
[Exl_JDISearch_User1]
GRANT
UPDATE ON
[dbo].[Consultants] TO
[Exl_JDISearch_User1]
GRANT
SELECT ON [dbo].[Counters] TO
[Exl_JDISearch_User1]
GRANT
UPDATE ON
[dbo].[Counters] TO
[Exl_JDISearch_User1]
GRANT
SELECT ON [dbo].[Links] TO
[Exl_JDISearch_User1]
GRANT
INSERT ON [dbo].[Links] TO
[Exl_JDISearch_User1]
GRANT
UPDATE ON
[dbo].[Links] TO
[Exl_JDISearch_User1]
GRANT
SELECT ON [dbo].[MLinkInfo] TO
[Exl_JDISearch_User1]
GRANT
INSERT ON [dbo].[MLinkInfo] TO
[Exl_JDISearch_User1]
GRANT
UPDATE ON
[dbo].[MLinkInfo] TO
[Exl_JDISearch_User1]
GRANT
SELECT ON [dbo].[Reminders] TO
[Exl_JDISearch_User1]
GRANT
INSERT ON [dbo].[Reminders] TO
[Exl_JDISearch_User1]
GRANT
UPDATE ON
[dbo].[Reminders] TO
[Exl_JDISearch_User1]
GRANT
CREATE PROCEDURE
TO [Exl_JDISearch_User1]
GRANT
CREATE FUNCTION
TO [Exl_JDISearch_User1]
GRANT SELECT ON SCHEMA::[dbo] TO
[Exl_JDISearch_User1]
GRANT
ALTER ON
SCHEMA::[dbo]
TO [Exl_JDISearch_User1]
GRANT
EXECUTE ON
SCHEMA::[dbo]
TO [Exl_JDISearch_User1]
89.2. Create LogIn User and give Permission to ALL Tables, Views, Procedure and Functions
USE [Exl_VishnuTest1]
GO
CREATE
LOGIN [Exl_VishnuTest1]
WITH PASSWORD='bfa44584e0'
CREATE
USER [Exl_VishnuTest1]
FOR LOGIN [Exl_VishnuTest1]
------------------------------------------------------------
SELECT
'GRANT SELECT ON [dbo].['
+ Table_Name +
'] TO [Exl_VishnuTest1]'
FROM
INFORMATION_SCHEMA.TABLES
SELECT
'GRANT INSERT ON [dbo].['
+ Table_Name +
'] TO [Exl_VishnuTest1]'
FROM
INFORMATION_SCHEMA.TABLES
SELECT
'GRANT UPDATE ON [dbo].['
+ Table_Name +
'] TO [Exl_VishnuTest1]'
FROM
INFORMATION_SCHEMA.TABLES
SELECT
'GRANT DELETE ON [dbo].['
+ Table_Name +
'] TO [Exl_VishnuTest1]'
FROM
INFORMATION_SCHEMA.TABLES
------------------------------------------------------------
GRANT
CREATE PROCEDURE
TO [Exl_VishnuTest1]
GRANT
CREATE FUNCTION
TO [Exl_VishnuTest1]
GRANT
ALTER ON
SCHEMA::[dbo]
TO [Exl_VishnuTest1]
GRANT
EXECUTE ON
SCHEMA::[dbo]
TO [Exl_VishnuTest1]
GRANT
SELECT ON
SCHEMA::[dbo]
TO [Exl_JDISearch_User1]
90. Handling only Single
Insert/Delete in the Update Trigger (Not for BulkUpdates)(IMP
IMP IMP)
CREATE
TRIGGER [dbo].
[CBO_U_TEST]
ON [dbo]
.[Consultants]
FOR
UPDATE
AS
BEGIN
SET
NOCOUNT ON;
IF((SELECT
COUNT
(*)
FROM INSERTED
)=1
)
BEGIN
IF
UPDATE(
[UserField1])
BEGIN
UPDATE
[dbo]
.[Consultants] SET
[UserField2] = CAST
((SELECT
COUNT(*) FROM
INSERTED) AS
NVARCHAR(10))
WHERE [ConsIntID]
=
'CS00000106'
END
END
END
Note: Use this IF((
SELECT
COUNT(*)
FROM
INSERTED)=1)Every time in the Trigger
91. Clean Cache and Clean
Buffer (Don't USE this, IMP IMP IMP)
Use DBCC FREEPROCCACHE to clear the
procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc
SQL statement to be recompiled rather than reused from the cache. If observing through
SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE
goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that
the optimizer has cached in memory and force SQL Server to compile new plans the
next time those procedures are run.
Use DBCC DROPCLEANBUFFERS to test queries
with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS
serves to empty the data cache. Any data loaded into the buffer cache due to the
prior execution of a query is removed.
DBCC
FREEPROCCACHE
DBCC
DROPCLEANBUFFERS
Warning: These scripts should almost never
be run on a production system. These are meant for debugging and testing on DEV.
If you do run these on PROD, be sure to understand the consequences.
As with clearing the entire cache, this is probably not something you want to do
with a production system unless you have a good reason. You may have one database
that is a special case, which needs to be tamed.
Like a broken record, I got to remind you that it is a bad idea to do this on a
production system.
How ever with out using this, upon executing a query and returning the results to
the user, it stored the data in its buffer pool for a little while. If you rerun
the query, Sql Server will be able to quickly return the results from memory rather
than going to disk again. This is much faster. But every few minutes, this buffer
pool is automatically cleared.
http://sqlavenger.wordpress.com/2011/05/23/clearing-the-plan-cache/
92. QUOTENAME Function
SELECT QUOTENAME
('Syntax-Example',
'"')
Output: "Syntax-Example"
SELECT
QUOTENAME('Syntax-Example'
,'''')
Output:
'Syntax-Example'
SELECT
QUOTENAME(
'Syntax-Example')
Output: [Syntax-Example]
93. How to catch the Dynamic
SQL return Value (Execute sp_executesql with return parameters)
Example1:
DECLARE @TempColumn
nvarchar
(100
)
= 'UserIDs'
,
@IDS nvarchar(
100),
@SQLQuery
NVARCHAR
(MAX)
SET
@SQLQuery = 'SELECT @TempIDs ='
+
@TempColumn
+
' FROM Companies
WHERE ( CompanyIntID = ''CM00000004'' ) AND (LEN(ISNULL( ' +
@TempColumn
+ ', ''''))>0)'
EXECUTE
SP_EXECUTESQL
@SQLQuery
,
N'@TempIDs nvarchar(100) OUTPUT'
,
@TempIDs = @IDs
OUTPUT
Example2:
DECLARE
@DynamicSQLOutPut TABLE(Value
NVARCHAR(MAX))
DECLARE
@Query NVARCHAR(MAX)
SET
@Query='
SELECT 2222'
INSERT
INTO @DynamicSQLOutPut
EXEC
(@Query)
SELECT
* FROM
@DynamicSQLOutPut
Example3:
--- Drop global temp table
IF
OBJECT_ID('tempdb..##GlobalTempTable_Consultants','u') IS
NOT NULL
DROP
TABLE ##GlobalTempTable_Consultants
DECLARE
@SQL_Query NVARCHAR(MAX)
SET
@SQL_Query = 'SELECT TOP 10 * INTO ##GlobalTempTable_Consultants FROM
Consultants'
EXEC(@SQL_Query)
SELECT
* FROM
##GlobalTempTable_Consultants
94. ISNUMERIC Check for
Real only
IsNumeric would be the obvious choice, but has some problems because it allows for
unexpected characters during the conversion. For example, the following strings
will return true from the IsNumeric function.
$12.09
1.4e3
2d4
If you want to allow fractional numbers, then you can add e0 to the isnumeric test.
Select
IsNumeric('$12.09'
+ 'e0')
Select IsNumeric('1.4e3'
+ 'e0')
Select
IsNumeric('2d4' + 'e0')
Select IsNumeric('3.7'
+ 'e0')
94.1. ISNUMERIC Check
for INT only
SELECT ConsIntID,
CAST(uuTemp
AS INT),
uuTemp
FROM Consultants
WHERE
LEN(uuTemp)
= 4 AND
ISNUMERIC(uuTemp+'.e0')
= 1
94.2. How to Use Regular
expression in sql query(Eg. Get Decimal Value from String Number)
SELECT ConsIntID,
CAST(uuTemp
AS INT),
uuTemp
FROM Consultants
WHERE
LEN(uuTemp)
= 4
AND
SubString(uuTemp,1,1)
LIKE '[1-9]'
AND
SubString(uuTemp,2,1)
LIKE '[1-9]'
AND
SubString(uuTemp,3,1)
LIKE '[1-9]'
AND
SubString(uuTemp,4,1)
LIKE '[1-9]'
95. How to print the messages
while Query is running(NOWAIT)
Prints get buffered, releasing "chunks" as the buffer fills up.
So use RAISERROR with serverity 0 and the NOWAIT option
DECLARE
@PRINTMessage
NVARCHAR(1000)
SET
@PRINTMessage =
CAST(@T_Count
AS
NVARCHAR
(100
))
+ ') ' +
REPLACE
(@T_ColumnName,
'%',''
)
+ ' - Done'
RAISERROR(@PRINTMessage
,0
,1
)
WITH NOWAIT
Note: Message can not have '%', it will think
it as string format. So replace '%' with '' if you have.
NOWAIT will return error if the original table has (transaction) locked on it.
NOLOCK will read the data irrespective of the (transaction) lock on it.
OR
WHILE
EXISTS
(SELECT 1
FROM [dbo].[Consultants]
WHERE uuResumeText_Clean
= 0)
BEGIN
UPDATE
TOP(1000) Consultants
SET uuResumeText
= dbo.CBO_CleanResumeText(uuResumeText), uuResumeText_Clean
= 1
WHERE uuResumeText_Clean
= 0
RAISERROR('Processed 1000 rows', 0, 1)
WITH
NOWAIT;
END
96. Checking Available
Drive Space from within SQL Server(Free Disk Space)
CREATE TABLE #drives
(drive
char, [free]
int)
INSERT INTO
#drives
EXEC master..xp_fixeddrives
SELECT
drive,
RTRIM
(CONVERT(
CHAR(50),([free]
/1024.0
)))
+
' GB' AS
FreeSpaceInGB
FROM#drives
DROP
TABLE #drives
SQL Server 2008 R2 SP1 and above:
SELECT
DISTINCT dovs.
volume_mount_point + ' '
+
RTRIM
(CONVERT(
CHAR(50),CONVERT
(REAL,
dovs.available_bytes
/1073741824.0
)))
+ ' GB'
AS
FreeSpaceInGB
FROM sys.
master_files mf
CROSS
APPLY sys.dm_os_volume_stats
(mf.
database_id,
mf.FILE_ID
)
dovs
97. Using Try and Catch
and ROLLBACK TRANSACTION(IMP)
BEGIN TRANSACTION
BEGIN
TRY
SET
QUOTED_IDENTIFIER OFF
SET
XACT_ABORT ON
-- When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error,
the entire transaction is terminated and rolled back.
-- This will help insufficient disk space erroe
-- some query here.
COMMIT
TRANSACTION
SET
QUOTED_IDENTIFIER ON
END
TRY
BEGIN
CATCH
ROLLBACK
TRANSACTION
DECLARE @Msg NVARCHAR(MAX)
SELECT @Msg=ERROR_MESSAGE()
RAISERROR('Error Occured: %s',
20, 101,@msg) WITH
LOG
END
CATCH
98.
Output results to text file using T-SQL(IMP)
DECLARE @OutputFile
NVARCHAR(100),
@FilePath NVARCHAR(100), @bcpCommand NVARCHAR(4000)
DECLARE @SQL
NVARCHAR(MAX) =
'SELECT * FROM sys.objects'
SET @bcpCommand = 'bcp "'+ @SQL +'
" queryout '
SET @FilePath = 'D:'
SET @OutputFile = 'FileName1.txt'
SET @bcpCommand = @bcpCommand + @FilePath
+ @OutputFile +
' -c -t, -T -S'+
@@servername
exec
master..xp_cmdshell @bcpCommand
99.
View the full content of a text or varchar(MAX) column in SQL
Server Management Studio(IMP)
DECLARE @S varchar(max)
SELECT @S
= 'Large Data'
SELECT @S
= @S + '
'
+ OBJECT_DEFINITION(OBJECT_ID)
FROM SYS.PROCEDURES
SELECT @S
AS [processing-instruction(x)] FOR XML PATH('')
Complete XML from Table:
1. Example:
SELECT
top 10 *
FROM
[dbo].[Consultants] FOR
XML AUTO
2.Example:
SELECT
top 10 *
FROM
[dbo].[Consultants] FOR
XML PATH
Column Data in XML from Table:
SELECT
ReqIntID, CAST((dbo.CBO_RemoveControlCharacters_which_Are_Not_Allowed_In_XML(Description))
AS XML)
AS Description
FROM
Requirements
CBO_RemoveControlCharacters_which_Are_Not_Allowed_In_XML
100.
Export Table Creation Schema with Default Values Query(IMP)
1.
Generate_Create_Table_Script
100.1
Bad habits to kick : avoiding
the schema prefix(IMP)
Without an explicit schema, SQL Server will first look under the schema associated
with your login. This can cause problems, obviously, if you have a table called
your_default_schema.foo and there is also a table in the database called dbo.foo.
The DEFAULT_SCHEMA and use dbo by default.
101.
Are there any disadvantages to always using nvarchar(MAX)?
1. 100 characters stored in an nvarchar(MAX) field will be stored no different to
100 characters in an nvarchar(100) field - the data will be stored inline and you
will not have the overhead of reading and writing data 'out of row'. So
no worries there.
2. If the size is greater than 4000 the data would be stored 'out of row'
automatically, which is what you would want. So no worries there either. However...
3. You cannot create an index on an nvarchar(MAX) column. You can use full-text
indexing, but you cannot create an index on the column to improve query performance.
For me, this seals the deal...it is a definite disadvantage to always use nvarchar(MAX).
102.
Convert Table Rows to Columns (IMP)
DECLARE @GivenTable TABLE(
ColumnName NVARCHAR(MAX)
NULL,
Column1 NVARCHAR(MAX)
NULL,
Column2 NVARCHAR(MAX)
NULL
)
INSERT INTO @GivenTable
SELECT 'ColumnName','Column1','Column2'
INSERT @GivenTable
SELECT *
FROM
(
SELECT 'Row1'
AS ColumnName, 'A1'
as Column1, 'B1' as Column2
UNION ALL
SELECT 'Row2'
AS ColumnName, 'A2'
as Column1, 'B2' as Column2
UNION ALL
SELECT 'Row3'
AS ColumnName, 'A3'
as Column1, 'B3' as Column2
UNION ALL
SELECT 'Row4'
AS ColumnName, 'A4'
as Column1, 'B4' as Column2
UNION ALL
SELECT 'Row5'
AS ColumnName, 'A5'
as Column1, 'B5' as Column2
UNION ALL
SELECT 'Row6'
AS ColumnName, 'A6'
as Column1, 'B6' as Column2
) TEMP
DECLARE @cols AS
NVARCHAR(MAX),
@Query AS
NVARCHAR(MAX)
SELECT @cols =
STUFF((SELECT
',' + QUOTENAME([dbo].[CBO_RemoveControlCharacters_Which_Are_Not_Allowed_In_XML](ColumnName))
from @GivenTable
FOR XML
PATH(''), TYPE
).value('.',
'NVARCHAR(MAX)')
,1,1,'')
SELECT *
INTO #newtable
FROM @GivenTable
SELECT *
from #newtable
SET @Query =
N'SELECT ' + @cols
+ N' from
(
SELECT ''Column1'' as ColumnNo, Column1, ColumnName AS ColumnName_T
from #newtable UNION ALL
SELECT ''Column2'' as ColumnNo, Column2, ColumnName from
#newtable
)
x
pivot
(
MAX(Column1) for ColumnName_T in ('
+ @cols + N')
)
p '
exec sp_executesql
@Query;
DROP TABLE #newtable
103.
List All The Column With Specific Data Types in Database
SELECT *
FROM
information_schema.columns
104.
Replace Repeated Characters with Single Character
DECLARE @Procedures
TABLE (IDCol INT
IDENTITY(1,1), ProcedureCode
NVARCHAR(MAX))
INSERT
INTO @Procedures
VALUES('')
PDATE @Procedures SET ProcedureCode =
'AAAAA'
WHERE IDCol
= 1
WHILE((SELECT COUNT(1) FROM @Procedures
WHERE ProcedureCode LIKE
'%AA%')
> 0)
UPDATE @Procedures SET ProcedureCode =
REPLACE(ProcedureCode,'AA','A') WHERE IDCol = 1
SELECT ProcedureCode FROM @Procedures WHERE
IDCol = 1
105.
Email Usage in cBizOne/Exelare
Example: Let us take a Field "uuAssistantEMail" then
to make it as Email Usage, we gave in Usage column as "uuAssistantEMail,EMail,Secondary"
This is not working, as Kris said he HardCoded first to Words "Email1,Email
/ Email2,Email" so said to Give us as like "EMail2,EMail,Secondary"
106.
Candidate Status LookUp Values in cBiz and Exelare
SELECT *
FROM Lookup
WHERE LookupFieldName
LIKE 'Reminders.ApptSubType,Status,Status%'
ORDER
BY LookupFieldName
107.
Reminder Column Names (Frontend) Differ from Database Table Name Fields
Date - Reminders.StartDateTime
Start Date - Reminders.EndDateTime
End Date - Reminders.RemDateTime
108.
INSERTING data from Linked Server (IMP)
Note: Inserting large data from Linked server is more time
consuming, since each and every Insert will be checked for Secutity Permission.
Solution Is: Insert N Number of Rows as one single Row on
Linked Server. At Linked Server Run that Query.
UPDATE [N80STL-SQL2.NETWORK80.COM].[cBizSoft].[dbo].[Vishnu_Update_Table_Job_Queries]
SET [Query] =
'SET QUOTED_IDENTIFIER OFF;
DELETE [cBizSoft].[dbo].[Vishnu_cBizOneTicketingDBEmailCount]
INSERT INTO [cBizSoft].[dbo].[Vishnu_cBizOneTicketingDBEmailCount]
VALUES
'
+ (
SELECT
STUFF(
(SELECT
(SELECT ',
'
WHERE
(RowNumber
% 1000 != 0)
UNION
SELECT
'
INSERT INTO
[cBizSoft].[dbo].[Vishnu_cBizOneTicketingDBEmailCount]
VALUES'
WHERE (RowNumber
% 1000 = 0)) + [dbo].[CBO_RemoveControlCharacters_Which_Are_Not_Allowed_In_XML](Col1)
FROM
(
SELECT
ROW_NUMBER() OVER
(ORDER
BY [SentToDisplayName])
AS 'RowNumber',
'("' +
[EMail] + '","'
+ [SentToDisplayName] +
'","' +
[UserIDs] + '","'
+ 'EMailsSent'
+ '",'
+ CAST(COUNT(1) AS
NVARCHAR(10))
+ ')' AS Col1
FROM [cBizOne].[dbo].[EMailsSent]
WHERE
LEN([EMail])
> 0 AND LEN([SentToDisplayName]) > 0
AND LEN([UserIDs]) >
0 AND [EMail] LIKE
'%.%' AND
[EMail] LIKE '%@%'
AND
([EMail]
+ [SentToDisplayName] + [UserIDs]
NOT LIKE '%"%')
GROUP
BY [EMail],[SentToDisplayName],[UserIDs]
) TEMP
ORDER
BY RowNumber FOR XML
PATH(''),
TYPE).value('.',
'NVARCHAR(MAX)')
,1,3,'')
)
+ '
UPDATE [cBizSoft].[dbo].[Vishnu_Update_Table_Job_Queries] SET [Query] = ''''
WHERE ([Table_Name] = ''Vishnu_cBizOneTicketingDBEmailCount'' AND
[Type] = ''EMailsSent'')
'
WHERE
([Table_Name]
= 'Vishnu_cBizOneTicketingDBEmailCount'
AND [Type] = 'EMailsSent')
Note: We are Inserting 1000 Rows at a Time as it is the Limitation in TSQL.
109.
Repeat a String No. Number of Times
SELECT REPLICATE('A',10)
110.
You Can Avoid Using time taking "dbo.GetReminderLinks('Status','Status',NULL)" in FROM Clause(IMP)
SELECT Reminders.RemIntID,Reminders.ApptType
as Type,Reminders.ApptSubType as
SubType,Reminders.Subject,Reminders.Notes,Reminders.StartDateTime AS
ActivityDate,Reminders.Done,
SubString(Reminders.UserIDs,2,Len(Reminders.UserIDs)-2)
as UserID,Consultants.ConsIntID,Consultants.Displayname as
Consultant,Consultants.JobTitle,
Contacts.ContactIntID,Contacts.DisplayName
as Contact,Contacts.CompanyName,Contacts.DirectPhone,
Requirements.ReqIntID,Requirements.ReqID
as JobID,Requirements.JobTitle as JobJobTitle, Consultants.UserField8
as Sourcer
FROM Reminders LEFT JOIN
Consultants ON ((SELECT COUNT(1)
FROM Links L1 WHERE Reminders.RemIntID=L1.FromID
AND L1.ToID=Consultants.ConsIntID) > 0)
LEFT JOIN
Contacts ON ((SELECT COUNT(1)
FROM Links L2 WHERE Reminders.RemIntID=L2.FromID
AND L2.ToID=Contacts.ContactIntID) > 0)
LEFT JOIN
Requirements ON ((SELECT COUNT(1)
FROM Links L3 WHERE Reminders.RemIntID=L3.FromID
AND L3.ToID=Requirements.ReqIntID) > 0)
WHERE Reminders.RemType='Appointment'
AND Reminders.ApptType='Interview'
/* AND ([[Reminders.ThisUserCanSee]]) */
/* AND ([[Consultants.ThisUserCanSee]]) */
/* AND ([[Consultants.UserField8]]) */
/* AND ([[Reminders.StartDateTime]]) */
/* AND ([[Reminders.UserIDs]]) */
/* AND ([[Reminders.Done]]) */
111.
NULLIF
SELECT UserField1,
CreateDate, COALESCE(NULLIF(UserField1,''),CreateDate)
FROM Requirements
DECLARE @Test nvarchar(10)
= NULL
SELECT
NULLIF(@Test,'')
SELECT
NULLIF('','')
SELECT
NULLIF('Vishnu','')
SELECT
NULLIF('Vishnu','Vishnu')
SELECT
NULLIF(0,1)
112.
If Requirements Linked Then Only Open Requirements
SELECT
Count(*) as XCount,Consultants.Owners,max('Offers') as
What
FROM Reminders,
Links b, Consultants
WHERE Reminders.RemType='Status'
AND Reminders.ApptType='Status'
AND
(Reminders.ApptSubType = 'Offer' OR
Reminders.ApptSubType =
'Hired')
AND
(Reminders.RemIntID=b.FromID
and b.ToID=Consultants.ConsIntID)
/* AND ([[Reminders.ThisUserCanSee]])
*/
/* AND ([[Consultants.ThisUserCanSee]])
*/
/* AND ([[Consultants.Owners]])
*/
/* AND ([[Consultants.UserIDs]])
*/
/* AND ([[Reminders.StartDateTime]]) */
AND
(
SELECT SUM(XCount)
FROM
(
SELECT COUNT(1)
AS XCount WHERE (SELECT COUNT(1)
FROM Links WHERE
(Links.FromID
= Reminders.RemIntID) AND
(Links.ToID
LIKE 'RQ%')) = 0
UNION ALL
SELECT COUNT(1)
AS XCount FROM Requirements,
Links WHERE (Links.FromID = Reminders.RemIntID)
AND (Requirements.ReqIntID =
Links.ToID)
AND (Requirements.ClosedDate
> GetDate() OR
IsDate(Requirements.ClosedDate)=0)
) TTT
) = 1
GROUP BY
Consultants.Owners
113.
Importing a large column (8000 chars) from Excel using SSIS (SQL Server Table Column Nvarchar(MAX))
Error Message:
Outputs[Excel Source Output].Columns[notes]" specifies
failure on truncation. A truncation error occurred on the specified object of the
specified component.
(SQL Server Import and Export Wizard)
Help Link:
http://dba.stackexchange.com/questions/47248/importing-a-large-column-8000-chars-from-excel-using-ssis
We finally resolved the issue.
It turns out that SSIS calculates the length based on the first handful of rows
in the excel file.
When we moved the rows with the longer data to the top the columns changed to unicode
text (allowing for the extra length).
113.1.
Not enough resources to display completely in Excel
Click on start>all programs->accessories->run>Excel /safe and check what
happens.
Note: There is a space before the ‘/ ‘.
If it works fine in the safe mode, the issue might be due to add-ins installed.
114.
Report Customization Hours Calculation(Format):
Report Customization Hours Calculation:
Report Name:
SQL Type (Normal/Complex):
Query Preparation Time:
Procedures/Functions/Views Needed:
XML Customization and filters:
Research Work:
Testing:
Changes in Requirement:
Total Hours to Develop:
115.
Changes the owner of the current database:
ALTER
AUTHORIZATION ON
DATABASE::Exl_addingt
TO cBiz_cbizsoft
116.
Make Sure assigen Empty String before Select Assignement in WHILE Loop(IMP):
DECLARE @OLD_ConsIntID NVARCHAR(max)
SET
@OLD_ConsIntID = 'Vishnu'
SELECT
TOP 1 @OLD_ConsIntID =
'Murthy'
WHERE
1=11
SELECT
@OLD_ConsIntID
117.
Drop Replication Column 'msrepl_tran_version' on non Replicated Database:
SELECT
'IF EXISTS
(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''' + DC.NAME
+ ''')
and Type = ''D'')
ALTER
TABLE ['
+ OBJECT_SCHEMA_NAME(SO.ID)
+ '].['
+ SO.NAME + '] DROP CONSTRAINT ['
+ DC.NAME + ']'
FROM
SysObjects SO
INNER
JOIN SysColumns
SC ON SO.ID = SC.ID
INNER
JOIN sys.default_constraints
DC ON SO.ID = DC.Parent_object_id
AND SC.colid
= DC.Parent_column_id
WHERE
SO.XTYPE = 'U'
AND SC.NAME
= 'rowguid'
UNION
ALL
SELECT
'DROP INDEX [' +
sys.tables.name +
'].[' + sys.indexes.name
+ ']'
FROM
sys.tables, sys.indexes,
sys.index_columns, sys.columns
WHERE (sys.tables.object_id
= sys.indexes.object_id
AND sys.tables.object_id
= sys.index_columns.object_id AND
sys.tables.object_id
= sys.columns.object_id
AND
sys.indexes.index_id =
sys.index_columns.index_id AND
sys.index_columns.column_id =
sys.columns.column_id)
AND
sys.columns.name =
'rowguid'
UNION
ALL
SELECT
'IF EXISTS (SELECT * FROM dbo.syscolumns where id = OBJECT_ID(N'''
+ SO.NAME + ''') and Name = ''rowguid'')
ALTER TABLE ['
+ OBJECT_SCHEMA_NAME(SO.ID)
+ '].['
+ SO.NAME + '] DROP COLUMN [rowguid] '
FROM
SysObjects SO
INNER
JOIN SysColumns
SC ON SO.ID = SC.ID
WHERE
SO.XTYPE = 'U'
AND SC.NAME
= 'rowguid'
ORDER
BY 1
118.
If Requirement Contact is Changed, then also change the Linked Status Contact:
UPDATE L1 SET L1.ToID = Requirements.LinkToIntID
FROM
Reminders, Links b,
Links d, Links l1,
Contacts, Requirements
WHERE
Reminders.RemType='Status' AND
Reminders.ApptType='Status' AND
(Reminders.RemIntID=b.FromID
and b.ToID=Contacts.ContactIntID) AND
(Reminders.RemIntID=d.FromID
and d.ToID=Requirements.ReqIntID) AND
(L1.FromID = Reminders.RemIntID AND L1.ToID LIKE
'CN%') AND
(L1.ToID != Requirements.LinkToIntID)
119.
How to query the names of encrypted stored procedures?
SELECT
ROUTINE_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_DEFINITION IS
NULL
ORDER
BY ROUTINE_NAME ASC
119.
Logical Processing Order of the SELECT statement.
-
FROM
-
ON
-
JOIN
-
WHERE
-
GROUP BY
-
WITH CUBE or WITH ROLLUP
-
HAVING
-
SELECT
-
DISTINCT
-
ORDER BY
-
TOP
119.1.
With RollUp
select CName,
sum(Value) as Value, sum(Value1)
as Value1
from
(
select
'aaa' as CName, 10 Value, 10
Value1
union
all
select
'bbb' as CName, 10 Value, 34
Value1
union
all
select
'ccc' as CName, 10 Value, 22
Value1
union
all
select
'aaa' as CName, 23 Value, 44
Value1
union
all
select
'bbb' as CName, 3 Value, 37
Value1
union
all
select
'ccc' as CName, 14 Value, 14
Value1
)
t
group
by CName
WITH
ROLLUP
121.
Difference between ntext , text, and image data types Vs nvarchar(max), varchar(max), and varbinary(max)
ntext , text, and image data types will be removed in a
future version of Microsoft SQL Server. Avoid using these data types in new development
work, and plan to modify applications that currently use them. Use nvarchar(max),
varchar(max), and varbinary(max) instead.
122.
Free SQL Server Health Check (sp_Blitz).
Download the Following File and run the procedures on you Local Database
Only (Not on Hosting Server)
First Responder Kit 20161115.
1. sp_Blitz.sql
EXEC
[dbo].[sp_Blitz]
2. sp_BlitzCache.sql
EXEC
dbo.sp_BlitzCache
3. sp_BlitzWho
4. sp_BlitzFirst.sql
EXEC
[dbo].[sp_BlitzFirst]
5. sp_BlitzIndex.sql
EXEC
dbo.sp_BlitzIndex
6. sp_BlitzTrace.sql
EXEC
sp_BlitzTrace @SessionId
= @@SPID,
@Action = 'start', @TargetPath =
'D:\Main\SQL\XEvents\Traces\'
GO
SELECT
name, state_desc
FROM
sys.databases
GO
EXEC
sp_BlitzTrace @Action =
'stop'
GO
EXEC
sp_BlitzTrace @Action =
'read'
GO
EXEC
sp_BlitzTrace @Action =
'drop'
GO
123.
Delete BackUp file from the server.
EXECUTE master.dbo.xp_delete_file 0,N'G:\1DemoDB_Vishnu.bak'
124.
Learn to use Union, Intersect, and Except Clauses.
Example: Learn to use Union, Intersect, and Except Clauses
125.
Get a List of all Primary Keys in a Database with IndexType (NONCLUSTERED/CLUSTERED).
SELECT TABLE_NAME, ISNULL(CONSTRAINT_NAME,
TABLE_NAME + '_PK') AS
CONSTRAINT_NAME,
ISNULL(COLUMN_NAMES, (SELECT
TOP 1 '['
+ COLUMN_NAME +
']' FROM
information_schema.COLUMNS WHERE
TABLE_NAME = TEMP1.TABLE_NAME
AND ORDINAL_POSITION =
1)) AS
COLUMN_NAMES,
ISNULL((SELECT 'WITH DROP_EXISTING' WHERE CONSTRAINT_NAME IS
NOT NULL),'')
AS With_Condition,
ISNULL(CAST((SELECT TOP 1 'CLUSTERED' FROM sys.indexes
WHERE (OBJECT_NAME(object_id)
= TABLE_NAME AND
type_desc = 'CLUSTERED')) AS
NVARCHAR(max)),
'NONCLUSTERED')
AS IndexType
FROM
(
SELECT
TABLE_NAME,
(SELECT
TOP 1 CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME =
T.TABLE_NAME)
AS CONSTRAINT_NAME,
(
STUFF((
SELECT ', [' +
COLUMN_NAME + ']'
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = T.TABLE_NAME
ORDER BY TABLE_NAME, ORDINAL_POSITION
FOR XML PATH('')), 1, 2,
'')
)
AS COLUMN_NAMES
FROM
information_schema.TABLES T
WHERE
(T.TABLE_TYPE = 'BASE TABLE')
)
TEMP1
126.
Alter user dbo.
Alter a SQL user’s database account; more specifically speaking a Login name’s
database user name
Use [database_name]
go
sp_changedbowner
'cBiz_cbizsoft'
127.
Using MERGE in SQL Server to insert, update and delete at the same time
In a typical data warehousing application, quite often during the ETL cycle you
need to perform INSERT, UPDATE and DELETE operations on a TARGET table by
matching the records from the SOURCE table.
Beginning with SQL Server 2008, now you can use MERGE SQL command to perform
these operations in a single statement.
The MERGE statement basically merges data from a source result set to a target
table based on a condition that you specify.
Note: Key Words
MERGE, BY
TARGET AND BY
SOURCE
Example:
--Create a Target table
DECLARE @Target_Table
TABLE (ProductID
INT PRIMARY
KEY, ProductName
NVARCHAR(100), Rate MONEY)
INSERT INTO
@Target_Table
VALUES (1, 'Tea', 10.00), (2, 'Coffee', 20.00), (3, 'Muffin', 20.00), (4, 'Biscuit', 40.00)
--Create Source table
DECLARE @Update_Insert_Delete_Table
TABLE (Operation NVARCHAR(100), ProductID
INT, ProductName
NVARCHAR(100), Rate MONEY
)
INSERT
INTO @Update_Insert_Delete_Table
VALUES ('Update', 3, 'Muffin', 30.00),
('Delete', 1,
'Tea', 10.00), ('Insert', 5, 'Pizza', 60.00)
SELECT *
FROM @Target_Table
SELECT *
FROM @Update_Insert_Delete_Table
-----------------------
--Synchronize
the TARGET table with
--refreshed data from SOURCE table
MERGE @Target_Table
AS T
USING @Update_Insert_Delete_Table
AS S
ON
(T.ProductID
= S.ProductID)
--When records are matched, update
--the records if there is any change
WHEN MATCHED
AND
(S.Operation
= 'Update') THEN
UPDATE SET
T.Rate = S.Rate
--When no records are matched, insert
--the incoming records from SOURCE
--table to TARGET table
WHEN NOT
MATCHED BY
TARGET AND S.Operation =
'Insert' THEN
INSERT
(ProductID, ProductName,
Rate)
VALUES
(S.ProductID, S.ProductName, S.Rate)
--When there is a row that exists in TARGET table
and
--same record does not exist in SOURCE table
--then delete this record from TARGET table
WHEN MATCHED
AND S.Operation
= 'Delete'
THEN
DELETE
--$action specifies a column of type
nvarchar(10)
--in the OUTPUT clause that returns one of
three
--values for each row: 'INSERT', 'UPDATE', or
'DELETE',
--according to the action that was performed
on that row
OUTPUT
$action,
DELETED.*, INSERTED.*;
SELECT *
FROM @Target_Table
1. MERGE statement with join conditions that produce unexpected results.
MERGE Target AS T USING Source AS S
ON (T.EmployeeID = S.EmployeeID AND T.EmployeeName LIKE 'S%' AND S.EmployeeName
LIKE 'S%' )
WHEN NOT MATCHED BY TARGET THEN
INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED THEN
UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE THEN
DELETE OUTPUT $action, Inserted.*, Deleted.*;
Solution:
Reducing the number of rows in the input stream early in the process by
specifying the additional search condition to the ON clause (for example, by
specifying ON
T.EmployeeID = S.EmployeeID AND T.EmployeeName LIKE 'S%' AND S.EmployeeName LIKE 'S%' ) might seem to improve query performance.
However, doing so can cause unexpected and incorrect results. Because the
additional search conditions specified in the ON clause are not used for
matching the source and target data, they can be misapplied.
2. INSERT, UPDATE, DELETE Order of Execution in Merge.
For every insert, update, or delete action specified in the MERGE statement, SQL
Server fires any corresponding AFTER triggers defined on the target table, but
does not guarantee on which action to fire triggers first or last.
It expects a unique value to be compared.
Help Link
128.
Getting the actual data size per row in a SQL Server table
declare @table
nvarchar(128)
declare @idcol
nvarchar(128)
declare
@sql nvarchar(max)
--initialize those two values
set
@table = 'Consultants'
set
@idcol = 'ConsIntID'
set
@sql = 'select '
+ @idcol
select
@sql = @sql +
', isnull(datalength(' +
name + '), 1) as '
+ name
from sys.columns
where object_id
= object_id(@table)
set
@sql = @sql +
', (0'
select
@sql = @sql +
' + isnull(datalength('
+ name + '), 1)'
from sys.columns
where object_id
= object_id(@table)
set
@sql = @sql +
') as rowsize from ' +
@table + ' order by
rowsize desc'
--PRINT @sql
exec
(@sql)
129.
Get all UserID's from a GroupName for View with LoggedInUser.
SELECT *
FROM
XUserGroups
WHERE
(GroupType = 'Departments')
ORDER
BY GroupName
select
*
FROM
XUserGroups G1,XUserGroups G2
WHERE
(G1.GroupType =
'Departments')
AND
(',' + G1.UserID + ','
LIKE [LoggedInUser]) AND
(G2.GroupName = G1.GroupName) AND
(G2.GroupType
= G1.GroupType)
130.
Get client IP address in the Database record 'client_net_address'.
ALTER table
[dbo].[SaveSets] add
[SQLLoginID] [nvarchar]
(40)
COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
ALTER
TABLE [dbo].[SaveSets]
WITH NOCHECK
ADD
CONSTRAINT
[DF_AuditTrailCompanies_SQLLoginID] DEFAULT
(CAST(CONNECTIONPROPERTY('client_net_address') AS
NVARCHAR(40))) FOR
[SQLLoginID]
131.
Import .CSV or .xls in to a table and insert from there to Consulants.
INSERT INTO
[dbo].[Consultants]
([ConsIntID], [ConsultantID],[UserField1],
[UserField2],[FirstName], [LastName] ,[Email1], [MobilePhone],[JobTitle]
,[VisaStatus],[ResumeFile], [UserIDs] ,[CreateDate],[EditDate])
SELECT
ConsIntid, ConsIntid AS
ConsultantID,[Resume_ID],Resume_File1, [First_Name], [Last_Name],[Email_ID],
[Phone], [Resume_Title],
[Work_Status], [Resume_File], USERIDS,
CreateDate, EditDate
FROM
(
SELECT 'CS'
+ REPLICATE('0',8-LEN(RowNumberID)) + RowNumberID
AS ConsIntid,
ConsultantID,[Resume_ID],[Resume_File] as Resume_File1,
[First_Name], [Last_Name], replace([Email_ID],'''',
'')
as [Email_ID],
[Phone], [Resume_Title],
[Work_Status],
replace([Resume_File], 'CbizResumes\', '') as [Resume_File], ',Admin,'
as USERIDS,
getdate()
As CreateDate,
getdate()
as EditDate
FROM
(
SELECT
cast(ROW_NUMBER()
OVER (ORDER BY
[Resume_ID] desc)
as nvarchar(max))
AS 'RowNumberID', *
from
[EproCanSQL]
)TT
)KK
DECLARE @Countt
INT
SET
@Countt = 1
WHILE(@Countt < 100000)
BEGIN
SET
NOCOUNT ON;
UPDATE
TOP (1) [dbo].[Consultants]
SET ConsultantID =
[dbo].[GenerateConsultantID](FirstName,
LastName)
WHERE
ConsultantID LIKE
'CS00%'
SET
@Countt = @Countt +
1
END
132.
Update dice Account information.
SELECT *
FROM
[Exl_Aquinas].[dbo].[Users]
WHERE
RecType = '01'
and [AppPermissions]
like '%;Summer2019!;%'
SELECT
[AppPermissions],
REPLACE(CAST([AppPermissions] AS
NVARCHAR(MAX)),';Summer2018!;',';Summer2019!;')
FROM
[Exl_Aquinas].[dbo].[Users]
WHERE
RecType = '01'
and [AppPermissions]
like '%;Summer2018!;%'
UPDATE TOP
(1)
[Exl_Aquinas].[dbo].[Users]
set [AppPermissions] =
REPLACE(CAST([AppPermissions]
AS NVARCHAR(MAX)),';Summer2018!;',';Summer2019!;')
WHERE
RecType = '01'
and [AppPermissions]
like '%;Summer2018!;%'
133.
Turning a Comma Separated string into individual rows.
;WITH tmp(ConsIntID, DataItem, Categories)
AS
(
SELECT ConsIntID,
CAST(LEFT(Categories,
CHARINDEX(',', Categories
+ ',') - 1) AS
NVARCHAR(MAX)),
STUFF(Categories, 1,
CHARINDEX(',', Categories
+ ','), '')
FROM [dbo].[Consultants]
WHERE LEN(ISNULL(Categories,
''))
> 0
UNION all
SELECT ConsIntID,
CAST(LEFT(Categories,
CHARINDEX(',', Categories
+ ',') - 1) AS
NVARCHAR(MAX)),
STUFF(Categories, 1,
CHARINDEX(',', Categories
+ ','), '')
FROM tmp
WHERE Categories > ''
)
SELECT
DataItem AS Categories,
COUNT(1) AS Count1
FROM
tmp
GROUP
BY DataItem
134.
using Table variable with sp_executesql.(IMP IMP)
if exists (select *
from sys.types
where name =
'TestTableType')
drop
type TestTableType
create
type TestTableType as
table (id
int)
GO
declare
@t TestTableType
insert
@t select 6*7
exec
sp_executesql
N'select * from @var',
N'@var TestTableType readonly', @t
135.
Create read only User for database.
USE master;
GO
CREATE LOGIN Vishnu_Exl_VishnuTest1_Read WITH PASSWORD = '******';
GO
USE [Exl_VishnuTest1];
GO
CREATE USER Vishnu_Exl_VishnuTest1_Read FOR LOGIN Vishnu_Exl_VishnuTest1_Read;
GO
ALTER ROLE db_datareader ADD MEMBER Vishnu_Exl_VishnuTest1_Read;
GO
DENY INSERT, UPDATE, DELETE, EXECUTE TO Vishnu_Exl_VishnuTest1_Read;
GO
