Tutorial by Examples: n

Queries that filter or sort data by some value in JSON column usually use full table scan. SELECT * FROM ProductCollection WHERE JSON_VALUE(Data, '$.Color') = 'Black' To optimize these kind of queries, you can add non-persisted computed column that exposes JSON expression used in filter or sort...

If you can use memory-optimized tables, you can store JSON as text: CREATE TABLE ProductCollection ( Id int identity primary key nonclustered, Data nvarchar(max) ) WITH (MEMORY_OPTIMIZED=ON) Advantages of JSON in in-memory: JSON data is always in memory so there is no disk access Ther...

PL/SQL uses IN, OUT, IN OUT keywords to define what can happen to a passed parameter. IN specifies that the parameter is read only and the value cannot be changed by the procedure. OUT specifies the parameter is write only and a procedure can assign a value to it, but not reference the value. IN ...

OPENJSON function parse JSON text and returns all key:value pairs at the first level of JSON: declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}'; SELECT * FROM OPENJSON(@json); keyvaluetypeNameJoe1age272ski...

OPENJSON function parses collection of JSON objects and returns values from JSON text as set of rows. declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00","Customer": "MSFT","Price":59.99,"Q...

OPENJSON function parses collection of JSON objects and returns values from JSON text as set of rows. If the values in input object are nested, additional mapping parameter can be specified in each column in WITH clause: declare @json nvarchar(4000) = N'[ {"data":{"num":&quot...

OPENJSON can extract fragments of JSON objects inside the JSON text. In the column definition that references JSON sub-object set the type nvarchar(max) and AS JSON option: declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00"...

JSON may have complex structure with inner arrays. In this example, we have array of orders with nested sub array of OrderItems. declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00", "Items":[{"Price"...

A RANK() Returns the rank of each row in the result set of partitioned column. Eg : Select Studentid,Name,Subject,Marks, RANK() over(partition by name order by Marks desc)Rank From Exam order by name,subject Studentid Name Subject Marks Rank 101 Ivan Maths ...

Same as that of RANK(). It returns rank without any gaps: Select Studentid, Name,Subject,Marks, DENSE_RANK() over(partition by name order by Marks desc)Rank From Exam order by name Studentid Name Subject Marks Rank 101 Ivan Science 80 1 101 Ivan ...

In SQL Server, there are two categories of triggers: DDL Triggers and DML Triggers. DDL Triggers are fired in response to Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER and DROP. DML Triggers are fired ...

SQL Server 2012 It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need date/integer format. Then this will help us to achieve the same. Syntax: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] ) TRY_CON...

When you convert expressions from one type to another, in many cases there will be a need within a stored procedure or other routine to convert data from a datetime type to a varchar type. The Convert function is used for such things. The CONVERT() function can be used to display date/time data in v...

// Define new router group $api = new \Phalcon\Mvc\Router\Group([ 'module' => 'api', ]); $api->setPrefix('/api/v1'); // API routes (Maps to Cotnroller::Action) $api->addGet('/users', 'Users::index'); $api->addGet('/users/search/{query}', 'Users::search'); $api->addGet('/...

$router = new \Phalcon\Mvc\Router(false); $router->removeExtraSlashes(true); $request = new \Phalcon\Http\Request(); $action = strtolower($request->getMethod()); // get, post, etc. $modules = ['calendar', 'main', 'user']; // names of the modules you create // you can define other static...

In this example every positioned element creates its own stacking context, because of their positioning and z-index values. The hierarchy of stacking contexts is organized as follows: Root DIV #1 DIV #2 DIV #3 DIV #4 DIV #5 DIV #6 It is important to note that DIV #4, DIV #5 and D...

A list consists of <li> elements inside a containing element (<ul> or <ol>). Both the list items and the container can have margins and paddings which influence the exact position of the list item content in the document. The default values for the margin and padding may be differe...

Sometimes, a list should just not display any bullet points or numbers. In that case, remember to specify margin and padding. <ul> <li>first item</li> <li>second item</li> </ul> CSS ul { list-style-type: none; } li { margin: 0; pad...

Using the overflow property with a value different to visible will create a new block formatting context. This is useful for aligning a block element next to a floated element. CSS img { float:left; margin-right: 10px; } div { overflow:hidden; /* creates block formatting context...

Its possible to change *.vmoptions and idea.properties files without editing them in the PhpStorm installation folder. Follow the steps below: Step 1: Run Help - Edit Custom VM Options... Step 2: Confirm the creation of the configuration file, if prompted Step 3: Add following lines if yo...