Parsing and binding variables for SELECT .. WHERE column IN ( .. ) queries

You must bind the elements of an IN list, otherwise Oracle will consider these separate statements. Any differences in the text of the SQL will cause a new statement to be parsed (excepting some cases when cursor_sharing = force). Therefore your examples with :a, :b and :a, :b, :c will be considered different SQL statements.

Each element must be bound to a separate variable. If you bind 'fred,nick' to one variable, Oracle will look for the string "fred,nick", instead of the separate items fred and nick.

If you have variable in-lists, but don't want to be parsing a new statement for each additional item, you'll need to pass the elements as a comma separated string into one bind variable, then parse the elements out yourself.

Tom Kyte discusses a couple of ways to do this here. Basically you can do this by writing a function which returns a nested-table which you can wrap a table function around to make it a row source. Alternatively it can be done in pure SQL by using the connect by level <= :num_of_rows trick to generate the number of elements you need and parsing the bind variable that way.


jkavalik is right.

I'll say it more strongly: PARTITION BY HASH is possibly useless in any situation for enhancing performance.

Your table is referenced only by the PRIMARY KEY(id), correct?  And it is InnoDB, correct?  If you have a million rows, the BTree that contains the PK and all the data is about 3 levels deep.  For a _trillion_rows, it will be about 6 levels.

The million-fold increase in data size (from 1M to 1T) might slow down a "point query" by a factor of 2.  That's all.


Federated tables are not transactional by nature, so you don't want to put a trigger on it. Alternative would be to put a trigger to post to another local table.
See: Federated tables and triggers


What makes it so much faster to write to the "WAL" compared to directly to the "real database"?

You must have missed the answer while reading documentation:

Using WAL results in a significantly reduced number of disk writes, because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction. The log file is written sequentially, and so the cost of syncing the log is much less than the cost of flushing the data pages. This is especially true for servers handling many small transactions touching different parts of the data store. Furthermore, when the server is processing many small concurrent transactions, one fsync of the log file may suffice to commit many transactions.


How to fix discrepancies between publisher and subscription data?

I'll answer this in 2 parts.

First, if you want to get the data back in sync, then you should reinitialize. Check to see what is changing the data though. If this is a big table, reinitialization could take a while.

Secondly, If the subscriber isn't read only and you are trying to do transactional replication, you are going to run into this error a lot. People, apps, whatever can change a couple rows and boom, the emails start flying in. If you need both sides to be able to CRUD data, then I would use merge replication. This enables both sides to update data.


It isn't. Not should it be. Tables resolve to an oid that represents their relation.

SELECT 'test_1'::regclass::oid;
SELECT oid, relname FROM pg_catalog.pg_class WHERE relname = 'test_1';
SELECT oid, relname FROM pg_catalog.pg_class WHERE oid = 'test_1'::regclass::oid;

Changing their "name" is essentially a catalog update on relname. How would what you're talking about look anyway: what would happen if you called test_1_funct after the rename? What type would it expect?


Should this be an identifying relationship or not?

Patient and SpiProfessional are uniquely identified only by their relationship to User, which means they are in an identifying relationship. It can also be helpful to think of them as "depending" on User.

That was the original question. Then there were two design questions:

  So how do I recognize which one he is or whether he could be both(if that's possible)?

Without knowing more about the application's requirements or application logic, it seems that information can be determined by simple reports.

For example, to find Patients who are not also pros:

SELECT u.* , p.*
FROM User u
INNER JOIN Patient p
ON u.idUser = p.idUser
LEFT OUTER JOIN SpiProfessional sp
ON  u.idUser = sp.idUser
WHERE sp.idUser IS NULL

If you do not care about the User only being one type, then cut off the last three lines. In any case, the best way to do this depends on your application requirements.

  Should Patient and SpiProfessional have additional primary keys of their own?

It's becoming a popular practice to give every table a single column primary key. I like doing this, but it is a preference.

See also this post on StackOverflow.


OPENXML to get content by using element and attribute name

You need to use a different XPath expression to match the attribute values, eg var[@dimension="task"]/string.
Like this:
declare @xmldata xml

set @xmldata =
'<?xml version="1.0"?>
<wddxPacket version="1.0">
    <data>
        <var dimension="task"><string>Task 1</string></var>
        <var dimension="startdate"><string>2020-11-14</string></var>
    </data>
    <data>
        <var dimension="startdate"><string>2020-11-15</string></var>
        <var dimension="task"><string>Task 2</string></var>
    </data>
</wddxPacket>'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata

SELECT *
FROM OPENXML(@hDoc, '/wddxPacket/data',2)
        WITH(
            task          varchar(200) 'var[@dimension="task"]/string',
            startdate     varchar(200) 'var[@dimension="startdate"]/string'
    )

EXEC sp_xml_removedocument @hDoc

outputs
task    startdate
------- ------------
Task 1  2020-11-14
Task 2  2020-11-15


There are many tools and ways better ways to do SQL editing  for Access 2007 other then using msword , at least i would use Notepad++

below are tools i used and are good

RazorSQL
SQuirreL SQL

here is a 2 post1 and post2 in Stackoverflow similar to your issue

but if you want my opinion to this problem, the best thing i suggest is to use SSMS and add a linked server to your Access file, i like SSMS


Select from multiple rows without duplicate values, with all random data

I broke this down using pivot and not exists. I really would handle this in the presentation layer though.

--load test data
declare @table table (c1 int, c2 int, c3 int)
insert into @table
values
(1,1,1)
,(1,1,1)
,(2,3,2)
,(4,2,4)
,(5,4,6)
,(7,5,8)
,(9,7,11)
,(11,9,13)
,(14,16,15)

--get our unique values in a cte to pivot later
;with cte as(
select
    --here we add a RN so that we can use pivot without losing values
    r = row_number() over (partition by Col order by (select 1))
    ,i.*
from
    (
    --for each column, we get the unique values where they don't exist in the other two columns
    --we union them together, but give them 1 /2 / 3 column identifier
    select
        1 as Col, c1.c1 as val
    from
        (select distinct t1.c1 from @table t1
         where  not exists (select 1 from @table t2 where t2.c2 = t1.c1)
            and not exists (select 1 from @table t3 where t3.c3 = t1.c1)) c1
    union
    select
        2 as col, c2.c2
    from
        (select distinct t1.c2 from @table t1
         where  not exists (select 1 from @table t2 where t2.c1 = t1.c2)
            and not exists (select 1 from @table t3 where t3.c3 = t1.c2)) c2
    union
    select
        3 as col, c3.c3
    from
        (select distinct t1.c3 from @table t1
         where  not exists (select 1 from @table t2 where t2.c1 = t1.c3)
            and not exists (select 1 from @table t3 where t3.c2 = t1.c3)) c3
    ) i
)

--simple pivot
select
    [1], [2], [3]
from cte
pivot
(max(Val) for Col in ([1],[2],[3]))
p

RETURNS

+------+------+----+
|  1   |  2   | 3  |
+------+------+----+
| 14   | 3    |  6 |
| NULL | 16   |  8 |
| NULL | NULL | 13 |
| NULL | NULL | 15 |
+------+------+----+


How to concat one column of multiple rows (group by clause used in query)


The major problems with triggers are:

a) They are difficult to debug

b) Trigger cascades can be fatal to your database - take for example if you have an after update trigger in one table which updates another table, which also has an after update trigger, then you will run into lots of issues

A few questions:

a) How often are the "computed" summaries used within your application?

b) How often are the "computed" summaries required to be updated?

c) Would you consider creating a "reporting" table which is refreshed on a regular basis with the major "computations" that you need?


Creating a View using a query created by sp_executesql

So - expanding on your original script, the following checks to see if the view already exists (and if it does, checks to see if the view definition will be changing).  There isn't much sense in dropping the view and recreating it if it isn't really changing.  If the view already exists and the new view will be different, then the current view is dropped.

The script may need a little 'clean-up'

/*
--initially create 2 tables and run the script code
CREATE TABLE dbo.xx_table01 (col1 int)
CREATE TABLE dbo.xx_table02 (col1 int)

--add another table and run the script code
CREATE TABLE dbo.xx_table03 (col1 int)

--add another table and run the script code
CREATE TABLE dbo.xx_table04 (col1 int)

--add another table and run the script code
CREATE TABLE dbo.xx_table05 (col1 int)
*/
--
DECLARE @CurrentView nvarchar(MAX) = null
DECLARE @SchemaName nvarchar(400)
DECLARE @TableName nvarchar(400)
DECLARE @DynSQL nvarchar(MAX)
DECLARE @DynDROP nvarchar(MAX) = 'DROP VIEW XX_VIEW'
SET NOCOUNT ON
Set @DynSQL = 'CREATE VIEW XX_VIEW AS '

set @CurrentView = (select VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='dbo' and TABLE_NAME='XX_VIEW')

DECLARE cursor1 CURSOR FOR
    select TABLE_SCHEMA,TABLE_NAME
    from INFORMATION_SCHEMA.TABLES
    where
    TABLE_SCHEMA='dbo' AND
    TABLE_NAME like 'xx_table%'

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @SchemaName, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Add the select code.
    Set @DynSQL = @DynSQL + 'Select * from ' + @SchemaName +'.' + @TableName
    FETCH NEXT FROM cursor1
    INTO @SchemaName, @TableName

    -- If the loop continues, add the UNION ALL statement.
    If @@FETCH_STATUS = 0
    BEGIN
        Set @DynSQL = @DynSQL + ' UNION ALL '
    END

END

CLOSE cursor1
DEALLOCATE cursor1

IF @CurrentView = @DynSQL
    PRINT 'VIEW IS THE SAME, NEW VIEW WASN''T CREATED'
ELSE
    BEGIN
        if @CurrentView is not null
        BEGIN
        print @DynDROP
            exec sp_executesql @DynDROP
        END
        PRINT @DynSQL
        exec sp_executesql @DynSQL
    END


Information on Oracle MKSTORE utility for creating wallet

General Information about Oracle Wallet is in the Advanced Security Guide.

This is the link to managing wallets from the command line using the general orapki tool.

This is the link to managing wallets using the MKSTORE command specific to credentials storage.

*add

Here is some q&a on the steps on asktom. He also notes an Oracle bug 4395883 that can affect using connect identifiers 4, 8 or 12 bytes in length.

