Update statistics



Dovnload 169.16 Kb.
Datum20.08.2016
Grootte169.16 Kb.


SQL
sp_recompile

EXEC WITH RECOMPILE

UPDATE STATISTICS

sp_updatestats


CREATE of ALTER WITH RECOMPLIE
SECURITY PLAN
System Stored Procedures:

sp_helprotect


Fixed Server Roles:

sysadmin System Administrators

securityadmin Security Administrators

serveradmin Server Administrators

setupadmin Setup Administrators

processadmin Process Administrators

diskadmin Disk Administrators

dbcreator Database Creators

bulkadmin Bulk Insert Administrators
Fixed Database Roles:

db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader db_denydatawriter db_owner db_securityadmin public


PRIVILEGES

DELETE


INSERT

REFERENCES

SELECT

UPDATE
STORED PROCEDURES



sp_server_info

sp_helpfile

EXECUTE master.dbo.sp_MSforeachdb 'use [?] exec sp_helpfile'

sp_who
sp_table_privileges
sp_tables

sp_tables @table_owner = 'dbo'

sp_tables @table_type = "'TABLE'"

sp_tables @table_name = '%naam%'

sp_stored_procedures

sp_stored_procedures @sp_name = '%naam%'


sp_columns
sp_pkeys

sp_fkeys


sp_helpdb

sp_help


sp_helptext [naam van view of stored procedure]
sp_dboption

Geeft een’ lijst met alle mogelijke opties

sp_dboption ’ Naam van Database

Geeft alle opties die zijn geactiveerd


sp_grantlogin

sp_grantlogin [username]

sp_addsrvrolemember 'username', 'sysadmin'
sp_denylogin

sp_revokelogin


sp_helplogins
sp_spaceused
sp_addapprole
sp_helplanguage

sp_helplanguage @language = @@LANGUAGE




ABBREVIATIONS

SARG search argument

DSS Decision Support System

OLTP Online Transaction Processing

DTS Data Transformation Services

MARS Multiple Active Record Sets


SETTABLE DATABASE OPTIONS (sp_dboption)

ANSI null default

ANSI nulls

ANSI padding

ANSI warnings

arithabort

auto create statistics

auto update statistics

autoclose

autoshrink

concat null yields null

cursor close on commit

db chaining

dbo use only

default to local cursor

merge publish

numeric roundabort

offline


published

quoted identifier

read only

recursive triggers

select into/bulkcopy

single user

subscribed

torn page detection

trunc. log on chkpt.


STATEMENTS

GRANT SELECT ON Orders TO Accounting

sp_addapprole OrderApp, secret – secret is het wachtwoord

GRANT ALL ON Orders To OrderApp



XML

DECLARE @idoc INT

DECLARE @doc NVARCHAR(1000)

SET @doc =

'









































'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc


SELECT * FROM

OPENXML (@idoc, 'Root/Customers/Orders/OrderDetails')

WITH

(

CustomerID INT '../../@CustomerID'



, CompanyName NVARCHAR(20) '../../@CompanyName'

, OrderID INT '../@OrderID'

, OrderDate DATETIME '../@OrderDAte'

, ProductID INT

, Quantity INT

)
EXEC sp_xml_removedocument @idoc

GO
OPLETTEN!

Een alias kan alleen in de FROM clause worden toegekend.


INSTALLEREN VOORBEELD DATABASES
Microsoft SQL Server Community Projects & Samples

http://codeplex.com/SqlServerSamples

AdventureWorks

SQL Server 2005 Samples and Sample Databases



http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

AdventureWorks

SQL Server 2005 SP2a

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

Downloads & Files


AdventureWorksDB.msi

source code, 28053K, uploaded May 7 2007 - 264277 downloads


AdventureWorksDBCI.msi

source code, 29177K, uploaded May 7 2007 - 36956 downloads


AdventureWorksBI.msi

source code, 7393K, uploaded May 7 2007 - 60642 downloads


