Arty Martynas Kancleris

Sr. MS SQL Server DBA, MCSE, MCSA, MCITP, MCTS

USE [Curriculum Vitae]
Nationality: Lithuanian
DOB: 1980 March 26
Mobile: +370.686.60.477 LT
Email: m.kancleris@gmail.com
Skype: martynas.kancleris
LinkedIn: http://www.linkedin.com/in/kancleris
CV (pdf) CV (docx) Recommendations (Linkedin)
Updated: 2015 September 20

SQL Database Engine statements used daily 
========================================

SELECT, INSERT, UPDATE, DELETE, MERGE, BULK INSERT

CREATE TABLE, DROP TABLE, ALTER TABLE
CREATE PROCEDURE, DROP PROCEDURE, ALTER PROCEDURE
CREATE FUNCTION, DROP FUNCTION, ALTER FUNCTION
CREATE TRIGGER [DATABASE|ALL SERVER], DROP TRIGGER, ALTER TRIGGER
CREATE VIEW, DROP VIEW, ALTER VIEW
CREATE [UNIQUE] INDEX, DROP INDEX, ALTER INDEX
CREATE DATABASE, DROP DATABASE, ALTER DATABASE
CREATE LOGIN, DROP LOGIN
CREATE USER, DROP USER
CREATE SCHEMA
CREATE ROLE
TRUNCATE TABLE
GRANT SELECT, EXEC, UDPATE, INSERT, DELETE
REVOKE SELECT, EXEC, UDPATE, INSERT, DELETE

WITH [XMLNAMESPACES]
SELECT
WHERE
ORDER BY ASC, DESC
INNER JOIN, LEFT JOIN, CROSS APPLY
SUM, AVG, MIN, MAX, COUNT
GROUP BY, HAVING, WITH ROLLUP
CASE WHEN ... THEN ... ELSE ... END
DENSE_RANK, ROW_NUMBER, OVER, PARTION BY
TOP, WITH TIES
WITH(NOLOCK, INDEX(), READPAST, TABLOCK, TABLOCKX, HOLDLOCK, UPDLOCK)
OPTION(FAST 1)
FOR XML AUTO, ELEMENTS, XPATH, ROOT, RAW
UNION, UNION ALL, INTERSECT, EXCEPT
PIVOT
CONTAINS, FREETEXT
OPENROWSET, BULK SINGLE_BLOB
COLLATE

INSERT
OUTPUT
VALUES

UPDATE 
TOP
SET
CASE WHEN ... THEN ... ELSE ... END
INNER JOIN, LEFT JOIN
SELECT, TOP, ORDER BY 
WHERE

DELETE
TOP
OUTPUT
INNER JOIN, LEFT JOIN
WHERE

SCOPE_IDENTITY
CONTEXT_INFO

@@ROWCOUNT, @@NESTLEVEL, @@PROCID, @@SPID, @@TRANCOUNT, @@ERROR, ERROR_MESSAGE

+, -, /, *, &

SET NOCOUNT ON
SET TEXTSIZE
SET DATEFIRST

DECLARE
SET
WHILE, BEGIN, END
IF ELSE
AND, OR, BETWEEN, IS NULL, IS NOT NULL, LIKE
GOTO
BREAK
RETURN
EXEC 
WAITFOR DELAY
RAISERROR WITH NOWAIT, LOG
PRINT
CONVERT, CAST
EXISTS

GETDATE, GETUTCDATE, DATEADD, DATEDIFF, DATEPART, ISDATE, DATENAME
COALESCE, ISNULL, NULLIF, LTRIM, RTRIM, REVERSE
LEN, DATALENGTH, LOWER, UPPER, CHAR, NCHAR, ASCII, UNICODE
REPLACE, SUBSTRING, STUFF, CHARINDEX, PATINDEX, ISNUMERIC
NEWID, APP_NAME, ORIGINAL_LOGIN
CEILING, ROUND, FLOOR, RAND

BEGIN TRAN
COMMIT TRAN
ROLLBACK TRAN
XACT_STATE

BEGIN TRY
END TRY
BEGIN CATCH
END CATCH

DECLARE [GLOBAL] CURSOR READ_ONLY FAST_FORWARD STATIC FOR
OPEN 
FETCH NEXT FROM ... INTO
@@FETCH_STATUS
CLOSE
DEALLOCATE

CREATE TABLE #t 
CREATE TABLE ##t
DECLARE @t TABLE

int, varchar, nvarchar, money, char, varbinary, bigint, datetime, decimal, 
uniqueidentifier, tinyint, smallint, bit

IDENTITY(1,1), PRIMARY KEY, NOT NULL
DEFAULT
REFERENCES, CHECK
COMPUTE

sp_executesql 
xp_cmdshell, NO_OUTPUT

sp_who
DBCC INPUBUFFER, DBCC CHECKDB, DBCC CLEANTABLE
KILL
OBJECT_ID
OBJECT_DEFINITION
DB_ID
HAS_DBACCESS
sys.columns, sys.objects
sp_addrolemember

xml.nodes, xml.query, xml.value

Stored procedures
-----------------
sp_recompile
EXECUTE AS OWNER
RECOMPILE

Triggers:
---------
inserted, deleted, COLUMNS_UPDATED
AFTER INSERT, UPDATE, DELETE
INSTEAD OF INSERT, UPDATE, DELETE
ROLLBACK

Indexes:
--------
ALLOW_PAGE_LOCKS, ALLOW_ROW_LOCKS, IGNORE_DUP_KEY, FILLFACTOR
INCLUDE
WHERE
REBUILD, REORGANIZE

Server:
-------
Used to turn on xp_cmdshell, OLE automation
sp_configure
RECONFIGURE

Databases:
----------
Required to run unsafe CLR stored procedures
SET TRUSTWORTHY ON

Linked server:
--------------
Link with SQL Server, Excel, Access
Used to examine Excel files
sp_tables
sp_tables_ex
sp_columns
sp_columns_ex

OLE automation:
---------------
Used to download web pages with WinHTTP and automate Microsoft Word
sp_OACreate
sp_OADestroy
sp_OAMethod
sp_OAGetProperty
sp_OASetProperty

Database mail:
--------------
sp_send_dbmail

SQL Server Agent:
-----------------
sp_start_job, sp_stop_job

CLR:
----
Stored procedures, functions
Used to get file size, perform string search within files, others

Service Broker:
---------------
Used to run stored procedures simultaneously

Full-text Search:
-----------------
Used to index pdfs, office documents, htmls

Endpoints:
----------
Used to export data

DBO: Martynas Kancleris
Hey.lt