Here is a blog entry that contains a script for rapidly creating wallet entries. Another entry on that blog about the bug above.

Another blog entry about the whole process.

-createSSO means autologin (aka no password required)

-createLSSO means -auto_login_local (require the hostname matches where the wallet was created)

-createALO means -auto_login_only (require the hostname and the user the wallet was created on/under to match)

This does not appear to be documented under mkstore but is documented under orapki which is a companion utility.  Note auto_login_local security feature can be spoofed and offers little additional protection.  Search the web and you can find more details about why auto_login_local is ineffective.

https://docs.oracle.com/cd/E11882_01/network.112/e40393/asoappf.htm#ASOAG9833


MySQL not updating a row (zero row(s) affected)

It was an undocumented trigger that was causing the problem.


Yes, attaining consistent prefix reads is still a problem in replicated non-partitioned systems (or for reads within a single partition of a partitioned replicated system which gets the same treatment as a non-partitioned replicated system for this issue) IF replication is asynchronous or semi-synchronous.
A single client could read from follower1 and then make a subsequent read to follower2. If follower2 is lagging, the second read of data from that partition may reflect an older state of your data model than the first read of data served by follower1. So any business logic that requires the causality sequence could become erroneous.
Now, this leads to the next point: if you achieve the monotonic reads guarantee for the client, a concept usually discussed in the context of reading from a single partition or unpartitioned system, then you also get the consistent prefix reads guarantee under the single-partition assumption. The way to do this is to force the same clientID to read from the same replica, or only have a single replica (the leader), or force all the replication to be synchronous. If you have multiple partitions, on the other hand, achieving monotonic reads per partition still don't guarantee you consistent prefix reads, which is the point Martin makes in the book.
To correct what you said:

In a single leader replicated system, the single leader acts as a synchronization point.

No, the single leader is only a synchronization point for writes, but could be a synchronization point for both reads+writes if those reads are forced to go through the leader. When doing transactions involving both read/writes, you want to go through the leader. When doing reads where stale data is fine, you would want to make use of your replicas.

All follower replicas will see the writes in the correct order, so I do not see how a third-party observer would see the writes in the wrong order.

No, as per my example, even though replicas eventually see all writes in the correct order, the client can read from different replicas and see the writes out of order unless you constrain otherwise to achieve monotonic reads.

I am starting to arrive at the conclusion that causality can be violated in a multi-leader replicate system because the lag between L2 and F1 can be larger than L1 and F1.

Yes but also in a single-leader replicated system as I hope I have explained properly above.


Unable to get any type of connection using MySQL on LAN

Are you using Putty to log in to DB server or some other server?

If its DB server then, error can be caused by following

Incorrect username/password
Incorrect port number
MySQL DB is not up and running

If you are trying from different host to connect as in remote DB host make sure

you have connectivity between the hosts and
there is no IP based restrictions. As you know admin can restrict access to certain IP address.

Hope this helped.


I am facing issue while decryption (Gives NULL value), While export/Import my Colum level encrypted data

I think the solution to your problem can be found in this post (highlighting mine):

How to Create Two Identical Symmetric Keys

To summarize:

  SQL Server offers a sophisticated encryption key management system
  allowing for multiple levels of symmetric and asymmetric keys that
  protect each other. (See: The SQL Server Encryption Hierarchy)
  However, there is one use case that is only marginally supported by
  SQL Server: Creating the same symmetric key in two databases or even
  on two separate servers.

  There are only two methods to achieve this. The first is the use of an
  External Key Management solution in the form of an EKM module.
  However, EKM modules are often rather pricey investments and therefore
  not for everyone. The second method, and the only one not requiring
  external components, is a special form of the CREATE SYMMETRIC KEY
  statement. It allows us to specify a key source and an identity value.
  If identical values are used for these parameters, the resulting keys
  will be identical too.

CREATE SYMMETRIC KEY WITH KEYSOURCE Example

The extended syntax of the CREATE SYMMETRIC KEY statement is fairly straight forward, you just need to add the KEY_SOURCE and IDENTITY_VALUE clauses right after specifying the algorithm:

CREATE SYMMETRIC KEY SymmetricKey1
  WITH ALGORITHM = AES_256,
       KEY_SOURCE = '********************',
       IDENTITY_VALUE = 'SymmetricKey1'
  ENCRYPTION BY CERTIFICATE KeyProtection;

The key source is used to generate the actual key bits. It is using some form of key expansion algorithm. However, and that is my biggest concern with this method, which algorithm is used is not published. That means, while we can select the AES 256 algorithm for the key, if we actually get AES 256 level protection is unclear.

To clarify, the encryption algorithm used for an AES 256 key is always AES 256. However, if the key can be guessed easier than a random 256-bit key, you are dealing with a weakness. As we do not know how the key is derived, we also do not know how secure it is.
The Identity Value

The string that is specified in the IDENTITY_VALUE clause is not used for the generation of the actual key. It is only used to generate the key GUID that helps SQL Server identify the key necessary for decryption of an encrypted value. The DECRYPTBYKEY function does not allow us to specify the symmetric key to use for the decryption. Instead, SQL Server stores the GUID of the key that was used to encrypt the value as part of that value. That way, SQL Server will always know which key to use.

When looking at the documentation, you can get the impression, that the IDENTITY_VALUE needs to be kept a secret. However, it being unknown does not in any way increase security. If the KEY_SOURCE is known but the identity value isn't, we can just create a new key with a new GUID and then replace the key GUID within the encrypted value. As that value is just prepended to the actual cipher text, this is a simple string substitution operation.

  Summary

  The CREATE SYMMETRIC KEY statement allows us to specify a string as a
  KEY_SOURCE. That value is used to generate the actual key bits in a
  repeatable way. Therefore, we can use this feature to create the same
  key in different location.


Got an Idea from Mikael Eriksson and a help from a team lead @ work

  select Table1.AssetObjID as "@AssetObjID",
           Table1.Asset_ID as "@Asset_ID",
           Table1.FromLR as "@FromLR",
           Table1.AssetType + ', ' + Table1.StreetName + ', ' +  Table1.FromMunicNo   as "@FirstRow",
           (
           select Table2.ACIObjID as "@ACIObjID",
                  Table2.PAssetObjID as "@PAssetObjID",
                  Table2.Feature_ID + ', ' + Table2.FeatureName   AS "@ChildOfFirstRow",
                  (
                  select Table3.ITMObjID as "@ITMObjID",
                         Table3.Item_ID + ',' + Table3.[DESC] as "@GrandChildOfFirstRow"
                  from Table3
                  where Table1.AssetObjID = Table3.AssetObjID and
                        Table2.ACIObjID = Table3.ACIObjID
                  for xml path('Table3'), type
                  )
           from Table2
           where Table1.AssetObjID = Table2.PAssetObjID
           for xml path('Table2'), type
           ),
(
           select Table3.ITMObjID as "@ITMObjID",
                         Table3.Item_ID + ',' + Table3.[DESC] as "@GrandChildOfFirstRow"
                  from Table3
                  where Table1.AssetObjID = Table3.AssetObjID and
                        Table2.ACIObjID <> Table3.ACIObjID
                  for xml path('Table3'), type
)
    from Table1
    where Table1.AssetType = 'xxxx'
    for xml path('Table1'), root('xml')

So It goes something like this:

<Table1 atrributes......... FirstRow=".........."/>
<Table2 atrributes......... ChildOfFirstRow="..........">
<Table3 atrributes......... GrandChildOfFirstRow=".........."/>
</Table2>
<Table3 atrributes......... GrandChildOfFirstRow="This GrandChild took the importance as equal to ChildOfFirstRow"/>


Is there a Successor or an Alternative to mk-parallel-dump?

A bit late to the party but as a former mk-parallel-dump user I came across the same question recently.  I tried using the parallelism feature of MySQL mysqlimport however that only works for different tables even if you break up the dump files into smaller chunks.  Shame.

However I then discovered MySQL Data Dumper which is apparently being developed/maintained by Percona people.

MySQL Data Dumper project page

There are source tar balls for the various versions downloadable here:

Source downloads

The latest release supports dumping of AWS RDS instances too.  A typical invocation might be like:

./mydumper --user=root -o dump --rows=100000 -B database_name
./myloader --user=root -d dump -B database_name

You can also chunk by statement size, or number of output bytes.  By default dumping and loading use four threads, but you can set this.  There are a number of options available to control locking strategies and limiting which tables are dumped (the examples above do all the tables). For dumping across a slower network connection it supports data compression.

So far my experiments have been good although as ever I think you have to be careful to optimize MySQL for heavy parallel write access to get the best import time paying special attention to things like InnoDb log file and buffer sizes and concurrency settings.


SSRS 2017 add datasource "Something went wrong"

After further investigation I solved the problem by configuring SSRS instances to use both HTTP and HTTPS - NLB is communicating over HTTP but if needed (in case of data sources) SSRS instances accept communication over HTTPS


Single Column index or 3 column index in MS Access

I'd say it will make only a negligible difference, but that's from the gut, as factors such as the number of data sets, CPU, RAM and HDD/SSD play a role.

However, I would technically tend to go for the three-field solution, as it gives you flexibility.

You can use them more flexibly in queries.

And consider the case that you might need to replace the value of one of the fields with another one later. Then the fun really starts with the one-field solution...


Setting Up Multi-Server Administration---SQL Server Account

One weakness of MSX is that target servers uses Windows authentication and you can't change that.
I.e., your target Agent need a domain account as a service account and that account need to exist as a Windows login on the master SQL Server (and also be a user in the msdb database there and a member of the TargetServersRole).


Is it possible to restore a SQL Server 2012 Standard backup to a SQL Server 2012 Web Edition instance?

Yes, you can restore the backup of Standard edition to web edition. Just make sure to use restore database .. with move if the file locations are different between the servers.

See the comparison matrix for more details.


DELETE - DATA MANIPULATION LANGUAGE(DML)

The DELETE statement in any RDBMS is considered a DML statement.  Also known as CRUD (Create, Read, Update, Delete), this kind of statement is intended to manipulate data in a database without affecting the underlying structure of the objects.  What this means in practical term is:

A DELETE statement can be fine-tuned using a predicate via either WHERE or JOIN to delete some or all rows in a table.
A DELETE statement will be logged by the database and can be rolled back within a transaction if the statement fails.
Typically a DELETE will take row level locks on the data it is deleted, though this could escalate higher as necessary.
Because of the transactional overhead, DELETE can be "slow" (this is relative), but safer because it is fine-grained.

