Jul 9, 2013

SQL Server T-SQL Tips & Tricks

Introducing

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.

Background

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.

Getting Started

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.
Table Of Content

xp_cmdshell

How to run CMD commands in SQL Server?

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.

fortunately this procedure is disabled by default because of security concerns.

How to Enable xp_cmdshell in SQL Server?

this is going to be easy, at the first we need to enable advanced option with sp_configure procedure like this
--it doesn't really show anything advanced
sp_configure 'show advanced options','1';
reconfigure;--it's required
then after enabling advanced options and reconfigure the system, now it's time for enabling xp_cmdshell procedure
sp_configure 'xp_cmdshell','1';
reconfigure;
And 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.
exec xp_cmdshell 'echo %JAVA_HOME% , %INTELOCLSDKROOT%;'
exec xp_cmdshell 'echo %WinDir%;'
exec xp_cmdshell 'echo ''Hi Buddy'';','no_output'
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 command
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

1: you cannot run every all applications, generally terminal applications work without any problem but you cannot run a GUI application like notepad.exe
2: it waits(block) until the command you run response.
3: you cannot set or get application's stream directives.

Example

now let have a good example.

How to call BCP tool is SQL Server?

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_cmdshell
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?

easy using sp_executesql procedure
Both 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.

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

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.
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?

Yes
While 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?

yes, the installation path is located in registry.
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?

yes, find the installation path, and run the SQL agent process by command.
--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?

Yes

how to enable SQL Authentication mode by T-SQL?

Set the (HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer)loginmode key to 2
--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?

Cursor 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.

How to declare a cursor in SQL Server?

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.
--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
Unlike explicit transactions, by default cursors are alive until you deallocate(remove) them by deallocate command
--removing cursor c completely, it's better close it before deallocation
deallocate c;
for 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 cursor
  +------------------------------------------+
  |--> 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

The 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.
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?

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.
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

yes, using instead of triggers.
Just create an instead of trigger on target object for desired event(s), but SQL doesn't (instead of) support it for every DDL operations of course.

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

Using 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
As 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

this story is same for instead of triggers in SQL server too. the following list is showing the key notes about the instead of triggers.
  • 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.
  • There is only ONE instead of 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.
  • 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?

yes, but not as same as you do in Java or C, using Service broker

What does service broker do in SQL Server?

service broker is a semi-implementation of Message Oriented Architecture(MOA)

What is MOA?

it's a pattern(architecture) designed for heterogeneous to communicate with each other. The main MOA features are listed below:
  • 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
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.

What are a MOA's components?

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.
  • 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
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?

Simple, as following order.
  1. Activate the service brokers in both client and server points(in this example both client and server are same)
  2. Create a procedure for handling request in server
  3. Create Message type and contract in both client and server points
  4. Create the server queue and bind the procedure to
  5. create the server service and bind the service to it
  6. create the queue in client
  7. create the service in client for sending messages
  8. 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
*/
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

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

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.
exec sp_configure 'clr enabled', 1
go
reconfigure;
go
Then code the business in .Net
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");
    }
  }
}
Then compile the class, and get the .dll or .exe file.

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.

How to run unsafe managed codes in SQL Server?

Easy, just tell the SQL be relax and trust every one, no enemies here.
-- tell sql take it easy, why so restriction?! be open
alter database mydb set trustworthy on
Okay, time to register your assembly (.dll) file in SQl server with unsafe permission.
create assembly myAssembly authorization dbo
from 'C:\managed\managed.dll' with permission_set = unsafe
Okay and now, it's time to create a procedure and point to the actual method in the assembly.
create procedure writeToFileProc (@name nvarchar(1024),@data nvarchar(4000)) as external name
myAssembly.[wasys911992.blogger.example.sql.StoredProcedures].writeToFile;
and finally have a try, let's have all together
--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';
Except 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

Use one insert command to insert multiple records

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.
--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
10 inserts requires 10 transaction, while 1 insert needs one, in other word, one big load is better than to many small ones.

How to prevent inserting empty string in SQL Server?

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.
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!*/ );
simple remove the spaces with ltrim() function, and check the value should not be as a empty string ''.

How to insert single-quote character in SQL Server?

Simple, two single-quote together means one single-quote in SQL server.
insert messages values('Hi I''m a big fan of ''Java!'' Yeah''');

Encrypt critical data

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!
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?

We all know index improves select fetching performance, but beside it's really helpful for DQL(select) 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?
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');
as you see, without indexes , DMLs are fast as Rocket, but what if we have indexes here?
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 :(');
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

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.

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.
select userId from users where username= '{Username Value}' and password= '{password Value}';
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 this
select userId from users where username= 'Fender' and password= 'anything' or 1=1 ;--';
So 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, BUT DON'T BE SURPRISED, 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.

Nothing more 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.