AdventureWorksBICI.msi

source code, 16764K, uploaded May 7 2007 - 19580 downloads


AdventureWorksLT.msi

source code, 2251K, uploaded May 7 2007 - 42111 downloads


Release Notes


Sample Databases for Microsoft SQL Server 2005 SP2 licensed with the MS-PL license.

These installers require you to manually attach the database after installation. See the Release Notes for details.

AdventureWorksDB is the Adventure Works Cycles OLTP sample databases.
AdventureWorksBI is the Adventure Works Data Warehouse and Analysis Services database project.
AdventureWorksLT is a very simple sample database based on the Adventure Works Cycles OLTP sample database.

The CI versions of some of the databases use a case-insensitive collation, as does the AdventureWorksLT sample database. The others are case-sensitive.



AdventureWorks installeren op SQL Server 2008

Microsoft SQL Server Management Studio Complete (expires in 180 days) 10.0.2531.0

Microsoft Analysis Services Client Tools 10.0.3798.0

Microsoft Data Access Components (MDAC) 6.0.6002.18005

Microsoft MSXML 4.0 5.0 6.0

Microsoft Internet Explorer 8.0.6001.18928

Microsoft .NET Framework 2.0.50727.4200

Operating System 6.0.6002


AdventureWorks2008_SR4.exe uitvoeren

Dit programma kopieert een aantal bestanden naar de folder:

C:\Program Files\Microsoft SQL Server\100\tools\Samples, o.a. het bestand DatabaseInstaller.exe.

Het uitvoeren van dat laatste bestand zorgt er voor dat de diverse databases van AdventureWorks geïnstalleerd worden.


DATE and TIME

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/83e378a2-6e89-4c80-bc4f-644958d9e0a9.htm#DateandTimeFunctions


ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/3b078d5b-526c-4884-906a-e4442805795f.htm#UsingOperatorswithDateandTimeDataTypes

DECLARE @datetime DATETIME = '20081020'

DECLARE @datetime DATETIME = STR(YEAR(GETDATE())) + '-' + STR(MONTH(GETDATE())) + '-' + STR(DAY(GETDATE()))



datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

weekday

dw, w

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

microsecond

mcs

nanosecond

ns

SELECT DATEPART(WEEK, GETDATE())

SELECT DATEPART(WEEK, '20081231')

SELECT DATEDIFF(YEAR, '19550110', GETDATE()) AS 'JAREN'

SELECT DATEDIFF(MONTH, '19550110', GETDATE()) AS 'MAANDEN'

SELECT DATEDIFF(WEEK, '19550110', GETDATE()) AS 'WEKEN'

SELECT DATEDIFF(DAY, '19550110', GETDATE()) AS 'DAGEN'

ssms.exe /?




Login


Authentication Mode

Er zijn twee vormen van Server authentication:



  • Windows Authentication mode

  • SQL Server and Windows Authentication mode

Windows Authentication is de default.


Microsoft SQL Server Management Studio, Object Explorer, Server name, Properties, Security page, Server authentication
Provides information about logins and the users associated with them in each database.

sp_helplogins [login name]



Stored Procedures

sp_help [name stored procedure] Geeft dbowner en parameters

sp_helptext [name stored procedure] Geeft tekst van stored procedure

Primary Key, Foreign Key

sp_pkeys [table name]

sp_fkeys [table name]
sp_helpconstraint [table name]

SELECT * FROM sys.server_role_members

sp_helpsrvrole

EXECUTE sp_helpsrvrolemember sysadmin

EXECUTE sp_helpsrvrolemember securityadmin

EXECUTE sp_helpsrvrolemember serveradmin

EXECUTE sp_helpsrvrolemember setupadmin

EXECUTE sp_helpsrvrolemember processadmin

EXECUTE sp_helpsrvrolemember diskadmin

EXECUTE sp_helpsrvrolemember dbcreator

EXECUTE sp_helpsrvrolemember bulkadmin
EXECUTE sp_srvrolepermission sysadmin