TRUNCATE - DATA DEFINITION LANGUAGE(DDL)

TRUCNATE is considered a DDL statement, meaning that it is intended to alter how objects are defined in a database.  Usually, DDL statements are CREATE, ALTER, or DROP, but TRUNCATE serves a particular purpose, that of "resettting" a table by removing all the rows.  The methods of this differ between the RDBMS engines and I would recommend looking at the specifics of MySQL. The practical implications of a TRUNCATE are:

TRUNCATE can not be fine grained.  If successful, it will remove all the rows from your table.
TRUNCATE is typically not logged.  This varies by RDBMS and I would suggest you looks more specifically at how MySQL handles it. (Hint, it varies by version.)
TRUNCATE requires a table metadata lock to execute.  How this is actually implemented can be specific to RDBMS, but essentially the TRUNCATE process must prevent other processes from mucking with the table in order to execute its DDL.
Because it is (typically) not logged and does not use predicates, a TRUNCATE will be faster than a DELETE, but less safe.

DROP TABLE - DATA DEFINITION LANGUAGE(DDL)

DROP TABLE goes further than a TRUNCATE in that it actually removes the table from the database completely.  This includes removing all associated objects, such as indexes and constraints.  If you dropped a table, not only would you remove all the data, but the structure as well.  This will usually be done when a table is no longer needed.  The primary concern is that since a DROP is DDL, you typically can't undo it.  Some RDBMS engines will allow you to wrap DDL within a transaction so you can roll it back, but this is not considered best practice and should be avoided.


Maintenance plan don't stop & don't backup all databases

If the job is still running, check what command it's currently executing with Adam Machanic's excellent free stored procedure, sp_WhoIsActive. After installing it (typically in the master database), you can run sp_WhoIsActive to list the running queries and see what command they're executing right now.

There's even a Percent Complete column that gets populated for backups and restores.

You might have a particularly large database (or log file) that's just in the midst of getting backed up.

For further followups & clarification, try taking a picture or copy/pasting the sp_WhoIsActive results in, showing what the maintenance plan's currently doing.


how to avoid duplicated column in two different tables?

I think that you incorrectly identified relationship between users and user_profiles;  I usually see it's either 1:0..1 (user can -but doesn't have to- have at most one profile) or 1:0..n(user can have many profiles). Surely, if it's really 1:1 in your system, it makes sense to get rid of user_profiles and add nickname to users.

There is nothing wrong with having join in the query, as dezso mentioned in comments, it's RDMS main purpose and responsibility to perform such operations in fast and reliable way.
Also, in my understanding there are just very few places in the system where you need to retrieve information from user_profiles; normally you just display basic information from users.

Finally, if it's 1:0..1 relationship, there is absolutely no need in user_profiles.id column - user_profiles.user_id should be primary key and foreign key to users.


Any way to estimate the required space for a SQL dump file?

I have dealt with questions like this in both directions

Mar 20, 2013 : How can I determine the size of a dump file prior to dumping?
May 21, 2012 : How large will a MySQL database be relative to the dump file?

Your question is a little different because you did not give anything about the target MySQL Instance (what Storage Engine will be used, which columns are TEXT/BLOB, what indexing is needed for your data, etc.)
IMHO the most straightforward way I can think of is the following
METHOD #1
Write some Perl/Python script to sum the length of all XML tag delimiters. Subtract the sum you get from the length of the file. That would essentially be the size of a CSV file less delimiters. This would be the bytes of the raw data.
METHOD #2
Do METHOD #1 and also take the count of all XML tag delimiters and add that count to the result of METHOD #1. That would like adding commas back into a CSV file. This make be closer   in size to an actual CSV should you print the output to such a file.


Oracle 11g doesn't support the LIMIT clause, though the impending 12c release is rumored to support it.

Anyway, you can do this using an analytic windowing function:

select * from
(
  select salary, row_number() over (order by salary desc) as rn
  from emp
)
where rn = 4;

You can also do this using rownum, but I find the above way to look cleaner.

Example SQL Fiddle for you to mess around with.


SQL Server select records from header and body table

I don't see why this won't work:
IF OBJECT_ID(N'dbo.Header', N'U') IS NOT NULL
DROP TABLE dbo.Header;
IF OBJECT_ID(N'dbo.Body', N'U') IS NOT NULL
DROP TABLE dbo.Body;
GO
CREATE TABLE dbo.Header
(
    iGroup int
    , sName varchar(50)
    , iValue int
);
CREATE TABLE Body
(
    iGroup int
    , sName varchar(50)
    , iValue int
);

INSERT INTO dbo.Header (iGroup, sName, iValue)
VALUES (1, 'test1', 10)
    , (2, 'test2', 20);

INSERT INTO dbo.Body (iGroup, sName, iValue)
VALUES (1, 'test3', 30)
    , (2, 'test4', 40);

;WITH src AS
(
    SELECT iGroup
        , sName
        , iValue
        , 0 as sort_by
    FROM Header
    UNION ALL
    SELECT iGroup
        , sName
        , iValue
        , 1 as sort_by
    FROM Body
)
SELECT Id = ROW_NUMBER() OVER (ORDER BY sort_by, iGroup, sName)
    , src.*
FROM src
ORDER BY sort_by, iGroup, sName;

Results:
╔════╦════════╦═══════╦════════╗
║ Id ║ iGroup ║ sName ║ iValue ║
╠════╬════════╬═══════╬════════╣
║  1 ║      1 ║ test1 ║     10 ║
║  2 ║      1 ║ test3 ║     30 ║
║  3 ║      2 ║ test2 ║     20 ║
║  4 ║      2 ║ test4 ║     40 ║
╚════╩════════╩═══════╩════════╝

Also, don't use reserved keywords as column names, please!


How to improve the performance of this select query?

Based on your comments, here is a maybe faster version:

SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.COL4 = TABLE2.COL2
WHERE TABLE1.COL1 IN ('1','TEXT','HO TED')
  AND TABLE1.COL2=1800
  AND TABLE1.COL3>=100
  AND TABLE2.COL1=10519198
  AND TABLE2.COL3=('TEXT')

And you need to create a tree-index on TABLE1(COL2, COL1, COL3). Beware: hash-based index weren't o.k, because of your condition of TABLE1.COL3>=100 is an interval-query, which is impossible to be fastened by hash-based indices! This is also the cause, why must be COL3 the last element of the TABLE1(COL2, COL1, COL3) index.

Another index which can make things better were a hash-based index on TABLE2(COL1,COL3).

Further indices which will probably help: TABLE1(COL4) and TABLE2(COL2) (both).

None of the indices should be unique.


How implement a cancel attribute in a table?

I'm not sure I like NULL for lack of being cancelled. I think I'd rather have an Active flag that is 1 by default, and set to 0 when something is cancelled. Now your checks are simply WHERE active = 1 or WHERE active = 0, instead of dealing with all the OR IS NULL or OR IS NOT NULL checks, and a row can be inactive for other reasons other than "canceled" should that model ever mature into other reasons. You can use filtered indexes to make the queries of one type or the other slightly more efficient if you are using SQL Server 2008+. NULL implies unknown, and in the case where you only have two states, "not cancelled" is not unknown.

As for the who, why, when? Perhaps that is a separate table altogether, and can act like an audit log (including if entities are marked as cancelled, then made active again, then cancelled again, etc). It would just be something like:

CREATE TABLE dbo.<something>AuditLog
(
    key INT NOT NULL FOREIGN KEY REFERENCES dbo.<something>(key),
    action NVARCHAR(32) NOT NULL,
      -- perhaps a CHECK here or a foreign key to an
      -- action code table, e.g. 'made active, made inactive'
    who NVARCHAR(64) NOT NULL,
    when DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    why NVARCHAR(MAX)
);


If you want to validate your user's password, one of the best algorithms to use is bcrypt.

I'd recommend you implement the encryption and validation at your application layer, so you work with plain text data less time possible.

But if you are looking for one way to do that on PostgreSQL's side, you can use pgcrypto extension. So you have some steps:

Install the contribs package on your system (if not done already).
Connect to your database and create the extension:

CREATE EXTENSION pgcrypto;

Your users tables will need at least the following fields:

CREATE TABLE users(
    login text PRIMARY KEY,
    passwd text NOT NULL
);

When you insert a new password, you do:

INSERT INTO users(login, passwd)
VALUES('my_login', crypt('my_password', gen_salt('bf')));

being 'my_login' the user's login (e-mail, username, etc.), and 'my_password' the plain users password. And 'bf' the encryption algorithm you are using, being 'bf' the bcrypt.
Once your user is there, to validate its password you do:

SELECT count(*) AS match
FROM users u
WHERE
        u.login = 'my_login'
        AND u.passwd = crypt('my_password', u.passwd);

