Here are two best practices for partitioning that pertain to the question:

Keep an empty staging partition at the leftmost and rightmost ends
of the partition range to ensure that the partitions split when
loading in new data, and merge, after unloading old data, do not
cause data movement.
Do not split or merge a partition already populated with data
because this can cause severe locking and explosive log growth.

http://www.informit.com/articles/article.aspx?p=1946159&seqNum=5
If the leftmost end of your partition is empty, use ALTER PARTITION FUNCTION SPLIT RANGE to add new ranges to the partition function.
To check if the leftmost partition is empty, use a query like the following:
DECLARE @PartitionFunctionName sysname = 'YourPartitionFunctionNameHere';

SELECT
p.partition_number, SUM(pst.row_count) RowCountInPartition, pf.name PartitionFunction, ps.name PartitionScheme
FROM sys.dm_db_partition_stats pst
INNER JOIN sys.partitions p ON pst.partition_id = p.partition_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE pf.name = @PartitionFunctionName
GROUP BY p.partition_number, pf.name, ps.name;

If the first partition is not empty, the best practices recommend that you create a new function with all values, create a new table on that function, then insert the data to the new table.
Also, if the left partition just has a few records, a split may be fine. Not sure on that as I've never tried it.
Whatever you do, make sure to leave some empty partitions at the leftmost and rightmost partition when you're finished. I might even go so far as to creating partition ranges for 0 and 1, then add a check constraint to prevent the first partition from getting data in it. Do the same thing for the end.


You could also split the string and then search on the splitted values.
An example could be by creating and using this function.

Starting from SQL Server 2016 you could use the built in STRING_SPLIT() function.

Creating the function from the blogpost and executing this query afterwards

SELECT DISTINCT Col1,Col2
FROM dbo.Searching
CROSS APPLY dbo.fnSplitString(col2,',')
WHERE splitdata in ('ABS','MCM');

Result

Col1    Col2
1   ACA,ABS,PHR
3   MCM

DB<>Fiddle

An optimization could be proactively splitting the table into a permanent one so you don't need to do splitting on the entire table everytime.  (If the values are static)

EDIT

Comment by @Erikdarling

  There are better ways to split strings: Comparing string splitting /
  concatenation methods, including a much better one at SSC by Jeff
  Moden.

The function by Jeff Moden

After creating the function in his blogpost, you could use a query that resembles the previous one.

  SELECT Col1,Col2
  FROM dbo.Searching
  CROSS APPLY dbo.DelimitedSplit8k(Col2,',') split
  WHERE split.Item in ('ABS','MCM');

DB<>Fiddle


PostgreSQL SELECT primary key as "serial" or "bigserial"

Your query would fail, because the standard name of an integer is "integer", not "int". You can avoid this kind of error by comparing the internal regtype OID instead of a text representation. Many basic data types have several alias names, they all resolve to the same internal registered type.
That aside, you can largely simplify and improve:

SELECT a.attname
     , CASE a.atttypid
         WHEN 'bigint'::regtype THEN 'bigserial'
         WHEN 'int'::regtype    THEN 'serial'
         ELSE format_type(a.atttypid, a.atttypmod)
       END AS type
FROM   pg_index     i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
WHERE  i.indrelid = 'tbl'::regclass
AND    i.indisprimary
AND    a.attnum = ANY(i.indkey);

While this improves the query, it's still not doing what you hope it would.

Just because an integer column is (part of) the primary key, that doesn't make it a serial column, yet. Here is a detailed assessment of what a serial is:

Safely and cleanly rename tables that use serial primary key columns in Postgres?

You don't find anything for the presented table, because you are basing your query on pg_index, which is completely unrelated to serial types. A serial does not have to be indexed, only primary keys happen to be indexed.

Safe solutions

Just detect the serial type of the PRIMARY KEY:

SELECT a.attrelid::regclass::text, a.attname
     , CASE a.atttypid
         WHEN 'int'::regtype  THEN 'serial'
         WHEN 'int8'::regtype THEN 'bigserial'
         WHEN 'int2'::regtype THEN 'smallserial'
       END AS serial_type
FROM   pg_attribute  a
JOIN   pg_constraint c ON c.conrelid  = a.attrelid
                      AND c.conkey[1] = a.attnum
JOIN   pg_attrdef   ad ON ad.adrelid  = a.attrelid
                      AND ad.adnum    = a.attnum
