Introducing

Background

Getting Started

xp_cmdshell

How to run CMD commands in SQL Server?

Today everyone is talking about NoSQ, but SQL is still an essential language that every programers should know enough about it as same as JavaScript and XML. MS SQL Server is a SQL engine that enables programmer to forward all data-related processes and worries to it, and just interact with it by SQL. now let's have a closer look at MS SQL Server.Because we are talking about tips here, so you need a good background of SQL ISO and MS SQL Server, not as a DBA, but have to know enough about DML, and DDL scripts.We (Programmers) are usually have tight contact by their IDE (Eclipse, KDEV, NetBeans,...), in fact IDE is our best friend, but most of the times we need to persist our data and here the first guy we can think about it is SQL.Each developer has a favorite (best-fit) database for saving the data (Derby, MySQL, PostgreSQL, Oracle, ... ), unfortunately most of the times we just run some commands to the server. We imagine a database is exact as same as ANSI SQL ISO definition (that should be, but who does follow the rules?).Many of us just install and configure a login account in the DB and don't care about anything else. and what IF that database is SQL Server? so let's get started.easy by using xp_cmdshell.there is a good?! guy in MS SQL called xp_cmdshell, this procedure is used for running CMD commands, I think this is not really required while SQL server supports Managed Beans too, BUT I have to mention this procedure has defined before managed code introduced.

How to Enable xp_cmdshell in SQL Server?

--it doesn't really show anything advanced sp_configure 'show advanced options','1'; reconfigure;--it's required

sp_configure 'xp_cmdshell','1'; reconfigure;

exec xp_cmdshell 'echo %JAVA_HOME% , %INTELOCLSDKROOT%;' exec xp_cmdshell 'echo %WinDir%;' exec xp_cmdshell 'echo ''Hi Buddy'';','no_output'

declare @res int; exec @res=xp_cmdshell 'C:;','no_output' if(@res=0) begin print ('Drive C is available :D'); end else begin print ('Drive C is not available !!!'); end exec @res=xp_cmdshell 'F:;','no_output' if(@res=0) begin print ('Drive F is available :D'); end else begin print ('Drive F is not available'); end

Limitations

Example

How to call BCP tool is SQL Server?