If the above query returns 1, then the login was successful (login and passwd matched), if it returns 0, then the login failed (either login or passwd didn't match).

But again, I still recommend you use the same process in your application instead, because that way you don't ever need to send the plain password to the database, and work with encrypted password as soon as possible. To do on the application the idea is that your query returns the encrypted password, and you just validate it app-side, like (Python-based example):

import bcrypt
import psycopg2

def validate_login(user_login, user_passwd):
    con = psycopg2.connect('<your connection string>')
    cur = con.cursor()
    cur.execute("SELECT passwd FROM users WHERE login = %s", (user_login,))
    db_passwd = cur.fetchone()
    cur.close()
    con.close()

    if db_passwd == None:
        return false # login not found
    else:
        if bcrypt.hashpw(user_passwd, db_passwd[0]) == db_passwd[0]:
            return true # login successfully
        else:
            return false # login exists but passwd is incorrect


Create a "barrier" (prevent parallel execution) in Oracle PL/SQL?

Oracle has several locking mechanisms that you can use to serialize operations:

You could create a single-row table that is locked at the beginning of the procedure. The lock will be locked until the transaction ends (commit/rollback).
You could request a lock with DBMS_LOCK.request. This lock can be maintained for the duration of the session or until you call DBMS_LOCK.release. This lock can be maintained across commits.

Example of serialization using DBMS_LOCK:

SQL> CREATE OR REPLACE PROCEDURE serial IS
  2     l_lock_handle  VARCHAR2(128 BYTE);
  3     l_lock_request INTEGER;
  4  BEGIN
  5     dbms_lock.allocate_unique(lockname=> 'MY_SERIAL_PROC', lockhandle => l_lock_handle);
  6     LOOP
  7        l_lock_request := dbms_lock.request(lockhandle        => l_lock_handle,
  8                                            timeout           => 5,
  9                                            release_on_commit => FALSE);
 10        CASE l_lock_request
 11           WHEN 0 THEN
 12              EXIT; -- success
 13           WHEN 1 THEN
 14              dbms_output.put_line('lock already reserved, wait...');
 15              dbms_lock.sleep(5); -- sleep 5 seconds before retrying
 16           ELSE
 17              raise_application_error(-20001, 'Lock error: ' || l_lock_request);
 18        END CASE; --
 19     END LOOP;
 20     ----------------------------------------
 21     -- serialized block of code           --
 22     -- (lock will be kept accross commit) --
 23     ----------------------------------------
 24     dbms_lock.sleep(30);
 25     ----------------------------------------
 26     -- End of serialized code             --
 27     ----------------------------------------
 28     l_lock_request := dbms_lock.release(lockhandle => l_lock_handle);
 29  END;
 30  /

Procedure created

Only one session can run this procedure at the same time:

session1> exec serial;
                                   session2> exec serial;
                                   lock already reserved, wait...
                                   lock already reserved, wait...
                                   lock already reserved, wait...

PL/SQL procedure successfully completed

                                   PL/SQL procedure successfully completed.


How to check growth of database in mysql?

This is very funny. I just answered a question where I posted queries you can run to tell you how much disk space is consumed by each storage engine by database.

See that post from Jan 13, 2013 : Translating backup size to database size

In essence, you need to consult the information_schema for data and index sizes as viewed by mysqld. Here are some examples of how to get the needed information:

MyISAM

To get the amount of diskspace used by all your MyISAM tables, run this:

SELECT
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM
(
    SELECT SUM(data_length+index_length) myisam_bytes
    FROM information_schema.tables WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','mysql')
) A;

To get this by database

SELECT db,
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM
(
    SELECT table_schema db,SUM(data_length+index_length) myisam_bytes
    FROM information_schema.tables WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','mysql')
    GROUP BY table_schema
) A;

To get this by database and engine

SELECT db,engine,
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM
(
    SELECT table_schema db,engine,SUM(data_length+index_length) myisam_bytes
    FROM information_schema.tables WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','mysql')
    GROUP BY table_schema,engine
) A;

InnoDB

To get the amount of diskspace used by all your InnoDB tables, run this:

SELECT
    innodb_bytes/power(1024,1) innodb_kb,
    innodb_bytes/power(1024,2) innodb_mb,
    innodb_bytes/power(1024,3) innodb_gb
FROM
(
    SELECT SUM(data_length+index_length) innodb_bytes
    FROM information_schema.tables WHERE engine='InnoDB'
) A;

To get this by database

SELECT db,
    innodb_bytes/power(1024,1) innodb_kb,
    innodb_bytes/power(1024,2) innodb_mb,
    innodb_bytes/power(1024,3) innodb_gb
FROM
(
    SELECT table_schema db,SUM(data_length+index_length) innodb_bytes
    FROM information_schema.tables WHERE engine='InnoDB'
    GROUP BY table_schema
) A;

To get this by database and engine

SELECT db,engine,
    innodb_bytes/power(1024,1) innodb_kb,
    innodb_bytes/power(1024,2) innodb_mb,
    innodb_bytes/power(1024,3) innodb_gb
FROM
(
    SELECT table_schema db,engine,
    SUM(data_length+index_length) innodb_bytes
    FROM information_schema.tables WHERE engine='InnoDB'
    GROUP BY table_schema,engine
) A;

CAVEAT #1

I have discussed this many times before

Sep 13, 2011 : Determining max database and table size supported and present size
Dec 01, 2011 : MySQL Workbench Database Sizes
Jul 07, 2012 : Find the largest databases on my server

CAVEAT #2

Keep in mind that these queries will report how much actual space is occupied for data and indexes. The actual file sizes may actually be more than the information_schema says. That being the case, you must run OPTIMIZE TABLE tblname; for each table that has fragmentation.

For example, suppose you have a MyISAM table mydb.mytable and you run this query:

SELECT
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';

Goto to the OS and run on of the following:

ls -l mytable.MY[DI] (Linux)
dir mytable.MY* (Windows)

The sum of the bytes listed may be more than what the information_schema says. If that is the case, go run OPTIMIZE TABLE tblname; and run the same query, you may notice that the table will shrink.

That being said make sure run periodically run OPTIMIZE TABLE against all your tables that experiences heavy updates, delete, and inserts.


The meaning of the Roles in SQL Server 2008 R2

Roles are predefined sets of permissions on the database or on the server, that can be granted to the user as a package. Best practice is creating your own set of roles for the database, with the exact permissions that they need.


The question is, why is SQL server on both VMs not using max IO speed of disks when it is clearly available?

Your testing methodology:

  2.) Backup is a single file 3.) Slower one is on SAN with RAID 1, Faster one RAID 1 attached disks 4.) When doing the restore on both instances, servers are idle, and nothing except SQL server is active. Throughput is calculated by comparing fileshare copy/paste through resource monitor, by crystal disk mark, by disk speed during primary filegroup backup, by this restore with filestream.

Unfortunately the testing methodology was flawed, severely, when it comes to how SQL Server works.

  2.) Backup is a single file

This will cause SQL Server to use very little resources to read and write so as not to create too much contention on the files and volumes. To get the best backup/restore performance you'll need to backup and restore to/from multiple files and will probably have to change the buffercount and maxtransfersize options also.

  3.) Slower one is on SAN with RAID 1, Faster one RAID 1 attached disks

I would totally expect that, there would be more latency to get to the SAN than DAS. This probably (other than latency) won't effect the outcome too awful much, assuming the SAN has the same or more cache than the local disk controllers and the latency isn't too terrible.

  Throughput is calculated by comparing fileshare copy/paste through resource monitor, by crystal disk mark [...]

File copies and crystal disk mark are using multiple threads to read and write data combined with buffered I/O. They use completely different flags when opening/writing to files on disk, among even more differences. What's applicable to these tests isn't applicable to other applications or services. It's not that one is good or bad, just they behave completely different.

How can you speed this up?

Backup/Restore to multiple files
Change BUFFERCOUNT and MAXTRANSFERSIZE
Use separate disks on separate controllers for reading vs writing


MSDN has updated the documentation

  Option 6: Cloudera 5.1, 5.2, 5.3, 5.4, 5.5, and 5.9 on Linux

https://msdn.microsoft.com/en-us/library/mt143174.aspx


Implementation of an employee and customer scenario in database

I would suggest creating the ERD a bit differently, like this:

Users (contains data for both customers and employees, and their shared attributes such as name and age)
Roles (this would contain a list of roles that define a person's set of permissions. "Customer" and "Employee" could be two such roles)
UserRoles (this would be a many-to-many relationship table connecting Users to Roles. In other words: A User can have one or more Roles)

If there's no need for additional roles other than Customer and Employee, and also if the permissions set of a Customer are always contained within that of an Employee, and also if an Employee doesn't have attributes that a Customer doesn't, then I would even rather add a column "is_employee" to the Users table and this would make both Roles and UserRoles unnecessary.

If, on the other hand, Customers and Employees are generally very different entities with different attributes (other than, let's say, name and age), then I'd suggest that you'd create 3 tables:

Users
Customers
Employees

The Users table would contain the shared attributes only (i.e. name and age).
The Customers and Employees tables would have their own unique attributes that are not shared, and both would have a foreign key "user_id" linking to Users.

If we're talking conceptually, what you'd want to do, generally, is create some sort of entity that would be used by both Customers and Employees (Users in my example), and thus avoid duplicating the same attributes in more than one table.

This is rather basic normalization methodology.

If you're unfamiliar with it, I'd strongly recommend you would study it a bit:

https://www.google.com/search?q=database+normalization+tutorial


View with SentryOne Plan Explorer from SSMS context menu doesn't work if Plan Explorer is already open

Well, the best option is to just generate the plan from within SentryOne Plan Explorer in the first place, instead of switching back and forth. You get much richer metrics this way, because it captures a bunch of things SSMS doesn't, including actual costs, I/O, and Index Analysis.
But yes, this was a defect in an earlier release, that came about after the add-in had to change yet again due to the evolution of SSMS and how it works with add-ins.


In what cases is using ClickHouseDb and the like a neccessity?

Postgres and its ilk were written as general-purpose OLTP database servers. Can it handle multiple inserts and reads concurrently? Yes it can, but it saturates at a much lower level than a specific purpose-built analytics engine will. Clickhouse is still a relational database - it presents its public API as tables of pre-defined tuples, and is interacted with via SQL - just one that is highly optimized internally for answering aggregate queries on large datasets. Postgres could answer the same queries but would take longer and require more disk, memory and CPU.
A good analogy is with cars. Postgres is like the family car. It can comfortably get the kids, the dog and all the gear to a camping trip, without using too much fuel, or costing too much to purchase or maintain. Depending on the model it can go faster or slower, carry more gear or less, be more fuel efficient or thirstier. It will not, however, win an F1 race. Nor will it carry 12 tonnes of ore out of a quarry. Nor does it come with a stretcher and bottled oxygen for ambulance work. If your workload has these needs you talk to a niche vendor to acquire a very specific product that will do one job and do it really well, and pay the corresponding purchase and maintenance costs.
The complementary question is why not use Clickhouse exclusively? It's because that product is not good for a mixed random read/ write workload where Postgres excels. An ecommerce site run off Clickhouse would be awful!


DB select query performance when queried by partial key of primary key

Assuming your DBMS is using BTree indexes, there is a chance that there may be a small speed-up in certain situations.

To perform a lookup on a BTree the system starts at the root node, follows links through the intermediate nodes to the leaf node.  The fewer columns there are in the index the more index keys will fit in a page and the fewer levels will be needed in the BTree. With fewer levels there will be fewer page reads for each lookup.

There are a lot of caveats to this. For example, in most realistic scenarios the fanout in a BTree is such that for medium-large table the index becomes 3 levels deep and stays that way for a very long time, irrespective of the keys in the index. Also it is likely most of your index pages are in memory already so the additional read time is trivial.

Against the slim possibility of increased read speed you must balance the absolute certainty of decreased write speed. Each row inserted must now be written to the the table, the {a} index and the {a,b} index.

Most optimisers will consider indexes where the leading keys are matched in the query. Say you define and index on {a,b,c,d}. That would be considered for queries that have predicates on a, a and b, a and b and c, or all four columns. (I say "consider" and not "used". Choosing which index to actually use is a whole different discussion.) So for a wide class of queries you get some potential benefit without the addition write overhead. Here's more detail on that.

How much storage will an index take?  Order-of-magnitude I'd say the size of the columns times the number of rows.  So if you have an integer (4bytes) and 1 million rows, the index will take about 4MB.  It might be 3MB, it might be 10MB, depending on a lot of things.  But it won't be 1KB and it won't be 1GB.

As a rule of thumb I would avoid keys with duplicate leading indexes.  If you identify a very specific use case, after careful testing, and having considered the load on the system as a whole, then you may think of adding some. Not as a matter of course, however.


Yes, it's common to keep the username in one table, and then reference it by the id associated with that username. Then the idnum stays the same in all your tables, and you can change the username in one place.

You need to add idnum to each table where you store username.

ALTER TABLE favoritecolors ADD COLUMN idnum INT;

The idnum will be NULL by default after you create this new column.

Then update it to the idnum value matching the corresponding username:

UPDATE TABLE favoritecolors AS f
JOIN usernames AS u ON f.username = u.username
SET f.idnum = u.idnum;

If any of your tables contained a username that is not found in the usernames table, it won't update the idnum. So the idnum will still be NULL. Search to make sure this didn't happen:

SELECT username FROM favoritecolors WHERE idnum IS NULL;

This should return no rows.

After you map all your rows with a valid idnum, you can choose to drop the superfluous username from the dependent table, so the username is maintained in just one table (usernames).

ALTER TABLE favoritecolors DROP COLUMN username;

Make sure you first update all application code that queries username from any of your tables, so that it queries the idnum instead!


Does this error message indicate a SQL injection vulnerability?

It may, yes, though it is difficult to tell without knowing what the code is doing around that point. Anywhere where you take user input and place it into ad-doc SQL an easy injection route is presented to a malicious user simply by adding ' characters into the relevant input.

For instance if your code does something like:

results = dbObject.RunSQL("SELECT * FROM some_object WHERE person_name='" & request("name") & "'")

a malicious user can make a malformed HTTP request with the name value containing '; DELETE * FROM users_table; SELECT ' the code you present to the database becomes:

SELECT * FROM some_object WHERE person_name=''; DELETE * FROM users_table; SELECT ''

Perfectly we formed SQL so it will run, and if you have a table called users_table you have just lost its contents. There are much more clever attacks than this simple one possible by the same route. This isn't limited to SELECT statements - any ad-hoc SQL is potentially vulnerable to this.

Without seeing some of the relevant code, what seems to be happening is some ad-hoc SQL is being put together with a string that contains apostrophe characters resulting in incorrect syntax. So you likely have a bug that creates an injection attack route, though in this instance the bug is simply causing an error doe to some specific input value. Find it and fix it while the force is with you! Instigate a full code review too, there may be many more examples of the problem.

To fix this sort of error you need to look at two things:

Proper input validation. Refuse any request where a value seems to be in the wrong format. Expecting a UUID in a given variable? Then error and do nothing if you see anything that isn't a valid representation of that. Expecting a positive integer? Make sure the value contains nothing but numeric characters and throw out the request if not. Do this as early as possible, definitely before trying to touch your database with anything using values received from the client (in fact you should validate data from the database too before passing it back - bad data may have got into the database earlier either maliciously or due to a bug).
Use parametrised queries where ever possible - avoid ad-hoc SQL except where absolutely necessary. Your data layer (ADO, OLEDB, ...) should handle this cleanly for you meaning you do not need to worry about it misinterpreting characters that have special meaning, for instance apostrophes sometimes found in names like O'hare. For bulk inserts/updates where single procedure calls per row are too inefficient consider using an updatable recordset rather than ad-hoc INSERTstatements if such a concept is supported.
If you absolutely must put together ad-hoc SQL strings, be very very careful to escape special characters.

One thing I would recommend, though it can be costly, is having an instance of your application (not one containing real client data of course) properly penetration tested by a professional pen-testing company. The cost and hassle of this is nothing compared to losing all your data and/or customers if a hole they could have found is instead found and abused by a black-hat.


MySQL: can a table get stuck in the table cache?

The table cache does not store data, only MySQL table structs. The query cache cannot return old results because all the queries sing it are invalidated on write, and selects are blocked until that happens (actually causing some contention problems).

I will not discard a MySQL bug, as you are using a 9-year old unsupported version of the server, but you were too quick to discard cache issues/errrors on the other many layers of your application (client, server code, connector, your report code running on the wrong server, etc.).

To solve problems like this, you need to monitor queries sent and results returned at MySQL level and others, so you have enough information to identify the problem. I also recommend you to get familiar with the mysql command line client, as it is the best way to debug server issues.


PostgreSQL on Amazon RDS: out of memory error on bytea field > 64MB

PostgreSQL's handling of large binary fields is very memory-inefficient. It's quite normal for it to keep three or more copies of a given field in memory when procesing a row, albeit briefly. One TOASTed Datum, one deTOASTed Datum, one stringified hex-formatted Datum. I wouldn't be surprised if SSL added another copy.

That said, this doesn't really look like a memory issue to me. Lets say somehow PostgreSQL insanely stored TEN copies of the field. It's only 63MB. You'd need 640MB of RAM. That's nothing for your server. So unless RDS's postgres is patched to do things normal PostgreSQL doesn't do, or runs with some weird control groups configuration or something, it's unlikely to be a true memory problem.

I suspect the real issue is something else.

work_mem isn't relevant here, as it's only considered for things like sorts. It's not a memory quota for a backend.

"Out of memory" can be reported in error cases where something fails that should not usually fail, under conditions that are usually caused by not being able to allocate memory. Given that, I'm very suspicious of the "bad write retry" report from OpenSSL.

You've excluded issues with renegotiations, but in an opaque system like RDS it's hard to tell what else it might be.

Can you show more of the logs? That may be informative. Especially if you retry after setting log_error_verbosity = verbose and log_min_messages = debug1 in your configuration.

Also, what's the output of SELECT pg_column_size(x), octet_length(x) FROM mytable WHERE ..., where x is the problem row?

(Edit your question to add more info, then comment here to notify me)

If this were normal unpatched PostgreSQL on a real host I'd get you to attach gdb and capture the error so we could see exactly what's happening. But in this case your best bet will be to contact Amazon's support, since they're the only people who can actually see what's happening on the host.


Find closest match in two columns, one containing wildcards

To achieve that result you would have to use the LIKE operator and a REPLACE on the join like this:
CREATE TABLE T1 (Col1 varchar(256));

INSERT INTO T1
VALUES ('EXAMPLE A'), ('EXAMPLE B'), ('EXAMPLF A');

CREATE TABLE T2 (Col1 varchar(256));

INSERT INTO T2
VALUES ('EXAMPL*[AB]');

SELECT T1.Col1, T2.Col1
FROM T1
    INNER JOIN T2 ON T1.Col1 LIKE REPLACE(T2.Col1, '*', '%');

If you check the Arguments section of the logical operator LIKE, you'll see that the * does not stand for 'any string of zero or more characters' in SQL Server that's the role of %. The use of square brackets of your sample on the other hand is the same used in SQL Server, so it will work as expected without any change.


Get next row based on current row and conditional ordering

This should get you started:
SELECT *
FROM (
  SELECT Id, Name, LEAD(Id) OVER w AS Next_Id, LEAD(Name) OVER w AS Next_Name
  FROM table1
  WINDOW w AS (ORDER BY CASE WHEN Id < 1000 THEN Id ELSE 1000 END, Name)
) WHERE Id = 6;

Note that the order by is working correctly as it returns Id = 1011 as the next Id.
https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=ce891ef6631a142500b59ff2c686e108


SQL Developer: how to script output to query results grid

if you remove first two lines (var ... and exec), you can run this in sql_developer and get your results in a grid.

Now, if you must have variable declaration, you could replace the : to a & in "job.contract_year in (2013,:v_yr)" so it will prompt you to enter a value at run time.

If you must convert this to a procedure, then you need to go the ref_cursor route.


Only one simultaneous connection per user?

Is there any way such a connections-per-user limitation may have been defined on the database, and if so, how can I turn it off?

You are using Microsoft SQL-Server. You can limit the Resources with the Resource Governor. Have a look at the Microsoft Documents. There you can see which options might be set and you should check. If there is a limitation, you may find it there.

Documentation: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor


How to Resolve deadlock on concurrent MERGE on two tables

Concurrent MERGE statements will deadlock or produce PK violations by default, as the "scan" phase of the MERGE is performed without a restrictive lock.  You need to add a lock hint for this to work.  See https://blogs.msdn.microsoft.com/dbrowne/2013/02/25/why-is-tsql-merge-failing-with-a-primary-key-violation-isnt-it-atomic/ for MERGE locking details.

Or just fix it like this:

MERGE [dbo].[TableB] with (serializable) AS T
USING (SELECT * FROM @val) AS S
  ON ( T.Id = S.Id)
WHEN MATCHED THEN
    update set FieldB1 = S.FieldB1
WHEN NOT MATCHED THEN
    insert(TableAId, FieldB1) Values(S.TableAId, S.FieldB1);


It may be possible that both instances of MySQL are trying to record log entries to the same location (same file).

I do not think mysql allows more that one error log per MySQL instance.

Here is something further on using error log with mysqld and mysqld_safe:

  No error log renaming occurs when the logs are flushed in any case if
  the server is not writing to a named file.

  If you use mysqld_safe to start mysqld, mysqld_safe arranges for
  mysqld to write error messages to a log file or (as of MySQL 5.1.20)
  to syslog:

  • Before 5.1.20, mysqld_safe behavior is to log to a file, using the
  default error log file if the --log-error option is not given to
  mysqld_safe. Otherwise, mysqld_safe uses the file name specified using
  --log-error=file_name.

  • From 5.1.20 on, mysqld_safe has two additional error-logging
  options, --syslog and --skip-syslog.

  In 5.1.21 and up, the default with no logging options is
  --skip-syslog, which is compatible with the default behavior of writing an error log file for releases prior to 5.1.20. To explicitly
  specify use of an error log file, specify --log-error=file_name to
  mysqld_safe, and mysqld_safe will arrange for mysqld to write messages
  to a log file. To use syslog instead, specify the --syslog option.

  In 5.1.20 only, the following conditions apply: 1) The default is to
  use syslog, which is not compatible with releases prior to 5.1.20. 2)
  Logging to syslog may fail to operate correctly in some cases; if so,
  use --skip-syslog or --log-error.

  For logging to syslog, messages from mysqld_safe and mysqld are
  written with a tag of mysqld_safe and mysqld, respectively. As of
  MySQL 5.1.21, to specify a suffix for the tag, use --syslog-tag=tag,
  which modifies the tags to be mysqld_safe-tag and mysqld-tag.

  If you specify --log-error in an option file in a section that mysqld
  reads, mysqld_safe also will find and use the option.