WHERE  a.attrelid = 'tbl'::regclass   -- table name, optionally schema-qualified
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    a.atttypid = ANY('{int,int8,int2}'::regtype[]) -- integer type
AND    c.contype = 'p'                 -- PK
AND    array_length(c.conkey, 1) = 1   -- single column
AND    pg_get_expr(ad.adbin, ad.adrelid)
     = 'nextval('''
    || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
    || '''::regclass)';                -- col default = nextval from owned seq

Returns nothing if the PK isn't a serial type.

Addressing comment by @jpmc26

A simplified check for just:

pg_get_serial_sequence(attr.attrelid::regclass::text, attr.attname) IS NOT NULL

would only check if there is a sequence "owned" by the column, but not whether the column default is also set to draw numbers from the sequence. The documentation:

  The function probably should have been called pg_get_owned_sequence;
  its current name reflects the fact that it's typically used with
  serial or bigserial columns.

To show ALL columns with proper data type - replaced with the appropriate serial type where applicable:

SELECT a.attrelid::regclass::text, a.attname
     , CASE WHEN a.atttypid = ANY ('{int,int8,int2}'::regtype[])
          AND EXISTS (
             SELECT FROM pg_attrdef ad
             WHERE  ad.adrelid = a.attrelid
             AND    ad.adnum   = a.attnum
             AND    pg_get_expr(ad.adbin, ad.adrelid)
                  = 'nextval('''
                 || (pg_get_serial_sequence (a.attrelid::regclass::text
                                          , a.attname))::regclass
                 || '''::regclass)'
             )
        THEN CASE a.atttypid
                WHEN 'int'::regtype  THEN 'serial'
                WHEN 'int8'::regtype THEN 'bigserial'
                WHEN 'int2'::regtype THEN 'smallserial'
             END
        ELSE format_type(a.atttypid, a.atttypmod)
        END AS data_type
FROM   pg_attribute  a
WHERE  a.attrelid = 'tbl'::regclass  -- table name, optionally schema-qualified
AND    a.attnum > 0
AND    NOT a.attisdropped
ORDER  BY a.attnum;

The check on the column default might break with exotic settings for search_path. Didn't test all combinations.

db<>fiddle here


Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?

On 18th of November, 1883 at 12:00 (new time), standard time was adopted by the American railroads.
This means that before that time, Los Angeles used actual local time, based on mean solar time. After that, it was moved to its local time zone, which, being an integral offset of hours from the Greenwich Mean Time, was slightly different from the previous time.
Want to know more?

Download the tzdata Timezone Database from IANA: Time zones.

Inside, you'll find the definitions of the (many) timezones, which have lots of variations over time, along with lots of comments detailing what changes where made and when. It's entertaining reading!

Wikipedia has also some interesting facts, in the Wikipedia: Time zone page, regarding the 1883, November 18 change:

Railway time
...
Timekeeping on the American railroads in the mid-19th century was somewhat confused. Each railroad used its own standard time, usually based on the local time of its headquarters or most important terminus, and the railroad's train schedules were published using its own time. Some junctions served by several railroads had a clock for each railroad, each showing a different time.
... Dowd's system was never accepted by American railroads. Instead, U.S. and Canadian railroads implemented a version proposed by William F. Allen, the editor of the Traveler's Official Railway Guide. The borders of its time zones ran through railroad stations, often in major cities. For example, the border between its Eastern and Central time zones ran through Detroit, Buffalo, Pittsburgh, Atlanta, and Charleston. It was inaugurated on Sunday, November 18, 1883, also called "The Day of Two Noons", when each railroad station clock was reset as standard-time noon was reached within each time zone. The zones were named Intercolonial, Eastern, Central, Mountain, and Pacific. ...

Also note that this is not specific to Postgresql. This is valid for any software or operating system that uses the tzdata database (though of course many will be limited to dates either post 1970 or post 1901, so 1883 is beyond reach, but there are many, many other adjustments all over the place at different times).


How do I identify the source of the 17886 errors ("server will drop the connection") in my SQL errorlog?

Try to use
select * from sys.dm_os_ring_buffers

to get more details about source clients.
Try filter type RING_BUFFER_CONNECTIVITY.


Most efficient way of finding rows in a table that don't exist in another -- SQL Server

SELECT tbl_A.id
FROM tbl_A
LEFT JOIN tbl_B ON tbl_A.id = tbl_B.id
WHERE tbl_B.id IS NULL

This is most likely how I would do something like that. Just a simple join where the column in A does not equal the columns in B. The DBMS should take care of optimizations.

As for varchar and char: char is more efficient when using data that is all the same length.

Here is an SQLFiddle example http://sqlfiddle.com/#!9/bbbbc3/1


Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures

Querying a column that can contain nulls is more complex than querying a column that cannot.  So also is querying multiple tables more complex than querying one table.  I wouldn't let the avoidance of null drive the normalization.

For example, you mentioned that all devices will eventually be disposed and get a disposal date.  If there are no other columns in the Disposal table, then in my mind it makes more sense to put DisposalDate in the Device table.  Other tables like Sanitize might make more sense as separate tables because there are multiple data points that will not apply to some Devices.

Check constraints are great and should be used when possible, but there will always be times when a procedure is necessary.


Explain shows index doesnt working but i have an index

OR does not optimize well.  The usual workaround is to reformulate the query to be the UNION of multiple SELECTs to look for the alternatives.  However, your situation might work better with a second LEFT JOIN and COALESCE().

SELECT ..., COALESCE(r.sender, s.receiver), ...
    FROM ...
    LEFT JOIN ... AS r WHERE receiver = '...'
    LEFT JOIN ... AS s WHERE sender = '...'

Side notes:

What the heck is the id that needs to be varchar(250)?
There are two identical indexes on username; drop one.
Switch from MyISAM to InnoDB.  Virtually all arguments for MyISAM are "old wives' tales".


What you see in your execution plan is Sybase performing a DISTINCT operator.

When you do a SELECT DISTINCT in Sybase it first builds a Worktable (as you can see in step 1 of your execution plan) and then it selects sorted data removing duplicates from that worktable (which is what you see in step 2). This is documented

The sort algorithm doesn't appear to be disclosed so if the Sybase developers, with all their wisdom, introduced an optimization where they use data length to determine if values are duplicates, that could have the effect you describe as:

  I have reasons to believe that it does the sorting on the size of the
  data in column for different rows, and for rows of same length, it
  does a alphabetical

The moral of the story is:

If you want your data sorted add an ORDER BY clause

As to your question

  in what order does GETSORTED actually sorts the data?

In the order the algorithm can remove duplicates most efficiently.


Upgrade SQL 2008R2 to SQL 2016 on windows server 2012R2

Well, once you upgrade there is no going back. So that's a big danger in doing in place upgrades and why so many people don't do it. Some straight up won't do it for production. In place also has more downtime.

Unless you're on a modern OS (2016 or 2019) I wouldn't do an in-place upgrade as it limits your supportability given WS 2012R2 ended mainstream support last year.


Your newer .NET Framework app seem to be using Windows authentication to connect to SQL Server
If that is your intent, make sure the app itself runs on user machines on behalf of domain user that has a Windows-authenticated login at SQL Server
For example, if your SQL Server has login [Domain/Name.Surname] account, make sure your newer .NET Framework app runs on behalf of that domain account, then it should be able to connect to SQL Server
p.s. I am seeing this error from time to time when our developers launch SSMS on behalf of their local Windows accounts and trying to use Windows authentication to connect to SQL Server, instead of launching SSMS on behalf of their domain user accounts (which have corresponding logins at SQL Server)
p.p.s. if your intent was actually use SQL authentication for connecting app to SQL Server, remove Integrated Security = True/SSPI from connection string...


Can I kill these MSCRM related SQL processes?

The SQL backups have been failing for a few days... I tried to just do a quick manual full backup and got an error:

It seems like operation which conflict with backup are running at the time which backup was started so the backup failed. And as per your comment SPDI 186 is running which is showing backup which means that database backup is already running when new backup was being started. Concurrent full backup is not allowed when full backup is already running.

Also make sure no database shrink operation is running or Auto Shrink is not enabled. This can also interfere in backup operation.

select is_auto_shrink_on, name from sys.databases

let us see how many databases have auto shrink enabled

Yes you an kill backup operation (SPID 186 here) but you would loose all the time spent in taking backup and you would have to start all over again. Before doing that let us see how much backup has finished, what is output

select percent_complete,session_id,status,total_elapsed_time
from sys.dm_exec_requests where session_id=186

EDIT: There is NO restart required

Edit:

After discussing on chat it was identified that Veeam backup was running for past 2-3 days and was somehow hung. OP killed the process and it went into rollback state where it was again stuck OP has to ultimately restart the VM and the issue seems to have resolved.

Also advised OP to log a ticket with Veeam because that was definitely a third party Veeam backup issue.


If I understand you correctly, your original

  Unique (name, json_map_enum, entity_json_map_fk)

Is made up of

  name: name of the attribute
  json_map_enum: the position of this attribute in the entity
  entity_json_map_fk: reference to entity

You should be able to enforce this in the mapping table as well.

CREATE TABLE Entity_Attribute_Map (
    ATTRIBUTE_JSON_MAP_fk int references ATTRIBUTE_JSON_MAP(id),
    entity_json_map_fk int references entity_json_map(id),
    JSON_MAP_ENUM int,
    Primary Key (ATTRIBUTE_JSON_MAP_fk, entity_json_map_fk, JSON_MAP_ENUM)
)


Why does this RX-X lock not appear in Extended Events?

The single row insert acquires an X (exclusive) lock on the new row.

The SELECT attempts to acquire a range-shared, key shared (RangeS-S) lock.

This request is reported by the lock_acquired Extended Event as mode = RS_S.

It is reported by the Profiler event class Lock:Acquired as mode 13 (LCK_M_RS_S).

The requested mode is combined with the existing exclusive lock mode in Lock::CalculateGrantMode in sqlmin.dll. There is no combined mode of range-shared, key exclusive (RangeS-X) so the outcome of the calculation is range-exclusive, key exclusive (RangeX-X), which happens to be mode 15.

The grant mode calculation above is performed just before the extended event is generated by lck_ProduceExtendedEvent<XeSqlPkg::lock_acquired>. Nevertheless, both Profiler and Extended Events log the requested RangeS-S mode, not the resulting lock mode RangeX-X. This is counter to the limited documentation, which says:

  Mode | int | Resulting mode after the lock was acquired.

The mode column of the extended event has no documentation at all, and the description in the meta data is blank. Perhaps Microsoft themselves weren't even sure of the behaviour.

I have often thought it would be more useful if lock events reported both the requested and resulting modes, but that is not what we have. The current arrangement makes it pretty much impossible to track and match up lock acquisition and release.

There might be a good reason for reporting locks this way. If it doesn't meet your needs, you could open a support case with Microsoft, or create an Azure Feedback item.

LAST_MODE

The mysterious LAST_MODE is something Erik Darling has remarked on before. It is the highest map_key value in the list of lock modes exposed by sys.dm_xe_map_values:

SELECT
    DXMV.map_key,
    DXMV.map_value
FROM sys.dm_xe_map_values AS DXMV
WHERE
    DXMV.[name] = N'lock_mode'
ORDER BY
    DXMV.map_key;

╔═════════╦═══════════╗
║ map_key ║ map_value ║
╠═════════╬═══════════╣
║       0 ║ NL        ║
║       1 ║ SCH_S     ║
║       2 ║ SCH_M     ║
║       3 ║ S         ║
║       4 ║ U         ║
║       5 ║ X         ║
║       6 ║ IS        ║
║       7 ║ IU        ║
║       8 ║ IX        ║
║       9 ║ SIU       ║
║      10 ║ SIX       ║
║      11 ║ UIX       ║
║      12 ║ BU        ║
║      13 ║ RS_S      ║
║      14 ║ RS_U      ║
║      15 ║ RI_NL     ║
║      16 ║ RI_S      ║
║      17 ║ RI_U      ║
║      18 ║ RI_X      ║
║      19 ║ RX_S      ║
║      20 ║ RX_U      ║
║      21 ║ LAST_MODE ║
╚═════════╩═══════════╝

The memory structure accessed via the DMV (using sqlmin!CMapValuesTable) is stored starting at the address sqlmin!XeSqlPkg::g_lock_mode. Each 16-byte entry in the structure contains the map_key and a pointer to the string returned as map_value by the streaming TVF.

The strings are stored exactly as shown in the table above (though not in that order). It seems to be an error that entry 21 has a map_value of "LAST_MODE" instead of the expected "RX_X". Erik Darling has reported the issue on Azure Feedback.


Could it be that you need to take into account the System Cache and Kernel Memory pages?

It would help a lot if you added some actual numbers from your server and what you expect, to your question.

For example, here is my test server's memory state:

The code is:

SELECT 'Page File' AS MemoryType
    ,CONVERT(DECIMAL(10, 2), 1.0 * total_page_file_kb / 1024) AS Total_MB
    ,CONVERT(DECIMAL(10, 2), 1.0 * available_page_file_kb / 1024) AS Free_MB
    ,CONVERT(VARCHAR(5), CONVERT(DECIMAL(10, 1),
        100.0 * available_page_file_kb / total_page_file_kb)) + '%' AS Free
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 'Physical (RAM)'
    ,CONVERT(DECIMAL(10, 2), 1.0 * total_physical_memory_kb / 1024)
    ,CONVERT(DECIMAL(10, 2), 1.0 * available_physical_memory_kb / 1024)
    ,CONVERT(VARCHAR(5), CONVERT(DECIMAL(10, 1),
        100.0 * available_physical_memory_kb / total_physical_memory_kb)) + '%'
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 'Non-Physical'
    ,CONVERT(DECIMAL(10, 2),
         1.0 * (total_page_file_kb - total_physical_memory_kb) / 1024)
    ,CONVERT(DECIMAL(10, 2),
         1.0 * (available_page_file_kb - available_physical_memory_kb) / 1024)
    ,CONVERT(VARCHAR(5), CONVERT(DECIMAL(10, 1),
       100.0 * (available_page_file_kb - available_physical_memory_kb)
             / (total_page_file_kb - total_physical_memory_kb))) + '%'
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 'System Cache',CONVERT(DECIMAL(10, 2), 1.0 * system_cache_kb / 1024),NULL,NULL
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 'Kernel: Paged'
    ,CONVERT(DECIMAL(10, 2), 1.0 * kernel_paged_pool_kb / 1024),NULL,NULL
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 'Kernel: Non-Paged'
    ,CONVERT(DECIMAL(10, 2), 1.0 * kernel_nonpaged_pool_kb / 1024),NULL,NULL
FROM sys.dm_os_sys_memory
ORDER BY Total_MB DESC


Code review database design for storing college assessments & enrolments

Just a few thoughts.

I would not use ENUMs. I hate to say "always" (open to correction on this) but I would "always" use a FOREIGN KEY. Consider gender - 25 years ago, you could have had an ENUM ('M', 'F') - now you can have:

Unknown
Intersex
Non-binary
Decline-to-answer
Don't know
Don't care :-)

&c., &c...

With a FOREIGN KEY things are far easier to change into the future. Furthermore, you make the point that you want your database to be well-structured, extensible, and "intuitive" for future maintainers(*) - FOREIGN KEYs will do this by making your system portable to another RDBMS! After an FK, I'd go for a CHECK CONSTRAINT!

(*) upvoting for this alone! If only all system designers were so thoughtful - some of my  sleepless nights as a programmer might have been avoided! :-)

Check out this thread on SO (both question and accepted answer from the same poster). It tries to go through all options (but misses one - see below!) and the "consensus" (if such a thing can be said to exist on these sites) is, FKs, followed by CCs. Check out this - there's a reason that poster has a rep > 300,000 (not to mention a shedload here too)!

Gotchas. MySQL (amazingly!) doesn't support CKs! Plus, another factor mitigating against CCs is that, frequently, DBAs and/or sysadmins are the only operators allowed to change system tables (which is what modifying CCs involves) whereas ordinary programmers/users can change ordinary tables.

A corollary to this is that if you want tight control over disk activity, you might want the smallest possible amount of it on your system tables - every operation involving a CC involves querying them, so the overhead of querying the FK tables is not such a big deal after all.

A factor in favour of CCs over ENUMs is that you can use the LIKE and other operators on them - you can't with the latter. Finally, take a look at the horrible syntax here to compare values in ENUMs to other values (remember they're different types!). And if you're doing lookups of values for, say, dropdowns, you'd have to use something like this:

select n.nspname as enum_schema,
       t.typname as enum_name,
       e.enumlabel as enum_value
from pg_type t
   join pg_enum e on t.oid = e.enumtypid
   join pg_catalog.pg_namespace n ON n.oid = t.typnamespace

as opposed to:

select my_field from my_table;

All in all, I would strongly urge you to use FKs or CCs to enforce Declarative Referential Integrity (DRI) on your tables.

There are (mentioned only for completeness) ways of restricting values in tables - you could use DOMAINs or TRIGGERs. The former seem superior to ENUMs, whereas TRIGGERs (IMHO) should only be used for complex business logic!

Finally, to answer the question (phew...).

Your schema seems fine - it's pretty difficult to judge these things by eye. What I would do if I were you, is to start using/developing against it and see if you come up against problems. Get back to us here with specific issues if you do - your question is a bit broad! p.s. welcome to the forum!


How to convert varchar iPv4 Address into a number deterministically

One option is to use SQLCLR. It is rather easy to create a method to split the string on the periods, convert each to an Int64, and multiply by the appropriate power of 256. Be sure to set IsDeterministic=true in the SqlFunction attribute, which will not only allow it to be persisted, but will also allow it to participate in parallel plans :-). And in fact, the SQL# SQLCLR library (that I created) contains functions (which are in the Free version) to do this conversion: INET_AddressToNumber and INET_NumberToAddress.

Test Setup:

CREATE TABLE #TempAddresses (IPAddress VARCHAR(15) NULL);

-- populate 1 million rows, with a NULL every 1000 rows
;WITH cte AS
(
  SELECT  TOP (1000000) ac1.column_id,
          ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [RowNum]
  FROM    master.sys.all_columns ac1
  CROSS APPLY  master.sys.all_columns ac2
)
INSERT INTO #TempAddresses ([IPAddress])
SELECT CASE cte.[RowNum] % 1000
         WHEN 0 THEN NULL
         ELSE CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
       END
FROM   cte

Regarding the following three tests, please note:

They are shown in order of fastest to slowest.
I ran each SELECT several times and kept the best time for each, not average time.

SQLCLR Test and Results:

DECLARE @Test1 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test1 =  -- Comment out "@Test1 = " to test returning the value
       SQL#.INET_AddressToNumber(CONVERT(NVARCHAR(15), tmp.[IPAddress]))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 2454 ms,  elapsed time = 5133 ms.
-- CPU time = 1594 ms,  elapsed time = 1617 ms. (into variable)

MartinSmith's Test and Results:

DECLARE @Test3 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test3 =  -- Comment out "@Test3 = " to test returning the value
         256 * 256 * 256 * CAST(FLOOR(LEFT(tmp.[IPAddress],3)) AS BIGINT)
       +       256 * 256 * CAST(FLOOR(SUBSTRING(tmp.[IPAddress],1 + CHARINDEX('.', tmp.[IPAddress]),3)) AS BIGINT)
       +             256 * SUBSTRING(REPLACE(tmp.[IPAddress],'.',SPACE(8)),19, 9)
       +                   RIGHT(tmp.[IPAddress], -1 + CHARINDEX('.', REVERSE(tmp.[IPAddress])))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 2781 ms,  elapsed time = 5151 ms.
-- CPU time = 2156 ms,  elapsed time = 2156 ms. (into variable)

O.P.'s Test and Results:

DECLARE @Test2 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test2 =  -- Comment out "@Test2 = " to test returning the value
       ((256*256*256) * CONVERT(BIGINT, LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),1, 9)))))
       + ((256*256)*CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),10, 9)))))
       + ((256)*CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),19, 9)))))
       + (CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),28, 9)))))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 3531 ms,  elapsed time = 5249 ms.
-- CPU time = 2515 ms,  elapsed time = 2534 ms. (into variable)

One thing to keep in mind regarding the SQLCLR option: using a SQLCLR function in a computed column is a dependency that will prevent the Assembly containing the function from being dropped. If you are able to use ALTER ASSEMBLY, then you won't being dropping and re-creating the Assembly. But ALTER ASSEMBLY is only allowed if there are no new or removed methods, and if the signatures of existing methods are still the same. If any of those changes are being made, then you will have to drop the Assembly and then create it again. And that will require that you first drop the computed column that is referencing the SQLCLR function contained in that Assembly. This might make the SQLCLR option less desirable in the use case of placing a function in a computed column, whereas if the function was in a query, Stored Procedure, View, etc, then this wouldn't be a concern.

It is possible to wrap the SQLCLR function in a T-SQL function and reference the T-SQL function in the computed column, but then you lose the benefit of SQLCLR functions being able to participate a parallel execution plan.


SQL Server datetime column datatype stores offset

You might want to consider looking at the following page:

Date and Time Data Types and Functions (Transact-SQL)

Ther you will find a link to the following information:

datetimeoffset (Transact-SQL)

The datetimeoffset data type is defined as:

  Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

An example is given as follows:

DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';
DECLARE @date date= @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date';

@datetimeoffset                  date
-------------------------------- ----------
2025-12-10 12:32:10.0000 +01:0   2025-12-10

(1 row(s) affected)

You might also want to look at the AT TIME ZONE (Transact-SQL) function when referencing a datetimeoffset value:

  Converts an inputdate to the corresponding datetimeoffset value in the target time zone. If inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate value is provided in the target time zone. If inputdate is provided as a datetimeoffset value, than AT TIME ZONE clause converts it into the target time zone using time zone conversion rules.


EMPTYFILE is just there is transfer all the pages to other files, so you subsequently can do ALTER DATABASE ... REMOVE FILE. This doesn't seem to be what we want here.

I wouldn't worry about this, just let it sort itself at next re-start. Remember that tempdb is re-created based on what sys.master_files says. This is the "template" for tempdb regarding number, placement, size etc of the files.

You adjust the size in sys.master_files using ALTER DATABASE ... MODIFY FILE. If you want help, just use SSMS, tempdb, properties, and adjust the size for any of the tempdb files to a larger size. Then script the command and adjust to your liking. (The reason to not specify a smaller size in the GUI is that it will generate a DBCC SHRINKFILE, which only (tries) to address the current size for tempdb and does nothing for the template. It is perfectly possible to specify a smaller size than current size using ALTER DATABASE for tempdb as it will only affect the "template" and not the current size.


Reset all sequences so that they continue after max(id) + 1?

I see you are using Django.
I implemented a command to resolve these issues for all databases in your settings file.
import psycopg2
from django.conf import settings
from django.core.management.base import BaseCommand
from django.db import connections

def dictfetchall(cursor):
    """Return all rows from a cursor as a dict"""
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

class Command(BaseCommand):
    help = "Resets sequencing errors in Postgres which normally occur due to importing/restoring a DB"

    def handle(self, *args, **options):
        # loop over all databases in system to figure out the tables that need to be reset
        for name_to_use_for_connection, connection_settings in settings.DATABASES.items():
            table_name = connection_settings['NAME']
            host = connection_settings['HOST']
            user = connection_settings['USER']
            port = connection_settings['PORT']
            password = connection_settings['PASSWORD']

            # connect to this specific DB
            conn_str = f"host={host} port={port} user={user} password={password}"

            conn = psycopg2.connect(conn_str)
            conn.autocommit = True

            select_all_table_statement = f"""SELECT *
                                    FROM information_schema.tables
                                    WHERE table_schema = 'public'
                                    ORDER BY table_name;
                                """
            # just a visual representation of where we are
            print('-' * 20, table_name)
            try:
                not_reset_tables = list()
                # use the specific name for the DB
                with connections[name_to_use_for_connection].cursor() as cursor:
                    # using the current db as the cursor connection
                    cursor.execute(select_all_table_statement)
                    rows = dictfetchall(cursor)
                    # will loop over table names in the connected DB
                    for row in rows:
                        find_pk_statement = f"""
                            SELECT k.COLUMN_NAME
                            FROM information_schema.table_constraints t
                            LEFT JOIN information_schema.key_column_usage k
                            USING(constraint_name,table_schema,table_name)
                            WHERE t.constraint_type='PRIMARY KEY'
                                AND t.table_name='{row['table_name']}';
                        """
                        cursor.execute(find_pk_statement)
                        pk_column_names = dictfetchall(cursor)
                        for pk_dict in pk_column_names:
                            column_name = pk_dict['column_name']

                        # time to build the reset sequence command for each table
                        # taken from django: https://docs.djangoproject.com/en/3.0/ref/django-admin/#sqlsequencereset
                        # example: SELECT setval(pg_get_serial_sequence('"[TABLE]"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "[TABLE]";
                        try:
                            reset_statement = f"""SELECT setval(pg_get_serial_sequence('"{row['table_name']}"','{column_name}'),
                                                    coalesce(max("{column_name}"), 1), max("{column_name}") IS NOT null) FROM "{row['table_name']}" """
                            cursor.execute(reset_statement)
                            return_values = dictfetchall(cursor)
                            # will be 1 row
                            for value in return_values:
                                print(f"Sequence reset to {value['setval']} for {row['table_name']}")
                        except Exception as ex:
                            # will only fail if PK is not an integer...
                            # currently in my system this is from django.contrib.sessions
                            not_reset_tables.append(f"{row['table_name']} not reset")

            except psycopg2.Error as ex:
                raise SystemExit(f'Error: {ex}')

            conn.close()
            print('-'*5, ' ALL ERRORS ', '-'*5)
            for item_statement in not_reset_tables:
                # shows which tables produced errors, so far I have only
                # seen this with PK's that are not integers because of the MAX() method
                print(item_statement)

            # just a visual representation of where we are
            print('-' * 20, table_name)


find and insert row to another table using mysql trigger

This should do the trick for you:

DELIMITER $$
DROP TRIGGER IF EXISTS `employee_INSERT` $$
CREATE TRIGGER `employee_INSERT`
AFTER INSERT ON `employee`
FOR EACH ROW
BEGIN
    INSERT INTO employee_tools (Id, Tool)
    SELECT new.Id, tools.Tool_Name
            FROM tools
            WHERE tools.Division = new.Division;
END $$
DELIMITER ;


JDBC prepared statement - make a token null

You can use setNull():

preparedStatement.setNull(8, Types.VARCHAR);

Although I have seen drivers that do not properly support that, in that case simply try:

preparedStatement.setString(8, null);


Unique time range or database schema for a scheduling system

You should implement this on the database side in the method that can be called by your create schedule time and modify schedule time methods.


How to query from the result of a changed column of a table in postgresql

I was able to figure out as

select *
from
(
  SELECT from_iso8601_date(substr(timestamp,1,10)) as day,*
  FROM "nds"."unacast_sample_august_2018"
 )
WHERE  day > date_parse('2018-08-26', '%Y-%m-%d')
 limit 10


What is the best practices to backup and restore full and partial database with SQL Server 2012 and C#?

Remmber you need the following assemblies:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoExtended

More info http://technet.microsoft.com/de-de/library/microsoft.sqlserver.management.smo.aspx

using System;
using System.IO;

namespace ConsoleApplication2
{
 using Microsoft.SqlServer.Management.Common;
 using Microsoft.SqlServer.Management.Smo;

 public class SQLServerTools
 {
      public static void SqlBackup(
           // Sql Server name.
        string serverName,
           // The database name on which the backup operation runs.
        string databaseName,
           // The desired backup filename.
        string destinationBackupFilename,
           // this if you want to back partial database without files
    bool partial)
      {
           Console.WriteLine(
             "Backup on the server {0} the database {1} to the file {2}",
             serverName,
             databaseName,
             destinationBackupFilename);

           // Connect to default sql server instance.
           var sqlServer = GetSqlServer(serverName);

           // Define a Backup object variable.
           var sqlBackup = new Backup { Database = databaseName, };

           // Specify the type of backup, the description, the name, and the database to be backed up.
           if (partial)
           {
                sqlBackup.Action = BackupActionType.Files;
                sqlBackup.DatabaseFileGroups.Add("PRIMARY");
           }
           else
           {
                sqlBackup.Action = BackupActionType.Database;
           }

           // Declare a BackupDeviceItem.
           var deviceItem = new BackupDeviceItem(destinationBackupFilename, DeviceType.File);

           // Add the device to the Backup object.
           sqlBackup.Devices.Add(deviceItem);

           sqlBackup.Initialize = true;
           sqlBackup.ContinueAfterError = false;

           // Set the Incremental property to False to specify that this is a full database backup.
           sqlBackup.Incremental = false;
           sqlBackup.PercentCompleteNotification = 1;

           // Specify that the log must be truncated after the backup is complete.
           sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

           sqlBackup.PercentComplete += ProgressEventHandler;
           sqlBackup.Complete += ProcessCompleted;

           // Run SqlBackup to perform the full database backup on the instance of SQL Server.
           sqlBackup.SqlBackup(sqlServer);

           // Remove the backup device from the Backup object.
           sqlBackup.Devices.Remove(deviceItem);

           // Disconnect and dispose the connection.
           sqlServer.ConnectionContext.Disconnect();
      }

      public static void SqlRestore(
           // Sql Server name.
        string serverName,
           // The backup file directory.
        string backupFilePath,
           // The database directory.
        string databaseFolder,
           // The desired restored database name.
        string destinationDatabaseName,
           // The backup filename.
        string backupFilename,
           // The database log name.
        string databaseLogFileName,
           // This will restore the database in partial mode for more info about partial check in the stackoverflow
        bool partial)
      {
           // Current console cursor position.

           //Define a Backup object variable.
           var sqlRestore = new Restore
                              {
                                   Database = destinationDatabaseName,
                                   NoRecovery = false,
                                   ReplaceDatabase = true,

                                   // Specify the type of backup, the description, the name, and the database to be backed up.
                                   Action = RestoreActionType.Database
                              };

           var sqlServer = GetSqlServer(serverName);

           // If the backup directory not found then get the default.
           if (string.IsNullOrEmpty(databaseFolder))
           {
                const string SqlDataDirectory = "DATA";
                databaseFolder = sqlServer.InstallDataDirectory + Path.DirectorySeparatorChar + SqlDataDirectory;
           }

           // Declare a BackupDeviceItem
           var deviceItem = new BackupDeviceItem(
             backupFilePath + Path.DirectorySeparatorChar + backupFilename,
             DeviceType.File);

           const string SqlMdfExtention = ".mdf";
           const string SqlLdfLogExtention = "_log.ldf";

           var dataFileLocation = databaseFolder + Path.DirectorySeparatorChar + destinationDatabaseName + SqlMdfExtention;
           var logFileLocation = databaseFolder + Path.DirectorySeparatorChar + destinationDatabaseName + SqlLdfLogExtention;

           sqlRestore.RelocateFiles.Add(new RelocateFile(destinationDatabaseName, dataFileLocation));
           sqlRestore.RelocateFiles.Add(new RelocateFile(databaseLogFileName, logFileLocation));

           sqlRestore.ReplaceDatabase = true;
           sqlRestore.ContinueAfterError = false;

           sqlRestore.PercentCompleteNotification = 1;
           sqlRestore.PercentComplete += ProgressEventHandler;

           if (partial)
           {
                sqlRestore.Partial = true;
                sqlRestore.ContinueAfterError = true;
           }

           sqlRestore.Complete += ProcessCompleted;

           sqlServer.ConnectionContext.StatementTimeout = 60 * 60;

           // Add the device to the Restore object.
           sqlRestore.Devices.Add(deviceItem);

           // Run SqlRestore to perform the database restore on the instance of SQL Server.
           sqlRestore.SqlRestore(sqlServer);

           var database = sqlServer.Databases[destinationDatabaseName];
           database.SetOnline();

           // Remove the restore device from the restore object.
           sqlRestore.Devices.Remove(deviceItem);

           // Disconnect and dispose the connection.
           sqlServer.ConnectionContext.Disconnect();
      }

      private static Server GetSqlServer(string servername)
      {
           // Define Server connection
           var serverConnection = new ServerConnection(servername);
           var sqlServer = new Server(serverConnection);
           sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
           return sqlServer;
      }

      private static void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
      {
           Console.Write(e.Percent);
      }

      private static void ProcessCompleted(object sender, ServerMessageEventArgs e)
      {
           // Write if any error is exist.
           Console.WriteLine(e.Error.Message);
      }
 }
}


Learn about AUTO_INCREMENT instead of using max(directorID)+1.

moviedirector is a many:many mapping table.  See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table for optimal schema for such.  (may get you some brownie points)

"procedure unter MariaDB, it doe not work" elaborate.

(I see 2 clues so far that Deutsch is your native language.  But your English is great.)

SELECT directorID FROM director -- What is director?  It is not declared.  Stored Routines are tricky.  There are parameters (you prefixed them with par_ -- good); there are database/table/column names; there are @variables; there are DECLAREd variables; there are aliases (you don't have any); there are loop names (none).  Using the same 'name' for two different uses can cause mysterious errors.

Collation differences either lead to explicit errors or slow performance due to inability to use indexes.  So I don't think it is a collation problem.

I'm not sure, but I think the entire procedure can be replaced by a single INSERT .. ON DUPLICATE KEY UPDATE .. statement.


Redundant FK on parent relationship is denormalization?

Yes, it is denormalization. Any time you're copying data from one location in a database to another location (where the data could easily be obtained from its original location), it's denormalization by definition.

And, improving performance is a perfectly good reason to denormalize. That said, unless you have a huge number of clients, or the client_id is the only data from the sale table that you would normally need, then it shouldn't be a major concern. It's entirely reasonable to cause this particular instance of denormalization "cascading key structure", or anything else appropriate. Doesn't mean it's not also denormalization.

And, with respect to not having consistency issues - never say never....


Working Around Filtering Problems in Merge Replication

This is a complicated situation, but I think that the best solution would be to make an additional table (which I'll call UserFilters), just to hold the consolidated data from the Road, UserRegion and SyncSetting tables in a single combined row format and then to Join your Replication Filter to that table instead.  This UserFilters table would need to be maintained by triggers on the constituent tables so that when rows in Road, UserRegion or SyncSetting change, the rows in UserFilters would be automatically changed to match.  I will demonstrate how to do all of this below.

First a note; This table (UserFilters) is a de-Normalized table that is adjunct to your normal application data schema.  That is, it is an operational artifact that is only used to manipulate the Replication technology into doing what you want, and is not part of your application's data design/schema.  This use of denormalization is considered acceptable as it is solely to address technology limitations and is not directly used by your applications.

OK, here's how to do it:

1. Write Your Desired Filter Query In JOIN Form:

Here's your original filter query (unrolled, to make it easier to decompose):

select  *
from    StreetLight
where   roadId in
    (
    select  Id
    from    Road
    where   RegionId in
        (
        select  regionId
        from    UserRegion
        where   userid = 1
        )
    )
  and   exists
    (
    select  1
    from    syncsetting
    where   userid  = 1
      and   [table] = 'StreetLight'
    )

We want to re-write this into a format with only JOINs and WHEREs, but no subQueries.  (This can always be done but I won't go into how in this article.)  Like so:

SELECT  StreetLight.*
FROM    StreetLight
JOIN    Road        ON  Road.Id             = Streetlight.roadId
JOIN    UserRegion  ON  UserRegion.regionid = Road.RegionId
JOIN    syncsetting ON  syncsetting.userid  = UserRegion.userid
                    And syncsetting.[table] = 'StreetLight'
Where   UserRegion.userid = 1

2. Create A View  Consolidating All of the Filter Conditions

We use the JOIN clauses above to make a view that contains only the columns relevant to the Replication Filter, and that projects all of the rows necessary to completely describe all of the filter conditions.  This is easier than it sounds, it should look like this:

CREATE VIEW vwUserFilters As
SELECT
        Road.Id             As RoadId,
        Road.RegionId       As RegionId,
        UserRegion.userid   As UserId,
        syncsetting.[table] As syncTable
FROM    Road
JOIN    UserRegion  ON  UserRegion.regionid = Road.RegionId
JOIN    syncsetting ON  syncsetting.userid  = UserRegion.userid
--  Comment the next line to generalize for all tables
--                  And syncsetting.[table] = 'StreetLight'

Note that we have also generalized the View's expression to cover all users.  Also, we have commented out the SyncSetting "[table] = 'StreetLight'", in order to generalize this for all tables covered by SyncSetting and Road (which may or may not be valid, you'll have to decide that).

3. Create The UserFilters Table

Create the UserFilters table using the view as a guide for the columns.  However, we'll want to add an IDENTITY column for key/index performance reasons:

CREATE TABLE adjUserFilters
(
    filterId    INT Identity(1,1) PRIMARY KEY,
    RoadId      INT         NOT NULL,
    RegionID    INT         NOT NULL,
    userid      INT         NOT NULL,
    syncTable   VARCHAR(32) NOT NULL
)

Then populate it using the View:

INSERT INTO adjUserFilters
SELECT RoadId, RegionID, UserId, syncTable FROM vwUserFilters

For performance and locking reasons you'll want a lot of index paths.  I cannot say for sure what they should be, but here's what I would start with:

CREATE UNIQUE INDEX IX_adjUserFilters
    ON dbo.adjUserFilters(userid, syncTable, RoadId)
CREATE INDEX IX_adjUserFilters_1
    ON dbo.adjUserFilters(RegionID, userid)
CREATE NONCLUSTERED INDEX IX_adjUserFilters_2
    ON dbo.adjUserFilters(RoadId, userid)

4. Add Maintenance Triggers to the Constituent Tables

You will need to add triggers to the Road, UserRegion and SyncSetting tables to keep the contents of [adjUserFilters] in synch with these tables when they are modified.  They should look like this:  First the Road table:

CREATE TRIGGER dbo.trRoad_MaintainUserFilters_IUD
   ON  dbo.Road
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   RoadId IN(Select d.Id From deleted As d)

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable
    FROM    vwUserFilters As v
    JOIN    inserted      As i  ON i.Id = v.RoadId
END

The UserRegion table:

CREATE TRIGGER dbo.trUserRegion_MaintainUserFilters_IUD
   ON  dbo.UserRegion
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   EXISTS(
        Select * From deleted As d
        Where   d.UserId    = adjUserFilters.userid
          And   d.RegionId  = adjUserFilters.RegionId
        )

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable
    FROM    vwUserFilters As v
    JOIN    inserted      As i
        ON  i.UserId    = v.userid
        And i.RegionId  = v.RegionId
END

And finally, the SyncSetting table:

CREATE TRIGGER dbo.trSyncsetting_MaintainUserFilters_IUD
   ON  dbo.syncsetting
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   EXISTS(
        Select * From deleted As d
        Where   d.UserId    = adjUserFilters.userid
          And   d.[table]   = adjUserFilters.syncTable
        )

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable
    FROM    vwUserFilters As v
    JOIN    inserted      As i
        ON  i.UserId    = v.userid
        And i.[table]   = v.syncTable
END

5. Create Your Replication Filter Query

Now you can create your Replication Query Filter for "user1" like so:

select  StreetLight.*
from    StreetLight     As t
JOIN    adjUserFilters  As f
    ON  f.syncTable = 'StreetLight'
    And f.RoadId    = t.RoadId
Where   f.userid    = 1
-- (corrected)

As requested, this is the script you would use to synchronize the adjUserFilters on a periodic basis, instead of from Triggers:

DELETE FROM adjUserFilters;

INSERT INTO adjUserFilters
SELECT RoadId, RegionID, UserId, syncTable FROM vwUserFilters

Off hand, I am not sure how you would insure that this runs right before or as part of the Merge Synchronization Job, but it probably can be done.


Can anyone offer an opinion on handlersocket vs memcached performance in MySQL 5.6

I would recommend using InnoDB memcached over Handlersocket.  The advantage of InnoDB memcached is that:

It uses a standard memcached protocol, with drivers available for many programming languages.
It does not automatically allow access to all tables in your database, but instead tables 'opt-in' via a mapping containers table.
It has a stated support policy of 8 years.  MySQL 5.6/5.7 support already exists for InnoDB memcached, and these version upgrades are required to unlock many of the scalability enhancements.


Like predicate to match a whole word only

Your best bet is probably to use REGEXP, which needs some special installation prior to being usable.

In that case, you'd use:

WHERE
    name REGEXP 'e\w*o'

This regular expression means: (a literal 'e') followed by (zero or more word characters) followed by (a literal 'o').

This approach won't shy away from cases like 'This hello will work'

References:

How do I use regex in a SQLite query?
Online Regular Expression tester - Regex101.com

Alternative:

If you can't install the required packages to have REGEXP available, you can probably use a user-defined function to perform the "word-by-word" LIKE check. Let's imagine it's called check_words_like(sentence, like_predicate), I'd use in the following fashion:

SELECT
    whatever
FROM
    (SELECT
        name, whatever
    FROM
        tbl
    WHERE
        name like '%e%o%'
    ) AS q1
WHERE
    check_words_like(q1.name, '%e%o%') ;

This still requires some C programming and interfacing, and might be as complicated as getting the right REGEXP package.


How to get count of multiple tables from linked and local servers

Use four-part-naming, like this:

SELECT 'TableA' AS Tables
    , COUNT(*) AS 'LinkedServer' FROM [Linked-Server-Name].[Database].[schema].[tablename]
UNION
SELECT 'TableB' AS Tables
    , COUNT(*) AS 'LocalServer' FROM [Database].[schema].[tablename]

Assuming your linked server is named Production, you could use:

SELECT Tables = 'TableA'
    , Production = (SELECT COUNT(*) FROM [Production].[Database].[schema].[tableA])
    , Local = (SELECT COUNT(*) AS FROM [Database].[schema].[tableA])

UNION
    , Tables = 'TableB'
    , Production = (SELECT COUNT(*) FROM [Production].[Database].[schema].[tableB])
    , Local = (SELECT COUNT(*) FROM [Database].[schema].[tableB])


I'm not sure why your trigger isn't working - unless it's because other rows have been inserted in between the two triggers firing.

I suspect your issues may be mitigated by using a compound trigger along the lines of:

CREATE OR REPLACE TRIGGER stg_control_inserts
  FOR insert on Distributionplan
    COMPOUND TRIGGER

    v_job_id stg_control.job_id%TYPE;
    v_job_name stg_control.job_name%TYPE := 'DistributionPlan';

   BEFORE EACH ROW IS
   BEGIN
     INSERT INTO stg_control (job_name, job_id, execution_start, exec_tocore_start)
     VALUES (v_job_name || ' ' || SYSTIMESTAMP, v_job_id, SYSTIMESTAMP, SYSTIMESTAMP)
     RETURNING job_id INTO v_job_id;
   END BEFORE EACH ROW;

   AFTER EACH ROW IS
   BEGIN
     UPDATE stg_control
     SET    execution_end = '14/05/19 00:00:00,493000000'
     WHERE  job_id = v_job_id;
   END AFTER EACH ROW;
END stg_control_inserts;
/

N.B. untested.


Performance of a View with Hundreds of Left Joins

You could try UnPivoting your data and then Pivoting the results:

Tables:

Create Table #tbl
(
Id Int,
[Name] VarChar(20),
attr1 Int,
attr2 Int,
attr3 Int,
attr200 Int
)
Insert Into #tbl Values
(1,'First',1,2,4,2),
(2,'Second',3,3,5,1)

Create Table #StringMap
(
AttrName VarChar(25),
[Value] Int,
[Label] VarChar(25)
)
Insert Into #StringMap Values
('attr1',1,'Small'),
('attr1',2,'Medium'),
('attr1',3,'Large'),
('attr2',1,'A'),
('attr2',2,'B'),
('attr2',3,'C'),
('attr200',1,'Purple'),
('attr200',2,'Blue'),
('attr200',3,'Green')

Query uses CTEs to UnPivot your table to have a single join to your StringMap table.  It then Pivots back the results

;With cte As
(
  Select
        id, [name], attr, vals
  From #tbl
  UnPivot
    (
      vals
      For attr IN (attr1, attr2, attr3, attr200)
    ) As u
  ), cte1 As
(
  Select cte.id, cte.[Name], cte.attr, s.[Label]
  From cte
  Inner Join #StringMap s
    On s.[Value] = cte.vals And s.AttrName = cte.attr
)
Select [Name] As [Name],
       [attr1], [attr2], [attr3], [attr200]
From
(Select [Name], [Label], attr From cte1) As src
Pivot
(
Max(Label) For attr IN ([attr1], [attr2], [attr3], [attr200])

) As piv

Result:

Name    attr1   attr2   attr3   attr200
First   Small   B       NULL    Blue
Second  Large   C       NULL    Purple

Note:
With so many columns, you'll want to search the site for using dynamic Pivots Such as: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query


SQL Server 2008 Timeout expired rebuild and reorganize index solve the issue

You have a performance issue at your hands. Investigate it appropriately, using a performance investigation methodology like Waits and Queues. USE is also a great performance troubleshooting framework.

Stop doing random changes in hope something improves. Measure, identify, then act appropriately. Establish baselines. Read the entire Monitor and Tune for Performance chapter on MSDN.


Can an active session in oracle be dead?

You should query
V$SESSION_WAIT
to see what your statement is doing. A short description of the wait events can be found in "Part IV Appendixes", "C Oracle Wait Events" of the "Database Reference"


Which BI solutions cooperate with Postgres database and aren't too expensive?

If you are comfortable with excel you could import the data directly with an odbc connection.

If you wanted olap capability you can do that with a tabular model within excel.

In general most BI solutions will work with any relational database via an OLE or ODBC connection. If the product doesnt have a native adapter you could use a 3rd party ETL tool to move the raw data to a db that is supported.


Solutions to 4-byte utf-8 characters in mysql 5.1

Opinion

It's probably better to throw an exception during your applications input checking and not pass the buck to the database.

Workaround

There is a "workaround" but your mileage may vary:
http://forge.mysql.com/worklog/task.php?id=3780

Brute Force?

You could convert your front end table VARCHAR field to a BLOB and store as binary data to cure the current problem. ...and of course that invites a host of other problems by using BLOBs instead of VARCHAR.

Upgrading

UTF32 may solve the problem and upgrading to 5.5.x isn't has hard as you'd think. Create a replicated slave (farm), sync up and promote it to master.


Should an OFFLINE database be SET EMERGENCY before dropping?

Note: make sure you have a final full backup of the database before you drop it, just incase if you need to restore.

Normally, EMERGENCY mode is used for getting your database out of corruption.

You are right, that EMERGENCY mode will allow only sysadmin members and the db will be read-only - nothing gets written to T-LOG.

I would suggest that get the database to RESTRICTED USER and then drop it.

e.g. ALTER DATABASE your_db_name SET RESTRICTED_USER or READ_ONLY

Note: This is just my preference which distinguish between EMERGENCY mode --> use to take DB out of corruption vs RESTRICTED_USER or READ_ONLY --> give it chance to breathe before you drop !


How to query readonly replica in Always On cluster

After some help and hints (especially thanks to Andriy M) I've managed to understand what I actually want and how to do it. This article from Microsoft helped as well: https://technet.microsoft.com/en-us/library/hh403414.aspx

First I had to create a read only listener. And then at the other server add linked server like this (borrowed from: https://stackoverflow.com/a/19585304):

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server     = N'cluster-readonly', -- just a name
    @srvproduct = N'SQL', -- anything except 'SQL Server'
    @provider   = N'SQLNCLI11',
    @datasrc    = N'ClusterListener', -- name of the readonly listener
    @provstr    = N'ApplicationIntent=ReadOnly',
    @catalog    = N'AnyActualDataBase' -- system database like 'master' is no good
GO

How do I check, where am I:

SELECT * FROM OPENQUERY([cluster-readonly], 'SELECT @@SERVERNAME')

Here's some trick - if I don't specify @catalog, then connection will be to the PRIMARY node for some reasons.


innodb_flush_log_at_trx_commit=1 and flush-to-disk reality

It is obvious to have better performance for the value of innodb_flush_log_at_trx_commit as 2 than 1.

As commit operations are fairly dependent on underlying system even fooling-by-OS is an extra step one takes for every commit.

For value of 1 flushing to disk operations are bound to happen every transaction while for 2 it happens once a second (depending on OS scheduling)

Oprn. is "slow" because  you do ensure the data durability & consistency.
Oprn. is "Fast" because you did less work (disk operations). Even for the "lies" mysqld goes an extra step.

For innodb_flush_log_at_trx_commit=1:

for every transaction {
do-log-write
do-disk-commit (actual / fake )
}

For innodb_flush_log_at_trx_commit=2:

for every transaction {
do-log-write
}
for every second {
do-disk-commit (actual / fake )
}

Also your tests you performed contains multiple parameters for comparisons. You might also want to test with sync_binlog and flush_method changes. Mainly consider variable optimizations for better performance.


Updating a table with millions of records, its been 4 days

This query requires you to scan every row in the table because

I guess procodet or ProviderCode are not indexed
Even if they were indexed, you have a LEFT which is a function on a WHERE predicate
And you have COLLATE too which is effectively a function on a WHERE predicate

"a function on a WHERE predicate" means indexes won't be used

If you batch it (say on UPDATE TOP (10000) ... AND costPercentage IS NULL) then you need an index on costPercentage and this assume you are setting it.

The only solutions I see are

populate a new table in batches, based on, say, the primary key
create indexed, computed columns to hide the LEFT and COLLATE expressions, then run the update


As EJP say you can work manually using the stop/start slave, so you can stop replication using:

mysql> STOP SLAVE;

than resume when your checks are finished using:

mysql> START SLAVE;

Alternatively you can manage the delay of a replica using the MySQL Delayed Replication, note that this feature is available only on 5.6.x + MySQL version.

I think that a good idea where to start (but there are many configuration for your scenario) is to have 2 node attached in replica, one standard replica and one delayed replica:

Master
`--- Standard Replica
`--- Delayed Replica

In this configuration you have all the advantages in case of a master crash(promoting the standard replication) and all the advantages for checking your "past" :) and/or a point in time recovery


Replicate single table from Postgres 9.3 replica

You will need to replicate the table from the read/write master.

PostgreSQL's built-in physical streaming replication can only replicate the whole database server instance.

The replication systems that support single-table replication require the ability to use a trigger to write a record of changes. This can only be done on the upstream read/write master.

The BDR project plans to deliver a unidirectional version that can stream changes using only the transaction logs, with individual table selection. That would meet your needs, but it won't be ready for a few months at best, possibly longer. Keep an eye out on the PostgreSQL mailing lists and the above wiki for discussion of "UDR" if you're interested.


Quoting the manual:

A foreign key must reference columns that either are a primary key or
form a unique constraint.

So that's not necessarily limited to the PK. But if we start with pg_constraint, we get all FK constraints pointing to the target table automatically. No need to provide any key columns - except if you want to limit to certain FKs.
Using the object identifiers type regclass and table aliases, we can keep the function short and the result safe and unambiguous:
Basic query
SELECT format(E'SELECT * FROM %s t\nWHERE  NOT EXISTS (SELECT FROM %s'
            , c.confrelid::regclass
            , string_agg(format('%s WHERE %s = %s)', c.conrelid::regclass, src.cols, tgt.cols)
                       , E'\nAND    NOT EXISTS (SELECT FROM '))
FROM   pg_constraint c
     , cardinality(c.conkey) AS col_ct
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg(quote_ident(attname), ', ' ORDER BY fld.ord) -- original order
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.conkey) WITH ORDINALITY fld(attnum, ord)             -- possibly n cols
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.conrelid, fld.attnum)
   ) src
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END     -- parentheses for multiple columns
               , string_agg('t.' || quote_ident(attname), ', t.' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.confkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.confrelid, fld.attnum)
   ) tgt
WHERE  c.confrelid = 'my_table'::regclass -- target table name, optionally schema-qualified
AND    c.contype = 'f'  -- FK constraints
GROUP  BY c.confrelid;

Produces a query of the form:
SELECT * FROM my_table t
WHERE  NOT EXISTS (SELECT FROM schema1.tbl1 WHERE col1 = t.id)
AND    NOT EXISTS (SELECT FROM "tB-l2" WHERE ("COL2", col3) = (t.col4, t.col5));

Which returns all rows that are not currently referenced by any FK constraint.
If cardinality(c.conkey) > 1 then it's safe to also assume cardinality(c.confkey) > 1. So only count once to decide whether to add parentheses.
Full automation
To make this work for any input table dynamically, create a polymorphic function taking a row value of the table:
CREATE OR REPLACE FUNCTION f_orphans(_tbl anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE (  -- exactly the query from above
SELECT format(E'SELECT * FROM %s t\nWHERE  NOT EXISTS (SELECT FROM %s'
            , c.confrelid::regclass
            , string_agg(format('%s WHERE %s = %s)', c.conrelid::regclass, src.cols, tgt.cols)
                       , E'\nAND    NOT EXISTS (SELECT FROM '))
FROM   pg_constraint c
     , cardinality(c.conkey) AS col_ct
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg(quote_ident(attname), ', ' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.conkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.conrelid, fld.attnum)
   ) src
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg('t.' || quote_ident(attname), ', t.' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.confkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.confrelid, fld.attnum)
   ) tgt
WHERE  c.confrelid = pg_typeof(_tbl)::text::regclass  -- input goes here!
AND    c.contype = 'f'
GROUP  BY c.confrelid
);

END
$func$  LANGUAGE plpgsql;

Call (important)!
SELECT * FROM f_orphans(NULL::my_table);

Or:
SELECT * FROM f_orphans(NULL::myschema.my_table);

Related:

PostgreSQL: Pass table as argument in function
Retrieving all PK and FK
SQL order by query results in any arbitrary (but reproducible) manner
How to preserve the original order of elements in an unnested array?


Using MAX text or more specific, smaller type

Should I always use (n)varchar(max) for text columns?

No.
For SQL Server, the max data types should only be specified when there is no alternative. One should instead choose the correct base type (varchar or nvarchar) and specify an explicit maximum length that is appropriate to the data to be stored.
Physical storage is identical whether the column is typed as varchar(n) or varchar(max), so that is not the concern.
The reasons to not choose (n)varchar(max) everywhere revolve around features, plan quality, and performance.
An exhaustive list is probably not practical, but among other things, max columns:
Features

Require a separate constraint to enforce a maximum length
Cannot be a key in an index (so no unique constraints either)
May prevent online DDL (including index rebuilds and adding a new non-null column)
Are generally not supported for 'newer' features e.g. columnstore
See the product documentation for more specific features and limitations. The general pattern is that there are awkward limitations and restrictions around max data types. Not all limitations and side-effects are documented.

Performance

Require special handling in the execution engine, to account for the potentially very large size. Typically, this involves using a less efficient code path, with a streaming interface
May have similar unanticipated consequences for external code (and other SQL Server components like SSIS), which must also be prepared to handle data up to 2GB in size
Are assumed to be 4000 bytes wide in memory grant calculations. This is likely to lead to excessive memory reservation, which limits concurrency, and pushes valuable index and data pages out of cache memory
Disable several important performance optimizations
May extend lock duration
May prevent the optimizer choosing a (non-dynamic) seek plan
Prevent filters being pushed into scans and seeks as a residual
May increase tempdb pressure and contention (version dependant), since variables and parameters are also likely to be typed as max to match column definitions

In summary, there are so many subtle (and undesirable) side effects of unnecessarily using the max specifier that it makes no sense to do this. The minor 'convenience' of using a single declaration is no sort of compensation.
Evaluate each type in context, use the correct base type (varchar or nvarchar), and a sensible explicit length.
Further reading:

Performance comparison of varchar(max) vs. varchar(n) by Remus Rusanu
Read Committed and Large Objects by Craig Freedman
Capacity planning for tempdb
Deletes that split pages and forwarded ghosts (me)


Answer is in the article you are referring to:

  Here the planner has decided to use a two-step plan: the child plan
  node visits an index to find the locations of rows matching the index
  condition, and then the upper plan node actually fetches those rows
  from the table itself.

Word Output here means physical read of values. Child plan does not read values, it only reads locations (addresses) of rows, then parent plan actually reads the values. And next sentence provides explanation why two step process is chosen:

  Fetching rows separately is much more expensive than reading them
  sequentially, but because not all the pages of the table have to be
  visited, this is still cheaper than a sequential scan. (The reason for
  using two plan levels is that the upper plan node sorts the row
  locations identified by the index into physical order before reading
  them, to minimize the cost of separate fetches. The "bitmap" mentioned
  in the node names is the mechanism that does the sorting.)

Later in the text you'll find examples of two child processes where one process has width=244 and the other width=0. Usually child process will fetch row locations with 0 output, and then parent process actually output rows. It is not used always, but there are situations when it is faster than reading rows straight away.


SQL Server dates - can I get date and hour?

In SQL Server <= 2012 you can say:

SELECT CONVERT(CHAR(10), date_field, 120)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(HOUR, date_field)), 2)
  FROM dbo.table ...;

Or

SELECT STUFF(CONVERT(CHAR(13), date_field, 120), 11, 1, ':')
  FROM dbo.table ...;

In SQL Server 2012 this is much easier; you can say:

SELECT FORMAT(date_field, 'yyyy-MM-dd:HH')
  FROM dbo.table ...;

Though FORMAT is considerably slower, so if you are doing this at scale, it isn't the one I would use (and in fact I would highly recommend you consider formatting your date output at the presentation layer, not in SQL Server).

SQL fiddle: http://sqlfiddle.com/#!6/72276/5


Where to put a database in case of IP failover?

You'll want to put the SQL Server on its own server.  You may also need to protect the SQL Server from reboots or failed hardware by looking at an HA solution.  You can use Clustering (which requires shared storage), or database mirroring (with or without a witness), or peer to peer replication.


Get data from two tables with recursive relationships

BAD NEWS

MySQL does not support recursive data or table structures. Neither does it support recursive SQL.

GOOD NEWS

There is a hope. I have written some stored functions on how to retrieve hierarchies of data

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Dec 10, 2012 : MySQL: Tree-Hierarchical query

CAVEAT

You would have to implement this against your tables.
You also need to make sure you are entering data as a Tree and not as a Graph.

https://en.wikipedia.org/wiki/Tree_(data_structure)
http://wiki.answers.com/Q/DIFFERENCE_between_GRAPH_and_TREE_IN_DATA_STRUCTURES
https://stackoverflow.com/a/7707983/491757

For any row in the position_inherits table that does not have a parent, simply set that row's inherit_privs_from to zero(0). That way, you have establish root positions.


DTA is a good thing but you should always make the final decision yourself.
You should not blindly believe what DTA or SQL Server Missing indexes suggests you regarding indexes as they do not take into consideration many performance aspects such as update/insert intensity on the the table, they only consider performance enhancement of the certain query/table regardless of the overall workload.

So the answer to your question will be:

This has happened because DTA considers these indexes will improve performance of queries hitting this table.
Over-indexing any table will result in degraded performance on update/insert/delete operations, in more storage used, in longer backup, checkdb, index maintenance operations. It will potentially lead to more intensive use of RAM. As you see - excessive indexes have a lot of drawbacks.

The answer to 3 and 4 - read through the articles recommended below and make sure you understand your workload (read/write activity) and how indexes operate.
It is also a good practice to test such things first and apply in production if necessary.

SQL101: Indexing Basics by Kimberly Tripp
Are your indexing strategies working? (aka Indexing DMVs) also by Kimberly Tripp
SQL Server Index Design Guide in the TechNet Library


That is true in the context of binlog-do-db (and equivalent) using STATEMENT based replication. You should never use binlog-do-db unless you know what you are doing (or you will lose data because of replication filters). If you want to do replication filtering, you should use replicate-wild-do-table (and other wild options) on the replica. It is easy to filter out events, but impossible to put them back if they were never logged. ROW sometimes can also help saner filtering behaviour.

If you are not using replication filtering, all write commands that are not server specific are sent to the master's binlog, if enabled correctly and not truncated/expired.

You need to provide more information to understand the issue you suffered (mysql version, topology used and options).


Query Quill-Delta JSON array in Postgres

I've come up with a function and an index; this is the function, which takes alle the insert JSON-String keys and builds up a tsvector from them:

create or replace function public.fts_quill_delta(lang regconfig, delta text) returns tsvector as
$$
begin
return to_tsvector(lang, string_agg(value->>'insert', ' ')) from jsonb_array_elements(delta::jsonb) where jsonb_typeof(value->'insert') = 'string';
end;
$$
language 'plpgsql' immutable;

this is the index:

create index content_delta_fts on my_table using gin(fts_quill_delta(lang, content));

Then you can query the index with:

select from my_table where fts_quill_delta(lang, content) @@ 'hello & world'

Note that I added a lang column, used by the FTS engine to use correct I18N settings.


The initial syntax was incorrect

UPDATE `tracks` SET `time` = `time` AND `art` = 'default.jpg' WHERE `art` = '';

AND does not separate the columns to update. They should be separated by commas (,)

What you probably wanted was

UPDATE `tracks` SET `time` = `time`,`art` = 'default.jpg' WHERE `art` = '';


Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan

Well since my post I have managed to find my answer here.

The behaviour of whether a scan or seek will be performed when performing an implicit conversion from VARCHAR to NVARCHAR on the column in the predicate is dependent on the collation setting.

Older, legacy collation settings will cause an index scan and the newer collation settings will cause the seek


-- set chunk size
WITH cte0 AS ( SELECT 10 chunk
             ),
-- calculate max chunks count
     cte1 AS ( SELECT 1 + MAX(qty / chunk) chunks
               FROM test, cte0
             ),
-- generate numbers
     cte2 AS ( SELECT 1 num
               UNION ALL
               SELECT num+1
               FROM cte1, cte2
               WHERE num < chunks
             ),
-- prepare data
     cte3 AS ( SELECT num, Product, CASE WHEN qty > num * chunk
                                         THEN chunk
                                         ELSE qty - (num - 1) * chunk
                                         END qty
               FROM test, cte0, cte2
               WHERE CASE WHEN qty > num * chunk
                          THEN chunk
                          ELSE qty - (num - 1) * chunk
                          END > 0
             )
-- perform action
MERGE test
USING cte3
ON (test.Product = cte3.Product AND cte3.num =1)
WHEN MATCHED THEN
    UPDATE SET Qty = cte3.qty
WHEN NOT MATCHED THEN
    INSERT (Product, Qty)
    VALUES (cte3.Product, cte3.qty);

fiddle


Query to get number of items from different table reference in another table

Something like this will work, though is not necessarily efficient on all DB engines:

select Project_ID,
count(distinct Bottle_ID) bottlesCount,
count(distinct Plastic_ID) plasticsCount,
count(distinct Glass_ID) glassesCount,
count(distinct Cup_ID) cupsCount
from Project
left join Bottles using (Project_ID)
left join Plastics using (Project_ID)
left join Glasses using (Project_ID)
left join Cups using (Project_ID)
group by Project_ID;


database design help with multiple table relationships

Consider it this way. Products and possible options of products comprise system dictionary. Orders and specs are constraint by dictionary.

Products
--
Pizza
Pasta
;
Options
--
peperoni
sausage
peppers
sauce
cheese
onions
;
ProductOptions
--
Pizza peperoni
Pizza sausage
Pasta cheese
Pasta onions
;
Orders
--
1 Steve Pizza 12:40
2 Steve Pizza 12:35
;
OrderSpecs
--
1 Peperoni
1 Peppers
2 Sausage
2 Peppers
;


If you want a single row's value returned without the commas and semi-colons then the easiest way to accomplish this is with the REPLACE() function like this: REPLACE(REPLACE(FREQUENCY, ',', ' '), ';', ' ') "FREQUENCY".


While I cannot confirm this with any official documentation, the reason must be based on the total size of included columns and the cost.

With the test i conducted, the indexes with 10 columns and 3 columns had exactly the same cost and logical/physical reads, which I assumed to be the same with the original poster's environment.  I gradually increased the size of the 4th column, and at certain point, the opimizer started choosing the 1st index for the 1st statement.  The table used had about 55k rows.

This only shows an anecdotal evidence and a theory on certain behavior of the optimizer.  I hope  someone who really knows the meanism of it will explain us all why, if this theory isn't correct.


You should use sp_executesql with output parameter.

declare @S nvarchar(max) = 'select @x = 1'

declare @xx int
set @xx = 0

exec sp_executesql @S, N'@x int out', @xx out

select @xx

I've got the example from here.


What are 'Range' and 'Radius' queries and what is the difference between them?

Range stands for one-dimensional distance while radius is standing for two-dimensional one.

Let's the data is the timestamp. It is one-dimensional and you can define part of data "in range" or timestamp BETWEEN a AND b. If your data represents the plane of points x:y then you can define part of them as "points closer than radius Z to the given point" or (x-x1)^2 + (y-y1)^2 < Z^2.


How to insert Error_Message using EXECUTE sp_executesql

Let's start simpler. To retrieve an ERROR_MESSAGE() derived during execution of dynamic SQL, outside of the dynamic SQL, one way is to use an output parameter.

SET NOCOUNT ON;

DECLARE @msg nvarchar(max);

DECLARE @sql nvarchar(max) = N'BEGIN TRY
  SELECT 1/0;
END TRY
BEGIN CATCH
  SET @msg = N''Error: '' + ERROR_MESSAGE();
END CATCH;';

EXEC sys.sp_executesql @sql, N'@msg nvarchar(max) OUTPUT', @msg OUTPUT;
PRINT @msg;

Result:

Error: Divide by zero error encountered.

I'm having a hard time reverse engineering all your code to determine exactly what you are trying to do with the error message, and whether you want to do that for every row that fails, inside the dynamic SQL, or what. But I do feel like you should start simpler. Get it working for one thing, then try doing all this stuff/concatenation business.

To just update a row in a table (again, in a much simpler scenario than you have in the question):

CREATE TABLE #x(msg nvarchar(max));

INSERT #x(msg) VALUES(NULL);

DECLARE @sql nvarchar(max) = N'BEGIN TRY
  SELECT 1/0;
END TRY
BEGIN CATCH
  UPDATE #x SET msg = ERROR_MESSAGE()
  -- WHERE... ;
END CATCH;';

EXEC sys.sp_executesql @sql;

Result:

SELECT msg FROM #x;

---------------------------------
Divide by zero error encountered.

I think your main problem is missing parens, missing underscore, or using @@ERROR instead of ERROR_MESSAGE().


Probably you're moving the row when you set Deleted=1 and reading it again with your FAST_FORWARD cursor.  Use a STATIC cursor instead, which will iterate a copy of the data, and avoid mutating the data structure you are traversing.

DECLARE st CURSOR LOCAL STATIC FOR . . .


Use NEW in FROM clause in Postgres trigger?

NEW and OLDin trigger functions are records - each only defined where applicable. (So no OLD in an INSERT trigger, for instance.) You can perform ROW operations on them. Reference nested columns with dot notation (like table-qualified column names) like they were tables included in the FROM list. NEW and OLD are visible almost everywhere in SQL DML statements in a trigger function. (Except dynamic SQL with EXECUTE.)

To treat NEW as actual table you'd have to transform it first - which is typically not necessary. Like: (SELECT NEW.*). For a meaningful use case see chapter 3. in this related answer:

PostgreSQL use NEW in query for INSTEAD OF trigger

Your basic trigger function burns down to:

CREATE OR REPLACE FUNCTION unnest_and_normalize_json()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO many_to_one_table (parent_table_id, subfield_a, subfield_b)
   SELECT NEW.id
        , ja->>'subfieldA' AS subfield_a  -- column alias only for documentation
        , ja->>'subfieldB'
   FROM   unnest(NEW.jsonb_array_field) ja;  -- produces a derived table

   RETURN NEW; -- optional for AFTER trigger
END
$func$  LANGUAGE plpgsql;

This was just noise:

if NEW.jsonb_array_field is null then ...

The column jsonb_array_field is defined as:

jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],

It's an AFTER trigger, the NOT NULL constraint would already have raised an exception for NULL in jsonb_array_field at this point.

You can merge your two triggers into one:

CREATE TRIGGER unnest_and_normalize_json_on_insert
AFTER INSERT OR UPDATE ON parent_table  -- !
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

That said, you'd have to do a lot more to cover UPDATE and DELETE properly. Similar to this one (chapter The dark side):

Can PostgreSQL have a uniqueness constraint on array elements?


Connect to LocalDB via SQLCMD in PowerShell

The casing of the instance name does not matter.

You need to try:

sqlcmd -S "(localdb)\MSSQLLocalDB" -d testdb01

The "localdb" part needs to be enclosed in parenthesis as that is a special syntax that points to a SQL Server Express LocalDB-specific API that allows for automatic instance start-up upon being referenced in a connection string. The whole instance name needs to be enclosed in double quotes. The command-line shown above works for me, at least outside of PowerShell.


Query returned successfully when obvious error should occur

According to this ticket, the workaround is to set English language for messages in postgresql.conf, e.g.

lc_messages = 'en_GB.UTF-8'

See also this question on SO.


Can I reorganize a Primary Key after deleting some rows?

Both options delete all records from your table and restarts IDENTITY values.

TRUNCATE TABLE

TRUNCATE TABLE <schema>.<table_name>;

  Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

or

DBCC CHECKIDENT

DELETE FROM <schema>.<table_name>;
DBCC CHECKIDENT ('<schema>.<table_name>', RESEED, 0);

  Checks the current identity value for the specified table in SQL Server 2017 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.


Insert into multi table View in SQL SERVER

You can only do this (the trigger is irrelevant) when inserting in a partitioned view, which is view that can UNION ALL data in which the underlying tables have proper CHECK constraints that are disjoint together with a primary key, so an INSERT would unmistakably go in one underlying table.

Following your example, if you restrict some values for table A, others for table B and add a compound primary key:

Create table A(
    [id] int,
    value nvarchar(50),
    CHECK (value = 'A'),
    PRIMARY KEY (ID, value))

Create table B(
    [id] int,
    value nvarchar(50),
    CHECK (value = 'B'),
    PRIMARY KEY (ID, value))

The view stays the same:

create View V as (select id, value from A) UNION ALL (select id, value from B)

And now you can successfuly insert directly into the view (no trigger required):

insert into v (id, value) select 1,'B'
-- (1 row(s) affected)

  Is there's a way to decide (without changing the trigger) which is the
  default table of the view in manner to insert into it?

Inserting with value = 'B' matches the CHECK for table B (the partitioned column), so the row goes that way automatically. Since the checked column has to be part of the primary key, the SQL engine knows that this row belongs in this table and not in any other one, since they all have primary keys with the same column and different check values. You can't control this manually.

If you try to insert a value that's not supported in any CHECK constraint...

insert into v (id, value) select 1,'C'
--Msg 4457, Level 16, State 1, Line 1
--The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions.
--The statement has been terminated.

Check the DDL of the tables in which the insert of your view works and you will see the disjoint CHECK constraints.


How to update commands in Ola Hallengren's SQL Server jobs?

You can update steps without completely recreating everything and without pointing and clicking yourself to death in the UI. You can just write manual queries directly against msdb.dbo.sysjobsteps (start with SELECTs that issue the REPLACE() calls, then when you're happy, change it to UPDATE).

SELECT REPLACE(js.command, N'foo', N'bar')
  FROM msdb.dbo.sysjobsteps AS js
  INNER JOIN msdb.dbo.sysjobs AS j
  ON js.job_id = j.job_id
  WHERE j.name = N'name of the job';

When you're satisfied it's correct, only the first couple of lines change:

UPDATE js
  SET command = REPLACE(js.command, N'foo', N'bar')
  FROM msdb.dbo.sysjobsteps AS js
  INNER JOIN msdb.dbo.sysjobs AS j
  ON js.job_id = j.job_id
  WHERE j.name = N'name of the job';

It's tough to get more automated than that (e.g. update more than one job at once), since the string you want to replace might not be exactly the same in all job steps, and you might get false positives, too.


There are two types of activity to consider here: reads and writes.

As you correctly point out, MDF/NDF writes are done through the checkpoint process, and users shouldn't have their transaction time affected.

Reads happen when the data that is needed is not yet in RAM (in the buffer cache). So ideally, user activity shouldn't be affected often. But when it is, the data can be fetched quite quickly whether RAID 10 or 5. The pain point for RAID 5 is with writes not reads.

But if you lose a disk - what then?

With RAID 10 there's no loss of performance. When a replacement disk is put in, the mirror is copied over it. With RAID 5, every read requires some maths to be done. And when a new disk comes, it has to read a lot and write a lot.

But RAID 5 is cheaper. 1TB of disk will get you 800GB of space (depending on your setup), but RAID 10 will only get you 500GB. Disk activity is slightly more expensive though.

It's "fine" to put data files (not logs) on RAID 5. But if disk is cheap enough, RAID 10 is better.


Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE)

Adding to what is already mentioned by @Gameiswar, the other difference which I know with sp_updatestats, when you run it for all tables of database it ONLY updates statistics of tables which has at least one row changed. BOL says

  For disk-based tables, sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

So when you run sp_updatestats and it says statistics for all tables have been updated this is incorrect/misleading only for tables which has at least one row changed was stats updated.

While going with UPDATE STATISTICS you have quite lot of options to use for stats update.

Plus when you rebuild the index with full scan or with default options the stats are updated for that index so no need to rebuild stats again for it.

PS: Whatever the difference may be, I would not use sp_updatestats as the mechanism it uses to update stats is not good, why should i update stats for table if just one row has changed and I have 100K rows in table, it will just consume resources and cause more issue.

EDIT:

If you want to update stats selectively and do not want to run sp_updatestas use below query to filter out outdated stats. This query uses DMF and would work from SQL Server 2008 R2 SP2 , SQL Server 2012 Sp1 and above, Copied from Erin Stellato's blog

NOTE: Realize that different tables may have different thresholds and you will need to tweak the query above for your databases. For some tables, waiting until 15% or 20% of the rows have been modified may be ok. But for others, you may need to update at 10% or even 5%, depending on the actual values and their skew.

SELECT [sch].[name] + '.' + [so].[name] AS [TableName] ,
[ss].[name] AS [Statistic],
[sp].[last_updated] AS [StatsLastUpdated] ,
[sp].[rows] AS [RowsInTable] ,
[sp].[rows_sampled] AS [RowsSampled] ,
[sp].[modification_counter] AS [RowModifications]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
[ss].[stats_id]) sp
WHERE [so].[type] = 'U'
AND [sp].[modification_counter] > 0--change accordingly
ORDER BY [sp].[last_updated] DESC;

If you want to avoid all such hassle you can also use Ola Hallengren update stats script which takes care of lot of such things.


Take a look at the following KB article:  "Msg 8992" error message and inconsistent metadata in the SQL Server system catalogs

It looks like you have an inconsistency in the sys.sql_dependencies system catalog view.  As per the above KB article you can do one of two things to resolve this:

Restore a backup that doesn't have the current inconsistency
Create a new database and move all of the objects and data into the new database

Please see the above referenced KB article for further details.


Try this:

CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
BACKUP DATABASE foo TO DISK = 'c:\temp\x.bak';
BACKUP DATABASE bar TO DISK = 'c:\temp\x.bak';
GO
RESTORE HEADERONLY FROM DISK = 'c:\temp\x.bak';

Relevant columns for this scenario:

Position  DatabaseName
--------  ------------
1         foo
2         bar

To restore foo, whether it exists or not:

RESTORE DATABASE foo FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 1, REPLACE;

To restore bar, whether it exists or not:

RESTORE DATABASE bar FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 2, REPLACE;

If you don't specify WITH FILE, you will get a slew of errors, depending on whether the database in Position = 1 already exists.

The lessons here:

do not use common and generic names for backup files - name them for the database they represent and keep them separate.
use WITH INIT if you expect a backup file to only ever contain one copy of a backup.
understand what is in your backup by first running RESTORE
HEADERONLY and/or RESTORE FILELISTONLY.

If you show us those two columns from the output of RESTORE HEADERONLY and what database you are actually trying to restore, we can provide you with a specific command that should work.


Possible to get comprehensive view on all defined names in PostgreSQL?

RDBMSs implement their own constructs. Then they add hundreds of function names, internal and user-facing.

All of them for PostgreSQL are defined in the schema pg_catalog. You can access them with the S argument in psql. For example \dfS for show all system functions.

  On top of that, users go and define schema(ta)s, tables, indexes, custom functions, types, whatnot.

You can never know what users create. You'll have to check that out for each install by querying the catalog.

  Is there a truly comprehensive way to list all these 'defined words' complete with their roles, origins, comments etc. in a single huge list?

I'm not 100% sure it's what you want, but perhaps you're looking for the Reserved Keyword list?

Update

  "You'll have to check that out for each install by querying the catalog."—exactly, can you tell me such a query? – John Frazer Jun 17 at 8:03

Sure, so what you want to do is run psql -E, then run \?. This gives you the far more than what you've asked for (viz. list tables, views, and sequences , aggregates , access methods , tablespaces , conversions , casts , default privileges , domains , foreign tables , foreign servers , user mappings , foreign-data wrappers , functions , text search configurations , text search dictionaries , text search parsers , text search templates , roles , indexes , large objects, same as \lo_list , procedural languages , materialized views , schemas , operators , collations , table, view, and sequence access privileges , sequences , tables , data types , roles , views , foreign tables , extensions , event triggers , databases). Next pick one of them, like \df which lists functions,

You'll get a query like

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

This is internally the query that psql uses to get this information. You can cut it up though if you only want the name and don't care about whether or not the function is an aggregate, window, or trigger; and, you can cut it further if you don't care about the result and arguments types.

SELECT n.nspname as "Schema",
  p.proname as "Name"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2;

Etc., or of course you can query the slower information_schema interface which is part of the SQL spec and standardized.

SELECT routine_catalog, routine_schema, routine_name
FROM information_schema.routines
WHERE routine_schema NOT IN ('pg_catalog', 'information_schema');


My honest opinion is that - Don't reinvent the wheel as there is cost to reinvent it.

Highly suggest you to use Ola's index and stats maintenance solution.

It is very flexible and is highly awarded and tested. Also it does not use any of ms_foreach stuff that you have in your script.


Turned out that mysql scan all the undo looking for uncommitted transactions.
Since I had a very large undo space in the ibdata, I moved to use undo tablespaces. Setting up the undo truncate option keeps my undo tablespace very small. Now my db opens in 2-3mn top!
Thanks,


How to handle multiple associative tables?

Yes, if you want to reuse an image between multiple entities and a single entity can have multiple images you'll need to make it a many-to-many relationship. And yes, you would want to create multiple associative tables for each many-to-many relationship.

The only way around this would be to create one Entity table that all of the other tables pulled their IDs from, but I think what you currently have is easier to maintain going forward.