use master; go; create table v(id int identity(0,1),"name" varchar(64)); go insert v values ('911'),('992'),('991'),('970'),('972'),('You'); declare @bcpComm varchar(512),@r int; select @r=round(rand()*count(*),0) from v set @bcpComm='BCP "select ''The lucky person is :....''+[name] from master.dbo.v where id=''"'+ convert(varchar(10),@r)+''' queryout C:\users\lucky.txt -T -c -S'+@@servername exec @r=xp_cmdshell @bcpComm,'no_output'; if(@r=0)print 'Success, please check the C:\users\lucky.txt file'; else print('Error!'); drop table v;

Exec() and sp_executesql

How to run dynamic query in SQL Server?

How to dynamically create to many tables sequentially in SQL Server?

declare @com nvarchar(1024); declare @i int=0; while(@i<100)begin --step1: prepare the command set @com='create table MyTable'+convert(varchar(3),@i)+'(id int,[name] varchar(64));'; --set @com='drop table MyTable'+convert(varchar(3),@i); --step2: run the command exec sp_executesql @com; --exec(@com);--as same as above, the new face set @i=@i+1; end --step3: verify the work select * from sys.tables where name like 'MyTable%'

xp_regwrite and xp_regread

Is it possible to access registry in SQL Server?

fortunately this procedure is disabled by default because of security concerns.this is going to be easy, at the first we need to enable advanced option with sp_configure procedure like thisthen after enabling advanced options and reconfigure the system, now it's time for enabling xp_cmdshell procedureAnd now we have xp_cmdshell and he is ready for action, this procedure accepts two arguments, the first one is the process name with the input argument, and the second one is 'no_output'. 'no_output' means don't show the response of executed application.The results of called commands are shown as result set in SQL (no messages).the xp_cmdshell returns an integer number that it's result of run command1: you cannot run every all applications, generally terminal applications work without any problem but you cannot run a GUI application like notepad.exe2: it waits(block) until the command you run response.3: you cannot set or get application's stream directives.now let have a good example.using xp_cmdshell.The BCP, its a tool installed by SQL Server, it's used for copy data from SQL structure into user files. so because it's a executable application and it's not a SQL command we need to call it by xp_cmdshelleasy using sp_executesql procedureBoth exec() and sp_executesql are used for running dynamic SQL commands, just need to prepare the commands as a String object and call the sp_executesql.Both of exec() and sp_executesql are same, exec() is the new face of sp_executesql has added in SQL Server V9. This procedures are enable by default.easy, using a while loop, prepare each DDL statement and run the command string by sp_executesql procedure.The following example creates 100 tables dynamically.YesWhile it doesn't make sense, but this is possible to read an write registry with SQL Server.I'm wondering! SQL doesn't support arrays or partial foreign keys or hash index, or many things a good database needs, but it support access to registry, and I don't know when does a database programmer really need to access registry?! But HONESTLY I used this feature to much, because of many time my application couldn't access registry, SQL helped me out to much with this case.

Is it possible to SELECT the installation path in SQL Server?

declare @ipath nvarchar(256); --SQL SERVER V100 path, change SQL10 to SQL9 for V90 exec master..xp_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\Microsoft SQL Server\SQL10\Setup' ,N'SQLPath',@ipath output,no_output print @ipath

Is it possible to start SQL Agent in SQL?

--step 1: find installation path declare @ipath nvarchar(256); exec master..xp_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\Microsoft SQL Server\SQL10\Setup' ,N'SQLPath',@ipath output,no_output --step 2: prepare the start command command declare @sc nvarchar(1024); --SQL agent is located under INSTALLATION_PATH/binn/sqlagent.exe --set the server need to binds with SQL agent with -i parameter set @cmdComm=N'start '+@ipath+'\binn\SQLAGENT.exe -i '+cast(@@servername as nvarchar(2048)); declare @rv int; --execute the command line by xp_cmdShell exec @rv=xp_cmdshell @cmdComm; if (@rv=0)begin print 'Agent started successfully'; end else begin print 'Agent initiating failed!'; end

Is it possible to enable TCP protocol in SQL Server by T-SQL?

how to enable SQL Authentication mode by T-SQL?

--step 1: creating a login (mandatory) create login login_to_system_after_injection with password='Thank$SQL4Registry@ccess'; GO --step 2: enabling both windows/SQL Authentication mode /*some server specific configurations are not stored in system (SQL)*/ --set the value to 1 for disabling the SQL Authentication Mode after . . . exec xp_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2; --step 3:getting the server instance name declare @spath nvarchar(256); --SQL SERVER V100 path, use SQL9 for V90 exec master..xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL' ,N'SQL10',@spath output,no_output --step 4:preparing registry path declare @insRegPath nvarchar(1024)=N'Software\Microsoft\Microsoft SQL Server\' + @spath + '\MSSQLServer\SuperSocketNetLib\Tcp'; --step 5:enabling tcp protocol' exec xp_regwrite N'HKEY_LOCAL_MACHINE', @insRegPath, N'Enabled', REG_DWORD, 1 --generally tries to enable all addresses. NOT Recommended --step 6:enabling remote access EXEC sys.sp_configure N'remote access', 1 GO RECONFIGURE WITH OVERRIDE --reconfigure is required! GO --step 7:a system restart is required in order to enabling remote access. --step 7.1:shutting down the server shutdown --After this command you need to start the server implicitly yourself. --or just configure the Agent in order to start the server at any shutdown or failure

Cursor

What is cursor in SQL Server?

How to declare a cursor in SQL Server?

--forward-only cursor declaration, can just get the next result(record) declare cur cursor for select * from dbo.a --scrollable cursor declaration, can move freely through cursor declare cur scroll cursor for select * from dbo.a

--removing cursor c completely, it's better close it before deallocation deallocate c;

+------------------------------------------+ |--> Before the result @@fetch_status = -1 | |-> D A T A - record @@fetch_status = 0 | |-> D A T A - record @@fetch_status = 0 |---\ |-> D A T A - record @@fetch_status = 0 | \ A Cursor |-> D A T A - record @@fetch_status = 0 | / Context |-> D A T A - record @@fetch_status = 0 |---/ |-> D A T A - record @@fetch_status = 0 | |--> After the result @@fetch_status = -1 | +------------------------------------------+

Example

create table folks(id int identity(1,1),name varchar(32)); go insert folks values('911'),('918'),('5780'),('718'); --step1:declare the cursor declare cur scroll cursor for select * from folks; declare @id int,@name varchar(32); --step2:open the cursor open cur; --step3:fecth the very first record into variables fetch next from cur into @id,@name print('--------------------------') --step4:read the cursor to end while( @@FETCH_STATUS=0 ) begin print(convert(varchar(10),@id)+space(10)+@name); fetch next from cur into @id,@name; end print('--------------------------'); --step5:close the cursor (close the handler) close cur; --step6:finilize the cursor deallocate cur; drop table folks;

Always use cursor in triggers

What is a trigger?

create table folks(id int identity(1,1),name varchar(32)); go insert folks values('Apache'),('CodeProject'),('Google'),('sourceforge'), ('Eclipse'),('IBM'),('Oracle'),('Valve'); go --step1:creating the trigger create trigger no_close_src on folks for insert as begin --step2:creating the cursor --local keyword here ensures you the cursor is accessible just this block(procedure) declare insData cursor local for select name from inserted; declare @name varchar(32); open insData; fetch next from insData into @name; --step3:while it contains records while(@@fetch_status=0) begin set @name=lower(@name); --step4:check if ( @name='microsoft' or @name='adobe' or @name='autodesk' or @name='ea') begin print('Error, there is no room for close source guys('+@name+') here!'); rollback transaction; end --step5:fecth the next value and check it too fetch next from insData into @name; end end go insert folks values('github'),('ea'),('Sun'); select * from folks; drop table folks;

instead of triggers

is it possible to override a DML/DDL operation in SQL Server

instead of

instead of

instead of

How to avoiding insertion in a table without rollbacking the transaction?

instead of

create table empty_4_ever (id int identity(0,1),_name varchar(128)); go create trigger overrideInsert4Empty4Ever on empty_4_ever instead of trigger as begin --there is nothing to do --because this is an instead of trigger so there is no any transaction, no any insertion by db print('are you really sure you have inserted data?'); --if this have insert in empty_4_ever table, this is mean an actual insert into the table without calling itself again end go insert empty_4_ever values('we are wasting our time'),('have a try if you cannot believe it'); select * from empty_4_ever

yes, the installation path is located in registry.yes, find the installation path, and run the SQL agent process by command.YesSet the (HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer)loginmode key to 2Cursor is SQL data-type, this is used for saving a select result in order to seek through rows.Cursor is a really useful guy, specially when you want to fetch records one-by-one, generally most of the database systems support cursor because it has defined in SQL ISO.cursors are declared as same as variable declarations with a small difference, cursors variable doesn't follow variable name in SQL, there is no @ symbol at first of cursor variables.Unlike explicit transactions, by default cursors are alive until you deallocate(remove) them by deallocate commandfor using a cursor, you need to open the cursor, fetch information and close it. A cursor has a pointer that points to the current row in the result, just after you open a cursor, pointer points to nothing (before first), so for fetching the first row, we need to fetch the next result(move the pointer). the global @@fetch_status variable is used for determining the current status of the cursorThe following example tries to show folks table records in a specific format, as above diagram is showing, when a cursor get opened, the pointer is just before the first result, so we need to fetch next from the cursor, then iterate the cursor by a loop.Trigger is a functional component which is fired implicitly by a DML or DDL event, it's used for doing alternatives with an specific event.When you have a trigger on a DML operation, this is possible that user inserts or deletes more than 1 record by one insert call, so in the trigger you should have a pattern to check all of the records affected, that this could be done very easy by trigger.yes, usingtriggers.Just create antrigger on target object for desired event(s), but SQL doesn't () support it for every DDL operations of course.UsingAs mentioned instead triggers in SQL are like method overriding in Java, there is actual insert operation behind, but when you call the method, you are calling the overridden method, there is just one chance to invoke the original method/command, and this is overridden method calls the base method itself.



Click to enlarge

instead of

instead of

Because there is no any actual command performed by db, so there is no any transaction to commit or rollback.

If an instead of trigger calls its event again, it doesn't call itself again, the actual event will be invoked, for example an instead of trigger on table guys on insert, if inserts a value in table guys, it doesn't call itself again, it calles the actual insert.

trigger calls its event again, it doesn't call itself again, the actual event will be invoked, for example an trigger on table guys on insert, if inserts a value in table guys, it doesn't call itself again, it calles the actual insert. There is only ONE instead of trigger is allowed for each event on a member

trigger is allowed for each event on a member if there are 3 trigger(for, after, instead of ) on a member on same event(s), then the first firing trigger will be instead of trigger, other triggers will no fired UNLESS instead of trigger repeats the event.

) on a member on same event(s), then the first firing trigger will be trigger, other triggers will no fired UNLESS trigger the event. Use instead of if the whole of event process should overridden, or the rate of rollbacking data is to much high.

Multi-Threading in SQL Server

Is is possible to run threads in SQL Server?

What does service broker do in SQL Server?

What is MOA?

Sending sync or async messages to the target server

Holding events/messages in safe to prevent removing by system crashes

Scheduling to invoking a function in future

Sending messages across heterogeneous systems

What are a MOA's components?

Server: the main application, MOA implementation

Topic & Queue: are used for holding messages

End Point(service): the target function needs to be invoked

Agreement: is the protocol used for communication

Message type: the data-type for storing data, usually is typed XML

Contract: method for marshaling and unmarshaling message in order to transfer in network

this story is same fortriggers in SQL server too. the following list is showing the key notes about thetriggers.yes, but not as same as you do in Java or C, usingservice broker is a semi-implementation of Message Oriented Architecture(MOA)it's a pattern(architecture) designed for heterogeneous to communicate with each other. The main MOA features are listed below:And there is just one big disadvantage, and this is [it's unreliable] because there is no any certain method to monitor current state of a request in server from the client. SMS is an example of MOA.Generally a MOA system contains component listed below, but it's not a rule, MOA is architecture, is a definition. so there is no any rule defined to force every MOA implementations to have these components. for instance SQL Server doesn't have Topic and agreement.in SQL server we just need to create message type, queue, service, contract,and. as I said create, no implementation, it's really simple.



Click to enlarge

How to execute a procedure parallel in SQL server?

Activate the service brokers in both client and server points(in this example both client and server are same) Create a procedure for handling request in server Create Message type and contract in both client and server points Create the server queue and bind the procedure to create the server service and bind the service to it create the queue in client create the service in client for sending messages create a dialog conversation(session) and send message through session

/*At the server*/ create database _parallel go use _parallel go --step1: create the procedure (thread) create proc threadStart as begin declare @comm nvarchar(2048); --getting message from the messageCommPool queue receive top(1) @comm=convert(nvarchar(2048),message_body) from messageCommPool; --execute the message as sql command, or anything else exec(@comm); end go --step2: enable service broker alter database _parallel set enable_broker go --step3: define the message data-type create message TYPE messageComm validation = none go --step4: Create the receiving queue create queue [messageCommPool] with status=on, retention=on ,activation ( procedure_name = _parallel.dbo.threadStart, max_queue_readers = 32 , --maximum instance of parallel invocation execute as owner) go --step5: create the contract create contract theContract (messageComm SENT BY any) go --step6: Create Receive Service on Recieve Queue create service hostService on queue messageCommPool (theContract) go /*At the client*/ /* Create message type and contract IF and IF client is located in another server or db use clientDB; go alter database clientDB set enable_broker go --step3: define the message data-type create message TYPE messageComm validation = none go --step5: create the contract create contract theContract (messageComm SENT BY INITIATOR) go */ --step7: create the sending queue at client create queue sendQueue with status=on, retention=off; go -- create service sendService on queue sendQueue (theContract) go --step7: creating a procedure to sending message to teh server create proc runThread (@comm nvarchar(2048)) as begin --step8: initilizing the connection session declare @session uniqueidentifier --step9: open the connection begin dialog conversation @session from service sendService to service 'hostService'--hostService is the service name located at the target server on contract theContract with encryption = off; --step9: send the command send on conversation @session message type messageComm (@comm); end go --step10: have a try! :D begin declare @i int=0,@comm nvarchar(2048); while(@i < 10)begin set @comm=N'create table tableNumber'+(convert(nvarchar(2),@i))+'(id int identity(0,1),[name] char(16));' exec runThread @comm; set @i=@i+1; end end --step11: verify the try select * from sys.tables /* --step12: enything else? use master go DROP database _parallel go */

Is it possible to run a non-sql command in SQL?

exec sp_configure 'clr enabled', 1 go reconfigure; go

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.IO; namespace wasys911992.blogger.example.sql{ public class StoredProcedures{ //defining the method as a SQL proc [SqlProcedure()] //the method signaturie should be as same as procedure signature public static void writeToFile(SqlString filePath, SqlString data){ //write content to the file BinaryWriter bw=new BinaryWriter(new FileStream(filePath.Value,FileMode.Create)); bw.Write(data.Value); bw.Flush(); bw.Close(); //accessing sql pipe for sending message, works as print in sql SqlContext.Pipe.Send("Success! hello from PRAHS-C"); } } }

Simple, as following order.Service broker is a really nice feature in SQL Server, very useful, but SQL Agent would does the same as scheduling jobs, and you have better control on functions. Managed Code Yes, using managed codes.SQL Servers supports utilizing .Net platform in order to SQL-Unsupported and complex codes, for example compressing a file, or connecting to a URL, or killing a process.We just need to implement the target with a CLR language(C#, VC++, VB) and just create a procedure in SQL Server that points to the actual code, so the .Net modules will be invoked by SQL procedure call.This future is disabled by default, so first .Net communication should activated in the server.Then code the business in .NetThen compile the class, and get the .dll or .exe file.

How to run unsafe managed codes in SQL Server?

-- tell sql take it easy, why so restriction?! be open alter database mydb set trustworthy on

create assembly myAssembly authorization dbo from 'C:\managed\managed.dll' with permission_set = unsafe

create procedure writeToFileProc (@name nvarchar(1024),@data nvarchar(4000)) as external name myAssembly.[wasys911992.blogger.example.sql.StoredProcedures].writeToFile;

--step1: trust to everyone alter database mydb set trustworthy on go reconfigure go --step2: register the assembly in sql create assembly myAssembly from 'C:\managed\managed.dll' with permission_set = unsafe go --step3: create the procedure create procedure writeToFile (@filePath nvarchar(1024),@data nvarchar(4000)) as external name myAssembly.[wasys911992.blogger.example.sql.StoredProcedures].writeToFile; go --step4: have a try exec writeToFile N'C:/data.txt',N'Hello from managed codez, :D';

Use one insert command to insert multiple records

--never use this approach :( insert guys values( 1994,'911') insert guys values( 1996,'718') insert guys values( 1987,'970') insert guys values( 1995,'918') insert guys values( 1997,'5780') insert guys values( 1993,'914') insert guys values( 1997,'944') --use this instead in V100 and higher insert guys values( 1994,'911'),( 1996,'718'),( 1987,'970'), ( 1995,'918'),( 1997,'5780'), ( 1993,'914'),( 1997,'944') --use this for V90 and lower insert guys select 1994,'911' union all select 1996,'718' union all select 1987,'970' union all select 1995,'918' union all select 1996,'5780' union all select 1993,'914' union all select 1997,'944' union all

How to prevent inserting empty string in SQL Server?

create table t(n varchar(64) not null ,constraint chkn check (ltrim(n)!='')); insert t values('914 (this is okay!)'), (' 918 (this is okay too!)'), (' Code Project folks(this is still okay!)! '), (' '/*Not okay!*/),(null /*Not okay!*/ );

How to insert single-quote character in SQL Server?

insert messages values('Hi I''m a big fan of ''Java!'' Yeah''');