EXECUTE sp_srvrolepermission securityadmin

EXECUTE sp_srvrolepermission serveradmin

EXECUTE sp_srvrolepermission setupadmin

EXECUTE sp_srvrolepermission processadmin

EXECUTE sp_srvrolepermission diskadmin

EXECUTE sp_srvrolepermission dbcreator

EXECUTE sp_srvrolepermission bulkadmin


SELECT * FROM sys.sql_logins
sp_tables @table_type = "'TABLE'"
WAITFOR DELAY '00:00:01'
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint

, SCHEMA_NAME(schema_id) AS SchemaName

, OBJECT_NAME(parent_object_id) AS TableName

, type_desc AS ConstraintType

FROM sys.objects

WHERE type_desc LIKE '%CONSTRAINT'


SELECT s.name AS TABLE_SCHEMA

, o.name AS TABLE_NAME

, c.name AS COLUMN_NAME

, d.name AS CONSTRAINT_NAME

FROM sys.default_constraints d

JOIN sys.objects o ON o.object_id = d.parent_object_id

JOIN sys.columns c ON c.object_id = o.object_id

AND c.column_id = d.parent_column_id

JOIN sys.schemas s ON s.schema_id = o.schema_id

ORDER BY [TABLE_NAME]



Add Linked Servers
Ms-Access
DECLARE @SERVER VARCHAR(50)

SET @SERVER = N'SqlDb'

EXEC sp_dropserver @SERVER
EXEC sp_addlinkedserver

@SERVER = N'SqlDb',

@PROVIDER = N'Microsoft.Jet.OLEDB.4.0',

@SRVPRODUCT = N'OLE DB Provider for Jet',

@DATASRC = N'c:\apps\sources\SqlDB.mdb'

GO
SELECT * FROM SqlDb...snippets


Ms-Excel
DECLARE @SERVER VARCHAR(50)

SET @SERVER = N'ExcelData'

EXEC sp_dropserver @SERVER
EXEC sp_addlinkedserver

@SERVER = N'ExcelData',

@PROVIDER = N'Microsoft.Jet.OLEDB.4.0',

@SRVPRODUCT = N'Excel',

@DATASRC = N'C:\Users\jmond\Documents\Excel Data.xls',

@PROVSTR = N'Excel 8.0'

GO
SELECT * FROM ExcelData...Blad1$
CSV
DECLARE @SERVER VARCHAR( 50 )

SET @SERVER = N'MyArchive'

EXEC sp_dropserver @SERVER
EXEC sp_addlinkedserver

@SERVER = N'MyArchive',

@PROVIDER = N'Microsoft.Jet.OLEDB.4.0',

@SRVPRODUCT = N'OLE DB Provider for Jet (Text IISAM)',

@DATASRC = N'C:\Users\jmond\Documents\csv\',

@PROVSTR = N'Text'

GO
INSERT INTO MyArchive...MyData#csv

(

DataCol1, DataCol2



)

SELECT CompanyName AS Col1, ContactName AS Col2

FROM Customers

SELECT *

FROM MyArchive...MyData#csv

C:\Users\jmond\Documents\csv\MyData.csv:

Col1 Col2
C:\Users\jmond\Documents\csv\sxchema.ini:

[MyData.csv]

ColNameHeader=True

Format=TABDelimited

MaxScanRows=0

CharacterSet=ANSI

Col1=DataCol1 Char

Col2=DataCol2 Char


Uitzoeken

http://support.microsoft.com/kb/290415

en Format


csvDelimited

TABDelimited


http://www.eggheadcafe.com/software/aspnet/30132294/how-to-query-a-csv-file-i.aspx
?????

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;



GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;



GO

select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;

*.csv)};

DefaultDir=C:\myfolder;','select top 10 * from myfile.csv')

GO
!!!!

Schema.ini File (Text File Driver)



http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

All Sorts

EXEC [sp_Msforeachtable] 'SELECT * FROM ?;'