Since you started mysqld_safe to start mysqld, you just need to start mysqld_safe and explicitly choose a separate file for the error of the other MySQL Instance using the --log-error option.


Why mysql-noinstall5.6.x.zip includes ibdata file?

To give you an more extensive answer, inno_db stores these data
data dictionary aka metadata of InnoDB tables
change buffer
doublewrite buffer
undo logs

so there is a lot of information and it can get really big.
so mysql 5.6 stores there data, that it needs when it is restarted, and 5.5 doesn't.


Why does formatting a time with AM/PM specifier return null?

Edit: tt is a valid format specifier for datetime, but not time. The \ is also optional in datetime, but not time. Example at end.

I think the tt is the problem
I am not finding tt for time formatting - what is it supposed to do?

Did you try without using @FormatString?
Below works

declare @FormatString nvarchar(200) = N'hh\:mm'
declare @TimeChar nvarchar(200) = N'10:27'
select @FormatString
select @TimeChar
select convert(time, @TimeChar)
select format(convert(time, @TimeChar), N'hh\:mm')
select format(convert(time, @TimeChar), @FormatString)

Example:

select format(convert(time    , '10:27'), N'hh\:mm')   -- returns 10:27
     , format(convert(time    , '10:27'), N'hh:mm')    -- returns NULL (need to escape ':'):
     , format(convert(time    , '10:27'), N'hh:mm tt') -- returns NULL
     , format(convert(datetime, '10:27'), N'hh:mm tt') -- returns 10:27


sqlcmd is running but database shown no running query

Probably not too many people using CentOS & sqlcmd so I would investigate that first or
maybe you should try out mssql-cli instead ?
https://github.com/dbcli/mssql-cli/tree/master/doc/installation


How to manage log file size in large, heavy-use database

If you never anticipate doing a point-in-time restore, then you just need to do 2 things:

Change the database to SIMPLE recovery mode
Do large changes in small batches.

In SIMPLE recovery mode, the log file is used only for transactions in process. When the transaction completes, the space in the tran log will be marked for re-use, and the next transaction will just re-use the space*.