Encrypt critical data

create database secureGuys; go use secureGuys; go; --for encrypting a data, a binary field is required. create table DavidsFans(id bigint identity(-1,1),data varbinary(2048) not null); go --step1:create the key create symmetric key theKey with algorithm=AES_256 encryption by password='P!nk-FloyD-PULSE-1994'; go create trigger new on dbo.DavidsFans instead of insert as begin --saving inserted row(s) in a cursor declare C cursor local for select data from inserted; open C; declare @data varbinary(2048); fetch next from C into @data; --step2: open the symetric key for encrypting data open symmetric key theKey decryption by password='P!nk-FloyD-PULSE-1994'; --while we have row while(@@fetch_status=0)begin --step3: encrypt the data with encryptbykey function set @data=encryptbykey(key_guid('theKey'),@data); --step4: insert the encrypted data instead of pure data insert DavidsFans values(@data); fetch next from C into @data; end --step5: close the handler of key close symmetric key theKey; --close and destroy the cursor, it doesn't required if "close cursor on commit" has enabled close C; deallocate C; end --step6: have try insert DavidsFans values(cast('Me, 911992' as varbinary(2048))), (cast('Me Again' as varbinary(2048))), (cast('you' as varbinary(2048))); --step7: have look at encrypted data, how do they look? select * from DavidsFans go --a proceure for showing the data create proc _fetch as begin --step8: open the key again open symmetric key theKey decryption by password='P!nk-FloyD-PULSE-1994'; --step9: now decypt the data by decryptbtkey function select *,convert(varchar(2048),DecryptBykey(data)) as 'Real Data' from DavidsFans --step10: close the key handler close symmetric key theKey; end go exec _fetch go