EXEC [sp_Msforeachtable] 'PRINT ''?'';'
SELECT * FROM [sys].[schemas]

SELECT * FROM [sys].[tables]



Queries
Query met Grouping Sets

SELECT


SUM(TotalDue) AS 'Total Sales'

, DATEPART(YEAR, OrderDate)AS 'By Year'

, DATEPART(MONTH, OrderDate) AS 'By Month'

, DATEPART(WEEKDAY, OrderDate) AS 'By Weekday'

FROM Sales.SalesOrderHeader

WHERE YEAR(OrderDate) IN('2003', '2004')

GROUP BY GROUPING SETS

(

(DATEPART(YEAR, OrderDate))



, (DATEPART(MONTH, OrderDate))

, (DATEPART(WEEKDAY, OrderDate))

, (

DATEPART(YEAR, OrderDate)



, DATEPART(MONTH, OrderDate)

, DATEPART(WEEKDAY, OrderDate)

)

)

ORDER BY DATEPART(YEAR, OrderDate)



, DATEPART(MONTH, OrderDate)

, DATEPART(WEEKDAY, OrderDate)


Get the Create Scripts for all Stored Procedures
DECLARE @procName VARCHAR(100)

DECLARE @getprocName CURSOR

SET @getprocName = CURSOR FOR

SELECT Name = '[' + SCHEMA_NAME(SCHEMA_ID) + '].[' + Name + ']'

FROM [sys].[all_objects] WHERE TYPE = 'P'

AND [is_ms_shipped] = 0


OPEN @getprocName

FETCH NEXT

FROM @getprocName INTO @procName

WHILE @@FETCH_STATUS = 0


BEGIN

--PRINT 'EXEC [sp_HelpText] ' + @procName

EXECUTE [sp_helptext] @procName
FETCH NEXT

FROM @getprocName INTO @procName

END
CLOSE @getprocName

DEALLOCATE @getprocName

GO


Database readonly


Right Click database -> properties -> options
State -> database readonly -> false

Microsoft SQL Server Management Studio

-- Menu-balk, SSMS Tools, Text Format


Short cuts

Format Text CTRL + K, CTRL + G

Info ALT + F1 (sp_help)

Start Debugging ALT + F5


Columns headers in the result set

-- Menu-balk, Query, Query Options…

Window: Query Options

- Results

-- Text

Checkbox: Include columns headers in the result set


Create Database with Login and User


USE [master]
CREATE DATABASE [databasename]

-- DROP DATABASE [databasename]


CREATE LOGIN [loginname] WITH PASSWORD = 'uß3rn@m3'

-- DROP LOGIN [username]


EXEC sp_addsrvrolemember @loginame = 'login', @rolename = 'role'
EXEC [sp_helpsrvrole] @srvrolename = N'role'
USE [databasename]

CREATE USER [username] FOR LOGIN [username]

-- DROP USER [username] FOR LOGIN [username]
EXECUTE [sp_addrolemember] @rolename = N'db_owner'

, @membername = [username]

-- EXECUTE [sp_removerolemember] @rolename = N'db_owner'

-- , @membername = [username]


EXEC [sp_helpuser] @name_in_db = N'username'

EXEC [sp_helprolemember] @rolename = N'role'


EXEC [sp_defaultdb] @loginame = [username], @defdb = [databasename]
Zie ook: Microsoft SQL Server roles

http://www.mssqlcity.com/Articles/Adm/SQL70Roles.htm

Wizard ASP.NET SQL Server-installatie


%windir%\\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

Query meerdere selecties van één veld uit een tabel naast elkaar


CREATE PROCEDURE [dbo].[shoporder_select_overview]

AS

SELECT



YEAR(so.[ord_createdate]) AS [year]

, MONTH(so.[ord_createdate]) AS [month]

, SUM(so.[ord_total]) AS [total_paid_or_sent]

, ISNULL


