SET ROWCOUNT 1
DELETE FROM Table1
WHERE col1 = ‘0001’
Sqlserver notes : Issue – The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).
October 19, 2015Sqlserver notes
October 16, 2015Before deleting data from production server follow these steps. Its a good practice
select * from WriteOff_ReworkUsers
where Ref_ID in (189538,201177,201185,203533,203534,203563,203567)
begin tran
delete from WriteOff_ReworkUsers
where Ref_ID in (189538,201177,201185,203533,203534,203563,203567)
rollback tran
begin tran
delete from WriteOff_ReworkUsers
where Ref_ID in (189538,201177,201185,203533,203534,203563,203567)
commit tran
SqlServer notes : sqlserver script to find a string in entire database
April 1, 2015DECLARE @SQL VARCHAR(MAX)
DECLARE @SearchString VARCHAR(100)
SET @SQL=”
— ——————————————
— Enter the string to be searched for here :
—
SET @SearchString=’bracket’
— ——————————————
SELECT @SQL = @SQL + ‘SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + ” matches in column ”+”’
+ C.name + ”’+” on table ” + ”’ + SC.name + ‘.’ + T.name +
”’ [Matches for ”’+@SearchString+”’:] FROM ‘ +
QUOTENAME(SC.name) + ‘.’ + QUOTENAME(T.name) + ‘ WHERE ‘ + QUOTENAME(C.name) +
‘ LIKE ”%’ + @SearchString +
‘%” HAVING COUNT(*)>0 UNION ALL ‘ +CHAR(13) + CHAR(10)
FROM sys.columns C
JOIN sys.tables T
ON C.object_id=T.object_id
JOIN sys.schemas SC
ON SC.schema_id=T.schema_id
JOIN sys.types ST
ON C.user_type_id=ST.user_type_id
JOIN sys.types SYST
ON ST.system_type_id=SYST.user_type_id
AND ST.system_type_id=SYST.system_type_id
WHERE SYST.name IN (‘varchar’,’nvarchar’,’text’,’ntext’,’char’,’nchar’)
ORDER BY T.name, C.name
— Strip off the last UNION ALL
IF LEN(@SQL)>12
SELECT @SQL=LEFT(@SQL,LEN(@SQL)- 12)
EXEC(@SQL)
–PRINT @SQL
Sqlserver : Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created.
October 8, 2014Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created.
http://www.sql-server-performance.com/2009/saving-changes-not-permitted/
ERROR:
“Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”
This error happens because “Saving Changes is not permitted” when using SQL Server 2008 Management Studio to modify the structure of a table especially when SQL Server needs to drop and recreate a table to save the structural changes. It is always advised to make a structural change for a table using TSQL. However, it is a convenient option for database developers to use SQL Server Management Studio make such changes as the Prevent Saving Changes That Require Table Re-creation option is enabled by default in SQL Server 2008 Management Studio.
Disable “Prevent saving changes that require the table re-creation”
1. Open SQL Server 2008 Management Studio (SSMS). Click Tools menu and then click on Options… as shown in the snippet below.
2. In the navigation pane of the Options window, expand Designers node and select Table and Database Designers option as shown in the below snippet. Under Table Options you need to uncheck “Prevent saving changes that require the table re-creation” option and click OK to save changes.
This option when enabled prevents users from making structural changes to table using SQL Server Management Studio especially when SQL Server needs to recreate the table to save changes. By default, this option is checked and you need to uncheck this option to allow users to make any structural change through SSMS that require table recreation.
SqlServer notes : Inbuilt reports provided by Sqlserver itself
March 13, 2014In Object Explorer
right click on server name and select Reports->Standard Reports->
Performance -Top Queries by Average CPU Time
SQL SERVER – Comma Separated Values (CSV) from Table Column
January 22, 2014Is there a way to convert a single column record set such as
1
2
3
into ‘1,2,3’ string in SQL Server?
1st way :
CREATE FUNCTION [dbo].[fn_MyFunction] ( ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @str NVARCHAR(MAX) DECLARE @Delimiter CHAR(2) SET @Delimiter = ', ' SELECT @str = COALESCE(@str + @Delimiter,'') + AColumn FROM dbo.myTable RETURN RTRIM(LTRIM(@str)) END
2nd Way :select stuff( (select ',' + YourColumn from YourTable for xml path('')), 1, 1, '')
3rd Way :DECLARE @Out VARCHAR(2048) Select @Out = COALESCE(@Out + ', ', '') + [YourColumn] from YourTable print @Out
4th way Best one : SELECT SUBSTRING( (SELECT ',' + s.Name FROM HumanResources.Shift s ORDER BY s.Name FOR XML PATH('')),2,200000) AS CSV
Sqlserver notes : How to convert a recordset to a delimited string in SQL Server
November 13, 2013 DECLARE @str NVARCHAR(MAX)
DECLARE @Delimiter CHAR(2)
SET @Delimiter = ', '
SELECT @str = COALESCE(@str + @Delimiter,'') + AColumn
FROM dbo.myTable
RETURN RTRIM(LTRIM(@str))