Tutorial by Examples: sql

Dynamic queries are SET @sql = N'SELECT COUNT(*) FROM AppUsers WHERE Username = ''' + @user + ''' AND Password = ''' + @pass + '''' EXEC(@sql) If value of user variable is myusername'' OR 1=1 -- the following query will be executed: SELECT COUNT(*) FROM AppUsers WHERE Username = 'myusername...

In order to avoid injection and escaping problems, dynamic SQL queries should be executed with parameters, e.g.: SET @sql = N'SELECT COUNT(*) FROM AppUsers WHERE Username = @user AND Password = @pass EXEC sp_executesql @sql, '@user nvarchar(50), @pass nvarchar(50)', @username, @password Second ...

This is a simple trigger function. CREATE OR REPLACE FUNCTION my_simple_trigger_function() RETURNS trigger AS $BODY$ BEGIN -- TG_TABLE_NAME :name of the table that caused the trigger invocation IF (TG_TABLE_NAME = 'users') THEN --TG_OP : operation the trigger was fired IF (TG_O...

All programming languages allow us to assign values to variables. Usually, a value is assigned to variable, standing on left side. The prototype of the overall assignment operations in any contemporary programming language looks like this: left_operand assignment_operand right_operand instructions_...

Command Structure is sqlcmd -S yourservername\instancename -d database_name -o outputfilename_withpath -Q "your select query" Switches are as follows -S for servername and instance name -d for source database -o for target outputfile (it will create output file) -Q fo...

Azure SQL Database has different editions and performance tiers. You can find version, edition (basic, standard, or premium), and service objective (S0,S1,P4,P11, etc.) of SQL Database that is running as a service in Azure using the following statements: select @@version SELECT DATABASEPROPERTYEX...

You can scale-up or scale-down Azure SQL database using ALTER DATABASE statement: ALTER DATABASE WWI MODIFY (SERVICE_OBJECTIVE = 'P6') -- or ALTER DATABASE CURRENT MODIFY (SERVICE_OBJECTIVE = 'P2') If you try to change service level while changing service level of the current database is sti...

You can create a secondary replica of database with the same name on another Azure SQL Server, making the local database primary, and begins asynchronously replicating data from the primary to the new secondary. ALTER DATABASE <<mydb>> ADD SECONDARY ON SERVER <<secondaryserver&gt...

You can put your azure SQL Database in SQL elastic pool: CREATE DATABASE wwi ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = mypool1 ) ) You can create copy of an existing database and place it in some elastic pool: CREATE DATABASE wwi AS COPY OF myserver.WideWorldImporters ( SERVICE_OBJECTIV...

Procedures, functions, triggers, and types written in .Net languages are stored in .dll files. Once you create .dll file containing CLR procedures you should import it into SQL Server: CREATE ASSEMBLY MyLibrary FROM 'C:\lib\MyStoredProcedures.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS PERM...

If you have created .Net function, compiled it into .dll, and imported it into SQL server as an assembly, you can create user-defined function that references function in that assembly: CREATE FUNCTION dbo.TextCompress(@input nvarchar(max)) RETURNS varbinary(max) AS EXTERNAL NAME MyLibrary.[Nam...

If you have create .Net class that represents some user-defined type, compiled it into .dll, and imported it into SQL server as an assembly, you can create user-defined function that references this class: CREATE TYPE dbo.Point EXTERNAL NAME MyLibrary.[Name.Space.Point] You need to specify name...

If you have created .Net method in some class, compiled it into .dll, and imported it into SQL server as an assembly, you can create user-defined stored procedure that references method in that assembly: CREATE PROCEDURE dbo.DoSomethng(@input nvarchar(max)) AS EXTERNAL NAME MyLibrary.[Name.Space....

For example: You are going to migrate an existing column from non-required to required. In this case you might need to fill some default values in your migration for rows where the altered fields are actually NULL. In case the default value is simple (e.g. "0") you might use a default or d...

As with everything in Windows Azure, You have to have a Windows Azure account and an Azure Subscription. After you have both, go to https://portal.azure.com. From here, you can add new resources to your Azure subscription. Click New on the left menu.A new blade will be added to the right of you...

The following query will return informationa about qeries, their plans and average statistics regarding their duration, CPU time, physical and logical io reads. SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, avg_duration, avg_cpu_time, avg_physical_io_reads, avg_logica...

Before getting started, make sure you have the latest Azure PowerShell installed. Once installed, start an Azure PowerShell session from your machine. First, you'll need to log in and authenticate to Windows Azure. Add-AzureRmAccount You'll receive a dialog box asking for your Azure credentials...

update <SCHEMA_NAME>.<TABLE_NAME_1> AS A SET <COLUMN_1> = True FROM <SCHEMA_NAME>.<TABLE_NAME_2> AS B WHERE A.<COLUMN_2> = B.<COLUMN_2> AND A.<COLUMN_3> = B.<COLUMN_3>

In mysqld.cnf or equivalent: [mysql] prompt = '\u@\h [\d]> ' This achieves a similar effect, without having to deal with .bashrc's.

ORMLite is an Object Relational Mapping package that provides simple and lightweight functionality for persisting Java objects to SQL databases while avoiding the complexity and overhead of more standard ORM packages. Speaking for Android, OrmLite is implemented over the out-of-the-box supported da...