(* This isn't strictly true, there are "checkpoints" that run occasionally that actually clear the space for re-use, but for the most part it works without intervention; only finicky DBAs will likely care about the inner details here.)

So keep your transactions bite-sized:

If you need to delete all rows of a table, don't do a DELETE of 100m rows (that will blow up the log), do a TRUNCATE instead, or just DROP the table and recreate it.
If you can't do that (you need to delete just some rows), do it in smaller batches
When doing large data imports, learn about bulk imports or an SSIS Fast Loads that load data quickly and commits the data every so often
Same thing for updates, don't update every row of a massive table, use more selective WHERE clauses to limit your updates to ~100k rows at a time. Learn how to use cursors in SQL

The exact details are going to depend on your actual database and actual activity, of course, but a database with 1TB of data shouldn't have a tran log file any larger than, say, 100Gb, depending on how big your batches are.

With regard to shrinking the existing file, you can use the following script to see the free/used space in your data and log files:

SELECT DB_NAME() as dbname, type_desc, name as logical_name,
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;

If the used space in the log is high, then you likely still have open transactions. Look for the 3rd-party tool sp_WhoIsActive for digging into your current activity/open trans.

When you are ready to shrink, pick a reasonable target size, and execute a SHRINKFILE:

DBCC SHRINKFILE (NAME = 'mylogfile', SIZE = 100000)

(That size is in MB).

If your log file still grows, go back to sp_WhoIsActive to see what transactions are running so long, and figure out how to do them in batches.


Sure, Joe Sack writes that you can track it down for live sessions with the help of sys.dm_exec_cursors, like this:

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (53) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

With 53 being a current live session holding a cursor open. You can also use 0 to get all current open sessions.

Granted, this is different than sp_BlitzCache - it's only for currently running queries.


How to check for record which has no entry in another table with some specific value?

I typically write this as NOT EXISTS query because this aligns with the wording of the problem ("find everything in table1 where no corresponding row exists in table2")

select t1.*
from table1 t1
where not exists (select *
                  from table2 t2
                  where t1.id = t2.table1_id
                    and type = 'Some Value');

Depending on your DBMS, mcNets' solutions might be faster - that depends a lot on the query optimizer, in Oracle for example it won't make any difference.


Why did error messages change after upgrading to MySQL 5.7.9?

An off-the-wall guess:  You have a column (or something) that matches a new reserved word.

5.7 adds GENERATED, OPTIMIZER_COSTS, STORED, VIRTUAL as "Reserved" words, plus a number of "non-reserved" words.  Details in keywords.html.

In some contexts, use of a keyword can lead to a cryptic error message.  When you find the context, please file a bug report at http://bugs.mysql.com .

Another tip:  Search for 1054 in your sources, then look at what immediately precedes it.


Amazon Aurora MySQL5.6 Performance Schema column datatype modified and can't be corrected

This appears to be the result of the MySQL engine upgrade to version 5.7.3 or later, where the definition of some tables in the performance schema have changed.

If you were running standard MySQL, mysql_upgrade would have fixed that. You'll need to contact Amazon support for ways to upgrade your existing databases to the new engine version.

In the worst case you'd need to create new copies of each affected database and move the data.


LEFT and RIGHT JOIN multiple table, show all null data on mariadb 10.3

SELECT r.id, s.name student_name, c.name criteria_name, c.weight
FROM tbl_reporting r
JOIN tbl_student s ON r.id_student = s.id
JOIN tbl_criteria c ON r.id_criteria = c.id
UNION ALL
SELECT NULL, s.name, NULL, 0
FROM tbl_student s
WHERE NOT EXISTS ( SELECT NULL
                   FROM tbl_reporting r
                   WHERE r.id_student = s.id )

UNION ALL
SELECT NULL, NULL, c.name, 0
FROM tbl_criteria c
WHERE NOT EXISTS ( SELECT NULL
                   FROM tbl_reporting r
                   WHERE r.id_criteria = c.id );

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=989338bb30f2f5b4d7be4667763e9522


I would add Fleet_Unit.unit_id to the GPS_Device table as a FK. I would use a surrogate key as the PK, and put a unique constraint on device_ID.


Summary

SQL Server uses the correct join (inner or outer) and adds projections where necessary to honour all the semantics of the original query when performing internal translations between apply and join.

The differences in the plans can all be explained by the different semantics of aggregates with and without a group by clause in SQL Server.

Details

Join vs Apply

We will need to be able to distinguish between an apply and a join:

Apply

The inner (lower) input of the apply is run for each row of the outer (upper) input, with one or more inner side parameter values provided by the current outer row. The overall result of the apply is the combination (union all) of all the rows produced by the parameterized inner side executions. The presence of parameters means apply is sometimes referred to as a correlated join.

An apply is always implemented in execution plans by the Nested Loops operator. The operator will have an Outer References property rather than join predicates. The outer references are the parameters passed from the outer side to the inner side on each iteration of the loop.
Join

A join evaluates its join predicate at the join operator. The join may generally be implemented by Hash Match, Merge, or Nested Loops operators in SQL Server.

When Nested Loops is chosen, it can be distinguished from an apply by the lack of Outer References (and usually the presence of a join predicate). The inner input of a join never references values from the outer input - the inner side is still executed once for each outer row, but inner side executions do not depend on any values from the current outer row.

For more details see my post Apply versus Nested Loops Join.

  ...why is there an outer join in the execution plan instead of an inner join?

The outer join arises when the optimizer transforms an apply to a join (using a rule called ApplyHandler) to see if it can find a cheaper join-based plan. The join is required to be an outer join for correctness when the apply contains a scalar aggregate. An inner join would not be guaranteed to produce the same results as the original apply as we will see.

Scalar and Vector Aggregates

An aggregate without a corresponding GROUP BY clause is a scalar aggregate.
An aggregate with a corresponding GROUP BY clause is a vector aggregate.

In SQL Server, a scalar aggregate will always produce a row, even if it is given no rows to aggregate. For example, the scalar COUNT aggregate of no rows is zero. A vector COUNT aggregate of no rows is the empty set (no rows at all).

The following toy queries illustrate the difference. You can also read more about scalar and vector aggregates in my article Fun with Scalar and Vector Aggregates.

-- Produces a single zero value
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1;

-- Produces no rows
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1 GROUP BY ();

db<>fiddle demo

Transforming apply to join

I mentioned before that the join is required to be an outer join for correctness when the original apply contains a scalar aggregate. To show why this is the case in detail, I will use a simplified example of the question query:

DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

SELECT * FROM @A AS A
CROSS APPLY (SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A) AS CA;

The correct result for column c is zero, because the COUNT_BIG is a scalar aggregate. When translating this apply query to join form, SQL Server generates an internal alternative that would look similar to the following if it were expressed in T-SQL:

SELECT A.*, c = COALESCE(J1.c, 0)
FROM @A AS A
LEFT JOIN
(
    SELECT B.A, c = COUNT_BIG(*)
    FROM @B AS B
    GROUP BY B.A
) AS J1
    ON J1.A = A.A;

To rewrite the apply as an uncorrelated join, we have to introduce a GROUP BY in the derived table (otherwise there could be no A column to join on). The join has to be an outer join so each row from table @A continues to produce a row in the output. The left join will produce a NULL for column c when the join predicate does not evaluate to true. That NULL needs to be translated to zero by COALESCE to complete a correct transformation from apply.

The demo below shows how both outer join and COALESCE are required to produce the same results using join as the original apply query:

db<>fiddle demo

With the GROUP BY

  ...why does uncommenting the group by clause result in an inner join?

Continuing the simplified example, but adding a GROUP BY:

DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

-- Original
SELECT * FROM @A AS A
CROSS APPLY
(SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A GROUP BY B.A) AS CA;

The COUNT_BIG is now a vector aggregate, so the correct result for an empty input set is no longer zero, it is no row at all. In other words, running the statements above produces no output.

These semantics are much easier to honour when translating from apply to join,  since CROSS APPLY naturally rejects any outer row that generates no inner side rows. We can therefore safely use an inner join now, with no extra expression projection:

-- Rewrite
SELECT A.*, J1.c
FROM @A AS A
JOIN
(
    SELECT B.A, c = COUNT_BIG(*)
    FROM @B AS B
    GROUP BY B.A
) AS J1
    ON J1.A = A.A;

The demo below shows that the inner join rewrite produces the same results as the original apply with vector aggregate:

db<>fiddle demo

The optimizer happens to choose a merge inner join with the small table because it finds a cheap join plan quickly (good enough plan found). The cost based optimizer may go on to rewrite the join back to an apply - perhaps finding a cheaper apply plan, as it will here if a loop join or forceseek hint is used - but it is not worth the effort in this case.

Notes

The simplified examples use different tables with different contents to show the semantic differences more clearly.

One could argue that the optimizer ought to be able to reason about a self-join not being capable generating any mismatched (non-joining) rows, but it does not contain that logic today. Accessing the same table multiple times in a query is not guaranteed to produce the same results in general anyway, depending on isolation level and concurrent activity.

The optimizer worries about these semantics and edge cases so you don't have to.

Bonus: Inner Apply Plan

SQL Server can produce an inner apply plan (not an inner join plan!) for the example query, it just chooses not to for cost reasons. The cost of the outer join plan shown in the question is 0.02898 units on my laptop's SQL Server 2017 instance.

You can force an apply (correlated join) plan using undocumented and unsupported trace flag 9114 (which disables ApplyHandler etc.) just for illustration:

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY
(
    SELECT COUNT_BIG(DISTINCT mt2.Col_B) AS dc
    FROM   #MyTable AS mt2
    WHERE  mt2.Col_A = mt.Col_A
    --GROUP BY mt2.Col_A
) AS ca
OPTION (QUERYTRACEON 9114);

This produces an apply nested loops plan with a lazy index spool. The total estimated cost is 0.0463983 (higher than the selected plan):

Note that the execution plan using apply nested loops produces correct results using "inner join" semantics regardless of the presence of the GROUP BY clause.

In the real world, we would typically have an index to support a seek on the inner side of the apply to encourage SQL Server to choose this option naturally, for example:

CREATE INDEX i ON #MyTable (Col_A, Col_B);

db<>fiddle demo


Performance impact of sp_configure blocked process threshold change

Minimal hit. The cost of evaluating the blocked process list is already there since this is how deadlocks are detected. The cost of generating and writing an event in the server side log is quite low and any server IO should be able to handle an extra write into LOG/*.trc every 5 seconds (At worst). But 5 is a quite low setting, you may be flooded by reports. Is your server server blocking so well behaved as to consider any 5 second block an incident to investigate?

Make sure you monitor the generated traces and make sure you don't cause an self-induced outage due to disk space exhaustion...


Mimicking cascading foreign key referencing system table

If you are just looking for a way to get ids of the droped table, maybe this code will help you out :

CREATE OR REPLACE FUNCTION master.ddl_trigger_drop_table_fct()
  RETURNS event_trigger
 LANGUAGE plpgsql
   AS $$
DECLARE
    all_variables character varying;
    r RECORD;
    schema_name varchar (250);
object_type varchar (100);
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_dropped_objects () LOOP
        if r.schema_name = 'master' and r.object_type = 'table'
         then
            schema_name := r.schema_name;
            object_type := r.object_type;
            all_variables := concat(
                'classid: ', r.classid, chr(10)
                , 'objid: ', r.objid, chr(10)
                , 'objsubid: ', r.objsubid, chr(10)
                , 'original: ', r.original, chr(10)
                , 'normal: ', r.normal, chr(10)
                , 'is_temporary: ', r.is_temporary, chr(10)
                , 'object_type: ', r.object_type, chr(10)
                , 'schema_name: ', r.schema_name, chr(10)
                , 'object_name: ', r.object_name, chr(10)
                , 'object_identity: ', r.object_identity, chr(10)
                , 'address_names: ', r.schema_name, chr(10)
                , 'address_args: ', r.address_args);

            RAISE NOTICE '%', all_variables;
        end if;
    END LOOP;

    ... [do some stuff] ...
END;
$$;

and after you have adjusted this code to fit your needs, you just need to bind your function too your event trigger :

CREATE EVENT TRIGGER ddl_trigger_drop_table ON sql_drop WHEN TAG IN ('DROP TABLE')
   EXECUTE FUNCTION [schema_name].ddl_trigger_drop_table_fct();

But same as others : why do you want to do this might interests me a bit ;)


HSQL DB grows large and is full of zeroes

One explanation is the case where lots of rows of data are created but later deleted by the application. If these rows happen to be in the memory cache throughout, they are never written to disk.

The space for deleted rows is reused for up to 500 most recent items, the rest discarded.

The CHECKPOINT DEFRAG command can be used from time to time to compact a database that has lots of empty spaces.

The database engine does not write zeros. The zeros in the file are there when some file space is allocated for use.


Adjusting the my.cnf file for DB servers after an instance resize

The most important change would be
innodb_buffer_pool_size=6G --> 12G

The number of buffer_pool_instances should be 1 per GB, not one per core.  So, 12 in your new machine.  However, there is a limit of 16.  As for why...

I suspect it makes very little difference -- perhaps single-digit-percentage when the server is extremely busy.  (If a server is that busy, it is about to keel over; you have worse problems.)

Essentially nothing else requires changing.
After you have been running normally for at least a day, grab SHOW GLOBAL STATUS and SHOW VARIABLES; then get an analysis here:  http://mysql.rjweb.org/doc.php/mysql_analysis#tuning


Local Database for Importing/manipulating ~200gb of data

If you are the ONLY user connecting and manipulating data, I would suggest to download SQL Server 2016 DEVELOPER Edition (FREE) since it has tight integration with R.


SQL Server error "Missing family sequence number 2"

The error indicates that your .bak file is only a part of the backup. When the backup was written it was written across three files. If you can write a backup to multiple files on multiple drives, it will often complete faster.

According to this StackOverflow Q & A, RESTORE LABELONLY should help you confirm that the backup spans multiple files, but it won't let you actually restore the backup - you'd need all three files for that.

If you can locate all three files (and are sure they're from the same run of the backup), you should be able to restore the backup - you'll need to specify all three files in the RESTORE command (or through the SSMS restore dialog).

IF you can't, but you still have access to the original server, then you can take a new backup.

NOTE: If that original server has a DBA, work with him. You want to make sure that you taking your backup won't cause issues with any replication, log shipping, or ability to restore to a point in time.

If you're effectively the DBA, then:

Get into SQL Server Management Studio (SSMS), connect to the server, and expand Databases.
Right-click on the original database, and choose Tasks -> Backup... A dialog window will open.
On the first page of the dialog:

Make sure the database you want to back up is selected as the Source database.
Make sure the Backup Type is "Full".
Check on "Copy Only Backup".
Make sure backup component is set to "Database"
At the bottom of the window: if there are any existing entries in the "Destination" list, remove them.
Click "Add" to add a new backup file. Make sure that you know where the file will be (generally, you've only got the option to write to the DB server's local disk using this interface; if you need to write to a folder on a shared server, you can try pasting in the full UNC path to the shared server and entering the new file name in the file selection dialog. If that doesn't work, specify a local drive and a unique file name). Also, make sure the file name is unique - you don't want to overwrite any existing files on the server.
When you get back to the original Back Up dialog window, change to the "Options" page (left side of the window).
Under "Overwrite Media", select "Back up to a new media set, and erase all existing backup sets".

Yes, this sounds scary - however, this is primarily used to reinitialize a backup tape so any old data is erased; as long as you're writing to a new file name, everything should be fine.
Enter a new for the media set - something like " - one time backup" is sufficient.

You can set some of the options under "Reliability"; I'd just leave them all off.
You can accept the default for "Set backup compression" as well; I'd probably set tit to "Compress backup", but that's not critical. (If you're not running SQL Server 2008 or later, then this is either not there at all, or not relevant).
DO NOT click "OK", instead, click the little arrow by "Script" at the top of the dialog window, and choose "Script Action to New Query Window". Once you can see the script, click "Cancel".

Double-check the script - there should only be one file included in it.
If you wanted to write the file to a different location, you can change the file path in the script. Again, the server needs to be able to access the location you specify, so the account used to run the SQL Server service (and/or the SQL Server Agent service) needs to have access to the path where you want the file written.
Now, run the script.

Assuming all goes well, you can now use your new backup file to perform the restore to the other location. Again, the SQL Server you're restoring to will need to have access to the backup file, so you may want to copy it to that server.


Migration from SQL Server 2012 Ent to SQL Server 2014 STD

The scripts go over all of your tables, rebuild all the indexes and update their statistics.
The reasons they CAN improve performance is that they reduce the index fragmentation and force SQL Server to generate new execution plans to queries, that may be more suitable.

I'm not sure, though, why you need to run the first command which disables the indexes..

Before running it in production you need to verify they run well on a test system that is similar to the production system. Also do it during a maintenance window and not during the work day.


How to fail over availability groups on multiple remote servers

This solution doesn't use a CMS, but I think it'll work.  It's a little script I put together for searching through a comma delimited list of servers for SQL Operators that should be changed as DBAs are swapped in and out.

I modified the script for multiple AG failovers. You can adapt it to suit your specific environment.

You have to build a comma delimited list of of the secondary servers and availability groups:
server1,ag1,server2,ag2,....
...and plug that into the code.

This script's output is the executable code. The output from this sample script should look like this:

(1 row affected)
:CONNECT server1
ALTER AVAILABILITY GROUP [L1] FAILOVER;
GO

:CONNECT server2
ALTER AVAILABILITY GROUP [L2] FAILOVER;
GO

:CONNECT server3
ALTER AVAILABILITY GROUP [L3] FAILOVER;
GO

:CONNECT server4
ALTER AVAILABILITY GROUP [L4] FAILOVER;
GO

Then you copy/paste this into a new SSMS window, remove the non-TSQL lines like "(1 row affected)", switch to SQLCMD mode in SSMS, and execute.

What this script really needs is an intelligent way of knowing which server is secondary, so you don't have to know it for the CDL. I'll try to put that together and post an edit here.

Of course, test this on a couple guinea pig servers.

/*
This script will create a TSQL script that you can run separately in SQLCMD mode.

Steps:
1. Edit @Servers with a comma delimted list of your servers.
2. Edit @SearchVariable1 and @SearchVariable2 with the search variables.
        If you want to search for only 1 string, then make both variables that string.
        For example 'kanjana', which is part of the name of the DBA who had this set of servers before me.
    You can add more SearchVariableN variables. Edit the dynamic SQL statement.
3.  Edit the dynamic SQL to do whatever you need.
4. Copy and paste the result into a new window, specify Query | SQLCMD mode, and execute.
    Remove any non-TSQL lines, such as '(1 row affected)'
5. If logical, you can re-run the script to check the results.

*/

USE MSDB
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString
-- SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
GO

CREATE FUNCTION [dbo].[SplitString]
(
      @Input NVARCHAR(MAX),
      @Character CHAR(1)=','
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

-- User configuration variables:
DECLARE @Servers VARCHAR(MAX) = ''

-- ************ Edit these variables:
SET @Servers = @Servers +
    'server1,L1,server2,L2,server3,L3,server4,L4'

-- ************

-- other variables:
DECLARE @ThisServer VARCHAR(100)
DECLARE @ThisL VARCHAR(100)
DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @B CHAR(2) = CHAR(13) + CHAR(10)

DECLARE MyCursor CURSOR
    FOR SELECT * FROM dbo.SplitString(@Servers,',')
OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @ThisServer
FETCH NEXT FROM MyCursor INTO @ThisL

WHILE @@FETCH_STATUS = 0
BEGIN
    -- PRINT @ThisServer
    SET @SQL = @SQL + ':CONNECT ' + @ThisServer + @B
    SET @SQL = @SQL + 'ALTER AVAILABILITY GROUP [' + @ThisL + '] FAILOVER;'
    SET @SQL = @SQL + @B + 'GO' + @B + @B

    FETCH NEXT FROM MyCursor INTO @ThisServer
    FETCH NEXT FROM MyCursor INTO @ThisL
END

PRINT @SQL

CLOSE MyCursor
DEALLOCATE MyCursor

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString


XML Network Call to Application from SQL Server 2005

So one potential way of doing this would be to write your SQL Server query to output an XML string of the data you want to send by using FOR XML.

You could then create an SSIS package and use a Data Flow Task to save the XML to an XML file, meaning you're logging all the exports made.

A script task, following the success of the Data Flow Task, can then do the API call. You'd write standard C#, grabbing the file and calling the API method. You would then use the Task result variables to return Success/Failure based upon the response from the API.

However, you've said you're more of a C# Dev, so why not just write a stand alone App that does all this? It would be much more robust than SSIS!

Update

There is actually a Webservice task in SSIS that can apparently do what you'd require. However, I believe that if you're more comforatble developing in C#, that is the approach you should take.


How do I backup SQL Server Agent configuration?

They are actually all in the msdb database. However, it's not advisable to simply restored msdb over the existing one. The best solution is to script the jobs out on a weekly baseis. Take a look at this for some example steps on how to backup/restore jobs

http://www.sqlservercentral.com/Forums/Topic749226-146-1.aspx#bm749280


Check whether empty strings are present in character-type columns

The best way to do this is with a CHECK constraint, as you noted, possibly via a DOMAIN, e.g.

CREATE DOMAIN nonempty_string AS text
CONSTRAINT non_empty CHECK (length(VALUE) > 0);

then ALTER existing columns to use the domain.

If that's not possible, you will need to query INFORMATION_SCHEMA to find all columns of the target type across all tables, then for each column, dynamically generate a query to check. You can use PL/PgSQL and the EXECUTE statement for this; there are many examples of its usage this way elsewhere on Stack Overflow.

You cannot write a single query to do what you want. It just won't work. You'll have to use query generation from information_schema.


Why is creating and dropping a random index fixing this performance problem?

Dropping an index on your table will flush the execution plan that refer to this table from the cache. (I think the index need to contain a column that is referenced in the query but not 100% sure about that).

SQL then build a fresh execution plan which "fix" your issue.

You could try to rebuild the stats (instead of dropping an index), or drop the execution plan manually (see Sp_blitz store procedures to get the command easilly). You will probably have the same behavior (Query fix). If so, then you may want to read on parameter sniffing issue.

P.s. This is rarelly a good practive to have a table without cluster index. Usually, the only good case I've seen is for log table where you want the insert to be done real quick... but in your case, if you have nonclustered index, you will have overhead for the inserts anyway.


Can't insert arabic text into mysql database using mysql prompt

Since you said you can only use the Command prompt, my suggestion is to shift the character set to one that supports Arabic within the mysql client:

I just ran these commands on my laptop at home (Windows 8.1)

mysql> select * from information_schema.character_sets where description like '%arabic%';
+--------------------+----------------------+----------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION    | MAXLEN |
+--------------------+----------------------+----------------+--------+
| cp1256             | cp1256_general_ci    | Windows Arabic |      1 |
+--------------------+----------------------+----------------+--------+
1 row in set (0.00 sec)

mysql> select * from information_schema.global_variables where variable_name like 'version%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION                 | 5.6.21                       |
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION_COMPILE_MACHINE | x86_64                       |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql>

Start up the mysql client using --default-character-set like this

mysql -uroot -p --default-character-set=cp1256

In the event you are running on Linux, make sure you run

select * from information_schema.character_sets where description like '%arabic%';

and make sure you have a character set that support Arabic

Give it a Try !!!

There is a nice post in StackOverflow Save Data in Arabic in MySQL database. It has three nice queries to help inspect your database to make sure data can be saved with the right character set.


How does PostGIS make its data types compatable with Postgres?

Is there a way using bytea or any other stuff that I can directly store my instances in Postgres.

Yes, sure you can store anything as bytea. However, don't expect the database to know how to work with it. PostGIS is an extensive system that creates types, operators, access methods, indexes, and a slew of other stuff. You'll have to recreate all of that stuff for your type.

Alternatively, you can create something that reads a PostGIS types and experts one of your own types. This would be a C function. you can then create a cast from PostGIS's type, to your type and call all of your stuff -- your library -- working only on your native implementation.

This allows you to use all that is in PostGIS, and then still access all of the functionality you've got at the cost of a cast.


Publication shows up in secondary server

When you've restored the database on the secondary you actually moved all its properties, including the fact it's a publisher. In this case you don't have a working replication, just the attributes for replication enabled. So this won't let you drop the db as you want.

The simplest way to remove replication metadata for this db is by using the system stored procs:

-- ON THE PUBLISHER
use YourDb
Go

exec sp_cleanupdbreplication
exec sp_removedbreplication
GO

Just make sure you're running this script on the secondary, the one you want to drop, not on the production db.


BCP is a command line utility with switches that are used to control the import process, including the number of rows per batch.  The number of rows is not as important as the number of rows per second that are being imported.

BCP documentation and examples:
https://msdn.microsoft.com/en-CA/library/ms162802.aspx

The switch to control the batch size is:
-b batch_size

The speed of the import is affected by variables including the target table (indexes, heap vs clustered, etc.), the I/O capacity of the server, how busy the server is, the type of logging that is enabled, speed of the network, speed of the process on the client machine, etc.

I suggest you first start looking at the target table and go from there.


This started as a comment/questions but it got to long so I moved it here:

I'm really thrown by this question.  1.5mil rows isn't really all that big.  And the point behind an identity is that it's ever increasing.  If that's your CL you shouldn't be doing inserts into the middle of a page, certainly not often enough to cause the level of fragmentation you're seeing.

Couple of questions:

Are you doing IDENTIY_INSERTS?  Basically specifying what the identity value should be?  Or have you re-set the identity at some point so that you are inserting into the middle of the range?

Typically if you are doing inserts it looks like this:

5 6 7 8 < Next insert goes here >

But if you have something like this (assume your next identity value is 4)

 1 2 3 < Next insert goes here > 100 101

Then you could be seeing quite a few page splits.  But in the normal course of things you shouldn't be.

Is there any chance you are shrinking your database?  Auto_shrink or a maint plan/job that does shrinks?  If so it's the shrink that's causing your fragmentation not the clustered index.

In general there is nothing wrong with a HEAP and they can be faster for INSERTs.  My biggest concern with them tends to be if you are doing large numbers of deletes or updates (which you say you aren't).  In those cases you can get a space leak and end up with a table that is multiple GBs in size but has 0 rows.

Actual answer

Given you have a log file, and are only ever inserting, you could try dropping the PK and see how performance goes (in a test environment first of course). Once you've run some tests using your workload and seeing how things go then make your change in production and monitor there for a while. You might even consider dropping the identity column entirely.

Do check that SHRINK thing though.  That's a killer.


Postgres SELECT rows where JSON array contains "most" of values

You can use exists combined with json_array_elements

select id from items where exists
    (select 1 from json_array_elements(labels->'labels') f(x)
        where x->>'value' in ('peach', 'fruit', 'orange')
        having count(*)>=2
    );

It gives the answer you want, but good luck making it fast if the table is large.  (Changing from JSON to JSONB might speed it up slightly)


SQL Server 2008 R2 Edition Upgrade Process

You will need to use SQL Server 2008 R2 Enterprise Edition media to perform the in-place edition upgrade.  I am unaware of any option available that allows you to perform an edition upgrade without a version upgrade when using media from a different SQL Server version.

Will your licensing agreement cover this?  That wholly depends on your licensing (though I highly suspect it won't), but that question is best sent to your license partner/reseller.

If you have a MSDN license, you may still be able to pull down the binaries for SQL Server 2008 R2, so that would be one way to get the required media.


Google Datastore (NoSQL) backend for PHP CMS

Try this link https://cloud.google.com/appengine/docs/php/storage . This should get you started on the use and main concepts.
FYI joins are not applicable to the datastore but if you model out your data correctly you should have no problem.