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
- Exec() and sp_executesql
- xp_regwrite and xp_regread
- Cursor
instead oftriggers- Multi-Threading in SQL Server
- Managed Code
- Tips
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.
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 requiredthen 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.exe2: 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 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.
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?
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?
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?
Yeshow 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.aUnlike 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, usinginstead 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?
Usinginstead 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.
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 oftrigger calls its event again, it doesn't call itself again, the actual event will be invoked, for example aninstead oftrigger 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 oftrigger 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 beinstead oftrigger, other triggers will no fired UNLESSinstead oftrigger repeats the event. - Use
instead ofif 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 brokerWhat 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
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
How to execute a procedure parallel in SQL server?
Simple, as following order.- 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
*/
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; goThen 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.
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 onOkay, 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 = unsafeOkay 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.
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.
