Wizard ASP.NET SQL Server

SQL
sp_recompile
EXEC <stored procedure …etc.> WITH RECOMPILE
UPDATE STATISTICS
sp_updatestats
CREATE of ALTER <stored procedure> WITH RECOMPLIE
SECURITY PLAN
System Stored Procedures:
sp_helprotect
Fixed Server Roles:
sysadmin
securityadmin
serveradmin
setupadmin
processadmin
diskadmin
dbcreator
bulkadmin
System Administrators
Security Administrators
Server Administrators
Setup Administrators
Process Administrators
Disk Administrators
Database Creators
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
317469385
pagina 1 van 15
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
DSS
OLTP
DTS
MARS
search argument
Decision Support System
Online Transaction Processing
Data Transformation Services
Multiple Active Record Sets
SETTABLE DATABASE OPTIONS (sp_dboption)
ANSI null default
317469385
pagina 2 van 15
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 =
'<Root>
<Customers CustomerID = "0123" CompanyName = "ABC">
<Orders OrderID = "2345" OrderDate = "2001-05-01" >
<OrderDetails ProductID = "10" Quantity = "20"/>
<OrderDetails ProductID = "11" Quantity = "30"/>
</Orders>
<Orders OrderID = "3456" OrderDate = "2001-05-03" >
<OrderDetails ProductID = "12" Quantity = "50"/>
<OrderDetails ProductID = "13" Quantity = "80"/>
</Orders>
</Customers>
<Customers CustomerID = "0456" CompanyName = "CDE">
<Orders OrderID = "4567" OrderDate = "2001-05-03" >
<OrderDetails ProductID = "10" Quantity = "75"/>
<OrderDetails ProductID = "11" Quantity = "26"/>
317469385
pagina 3 van 15
</Orders>
<Orders OrderID = "5678" OrderDate = "2001-05-07" >
<OrderDetails ProductID = "12" Quantity = "86"/>
<OrderDetails ProductID = "13" Quantity = "72"/>
</Orders>
</Customers>
</Root>'
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-AF896AD8702E4E6E&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
317469385
pagina 4 van 15
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-bc4f644958d9e0a9.htm#DateandTimeFunctions
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/3b078d5b-526c-4884-906ae4442805795f.htm#UsingOperatorswithDateandTimeDataTypes
DECLARE @datetime DATETIME = '20081020'
DECLARE @datetime DATETIME = STR(YEAR(GETDATE())) + '-' + STR(MONTH(GETDATE())) + '-' +
STR(DAY(GETDATE()))
317469385
pagina 5 van 15
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
SELECT
SELECT
SELECT
317469385
DATEDIFF(YEAR, '19550110', GETDATE()) AS 'JAREN'
DATEDIFF(MONTH, '19550110', GETDATE()) AS 'MAANDEN'
DATEDIFF(WEEK, '19550110', GETDATE()) AS 'WEKEN'
DATEDIFF(DAY, '19550110', GETDATE()) AS 'DAGEN'
pagina 6 van 15
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
317469385
pagina 7 van 15
sp_helpsrvrole
EXECUTE
EXECUTE
EXECUTE
EXECUTE
EXECUTE
EXECUTE
EXECUTE
EXECUTE
sp_helpsrvrolemember
sp_helpsrvrolemember
sp_helpsrvrolemember
sp_helpsrvrolemember
sp_helpsrvrolemember
sp_helpsrvrolemember
sp_helpsrvrolemember
sp_helpsrvrolemember
sysadmin
securityadmin
serveradmin
setupadmin
processadmin
diskadmin
dbcreator
bulkadmin
EXECUTE
EXECUTE
EXECUTE
EXECUTE
EXECUTE
EXECUTE
EXECUTE
EXECUTE
sp_srvrolepermission
sp_srvrolepermission
sp_srvrolepermission
sp_srvrolepermission
sp_srvrolepermission
sp_srvrolepermission
sp_srvrolepermission
sp_srvrolepermission
sysadmin
securityadmin
serveradmin
setupadmin
processadmin
diskadmin
dbcreator
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',
317469385
pagina 8 van 15
@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
317469385
pagina 9 van 15
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]
317469385
pagina 10 van 15
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
317469385
pagina 11 van 15
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
Info
Start Debugging
CTRL + K, CTRL + G
ALT + F1 (sp_help)
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
317469385
pagina 12 van 15
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
317469385
pagina 13 van 15
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.
317469385
pagina 14 van 15
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 = [<loginname>], @defdb = [<databasename>]
Current database:
SELECT DB_NAME()
317469385
pagina 15 van 15