(

(

SELECT SUM([ord_total])



FROM [dbo].[shoporder]

WHERE YEAR([ord_createdate]) = YEAR(so.[ord_createdate])

AND MONTH([ord_createdate]) = MONTH(so.[ord_createdate])

AND [ord_statusnr] = 32

)

, 0


) AS [total_refund]

, ISNULL


(

(

SELECT SUM([ord_total])



FROM [dbo].[shoporder]

WHERE YEAR([ord_createdate]) = YEAR(so.[ord_createdate])

AND MONTH([ord_createdate]) = MONTH(so.[ord_createdate])

AND [ord_statusnr] = 16

)

, 0


) AS [total_returned]

FROM [dbo].[shoporder] so

WHERE so.[ord_statusnr] IN (2, 4)

GROUP BY YEAR(so.[ord_createdate]), MONTH(so.[ord_createdate])

ORDER BY YEAR(so.[ord_createdate]), MONTH(so.[ord_createdate])

EXEC [sp_detach_db] @dbname = N'AdventureWorks';


EXEC [sp_attach_db] @dbname = N'AdventureWorks'

, @filename1

= N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'

, @filename2

= N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf';

Optimization

Transact-SQL Optimization Tips



http://www.databasejournal.com/features/mssql/article.php/1437391/Transact-SQL-Optimization-Tips.htm
Tips, Tricks, and Advice from the SQL Server Query Optimization Team

http://blogs.msdn.com/queryoptteam/
Lengthy SQL Server Queries Will Consume Your CPU

http://www.mssqltips.com/tip.asp?tip=1500

Version


SELECT @@version

EXEC master..xp_msver


Microsoft SQL Server Management Studio

Bij iedere query staat recht onderin op de statusbalk het aantal rijen.



Triggers


SELECT

sys.objects.name AS [trigger]

, sys.tables.name AS [table]

, sys.objects.type

, sys.schemas.name AS [schema]

, *


FROM sys.schemas

RIGHT OUTER JOIN sys.tables ON sys.schemas.schema_id = sys.tables.schema_id

RIGHT OUTER JOIN sys.objects ON sys.tables.object_id = sys.objects.parent_object_id

WHERE sys.objects.type = 'tr'


SELECT

O.name


, O.type

, S.name AS [schema]

, OP.name AS parent_name

, OP.type AS parent_type

, SP.name AS parent_schema

FROM sys.schemas AS SP

RIGHT OUTER JOIN sys.objects AS OP ON SP.schema_id = OP.schema_id

RIGHT OUTER JOIN sys.objects AS O

LEFT OUTER JOIN sys.schemas AS S ON O.schema_id = S.schema_id ON OP.object_id = O.parent_object_id

WHERE O.type = 'TR'

ORDER BY O.type

Hello, I can not understand the use of Deny and Revoke, what is the difference?

very basic definitions:


Let's say you give me SELECT permission on table TEST.
If you DENY me SELECT on TEST, I still have SELECT permission, I just can't use it.
If you REVOKE my SELECT permission, I can't use it because I don't have it.

Functions

CREATE FUNCTION [dbo].[fun_GETDATE_1]()

RETURNS DATETIME

AS

BEGIN



RETURN GETDATE()

END
SELECT [dbo].[fun_GETDATE_1]() –- Geeft:

2010-08-29 09:29:06.143
maar
SELECT [dbo].[fun_GETDATE_1] -- Geeft:

The multi-part identifier "dbo.fun_GETDATE_1" could not be bound.


DECLARE @mydatetime DATETIME;

SELECT @mydatetime = [dbo].[fun_GETDATE_1]()

SET @mydatetime = [dbo].[fun_GETDATE_1]()

SELECT @mydatetime



How to set the default database on login


EXEC [sp_defaultdb] @loginame = [], @defdb = []
Current database:

SELECT DB_NAME()




pagina van



De database wordt beschermd door het auteursrecht ©opleid.info 2016
stuur bericht

    Hoofdpagina