PERSIST_SAMPLE_PERCENT

ON

The statistics will retain the set sampling percentage for subsequent updates that do not explicitly specify a sampling percentage.

OFF

The statistics sampling percentage will get reset to default sampling in subsequent updates that do not explicitly specify a sampling percentage.

Source: UPDATE STATISTICS (Transact-SQL) | Microsoft Docs

This keyword is added to T-SQL from SQL Server 2016 SP1 CU4.

Perdro Lopez (Microsoft Team) explained it deeply here .

Advertisements

All important commands you need to configure a Windows Server Core and install SQL Server on it

If you have to perform a core mode SQL Server installation, don’t be afraid, just know these commands:

  • create share folder :
net share "share folder"=c:\share /grant:everyone,full
  • restart server :
shutdown -r -t 0
  • configure server 2012+ :
sconfig.cmd
  • rename computer :
netdom renamecomputer %computername% /newname:Svr-1
  • set static ip address :
netsh interface ipv4 set address "Local Area Connection" static 10.10.10.10
  • join to domain :
netdom join Svr-1 /domain:YourDomain.com
  • configure time zone :
Control timedate.cpl
  • activate windows :
slmgr –ato
  • set the password to never expire (lab only) :
wmic useraccount where "Name='Administrator'" set PasswordExpires=false
  • system preparation tool :
%windir%\system32\sysprep\sysprep.exe /oobe /generalize /shutdown
  • enable remote desktop (%windir%\System32>) :
cscript scregedit.wsf /ar 0
  • disable remote desktop (%windir%\System32>) :
cscript scregedit.wsf /ar 1
  • enable the firewall to allow the remote desktop (%windir%\System32>) :
netsh advfirewall firewall set rule group="remote desktop" new enable=Yes
  • enable .net 3.5 on server 2012+ (powershell) :
Install-WindowsFeature NET-Framework-Core –Source D:\Sources\SxS\
  • install SQL Server Database Engine only :
Setup.exe /qs /ACTION=Install /FEATURES=SQLEngine /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /IACCEPTSQLSERVERLICENSETERMS

Configure Windows Server Core with Sconfig.cmd

If you are going to install SQL Server on Windows Server 2012+ Core edition, you can use sconfig.cmd to configure Windows server core easily.

Starting with Windows Server 2012, you can use the Server Configuration tool (Sconfig.cmd) to configure and manage several common aspects of Server Core installations. You must be a member of the Administrators group to use the tool.Sconfig.cmd is available in the Minimal Server Interface and in Server with a GUI mode.

To start the Server Configuration ToolChange to the system drive.

Type Sconfig.cmd, and then press ENTER.

The Server Configuration tool interface opens:

Source: Configure a Server Core Server with Sconfig.cmd

Create a Standalone R Server from the Command Line 

Run the following command from an elevated command prompt to install only Microsoft Machine Learning Server (Standalone) and its prerequisites.

Setup.exe /q /ACTION=Install /FEATURES=SQL_SHARED_MR /IACCEPTROPENLICENSETERMS /IACCEPTSQLSERVERLICENSETERMS

Source: Install Microsoft R Server from the Command Line | Microsoft Docs

T-SQL: How to Sort a Column that Contains Character-Separated Numbers 

we can use the sorting based on hierarchyid data type which in each node is based on numeric sort order.

SELECT *
FROM dbo.Letters
ORDER BY
CAST(N'/' + IndicatorCode + N'/' AS HIERARCHYID)

 

 

Source: T-SQL: How to Sort a Column that Contains Character-Separated Numbers – TechNet Articles – United States (English) – TechNet Wiki

What is the SSRS web portal  

The web portal is a web application that you open by typing the web portal URL in the address bar of the browser window. When you start the web portal, the pages, links and options that you see will vary based on the permissions you have on the report server. To perform a task, you must be assigned to a role that includes the task. A user who is assigned to a role that has full permissions has access to the complete set of application menus and pages available for managing a report server. A user assigned to a role that has permissions to view and run reports sees only the menus and pages that support those activities. Each user can have different role assignments for different report servers, or even for the various reports and folders that are stored on a single report server.For more information about roles, see Granting Permissions on a Native Mode Report Server.

Source: Web portal (SSRS Native Mode) | Microsoft Docs

Model many-to-many relationships using SQL Server Graph Database

A graph database is a collection of nodes (or vertices) and edges (or relationships). A node represents an entity (for example, a person or an organization) and an edge represents a relationship between the two nodes that it connects (for example, likes or friends). Both nodes and edges may have properties associated with them.

Source: SQL Graph overview | Microsoft Docs

SQL Server Machine Learning Tutorials

This article provides a comprehensive list of the tutorials, demos, and sample applications that use machine learning features in SQL Server 2016 or SQL Server 2017. Start here to learn how to run R or Python from T-SQL, use remote and local compute contexts, and optimize your R and Python code for a SQL production environment:

Source: SQL Server Machine Learning Tutorials | Microsoft Docs

Using the FORCESEEK Table Hint

If a plan contains table or index scan operators, and the corresponding tables cause a high number of reads during the execution of the query, as observed in the STATISTICS IO output, forcing an index seek operation may yield better query performance. This is especially true when inaccurate cardinality or cost estimations cause the optimizer to favor scan operations at plan compilation time.

Source: Using the FORCESEEK Table Hint

Enable AlwaysOn Availability Groups on a Windows Server Core 

Using SQL Server Configuration Manager RemotelyThese steps are meant to be performed on a PC running the client edition of Windows, or Windows Server that has the Server Graphical Shell installed.Open Computer Management. To open Computer Management, click Start, type compmgmt.msc, and then click OK.In the console tree, right-click Computer Management, and then click Connect to another computer….In the Select Computer dialog box, type the name of the Server Core machine that you want to manage, or click Browse to find it, and then click OK.In the console tree, under Computer Management of the Server Core machine, click Services and Applications.Double click SQL Server Configuration Manager.In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (), where is the name of a local server instance for which you want to enable Always On Availability Groups, and click Properties.Select the AlwaysOn High Availability tab.Verify that Windows failover cluster name field contains the name of the local failover cluster node. If this field is blank, this server instance currently does not support AlwaysOn Availability Groups. Either the local computer is not a cluster node, the WSFC cluster has been shut down, or this edition of SQL Server 2017 that does not support AlwaysOn Availability Groups.Select the Enable AlwaysOn Availability Groups check box, and click OK.SQL Server Configuration Manager saves your change. Then, you must manually restart the SQL Server service. This enables you to choose a restart time that is best for your business requirements. When the SQL Server service restarts, AlwaysOn will be enabled, and the IsHadrEnabled server property will be set to 1.

Source: Configure SQL Server on a Server Core Installation | Microsoft Docs