Indexes, friend or foe?

select

create table folks(id bigint identity(0,1) primary key, "name" char(8), age smallint,rate float); go --truncate table folks --getting start time declare @stratTime datetime=getdate(); declare @i int=0; declare @rand float; --we are trying to insert 10K records, where there is no any non-clustered indexes while(@i<10000) begin set @rand=rand(); insert folks values(convert(char(8),round((@rand*9998),0)), convert(tinyint,@rand*255), @rand); set @i=@i+1; end print('without indexes, 10K records got '+convert(varchar(64), datediff(ms,@stratTime,getDate()))+' ms :D, good enough');

truncate table folks create nonclustered index nameIdx on folks("name" desc); create nonclustered index ageIdx on folks(age asc); create nonclustered index rateIdx on folks(rate); go --getting start time declare @stratTime datetime=getdate(); declare @i int=0; declare @rand float; --we are trying to insert 10K records, where there is no any non-clustered indexes while(@i<10000) begin set @rand=rand(); insert folks values(convert(char(8),round((@rand*9998),0)), convert(tinyint,@rand*255), @rand); set @i=@i+1; end print('with indexes, 10K records got '+convert(varchar(64), datediff(ms,@stratTime,getDate()))+' ms :(');

SQL Injection

So there is a problem, by default, SQL Server and .Net doesn't trust the code, because it may harm the system, so by default a managed code cannot access external resources, and would just get some value and return some value, but in the above code, we tried to save the content in a FILE, so it means the code is trying to access a external resource here. this kind of codes are called unsafe while their unsafe too.Easy, just tell the SQL be relax and trust every one, no enemies here.Okay, time to register your assembly (.dll) file in SQl server with unsafe permission.Okay and now, it's time to create a procedure and point to the actual method in the assembly.and finally have a try, let's have all togetherExcept procedure, trigger, function, and data-type would be signed as managed code too, but DON'T use it if you DON'T need it, SQL is heavy enough, don't make it heavier with .Net. Tips Never use 10 insert command for 10 record insert, fortunately after years and years and years Microsoft finally provided multiple row insert with one insert command implicitly, while just before 2008 select-union all approach had used.10 inserts requires 10 transaction, while 1 insert needs one, in other word, one big load is better than to many small ones.Setting a field not null doesn't mean this field doesn't accept blank strings too! so for preventing empty strings, beside not null definition just add a check constrain to check the empty ones.simple remove the spaces with ltrim() function, and check the value should not be as a empty string ''.Simple, two single-quote together means one single-quote in SQL server.SQL Server supports encryption/decryption algorithm in order to secure important data, this is very useful when a database should get shared among developers, so this ensures you no one will able access the encrypt data UNLESS he got the password!We all know index improves select fetching performance, but beside it's really helpful for DQL() but it's just like a daemon(foe) for DMLs, because when you have insert, you have one insert in physical table, and one insert into each index. and the worst part is, inserting in indexes needs to find the exact value then insert, so this is going to be very huge process.I AM NOT saying don't use indexes, I want to say remove or disable indexes when you have a bulk update or bulk copy, just check the following example and see yourself.the following code is about a simpel table that doesn't have any non-clustered index, we are trying to insert 10K records with randomized value into it, how does it may take?as you see, without indexes , DMLs are fast as Rocket, but what if we have indexes here?This is not belong to SQL Server individually, this is belong to DBMSs, so just beware when you have some big DML task, it's better to either disable or drop indexes, then recreate/enable them after the bulk task.SQL Injection, try to run a command by values, in injection, you don't have any access to the target database, or you don't know even what is going on there!? what is database vendor? how many tables? how many objects? nothing, for example, injection would be appeared in a simple login form, for example, check this simple login form below.

select userId from users where username= '{Username Value}' and password= '{password Value}';

select userId from users where username= 'Fender' and password= 'anything' or 1=1 ;--';

BUT DON'T BE SURPRISED

Okay, now we should looking for possibilities, for example we have to think about the possible command that checks entered username and password, for example it could be like this.While it never going be like above, but any way, now you have a chance to inject your code with very last value, as above code, the password value is last thing in the query, so what if we enter " anything' or 1=1 ;-- " ? so the target generated command will be like thisSo we could inject our code easily, because while password is 99.9% in false, but 1 is equal to 1 every time, so you logged in to the system without any problem,, this issue has been solved in many applications today, BUT you still have chance to search through beginners have just established their website, and have some fun, but try to learn, instead of destroy.well SQL Server is a good database, but if you ask me, I prefer PostgreSQL, this is true, SQL Server is enterprise and has a big support and development team, You and I as a developer except the features related to the database from a database, while SQL supports web services but who really is going to implement its web service with SQL Server? it's really bad while SQL Server doesn't support arrays, modern join commands and algebra, maps, hash indexes, inheritance, and, and and ..., BUT it supports registry access and web services.so have a good SQL Server database.