Execute Script Using CMDSQL

Large scripts are hard to open in SQL Server Management Studio. These scripts can be easily executed through PowerShell as following:

 Invoke-Expression 'sqlcmd -S ServerNameHere -d DatabaseNameHere -U UserNameHere -P PasswordHere -i "D:\MyScript.sql"'

1-Place your script at drive as SQL

2-Execute PowerShell from SSMS

3-Paste above mentioned script after making required changes and press enter

SQL Server vNext and Discontinued Featuers

SQL Server vNext CTP2.0 (Community Technology Preview) is already released by Microsoft and we are expecting that SQL Server 2017 will be launched before September 2017.

 

Latest SQL Server stable version in market:

SQL Server 2016 (Version:13)

As per new rules since SQL Server 2016, only last two versions will remain compatible and any deprecated feature will not remain functional by only changing compatibility level.

Following are the main features which are deprecated in SQL Server 2016 and will not be available in SQL Server vNext.

Data Types:

  • text
  • ntext
  • image

Alternate: Use varchar(max), nvarchar(max), and varbinary(max) data types.

System Tables:

  • sysaltfiles
  • syscacheobjects
  • syscolumns
  • syscomments
  • sysconfigures
  • sysconstraints
  • syscurconfigs
  • sysdatabases
  • sysdepends
  • sysdevices
  • sysfilegroups
  • sysfiles
  • sysforeignkeys
  • sysfulltextcatalogs
  • sysindexes
  • sysindexkeys
  • syslockinfo
  • syslogins
  • sysmembers
  • sysmessages
  • sysobjects
  • sysoledbusers
  • sysopentapes
  • sysperfinfo
  • syspermissions
  • sysprocesses
  • sysprotects
  • sysreferences
  • sysremotelogins
  • sysservers
  • systypes
  • sysusers

 

Table hints:

Specifying table hints without using the WITH keyword. Example SELECT * FROM PMPTXFT (NOLOCK)

Alternate: Use WITH clause: SELECT * FROM PMPTXFT WITH (NOLOCK)

Textpointers:

  • WRITETEXT
  • UPDATETEXT
  • READTEXT
  • TEXTPTR()
  • TEXTVALID()

Transact-SQL

1-A string enclosed in quotation marks used as a column alias for an expression in a SELECT list:

string_alias‘ = expression

Alternate:

  • expression [AS] column_alias
  • expression [AS] [column_alias]
  • expression [AS] “column_alias”
  • expression [AS] ‘column_alias’
  • column_alias = expression

2-Not ending Transact-SQL statements with a semicolon.

Alternate: End Transact-SQL statements with a semicolon ( ; ).

 

Most important SQL Server Profiler is no more available in future. You have to learn Extended Events (EE).

Database Mirroring is also not available in next version. Use Always On.

Script: Remove Ghost Characters


/*
Script By: Aasim Abdullah
For: ReConnectSQL and http://www.connectsql.com
Purpose: to remove special characters from data
*/
Create FUNCTION [dbo].[RemoveGhostCharacters] (@s varchar(MAX)) returns varchar(MAX)
with schemabinding
BEGIN
if @s is null
return null
declare @s2 varchar(MAX)
set @s2 = ''
declare @l int
set @l = len(@s)
declare @p int
set @p = 1
while @p <= @l begin
declare @c int
set @c = ascii(substring(@s, @p, 1))
if @c between 32 and 126 OR @C BETWEEN 156 AND 157
set @s2 = @s2 + char(@c)
set @p = @p + 1
end
if len(@s2) = 0
return null
return @s2
END
GO

Continue reading “Script: Remove Ghost Characters”

Creating NC Indexes Repository

---Get all indexes basic properties

 SELECT  '[' + Sch.name + '].[' + Tab.[name] + ']' AS TableName,

            Ind.type_desc,

            CASE Ind.is_unique WHEN 1 THEN 'UNIQUE' ELSE '' END UniqueOrNot,

            CASE fill_factor WHEN 0 THEN 100 ELSE fill_factor END fill_factor,

        Ind.[name] AS IndexName,

        SUBSTRING(( SELECT  ', [' + AC.name + CASE IC.is_descending_key WHEN

                                                                              1 THEN '] DESC' ELSE ']' END

                    FROM    sys.[tables] AS T

                            INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

                            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

                                                                 AND I.[index_id] = IC.[index_id]

                            INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

                                                               AND IC.[column_id] = AC.[column_id]

                    WHERE   Ind.[object_id] = I.[object_id]

                            AND Ind.index_id = I.index_id

                            AND IC.is_included_column = 0

                    ORDER BY IC.key_ordinal

                  FOR

                    XML PATH('') ), 2, 8000) AS KeyCols,

        SUBSTRING(( SELECT  ', [' + AC.name +']'

                    FROM    sys.[tables] AS T

                            INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

                            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

                                                                 AND I.[index_id] = IC.[index_id]

                            INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

                                                               AND IC.[column_id] = AC.[column_id]

                    WHERE   Ind.[object_id] = I.[object_id]

                            AND Ind.index_id = I.index_id

                            AND IC.is_included_column = 1

                    ORDER BY IC.key_ordinal

                  FOR

                    XML PATH('') ), 2, 8000) AS IncludeCols

INTO #ALLindexes                   

FROM    sys.[indexes] AS Ind

        INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]

        INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]

            LEFT OUTER JOIN sys.dm_db_index_usage_stats AS usg_stats ON  Ind.index_id = usg_stats.index_id

                                    AND Ind.[OBJECT_ID] = usg_stats.[OBJECT_ID]  and usg_stats.database_id = DB_ID()

WHERE  Ind.type_desc ='NONCLUSTERED'

--AND Tab.name  = 'YourTableNameHere' -- uncomment to get single table indexes detail

AND Ind.is_unique <> 1

ORDER BY TableName




---Get create script as separate column

SELECT *,

'CREATE NONCLUSTERED INDEX ['+IndexName+ '] ON ' + TableName + '('+KeyCols+')' +

 CASE  WHEN IncludeCols IS NOT NULL

                  THEN ' INCLUDE ('+IncludeCols+') WITH (FILLFACTOR = '+CAST(fill_factor AS VARCHAR(10))+')'

                              ELSE ' WITH (FILLFACTOR = '+CAST(fill_factor AS VARCHAR(10))+')' END AS Script

FROM #ALLindexes







---drop table when not required

DROP TABLE #ALLindexes