Trying to check if a string contains a number exclusively

Perhaps you are over-complicating this by focusing too much on wanting a number. Take a step back for a moment. What you actually want is a substring without any digits on either side of it. The only way a number could be part of a larger number is to have at least 1 digit on either side of it, right? So as long as you only pass in numbers, then this definition should still produce numbers that do not have any digits on either side.

With that in mind, we just need 3 PATINDEX predicates to cover the passed-in value being on the far left, on the far right, or in the middle. Try the following as it seems to work:

GO
CREATE PROCEDURE #TestFindRunner
(
  @Runner VARCHAR(10)
)
AS
SET NOCOUNT ON;

DECLARE @Data TABLE
(
  [ID] INT NOT NULL PRIMARY KEY,
  [Runners] VARCHAR(50) NULL
 );

INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6');
INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45');
INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92');
INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6');
INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63');
INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL);
INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6');

SELECT tmp.*
FROM   @Data tmp
WHERE  @Runner COLLATE Latin1_General_100_BIN2 = tmp.[Runners]
OR     PATINDEX('%[^0123456789]' + @Runner COLLATE Latin1_General_100_BIN2,
                tmp.[Runners]) > 0
OR     PATINDEX(@Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2,
                tmp.[Runners]) > 0
OR     PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%'
                COLLATE Latin1_General_100_BIN2, tmp.[Runners]) > 0
GO

And then test with:

EXEC #TestFindRunner 0;
EXEC #TestFindRunner 2;
EXEC #TestFindRunner 4;
EXEC #TestFindRunner 8;
EXEC #TestFindRunner 11;
-- 0 rows

EXEC #TestFindRunner 3;   -- 4
EXEC #TestFindRunner 77;  -- 3
EXEC #TestFindRunner 111; -- 5
-- 1 row

EXEC #TestFindRunner 5; -- 2 and 3
-- 2 rows

EXEC #TestFindRunner 1; -- 6, 7, 8, and 9
-- 4 rows

EXEC #TestFindRunner 6; -- 1, 4, 6, 7, and 11
-- 5 rows

EXEC #TestFindRunner 7; -- 1, 2, 6, 7, 8, and 9
-- 6 rows

EXEC #TestFindRunner 9; -- 1, 2, 3, 6, 7, 8, and 9
-- 7 rows

The reason for having 3 variations of the PATINDEX is that PATINDEX search patterns are not Regular Expressions (RegeEx), contrary to what many people say / think (same with LIKE patterns). PATINDEX and LIKE patterns do not have quantifiers, so it is not possible to specify that the [^0123456789] single character replacement should be "0 or more"; it is "one and only one; no more, no less".

Forcing the binary collation (i.e. the COLLATE Latin1_General_100_BIN2 after each @Runner reference) ensures that we are only dealing with these 10 decimal digits and not any other character that might be considered equivalent

To put the above logic into an Inline Table-Valued Function (TVF) so that it is easier to use (and more efficient than a similarly easy-to-use scalar UDF), try the following:

USE [tempdb];
GO
CREATE FUNCTION dbo.IsRunnerPresent
(
  @Runner VARCHAR(10),
  @Runners VARCHAR(8000)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

  SELECT CONVERT(BIT,
    CASE WHEN @Runner COLLATE Latin1_General_100_BIN2 = @Runners
           OR PATINDEX('%[^0123456789]' + @Runner
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           OR PATINDEX(@Runner + '[^0123456789]%'
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           OR PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%'
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           THEN 1
         ELSE 0
      END) AS [RunnerFound];
GO

And then test with:

DECLARE @Runner VARCHAR(10);
SET @Runner = '6';

DECLARE @Data TABLE
(
  [ID] INT NOT NULL PRIMARY KEY,
  [Runners] VARCHAR(50) NULL
 );

INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6');
INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45');
INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92');
INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6');
INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63');
INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL);
INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6');

SELECT tmp.[ID],
       tmp.[Runners],
       fnd.[RunnerFound]
FROM   @Data tmp
CROSS APPLY dbo.IsRunnerPresentTVF(@Runner, tmp.[Runners]) fnd;

Which returns:

ID    Runners          RunnerFound
1     16+7+9+6         1
2     16+7+9+5         0
3     26+77+9+5        0
4     6+3+45           1
5     63,808,111,92    0
6     1-7-9,6          1
7     1-6-9,7          1
8     1-7-9,63         0
9     1-63-9,7         0
10    NULL             0
11    6                1


Insert a file into PostgreSQL BYTEA field using PHP

Your options are:

Use PDO, which properly supports binary parameters; or
Use pg_escape_bytea with string substitution into the query text.

See https://stackoverflow.com/q/17121791/398670 .


innodb foreign key.  It's just short hand naming convention.  You could call it asdfqwerty and the name would still work.


How can I verify I'm using SSL to connect to mysql?

From the client, just run status.  If this connection is using SSL, you'll get something interesting in the SSL row.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1

Connection id:      12
Current database:
Current user:       replicator@domU-12-31-39-10-54-BD.compute-1.internal
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.30-log MySQL Community Server (GPL)
Protocol version:   10
Connection:     boston.hugskeep.wstudent.com via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         44 min 49 sec

Threads: 2  Questions: 16  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 27  Queries per second avg: 0.005
--------------

mysql>

If this connection is not using SSL, you'll get:

SSL:            Not in use

You can also use:

mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0.00 sec)

mysql>

But I think the first is more attractive, and sure easier to type.


What are the criteria for a sql staying in the V$SQLAREA?

v$sql and v$sqlarea are views into the SQL area in the library cache (which is part of the shared pool, in the SGA).

What you see in those views are parsed/"compiled" statements that are in the cache. When a new statement comes in, Oracle checks to see if it already has it in that cache. If it does, it re-uses that (called a soft parse). (See SQL Sharing Criteria.)
If not, it has to do a full parse and find room in the cache to store it (hard parse). If there's no room left in the cache, older statements are aged out.

How long a statement stays in cache depends on the size of the cache and how your database is used. If you have "few" distinct queries and use bind variables everywhere, and a sufficiently large library cache, it should be mostly static after "warmup".
If you're not using binds at all and have many distinct queries, or simply have a very small library cache, it'll recycle very fast.

For more details, see Configuring and Using the Shared Pool and Large Pool in the Memory Configuration and Use section of the tuning guide, especially the Library Cache Concepts and following sections.


Depend on your database activity and database recovery mode you can manage TLOG size in different ways.

First of all to reduce TLOG usage you have to make transactions smaller.

If the database is in FULL recovery mode, do TLOG backups often.

As lots of guru's say - don't touch TLOG and TempDB they grew as they need to.


Schema design - Reference slightly different data in different tables

is this what business process of yours?

that must be

document table :

create table document (
  `id`        int unsigned auto_increment,
  `title`     varchar(128) not null,

  primary key(id)
);

block table

create table block (
  `id`             int unsigned auto_increment,
  `id_document`    int // foreign key to document
  primary key(id)
);

block_html (one-to-one relation to block table)

create table block_html (
  `id`             int unsigned auto_increment, // foreign key to block table and make it primary
  `type`      varchar(32) not null,
  `contents`  TEXT not null,

  primary key(id)
);

block image (one-to-one relation to block table)

create table block_image (
  `id`        int unsigned auto_increment,// foreign key to block table and make it primary
  `type`      varchar(32) not null,
  `url`       varchar(255) not null,
  primary key(id)
);


When setting up an AlwaysOn Availability Group, should the master database be one of the "Availability Databases"?

It is not possible to include master as an Availability Database within an Availability Group (AG). System databases (master, model, msdb, tempdb) cannot be part of the Availability Group.

System databases are instance-level objects. They are needed in order for the instance to be online & available. Availability Groups are a level below that, and provide database-level replication from one instance to another (or several others).

SQL Server will prevent you from adding system databases to an AG because those databases need to be read-write on every instance in order for the instance to be online.

Additionally, as stated in the answer on your other question, you should not be using the failover  partner connection string attribute. That attribute is designed for use with database mirroring--a different, deprecated availability feature. Instead, you should use an AG Listener, which will also provide proper direction to the primary node, and also allow you to use other AG-specific features, like read-only routing.

Getting back to your error....

Your code probably shouldn't rely on the master database, particularly in an AG, where your database could move to a different instance with a different master database. In the interest of the simplest & most bullet-proof AG architecture, I generally recommend that you go out of your way to make your database self-contained, or make the intentional, educated decision to put something outside of the database.

My first guess (and not knowing more about your code or your connection) is that your query shouldn't be referencing master at all. It could be just that your connection string is landing you in the master database instead of the database you want--so fix your code or connection string, not your AG.


Using SQL Server 2012 for Business Intelligence Features Only, No Database Engine

Perfectly reasonable proposal. No technical issues from your description of what you're aiming for.

I'm wary of commenting on licensing issues and would encourage you to verify you are correctly covered with either Microsoft or your reseller. In principle, the same licensing would apply for this arrangement as would apply if you were also deploying the database engine to that server.


Get max value when joining 3 tables in SQL

Assuming that  poitem.id is the same as po.id
SELECT
     pa.id,
     pi.id
FROM
    part pa INNER JOIN poitem pi ON pi.partid = pa.id
WHERE pi.id IN (SELECT po.id
                FROM po
                WHERE po.`date` = (SELECT MAX(`date`) FROM po))


Warnings: Why are statements with limit clause unsafe even if there isn't a limit?

You have LIMIT without ORDER BY. Therefore, it is meaningless.

Sets do not have implied orders without ORDER BY. The error message tells you this:

  This is unsafe because the set of rows included cannot be predicted

Does your DELETE have a trigger or such with a LIMIT?


Is there any way to add a witness without restarting the witness afterwards?

Here's a way to avoid restarting SQL server. It restarts the mirroring endpoint.

Run on the witness server:

--Run the following on a witness server after configuring a witness
USE [master];
DECLARE @Endpoint NVARCHAR(128);
DECLARE @Cmd NVARCHAR(MAX);
SELECT @Endpoint = (SELECT name
                    FROM sys.endpoints
                    WHERE type = 4);
SELECT @Cmd = 'ALTER ENDPOINT [' + @Endpoint + '] STATE = STOPPED;';
EXEC (@Cmd);
SELECT @Cmd = 'ALTER ENDPOINT [' + @Endpoint + '] STATE = STARTED;';
EXEC (@Cmd);

My servers are already in mirroring pairs with endpoints configured only for PARTNER roles.
My witness deployment procedure is as follows:

Add the witness role to each server's endpoint. Each server only supports one endpoint of type four.

--Run the following on a each witness server before configuring it as a witness to enable WITNESS and PARTNER roles
USE [master];
DECLARE @Endpoint NVARCHAR(128);
DECLARE @Cmd NVARCHAR(MAX);
SELECT @Endpoint = (SELECT name
                    FROM sys.endpoints
                    WHERE type = 4);
SELECT @Cmd = 'ALTER ENDPOINT [' + @Endpoint + '] FOR DATABASE_MIRRORING (ROLE = ALL);';
EXEC (@Cmd);

For Each Server:

Set the witness for each principal database:

USE [master]
ALTER DATABASE [TODO] SET WITNESS = N'TCP://WITNESS_FQDN:ENDPOINT_PORT';

For Each server configured to be a witness:

Manually fail-over all principal databases so that the server only contains mirrors
Restart the mirroring endpoint on the server to fix connection issues (refer to the script at the beginning of this answer)

After the mirrors have finished any log synchronization backlog, manually fail-over databases as needed so that the primary server is as it was initially.


You'll want PostGIS's ST_GeomFromGeoJSON().

SELECT DISTINCT ST_GeomFromGeoJSON(geojson)
FROM tbl;

Should work to get the points. However may find this is faster... I'd be interested in seeing a benchmark. ST_Union will eliminate duplicate points and geometries too.

SELECT ST_Union(ST_GeomFromGeoJSON(geojson))
FROM tbl;

Update

Is it faster, what advantages and disadvantages does this have?

DISTINCT requires a SORT. faster to sort floats than strings.
My ST_Mem_Size(ST_GeomFromGeoJSON(geojson)) shows 32 bytes (two floats).
Not sure how much space the raw JSON takes, likely more than 32 bytes.
Likely have to have these as ::geography at some time.
Processing the JSON and creating points is a one time cost but it may be significant.
ST_GeomFromJSON supports all geometry objects. Including MultiPoints.

Update2

If you already got the type as startstationgeom, just do

SELECT ST_AsGeoJSON(ST_Union(startstationgeom)) FROM table

That will output only unique geometries.


Automating SQL Server Merge Replication Processes

There are some sps (stored procs) on SQL Server, that can be used to setup merge replication.

Enabling merge replication on a db: sp_replicationdboption  'db name', 'merge publish', true

Creating a merge publication: exec sp_addmergepublication

Adding snapshot agent job: [exec sp_addpublication_snapshot ....

Adding each article: exec sp_addmergearticle

Configuring added/removed columns: exec sp_mergearticlecolumn (if needed only, by default, all columns are part of publication)

However, I am not sure of Transactional and other types of replication. Please let me know, if this is not what you intended.


How do I discover the Oracle Express workspace name?

There are two tables you can get this info from.

APEX_WORKSPACES and APEX_APPLICATIONS.

The schema that these tables reside in depends on the APEX version. By way of example, my vanilla Oracle 11.2 install has them in the APEX_030200 schema. To list: select USERNAME from DBA_USERS where USERNAME like 'APEX%'.


MS SQL - export tables to format compatible with PostgreSQL

Please refer to below thread. SSIS would help you in transferring data from SQL server to postgres
Micrsoft forum thread for similar issue

I have written article on how to transfer data from SQL server to postgres but this is applicable to SQL server 2008 and onwards you can read it though

How to transfer records from sql server to postgres


Set Order of top 10 Product manually in database

Just for fun you can design a data model for which updating a row to rank N requires you to scan N rows and to update the value for just 1. Displaying the top N ranks also requires you to scan N rows. One weakness is that filtering down to just the Nth rank also requires you to scan N rows. Perhaps this could be useful if someone had many rows in their table and didn't want to potentially update millions of rows to perform a single rank change.

The key is to store a separate internal value for the rank that's different than the displayed rank but still in the correct order. Here I will store the internal priority as NUMERIC(10,6). For each update we can find the previous RAW_PRIORITY value and subtract 0.000001 from it. That will preserve the order with just a single row update. With that precision end users change the ranks of items up to one million times before problems start happening. The precision and scale can be further increased depending on your requirements.

Define the table:

DROP TABLE IF EXISTS dbo.X_STORES;

CREATE TABLE dbo.X_STORES (STORE_NAME VARCHAR(100), RAW_PRIORITY NUMERIC(10,6));

INSERT INTO dbo.X_STORES VALUES
('Puma',1),
('Reebok',2),
('Dominos',3),
('Nike',4),
('NumeroUno',5)

CREATE INDEX X_STORES__PRIORITY ON dbo.X_STORES (RAW_PRIORITY) INCLUDE (STORE_NAME);

Depending on exact requirements it may make sense just to cluster the table on RAW_PRIORITY. We also want a view to make it easy to display ranks in a human readable format as well as to make updates easier:

CREATE OR ALTER VIEW dbo.X_STORES_PRIORITY_ORDER
AS
SELECT STORE_NAME, ROW_NUMBER() OVER (ORDER BY RAW_PRIORITY) [PRIORITY], RAW_PRIORITY
FROM dbo.X_STORES;

With our starting date set Nike is 4th:

SELECT STORE_NAME, [PRIORITY]
FROM dbo.X_STORES_PRIORITY_ORDER
ORDER BY [PRIORITY];

Results:

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Puma       ║        1 ║
║ Reebok     ║        2 ║
║ Dominos    ║        3 ║
║ Nike       ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝

Let's change Nike to be ranked second:

UPDATE dbo.X_STORES
SET RAW_PRIORITY = (
    SELECT v.RAW_PRIORITY - 0.000001
    FROM dbo.X_STORES_PRIORITY_ORDER v
    ORDER BY v.RAW_PRIORITY OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
)
WHERE STORE_NAME = 'Nike';

Results:

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Puma       ║        1 ║
║ Nike       ║        2 ║
║ Reebok     ║        3 ║
║ Dominos    ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝

Now let's change Dominos to be first:

UPDATE dbo.X_STORES
SET RAW_PRIORITY = (
    SELECT v.RAW_PRIORITY - 0.000001
    FROM dbo.X_STORES_PRIORITY_ORDER v
    ORDER BY v.RAW_PRIORITY OFFSET 0 ROW FETCH NEXT 1 ROW ONLY
)
WHERE STORE_NAME = 'Dominos';

Results:

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Dominos    ║        1 ║
║ Puma       ║        2 ║
║ Nike       ║        3 ║
║ Reebok     ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝


SQL Server seems to be ignoring specified backup file extension

When you go through the wizard you should

Click the differential option and
Specify diff in the Backup file extension on the Destination Tab
(it's bak by default, even for differential)

Most people aren't fans of maintenance plans since you are kind of blindly turning over
some control and they create SSIS packages which aren't fun to troubleshoot. Ola's scripts are super easy, well documented, and have a ton of options. You can always roll your own via Agent Jobs too.


Query multiple tables with foreign key filled or is null

This is what outer joins do. Use a LEFT (outer) JOIN:

select t1.id, t1.field1, t2.field2
from       table1 as t1
       left join
           table2 as t2
       on
           t1.id_table2 = t2.id ;

If you want the empty string (and not NULL) in the result when there is no match, replace t2.field2 in the select list with COALESCE(t2.field2, '').


Restore SQL Server database from .sql files

So you just need to execute the collection of schema scripts to recreate your database? You could execute them one by one or create a batch file and use SQLCMD to execute all script files in directory

for /r "%ScriptPath%" %%X in (*.sql) do (
   sqlcmd -S%ServerName% -d%DBName% -b -i "%%X"
)


"connectivity libraries needed are not found" error in IBM Data Studio

You may need to point IBM Data Studio to the database drivers. They are usually installed with DB2 under /myDB2_InstallationDirectory/SQLLIB/java.

The drivers themselves tend to be

db2jcc.jar
db2jcc_license_cu.jar

There are other drivers in that folder as well, but these two tend to be the main ones for Java (which IBM Data Studio is based on).


What is a "hashed transaction" in SQL Server Replication terminology?

In reality, this DMV (Dynamic Management View) won't be very helpful for a DBA or Developer and is geared to SQL server Product Support and Product Group. You can ignore this DMV.

Hash tables work well when there are a small number of items that fit into a bucket and this is an example of a DMV that support might use to check certain internal structures without the need of obtaining a dump or using a debugger.

There are various other DMVs that fall into this category and you'll find they either aren't documented at all, or there is only extremely light and vague documentation. This falls into that category.


sp_updatestats doesn't seem to work for non-sysadmin logins

You misunderstood the phrase

  Requires membership in the sysadmin fixed server role, or ownership of
  the database (dbo)

"ownership of the database" means that this login owns this database.

This is not the same as "is a member of db_owner database role"

Owner of a database is one single login, while there may be as many db_owners as you want.

And in fact if you execute

exec sp_helptext 'sp_updatestats';

in the first rows you'll find:

declare @dbsid varbinary(85)

select @dbsid = owner_sid
    from sys.databases
    where name = db_name()

-- Check the user sysadmin
if not is_srvrolemember('sysadmin') = 1 and suser_sid() <> @dbsid
begin
    raiserror(15247,-1,-1)
    return (1)
end

This controls if the executor is a member of sysadmin server role or he owns this database: suser_sid() <> @dbsid where @dbsid is owner_sid of current database.

To find out the owner of some database you should execute

select owner_sid
from sys.databases
where name = 'some_db';

And you also can find it using SSMS:


False-failures when mirrored in high-safety mode without a witness

OK, so this is high safety without automatic failover with no witness.

Here's the official books online answer:

  If the mirror server instance goes down, the principal server
  instance is unaffected and runs exposed (that is without mirroring the
  data)." Source

I have seen that to be true in my experience. In other words, disconnected, but available for reads and writes. The transaction log will grow until the situation is fixed, because it needs to keep the info to send to the mirror when it comes back around.

Note: I absolutely recommend setting up a test environment for yourself before you make any changes. Don't just take my word for it. You want a place to be able to stage the changes and need to test what happens for yourself before going live.

Edit: As for what happens when the mirror comes back, typically it will automatically resume in my experience, but I know of cases where it hasn't and TSQL had to be run to make it resume. Very easy to simulate in a demo environment to get yourself comfortable with it.


Computer restarted and Postgres 9.2 service cannot be started?

Start by commenting out the offending line in your postgresql.conf and retry.  It is possible that a change made some time ago caused this problem and you are only now seeing it because a reload operation or similar failed, and so PostgreSQL was running on old configuration.  If that works address what you have on that line (maybe get rid of the quotes?)
If that doesn't work, take a look elsewhere in the file.  Maybe you have an unterminated quote somewhere?

It really looks to me like you have a bad postgresql.conf file and that's why it isn't starting.


If function behave differently in MySQL 5.5.49

It's because in the comparison v_no_por_cabang = 0 you're converting v_no_por_cabang implicitly to a number. When you use single-quotes like in v_no_por_cabang = '0' you're correctly comparing two strings.

MySQL also tells you so, when you execute show warnings; afterwards. Result:

+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'POR1' |
+---------+------+------------------------------------------+

Fun fact: When your string starts with a number, MySQL converts the first number part of the string to a number.

root@localhost:playground > CALL sp_testing('1POR1',@out1);

+------------------+
| v_no_por_cabang1 |
+------------------+
| 1POR1            |
+------------------+
1 row in set (0.01 sec)

+------------------+
| v_no_por_cabang2 |
+------------------+
| 1POR1            |
+------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

root@localhost:playground > CALL sp_testing('0POR1',@out1);
+------------------+
| v_no_por_cabang1 |
+------------------+
| NULL             |
+------------------+
1 row in set (0.00 sec)

+------------------+
| v_no_por_cabang2 |
+------------------+
| 0POR1            |
+------------------+
1 row in set (0.00 sec)


T-SQL Check another table rows if match delete them

I can't say anything about SSIS but in SQL you can check whether a whole row is identical to another (including checking for NULL values which can get rather complicated otherwise), using this technique, explained by @PaulWhite in his blog post: Undocumented Query Plans: Equality Comparisons .

For example in you case. "For any identical rows of table B and table A, delete those B rows":

DELETE b                        -- from table B: Customer_information
FROM Customer_archive AS a
  JOIN Customer_information AS b
  ON a.pk = b.pk
WHERE EXISTS (SELECT a.* INTERSECT SELECT b.*) ;

I have serious concerns though about efficiency when the tables are big - and an archive table by definition is going to be quite big. The a.pk = b.pk is not needed really, as the pk columns are obviously included in the row check of the EXISTS but I kept them for efficiency. Assuming that the two tables have the same primary key and that after a period, most of the archived rows have been already deleted from table B, the PK indexes will have very few matching values so the join will be relatively fast - and using the row checks only for matching pk values.


view corrupted when viewed using design feature

Yes, the view designer is a horribly crippled, buggy piece of garbage you shouldn't be using to "design" your views. It should be called the view destroyer, because it can do exactly what you've observed: re-write any non-standard T-SQL into what it thinks you meant. And by "any non-standard T-SQL" I mean anything not predicted by the two developers who likely spent about a day testing this "feature."

Always, always, always modify your views using Script View As > Alter to > New Query Editor Window.

See these related Connect items:

http://connect.microsoft.com/SQLServer/feedback/details/465498/ssms-designer-is-sending-incorrect-alter-statement-to-engine-when-we-have-geomerty-data-type

Where I mention in the workarounds:

  Obviously a better approach is to right-click the view, script as alter to new window. I wonder when they'll admit defeat and ditch the visual designers altogether. They cause more problems than they solve, IMHO.

http://connect.microsoft.com/SQLServer/feedback/details/362679/ssms-right-click-on-a-view-to-edit

Where I do something similar, and

http://connect.microsoft.com/SQLServer/feedback/details/459202/no-edit-option-when-right-clicking-on-a-view-in-management-studio-2008

Where I ask:

  Didn't Edit bring up the horrible graphic designer for views, which was riddled with bugs and lacked support for several basic T-SQL constructs? Isn't that what Design does now?


First, we need to get a list of all buckets. if you already have a table buckets with all bucket_id (as PK), this part is not needed:

with
  buckets (bucket_id) as
  ( select distinct bucket_id
    from table_x
  ) ,

Then we get all bucket couples and the count of their common items - through a self-join:

  couples (bucket_a, bucket_b, cnt) as
    ( select a.bucket_id, b.bucket_id, count(*)
      from table_x a
        join table_x b
          on  a.bucket_id <= b.bucket_id
          and a.item_id = b.item_d
      group by a.bucket_id, b.bucket_id
    )

Then we can show our results:

select a.bucket_id      as bucket_a,
       b.bucket_id      as bucket_b,
       coalesce(cnt, 0) as common_items
from buckets a
  join buckets b
    on a.bucket_id <= b.bucket_id
  left join couples c
    on  a.bucket_id = c.bucket_a
    and b.bucket_id = c.bucket_b ;

The above will show, for your sample table:

bucket_a  bucket_b  common_items
================================
A         A         2
A         B         1
A         C         2
A         D         0
B         B         2
B         C         1
B         D         1
C         C         3
C         D         0
D         D         1

If you want the pivoted output, you'll need one more processing.

If the number of buckets is very low as you say, you may try this one as well:

with
  buckets (bucket_id) as
  ( select distinct bucket_id
    from table_x
  ) ,
select a.bucket_id      as bucket_a,
       b.bucket_id      as bucket_b,
       ( select count(*)
         from table_x aa
           join table_x bb
             on  aa.item_id = bb.item_d
         where a.bucket_id = aa.bucket_id
           and b.bucket_id = bb.bucket_id
       ) as common_items
from buckets a
  join buckets b
    on a.bucket_id <= b.bucket_id ;


Mysql Restore - ERROR 1227 (42000) at line 30908: Access denied DEFINER issue

sed -r -e 's/DEFINER\s*=\s*\S+//'

Notes:

-r : better syntax

\s : any whitespace (eg, tabs, too)

\S : non-space

(I tested on Ubuntu, not CentOS.)


After ID is updated, get a value from another table

Doing this in the database is easy with a trigger. However, it's all well and good doing this in the database, but we have no idea how the application itself works or will deal with this (if at all). For example, the application may display what the user has entered rather than the value that the trigger has automatically inserted. I think you're going to have to deal with this in the front-end, which is something we probably can't help you with.

Anyway, here's how to write a trigger to do this.

Test tables and data:

create table roads
(
road_id number primary key,
measure number
);

create table projects
(
project_id number,
road_id number,
from_measure number,
to_measure number
);

insert into roads values(1,1000);
insert into roads values(2,100);
insert into roads values(3,500);

Trigger:

create or replace trigger projects_btrig
before insert on projects
for each row
declare
    v_measure number;
begin
    select measure
    into v_measure
    from roads
    where road_id = :new.road_id;

    :new.to_measure := v_measure;
end;
/

Test:

SQL> insert into projects (project_id, road_id, from_measure) values (1,1,0);

1 row created.

SQL> select * from projects;

PROJECT_ID    ROAD_ID FROM_MEASURE TO_MEASURE
---------- ---------- ------------ ----------
     1         1            0          1000

SQL>


SQL Server Audit logs to either a binary file or Windows Application log or Windows Security log.  There are different options for reading the output depending on where you have logged to.  If you are logging to a binary file you could create a simple process that reads it with sys.fn_get_audit_file and outputs to a readable textfile, eg using SSIS, use an 'Execute SQL' task to run the following SQL and pipe the output to a text file:

SELECT *
FROM sys.fn_get_audit_file ( 'X:\SQLAudit\yourAuditName_*.sqlaudit', DEFAULT, DEFAULT );

You will need some way of tracking the files you have imported/exported so a simple table would help there.

A quick google shows someone else has done something similar with Powershell here.


SQL Server-Table has many AFTER INSERT triggers that insert into the table itself

Your issue is related to the nested triggers notion in SQL Server. This concept is explained as like below in MSDN

  Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels.

Particularly your issue you can limit the nested_triggers config as 0 so that the triggers will be only performed one time.

At first, we will set the nested_triggers value as 0. However, this option is on the server level to take account of this point.

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

And then we will re-execute your query and the resultset of the a will be liked as below.

+----+
| id |
+----+
|  0 |
|  2 |
|  1 |
+----+


How to confirm that object owners can grant and revoke privileges on objects he owns?

It isn't just "the default behavior", it's the only behavior. Owners always have full control on their own objects (including grants and revokes on those objects). That's why separation of container schemas from accounts that login to the database is so key to database security.

  Disclaimer
  The original answer was posted as a comment by @KrisJohnston

Addition

There is a slight twist to the answer in that the permissions can be changed so that the owner can longer modify his own objects, BUT the owner is then no longer the owner. This is a loss of ownership for the original owner.

This can be achieved using the following technique:

How to change an Oracle table owner

  create table bill.emp as select * from scott.emp;


DBCC CloneDatabase - sys.partitions rows value wrong

If you intend to use the cloned database for production use, specify the VERIFY_CLONEDB option which also clears the statistics in the cloned database.

VERIFY_CLONEDB
Verifies the consistency of the new database. This option is required
if the cloned database is intended for production use. Enabling
VERIFY_CLONEDB also disables statistics and query store collection,
thus it is equivalent to running WITH VERIFY_CLONEDB, NO_STATISTICS,
NO_QUERYSTORE. This option is available starting with SQL Server 2014
(12.x) SP3, SQL Server 2016 (13.x) SP2, and SQL Server 2017 (14.x)
CU8.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-clonedatabase-transact-sql?view=sql-server-ver15
This is an artifact of the history of this command.  Initially it was just for troubleshooting, but was later enhanced to enable you to use the cloned database for production.


This ended up being related to parameter sniffing. It just so happened that some oddly formed versions of this query were being executed RIGHT AFTER the stats were rebuilt. So the cached plan was not representative of the majority of the calls. I used the trick of copying the date parameters to local variables and this is working just fine, with little to no impact on performance. This doesn't answer why the histogram looks so "off", but it does explain my performance issues.


How can I count some rows before inserting?

Counting and subqueries are somewhat costly; let's try to avoid them.

If the latest question is less than 90 minutes ago, disallow.  This involves MAX() and an index on the timestamp column.
If 6 or more today (or today+yesterday), disallow.  This involves a summary table, see below.
If 50 or more ...  This involves the same summary table, plus a SUM() of only 30 rows, not 50, in worst case.  (OK that does not help much.)

Summary table something like...

CREATE TABLE Summary (
    author_id ...
    day DATE NOT NULL,
    ct TINYINT UNSIGNED,
    PRIMARY KEY(user_id, day)
) ENGINE=InnoDB;

INSERT INTO Summary (author_id, day, ct)
    VALUES (:id, CURRENT_DATE(), 1)
    ON DUPLICATE KEY UPDATE ct = ct+1;

-- How many "since midnight":
( SELECT ct FROM Summary WHERE author_id = :id
       AND day = CURRENT_DATE() ) >= 6  -- disallow
-- How many "since midnight yesterday" (overkill; see below):
( SELECT SUM(ct) FROM Summary WHERE author_id = :id
       AND day >= CURRENT_DATE() - INTERVAL 1 DAY ) >= 6  -- disallow
( SELECT SUM(ct) FROM Summary WHERE author_id = :id
       AND day >= CURRENT_DATE() - INTERVAL 50 DAY ) >= 50  -- disallow

Sinece the second only checks for "6 in the last 2 days", it needs this to further check:

-- Exact count since this time yesterday:
( SELECT COUNT(*) FROM Qanda WHERE author_id = :id
       AND timestamp > NOW() - INTERVAL 1 DAY ) < 6

The query against the summary table should be faster, hence is probably worth doing as a quicker check.

I would define the limit as "6 since midnight" in order to simplify and speed up the checks.

Qanda needs INDEX(author_id, timestamp)


Sort of. You can see though sys.dm_exec_requests executing a RECEIVE statement (including a WAITFOR RECEIVE). By simply peeking into the the currently executing text sys.dm_exec_sql_text(sql_handle) between the statement_start_offset and statement_end_offset you can see if the statement is RECEIVE or not (with some parsing...). Figuring out if an active transaction has issued a RECEIVE and is now processing returned messages is more complicated.

Is somehow of an unusual request, why do you need to know?


Why search by date field requires engine to touch all records?

As @Razor commented, the number reported by EXPLAIN is only an estimate, and it can be off from your true row count by +/- 10% or even more.

MySQL prefers not to use an index at all if the portion of the table selected by the query conditions is too large. Think of the index at the back of a book. Why don't they index the word "the"? Because it would just list every page in the book. If the word occurs on too many pages in the book, it's actually easier to just read the book cover-to-cover instead of using an index.

Similarly, in MySQL if the optimizer estimates that the values you are searching for are found on a significantly high number of rows (in my experience "significant" is at least 16-18%) then MySQL skips the use of the index and just does a table-scan instead.

Sometimes this is the wrong choice, and you can give MySQL a hint that a table-scan is actually much more expensive than the preferred index choice:

EXPLAIN select * FROM playdays FORCE INDEX(realdate) where realdate>=Date('2010,01,01') ;

Be careful about using index hints too liberally, because you effectively hard-code your index choices into your queries by using them, and that may become the wrong strategy as your data changes over time. They should be used in exception cases, when you can demonstrate that the optimizer make the wrong choice.


How to trim first digit off an integer value?

Two options.

First you know what the first column is going to be.  In which case it's a simple

SET Column2 = Column1 - 1000000

or

SET Column2 = Column1 % 1000000

The second has the benefit that the digit could be 1-9 with no problems.

If you have no idea what that first place is going to be and you still only want to get rid of that one value you could do something like this.

declare @test int = 1005
select CAST(SUBSTRING(CAST(@test as varchar(50)),2,999) AS int)

If you have to allow for negative numbers it gets a bit more complicated.

declare @test int = -1005
select CASE WHEN @test < 0 THEN -1 ELSE 1 END *
        CAST(SUBSTRING(CAST(ABS(@test) as varchar(50)),2,999) AS int)

Obviously this could get rather slow for big sets of data.


How to point SQL Server Python to a specific Python install?

You cannot point SQL Server to a different Python installation; per the docs you linked:

  SQL Server does not modify the Python executables, but you must use the version of Python installed by Setup because that version is the one that the proprietary packages are built and tested on. For a list of packages supported by the Anaconda distribution, see the Continuum analytics site: Anaconda package list.

The Python 3.5 runtime and interpreter are available from this site.


What about using the tables already in the MSDB database, Restorehistory, BackupSet and BackupMediaFamily?

These databases will already have all of the backup and restore history. I would check though that in your particular case what the behaviour looks like in the Restorehistory table. You should be able to check in these tables that after your restore process whether it was successful or not and provoke the appropriate action you want.

http://msdn.microsoft.com/en-us/library/ms188062.aspx


Is it realistic to expect a database of size over 50 GB (some 300+ million records) to be imported within 3-4 hours on a single server (MySQL)?

I read someone had accomplished 300,000 rows/second using 'load data infile' on a local server with ssd and 32gb ram

That sounds like taken from my blog posts (or at least those were my numbers and specs): https://jynus.com/dbahire/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results/ and https://jynus.com/dbahire/testing-again-load-data-on-mysql-5-6-5-7-8-0-non-ga-and-mariadb-10-0-10-1-and-10-2-non-ga/

As you can see my experience is based on actual tests; but not only laboratory tests like the above, I do those because they help me be ready to make sure my database backups (and recoveries) are generated correctly, as well as they can be performed reliably and fastly, handling daily both logical dumps and snapshots for the half a petabyte of data we store on our MariaDB databases: https://www.slideshare.net/jynus/backing-up-wikipedia-databases

A 50 GB database on a 32GB memory server is a very generous ratio, where 60% of the data could fit in the buffer pool. In that case, thoughput can be optimized greatly, as long as you setup your vm, os and mysql configuration for it (disabling the binary log, increasing the buffer pool and transaction log files, loosening consistency parameters during the import, etc). You will also want the original format to be optimized for easy loading, so you dont waste cpu cycles on parsing or converting the format or other changes, as well as doing it in large transactions as well as on several threads in parallel if possible.

As an example, my production has 1-2TB databases with billions of rows, which I can recover logically in 6-12 hours on a 512GB memory machine, including the many indexes.

Under the above right circumstances, with a mostly in memory database, I would be able to load remotelly in parallel a 50 GB database in around 30 minutes. Less than 1h if the storage is slow. Be careful because the tests asume dedicated resources; a cpu, memory or io limitation can create a bottleneck, leading to higher load times.


How can I check if BULK INSERT is running on a table?

There is no way to directly monitor the queries against a single table in a database. Instead, what you have is the ability to see queries running against that database. Then, you can filter the query text to determine which table or tables that query is running against. So, in order to do exactly what you want, you need to pick one of the methods for monitoring queries. Extended Events are historical more than they're current, although, you can look for statement starts without statement ends to find queries that are active. Query Store is historic only. Querying the DMVs is usually the best way to see what's immediately happening.
So, you want to query sys.dm_exec_requests to see what's currently running. Then you combine that with sys.dm_exec_query_text to see the specific query being run. Finally, can filter for both the BULK INSERT statement and your table name against the query text. If you don't want to write your own queries to combine these DMVs, as was pointed out in the comments, sp_whoisactive, is an open-source query that you can install on your servers. Then, using it, you do the same filtering against the query text.
Regardless of how you do it, you just have to filter on the query text. There's no other way to see what's running against a single table.


SQL Server 2012 - Large backup file size

Did you use INIT as a parameter to the backup command?

Otherwise, the dump is appended to the backup file.


delimiter |
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
END;
|
delimiter ;

If you don't set another delimiter than ; the statement will end at the first ; and your procedure definition will be incomplete. You  need to tell MySQL that the statement should end at the delimiter you defined. After that you can set the delimiter back with delimiter ;


Printing \G on shell using mysql client..?

This has always been a real pain-in-the-neck for me.

The mysql client seems to interpret the \\G as  \G
The OS seems to take that output and interpret \G as G

Unfortunately, this is the sequence that always happens.

I use the sed command to get around this as follows:

mysql -uroot -proot -Bse "select Concat('SHOW CREATE TABLE ',TABLE_SCHEMA ,'.',TABLE_NAME,' G') from information_schema.tables where TABLE_SCHEMA Not IN('mysql','information_schema','performance_schema');" | sed 's/ G$/\\G/g'

Notice that I replaced \\G with G (blank followed by G)

I have the sed command translate G$ (which means the blank capital G at the end of of line) and convert it to \G. Notice that sed is doing this conversion with OS output and mysql output.

You no longer have to deal with that \G issue. Simply plan for a blank capital G to be transalted by sed to \G.


What are the prime and non-prime attributes in this relation?

Your schema has three (candidate) keys:

HI
IJ
IK

While it is immediate to discover tha HI is a candidate key since it determines all the other attributes, you can see that this is true also for IJ and IK by calculating the closure of those attributes:

IJ+ = IJ
IJ+ = IJKL     (by adding the right part of J → KL)
IJ+ = IJKLH    (by adding the rigth part of K → H)
IJ+ = IJKLHMNO (by adding the right part of L → MNO)

analogously for IK:

IK+ = IK
IK+ = IKH      (by adding the rigth part of K → H)
IK+ = IKHJLMNO (by adding the right part of HI → JKLMNO)

For this reason, the prime attributes of the relation are:

HIJK

while the non prime attributes are:

LMNO

Note that there are no other candidate keys since M, N and O appear only on the right side of functional dependencies, so that they cannot “contribute” to any key. L appears both on a left part and on a right part of functional dependencies, but it is determined by HI, IJ and IK, and does not determine any of these attributes, so it cannot be part of a key. Finally, you cannot remove any attribute from HI, IJ and IK without losing the key property.


Clustered Index Scan when using greater than in WHERE clause

The values of variables aren't generally sniffed so it will just assume a flat 30% of the table will be returned for that greater than predicate against an unknown value (cf. Selectivity Guesses in absence of Statistics).

When you use the literal it can look up the known value in the column statistics to get a much more accurate estimate.

If it estimates such a high percentage of the table will match it is exceedingly unlikely to give you a plan with lookups (the exact tipping point depends on how wide the table and index is).

If you use option (recompile) the variable value is able to be sniffed however...

DECLARE @number bigint
SET @number = 123456
SELECT col1, col2
FROM t1
WHERE col3 > @number
OPTION (RECOMPILE);

Will likely give you the same plan as when using the literal.


Report Builder does not connect for a remote reporting Server

After a lot of digging around, I didn’t get anywhere, then eventually I got Microsoft involved to help me with the problem.

MS solution architect confirmed that the Report Builder requires a direct connection to SQL Server when launched hence the working with data sets in the report builder failed. Apparently this is BY DESIGN and it is not going to change.

This was in SQL Server 2008 R2 but the same is true for SQL Server 2012, 2014 and 2016. Not sure about 2017 but I doubt it very much if that has changed in 2017.

We ended up providing the client a machine on the network in DMZ to which end users connected remotely, only to work with the Report builder, it is a poor solution but it worked at that time and we needed a quick fix for it at that time.


Select records not associated to a specific tag in a many-to-many relation

SELECT DISTINCT events.id, events.title
  FROM events
  JOIN events_tags et1
         on events.id = et1.event_id
        and not exists ( select 1
                           from events_tags et2
                          where et2.event_id = et1.event_id
                            and et2.tag_id IN (1) );

And why a iden PK in events_tags?  Drop that.
Just have a composite PK of tag_ID, event_id in that order.

This might be faster but I doubt it

SELECT DISTINCT events.id, events.title
  FROM events
  JOIN events_tags et1
         on et1.event_id = events.id
        and et1.tag_id not IN (1)
        and not exists ( select 1
                           from events_tags et2
                          where et2.event_id = et1.event_id
                            and et2.tag_id IN (1)
                       );

I don't think MYSQL supports except

SELECT events.id, events.title
  FROM events
  JOIN (   select event_id
           from events_tags
           where tag_id not IN (1)
         except
           select event_id
           from events_tags
           where tag_id     IN (1)
       ) tt
    on tt.event_id = events.id


Implementation Idea for login structure (login id with two different tables)

First of all, every thing depends on business logic you have. If employees and customers it's fine to use 2 diferrent tables, to connect them with the table WEB_USER_LOGIN I would just append and field like 'usertype'.

If your database is Oracle you can have object tables to solve this problem, if its PostgreSQL you can hava inherited tables.

If its mysql I would use just one table with both fields, but if you prefer to use the left join it's ok but I would create a VIEW for it.


Oracle Triggers - Before Update and Before Insert - Separate or Combined?

The actions you're performing on the data is technically different between the insert/update portions, but what about the business logic difference? IMO, your best bet is to contain a functional unit of work inside of a trigger, so that there is no discrepancy in what that trigger does. In my experience, Developers/Business Analysts often tend to think in transaction workflows.

I have found this to be especially helpful when I or others have to troubleshoot the consequences of user data corruption as it relates to triggers being fired - the less triggers you have to try to piece together the logic for, the easier it is to see how data was manipulated.


I don't think your sample output is correct for product_id 10211.  You seem to be missing parameter_id 416.

Here is an example of using dynamic SQL (in SQL Server) to solve the problem.  You could probably adapt it to other RDBMS's.  I took the liberty of generating unique column names for parameter_id because using the same column name might be problematic for the PIVOT.

--Demo setup
set nocount on
DECLARE @cols AS NVARCHAR(MAX),@colsCoalesce AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
drop table if exists Table1
create table Table1 (product_family_id int, product_id int, parameter_id int)

insert into table1(product_family_id,product_id,parameter_id) values
(111,3227,334),
(111,3227,335),
(1112,10211,414),
(1112,10211,415),
(1112,10211,416),
(1112,10211,417),
(1112,11784,514),
(1112,11784,515),
(1112,11784,516),
(1112,11784,519)

--The solution
drop table if exists PivotTemp
select * into PivotTemp from
(
select product_family_id, product_id, parameter_id, 'parameter_id' + convert(varchar(5),ROW_NUMBER() over(partition by product_family_id, product_id order by parameter_id)) as GeneratedParameterHeading from table1
)a
--select * from PivotTemp
SElect @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.GeneratedParameterHeading)
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
SElect @colsCoalesce = STUFF((SELECT distinct ',' + 'coalesce(convert(varchar(100),' + QUOTENAME(c.GeneratedParameterHeading) + '),'' '') as ' + QUOTENAME(c.GeneratedParameterHeading)
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

print @cols
print @colsCoalesce
set @query = 'SELECT product_family_id, product_id, ' + @colsCoalesce + ' from
            (
                select product_family_id
                    , product_id
                    , parameter_id
                    , GeneratedParameterHeading
                from PivotTemp
           ) x
            pivot
            (
                 max(parameter_id)
                for GeneratedParameterHeading in (' + @cols + ')
            ) p '

print @query
execute(@query)

| product_family_id | product_id | parameter_id1 | parameter_id2 | parameter_id3 | parameter_id4 |
|-------------------|------------|---------------|---------------|---------------|---------------|
| 111               | 3227       | 334           | 335           |               |               |
| 1112              | 10211      | 414           | 415           | 416           | 417           |
| 1112              | 11784      | 514           | 515           | 516           | 519           |


Can we use FILESTREAM in case of SQL Server cluster

Yes, you can.  See: Set Up FILESTREAM on a Failover Cluster, https://msdn.microsoft.com/en-us/library/cc645886(v=sql.110).aspx


Do SQL Server Views update their data types automatically based on what tables feed them?

A simple example:

CREATE TABLE dbo.x(a INT, b NCHAR(4));
GO
CREATE VIEW dbo.vx AS
  SELECT a, b FROM dbo.x;
GO

ALTER TABLE dbo.x ALTER COLUMN a TINYINT;
ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4);
GO

SELECT a,b INTO #blat FROM dbo.vx;
GO

EXEC tempdb.dbo.sp_columns N'#blat';
GO

DROP VIEW dbo.vx;
DROP TABLE dbo.x, #blat;

Partial output:

COLUMN_NAME    TYPE_NAME    PRECISION
-----------    ---------    ---------
a              tinyint      3
b              nvarchar     4

So, in practical terms, yes, any interaction with the view should yield the new data types.

That said, I would always call sp_refreshview for any views that reference a table that has changed (and in fact I often use WITH SCHEMABINDING so that I can't alter a table without knowing about the views and other objects it affects - this can make cowboy development/deployment painful, but I guess that's kind of the point).

You can build the script to refresh all referenced views dynamically like this (this is for a single table; you'll need to incorporate this into your existing script to make it dynamic for all affected tables):

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'EXEC sp_refreshview '''
  + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ''';'
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.views AS v
ON d.referencing_id = v.[object_id]
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
WHERE d.referenced_id = OBJECT_ID('dbo.whatever')
GROUP BY s.name, v.name;

EXEC sp_executesql @sql;

Another reason you should always refresh all views after changing the base table(s), especially if your views use SELECT *:

CREATE TABLE dbo.x(a INT, b NCHAR(4));
GO
CREATE VIEW dbo.vx AS
  SELECT * FROM dbo.x;
GO

SELECT * INTO #b1 FROM dbo.vx;
GO

ALTER TABLE dbo.x ALTER COLUMN a TINYINT;
ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4);
GO

SELECT * INTO #b2 FROM dbo.vx;
GO

ALTER TABLE dbo.x ADD d INT;
GO

SELECT * INTO #b3 FROM dbo.vx;
GO

EXEC sp_rename N'dbo.x.b', N'c', N'COLUMN';
EXEC sp_rename N'dbo.x.d', N'b', N'COLUMN';
GO

SELECT * INTO #b4 FROM dbo.vx;
GO

EXEC tempdb.dbo.sp_columns N'#b1';
EXEC tempdb.dbo.sp_columns N'#b2';
EXEC tempdb.dbo.sp_columns N'#b3';
EXEC tempdb.dbo.sp_columns N'#b4';
GO

DROP TABLE #b1, #b2, #b3, #b4;
GO

Partial results:

-- initial:

#b1____    a    int        10
#b1____    b    nchar      4

-- correct:

#b2____    a    tinyint    3
#b2____    b    nvarchar   4

-- missing new column d:

#b3____    a    tinyint    3
#b3____    b    nvarchar   4

-- missing column c, b still points at "old" b:

#b4____    a    tinyint    3
#b4____    b    nvarchar   4

But then if we refresh the view:

EXEC sp_refreshview N'dbo.vx';
GO

SELECT * INTO #b5 FROM dbo.vx;

EXEC tempdb.dbo.sp_columns N'#b5';

DROP VIEW dbo.vx;
DROP TABLE dbo.x, #b5;

Results:

#b5____    a     tinyint     3
#b5____    c     nvarchar    4
#b5____    b     int         10

Note that the data types are correct now, but the columns are not in the order you would expect.


Right now you have only added one index on words for word. Therefore your query will right now result in at least one full table scan for both words and synonyms, probably even more - that might depend on your DBMS and it's query optimizer's abilities.

Try adding an index on words for word_id and on synonyms for at least source_index. This way your query will use the index and not do a full table scan.

You could probably improve on that by using covering indeces, e.g. (word_id, word) on words and (source_index, destination_index) on synonyms.


Find closed sub directory with full text search

If you take the path of interest and parse it into it's sub part, and use those to  build a search string, you should then be able to use that search string to find matches of all lengths and then pick the best fit.  There is an assumption that the folder structure is embedded in other data within the field (otherwise, why use Full Text Indexing), so the manner of finding the best match will depending on your specific context.

Here is some code the demonstrates the suggested path, include a sample way of selecting the best fit.

DECLARE @PathOfInterest NVARCHAR(MAX) = 'c:\Dir1\Sub2\File.txt';

DECLARE @SearchString NVARCHAR(MAX);

DROP TABLE IF EXISTS #Search;

WITH Builder AS (
    SELECT
        0 AS Score -- How close to the initla path it is
        , @PathOfInterest AS InitalPath -- Path
        , CAST(NULL AS NVARCHAR(MAX) ) AS SubPath -- SubPath
        , CAST(0 AS BIGINT)  AS DelimPos -- End of last Search
        , CAST(NULL AS NVARCHAR(MAX) ) AS SearchValue -- Cumulative Search Value
    UNION ALL
    SELECT
        Score + 1
        , InitalPath -- Pass through path
        , CAST(SUBSTRING(InitalPath, 1, CHARINDEX('\\', InitalPath, DelimPos + 1) - 1)AS NVARCHAR(MAX) ) -- Extract the string to the folder delimi
        , CHARINDEX('\\', InitalPath, DelimPos + 1) -- Find the next delim
        , CAST(
            CONCAT(
                SearchValue + ' OR ' -- When V is null, no leading OR
                , '("'
                , SUBSTRING(InitalPath, 1, CHARINDEX('\\', InitalPath, DelimPos + 1))
                , '")'
            ) AS NVARCHAR(MAX) -- Make sure the type matches the anchor of the recusive CTE
        )
    FROM Builder
    WHERE
        CHARINDEX('\\',InitalPath, DelimPos + 1) > 0
)
SELECT
    Score, SubPath, SearchValue
--INTO #Search
FROM Builder

SELECT @SearchString = SearchValue FROM #Search

SELECT * FROM #Search s

DROP TABLE IF EXISTS #Search;
SELECT
    FolderInfo
INTO
    #FirstPass
FROM FullTextIndexTables
WHERE CONTAINS(FolderInfo, @SearchString)

---- Select best fit from matching results
SELECT TOP 1 *
FROM
    #Search
CROSS APPLY (
    SELECT * FROM #FirstPass
    WHERE FolderInfo LIKE CONCAT('%', SubPath, '[^\]%' )
)
ORDER BY Score DESC

Notes:
 1. To preserve formatting in SO, I changed '\' to '\'.
 2. I did not have an environment to test the Full Index Search, so some
    tweaking may be needed.
 3. The selection criteria in the 'best fit' may produce inconsistent
    results if there are multiple matches with the same score (wobble).
    If this is of concern, additional ORDER BY criteria should be
    specified.


AWS RDS Post Optimisation after a huge delete

You may try below options.

Using Partition with DateRange

Create table with date range each partition containing weekly data.
Things to consider are selects and updates which means you should use partition name to specify any where conditions other than PK. Which means that there must be a code change.
Have a job that does:

alter table drop partition 1WEEKAGO_PARTITION_PARTITION-NAME

This solves quick removal of older data.
Rotate Table

Let's say your big table is table_a. Create an empty table, call it table_b, with a similar structure to table_a.
Once a week, follow below operations in non-peak or night hours for the database.

Create 3 triggers on table_a after insert/update/delete. Have it apply the same changes to table_b.
Now take the data from last week from table_a and apply it to table_b in 1000-record batches until today.

insert into table_b
select * from table_a
where date > date_time(LASTWEEK'S DAY)
and date < date_time(TODAY_TIME_AFTER_ADDING_TRIGGERS);

You may break down the where condition in thousand chunk records do this for the above where clause.

Ensure that the insert from table_a to table_b is started once if triggers have started to populate the data.
rename table_a to table_old, and table_b to table_a.
Once operations are going fine after rename, you may go ahead and drop table table_b.
Don’t forget to drop the triggers.

This solves weekly defragmentation and space usage. But it is a lengthy process.

For option 2 (Rotate Table), I also had reservations when touching a table that had 200 DMLs per second, and per week data of more than 200 GB. It worked. But you may simulate this in test environment and see the throughput. You should have solid confidence in the response time to apps and commands to trigger. Anyway your call. It's just the direction I could suggest which I have crossed. All the best.


Mysql: [match() against()] full text search order full field value match first

ORDER BY post title != 'traduction',
         MATCH...

Explanation:

!= will generate 0 for = and 1 for !=, so the exact match will come first.
Then the rest ORDER BY will control the ordering.

(Please indicate which table each column is in.)


Issues when Loading Data with MySQL LOAD DATA INFILE

You appear to have a few issues going on here and to resolve you can make a few adjustments to get your data to load without error.

I've source referenced and quoted the items in more detail below so you can read up on each for a more thorough explanation.

In short though essentially you can:

Use the LOCAL keyword with the LOAD DATA INFILE statement to get the data to load with MySQL without needing to adjust the my.ini file parameters

LOAD DATA LOCAL INFILE '<file>'

Use double backslashes [\\] in the folder path to separate folders rather than just one to escape the first backslash since by default a single backslash is used as a special escape character and ignored when used alone

C:\\Users\\YCM LLC\\Desktop\\HFT\\ES_test.csv

With the STR_TO_DATE function for the second argument you pass it telling it the format for the time value in the first argument, you should use the %h:%i:%s or %H:%i:%s format

time = STR_TO_DATE(@var2, '%h:%i:%s');

 Further References

  The LOCAL keyword affects expected location of the file and error
  handling, as described later. LOCAL works only if your server and
  your client both have been configured to permit it. For example, if
  mysqld was
  started with the local_infile system variable disabled, LOCAL
  does not work. See Section 6.1.6, “Security Issues with LOAD DATA
  LOCAL”.

  The LOCAL keyword affects where the file is expected to be found:

  If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a
  full path name to specify its exact location. If given as a relative
  path name, the name is interpreted relative to the directory in which
  the client program was started.

  When using LOCAL with LOAD DATA, a copy of the file is
  created in the server's temporary directory. This is not the directory
  determined by the value of tmpdir or slave_load_tmpdir,
  but rather the operating system's temporary directory, and is not
  configurable in the MySQL Server. (Typically the system temporary
  directory is /tmp on Linux systems and C:\WINDOWS\TEMP on
  Windows.) Lack of sufficient space for the copy in this directory can
  cause the LOAD DATA LOCAL statement to fail.

  source

  Within a string, certain sequences have special meaning unless the
  NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (\), known as the escape
  character. MySQL recognizes the escape sequences shown in Table 9.1,
  “Special Character Escape
  Sequences”.
  For all other escape sequences, backslash is ignored. That is, the
  escaped character is interpreted as if it was not escaped.

    Table 9.1 Special Character Escape Sequences

    Escape Sequence: \\

    Character Represented by Sequence: A backslash (\) character

  source

  Description

  The MySQL STR_TO_DATE function takes a string and returns a date
  specified by a format mask.

  Syntax

  The syntax for the STR_TO_DATE function in MySQL is:

    STR_TO_DATE( string, format_mask )

  Parameters or Arguments

  string

  The string value to format as a date.

  format_mask

  The format to apply to string. The following is a list of options for
  the format_mask parameter. These parameters can be used in many
  combinations.

  Short List

    Value: %H

    Description: Hour (00 to 23)

    Value: %h

    Description: Hour (00 to 12)

    Value: %i

    Description: Minutes (00 to 59)

    Value: %s

    Description: Seconds (00 to 59)

  source


Changing POSTGRES column datatype - should I drop indexes and then re-create?

The ALTER will automatically drop and rebuild the indexes (all indexes, not just on the column being altered).  There is no point in micromanaging the process yourself, unless you want to build the indexes CONCURRENTLY or something like that, so that that part can be done outside the maintenance window.


Comparing 2 functions for any differences in the result set

I believe this should help you.  First I create a counter variable that's equal to the total months you need to evaluate, in this case you specified January 1997 to September 2017. Next I create the start and end dates based on that counter.  Doing it this way allowed me to work around having special logic for the last day of every month (and leap years).  We throw it in a while loop based on the counter and use except logic to get any results that don't match.

Edit: I wanted to improve this so that there weren't four places where dates are hard coded, but since we know the dates for this particular question, I wanted to keep it as clean as possible and not add more date manipulation functions.

declare @StartDate date, @EndDate date, @counter int = (select datediff(mm,
'1/1/1997', '9/30/2017'));
while @counter >= 0
begin
    set @StartDate = dateadd(mm, @counter, '1/1/1997');
    set @EndDate = dateadd(mm, @counter, '1/31/1997');

    --Finding results in the original that are not in the new
    /*
    Select x, y, z
    FROM report.fnReportDealCore_original(@StartDate, @EndDate, -1)
    EXCEPT
    Select x, y, z
    FROM report.fnReportDealCore(@StartDate, @EndDate, -1)
    */

    --Finding results in the new that are not in the original
    /*
    Select x, y, z
    FROM report.fnReportDealCore(@StartDate, @EndDate, -1)
    EXCEPT
    Select x, y, z
    FROM report.fnReportDealCore_original(@StartDate, @EndDate, -1)
    */

    set @counter -= 1;
end


How to set datadir to different values based on the hostname in my.cnf?

A simple solution for that would be to keep the my.cnf file identical, and use symlinks on your machine sto point to whatever directory you want the data to be in. For example, assuming you my.cnf contains this:

[mysqld]
datadir=/mysql_data

On you linux host, you would:

cd /
ln -s /var/lib/mysql mysql_data

Add permissions to this folder for mysql user:

chown mysql:mysql /mysql_data


dba_registry_history vs dba_registry_sqlpatch and OJVM patch

If the database was patched properly, DBA_REGISTRY_SQLPATCH should list the OJVM patch. Below output is from one of my sandbox environments:

SQL> select DESCRIPTION, ACTION, STATUS, ACTION_TIME from dba_registry_sqlpatch;

DESCRIPTION                                                       ACTION          STATUS          ACTION_TIME
----------------------------------------------------------------- --------------- --------------- --------------------
Database PSU 12.1.0.2.190716, Oracle JavaVM Component (JUL2019)   APPLY           SUCCESS         2019-10-01 15:31:58
DATABASE PATCH SET UPDATE 12.1.0.2.190716                         APPLY           SUCCESS         2019-10-01 15:31:59

SQL> select comments from dba_registry_history;

COMMENTS
--------------------------------------------------------------------------------
RDBMS_12.1.0.2.0DBPSU_LINUX.X64_161210


In an ideal world, all of your data would be resident in the InnoDB buffer pool, all the time... so I had a really hard time thinking of a situation where evicting data from the InnoDB buffer pool would be a good thing, but I did find two cases.

I assume you're referring to this value:

mysql> show status like 'Innodb_buffer_pool_pages_free';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_free | 28    |
+-------------------------------+-------+
1 row in set (0.00 sec)

I can't directly address the Enterprise aspect, since I don't use it, but I can tell you that I was able to get "MySQL Community Server (GPL) 5.5.15-log" to actually free up space in the InnoDB buffer pool.

If you have a light enough traffic load or small enough data set that your InnoDB buffer pool is technically larger than is needed (and therefore already has blocks free, the value of innodb_buffer_pool_pages_free will actually increase if you delete rows from a table that's already in the buffer pool.

In the example above, I started out with a value of 35 on a test server with very low traffic.  I did SELECT * from a table that probably wasn't all in the pool, which dropped the value to 27.  Then I deleted everything from the table and the value rose to 29... so at least in some cases, deletes from a table that is already in the pool will trigger an increase in the pages_free value, assuming your load isn't such that the pages will be claimed by a different table as soon as they are freed -- which is the case on my servers with much larger datasets and heavier traffic.

Similarly, from a second session, I created a small temporary table using InnoDB.  This cost me 4 blocks from the pool.  Then I closed that session, and immediately regained the 4 blocks back.

These same tests on a busy server don't yield the same results.  The "free" pretty much stays at 0, which means I technically need a larger pool, though performance on that machine is quite adequate.

So the difference you are seeing may be a difference in traffic load and data size, rather than a difference in the server editions.

However, "free space" isn't something to be wished for, here, unless you gain that space by increasing the provisioned size of the pool.

I can think of no reason why actively evicting live data from the InnoDB buffer pool in the interest of "having more free space" there would be beneficial, at all, ever.

If a page is clean (i.e., its contents match the disk files where it came from because it's unmodified or modifications have been written back to the disk files), then it can be reused, without being "free" first.  The InnoDB buffer pool is exactly where you want as much of your data as possible to always be resident.


db2 convert regular table to partitioned using online table move

The only problem seems to be your missing quotes for the dates. As they are enclosed in the string the have to be escaped (with a second single quote) like in my example:

CALL SYSPROC.ADMIN_MOVE_TABLE(
    'ADMIN',
    'TRANSACTION',
    '', '', '', '', '',
    '(TRANSACTIONDATE) (STARTING FROM (''1/1/2010'') ENDING AT (''1/1/2017'') EVERY 1 MONTH)',
    '', '', 'MOVE' )


You can write to the SQL Server error logs via the t-sql RAISERROR command.

See this Technet article.


Without more detail about the procedure[1] and the table it is inserting into we can't offer any specific help. Some general advice though:

If you are inserting the rows individually, try to arrange them so that they are inserted in large blocks, all at once if possible.
Confirm that the procedure is in fact being slow due to its own activity rather than waiting for locks held by other processes to clear.
If you are pulling the inserted rows from a view or complex inline statement make sure that this is optimised (perhaps there is something like an avoidable table scan happening when reading what it needs before it even tries to write the new rows?).
If you are using a complex statement and/or processing data in steps, check that you are not spooling stuff to tempdb unnecessary.

[1] if it is "top secret" so you can't just include the code directly[2] or (if it is long) via pastebin or similar then perhaps a sanitised version?
[2] though if it is that top secret maybe you should be paying a consultant rather than asking for free advice in public ;-)

Update

The query plan posted does suggest that for that particular statement you have a read issue not a write issue. It look like you have a correlated sub-query that is providing an aggregated figure per row and there isn't a suitable index to filter on (though there is one that allows it to avoid an extra sort operation, hence the index scan was not followed by a sort). More than that (which is educated guesswork anyway) I can't say without the details already requested. For this specific statement: the SQL for statement 54 and the table+index structure of "object8" and "object9", preferably without obfuscation[3].

[3] By disguising names you hide something that might give us clues about what you are actually trying to achieve. Help us help you, provide the details requested and don't actively hide details unless you really have to.

Regarding Cost Percentages

As Aaron said, the cost percentages are based on approximate times for a particular configuration of developer PC at Microsoft in the late 90s. Even then they were not intended to be anything other than estimates/indicators and IIRC the calculations have not been jiggled to account for changes in CPU and memory technology since. Also the calculation works on logical operations: they don't differentiate between work performed in memory and physical IO operations. Be careful too to consider the rest of the query and not just that one expensive chunk - it may be that the expense is caused by a missed optimisation elsewhere that results in an otherwise efficient operation to be run millions of times instead of a few or just one - the cost % may lead you to try to optimise that part when in fact the core problem is a step or a few away.


Why set Auto Update Statistics to False?

This is too long for a comment so I'll chime in with another case where one might want to turn off auto update stats.  I've worked with databases supporting high-volume OLTP workloads and a stringent query performance SLA in milliseconds.  Nearly all queries were trivial with a lot of attention to query and index tuning detail and some of the tables were quite large.  There wasn't much value in updating stats during peak periods in this situation and auto-update stats would violate the SLA.  Consequently, maintenance was done during non-peak periods via a scheduled job.

Another option is to turn on both AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC database options.  This will allow queries to proceed with execution plans based on stale statistics rather than incur the overhead of updating stats synchronously.  This is especially appropriate for an OLTP workload as long as the server is sized to accommodate the query workload plus the background stats update.


Sorting by DATETIME slower than by TIMESTAMP?

You can shorten the running type of queries by indexing certain columns.

Since you mentioned sorting DATETIME fields, one of the best ways to bypass sorting of temp table results is to use a covering index which includes the DATETIME as the last column of any given compound index you need.

I know this is entirely possible because in a link about Covering Indexes By Ronald Bradford, he gave a list of future topics he was giving. Here is that list:

Some future topics on indexes not discussed here include:

Using UNIQUE Indexe
The impact of NULL columns and values on indexes
Eliminating filesort by using indexes <<<---- (PLEASE NOTE THIS)
The affect of too many indexes
Index cardinality


Is it possible to grant trigger permissions on only one schema MySQL

This is going to depend greatly on what version of mysql you are on. If you are on 5.1 or higher (specifically later than 5.1.6)
https://stackoverflow.com/questions/19815837/in-mysql-what-privileges-are-required-for-executing-a-trigger

MySQL takes the DEFINER user into account when checking trigger privileges as follows:
At CREATE TRIGGER time, the user who issues the statement must have the TRIGGER privilege. (SUPER prior to MySQL 5.1.6.)
At trigger activation time, privileges are checked against the DEFINER user. This user must have these privileges:
The TRIGGER privilege. (SUPER prior to MySQL 5.1.6.)
The SELECT privilege for the subject table if references to table columns occur using OLD.col_name or NEW.col_name in the trigger body.
The UPDATE privilege for the subject table if table columns are targets of SET NEW.col_name = value assignments in the trigger body.
Whatever other privileges normally are required for the statements executed by the trigger.


Is it good practice to refer to a table with different names in a database schema?

There are many examples of best practices for schema naming e.g. here and here
Keeping it simple and based on experience and opinion of course, when designing schemas, we want:
PrimaryKey: Auto-incremented value in the naming pattern <entity name(singular)>_ID e.g. thing_id or ThingID.
UniqueKey: Add a unique constraint on a unique key. In a strong entity, usually on a real-world reference (e.g. student_id). In associative tables, these are usually on combinations of foreign keys (e.g student_id, course_id).
Foreign Keys: These are references to auto-incremented values in their source tables and so are the same data type and name as the values they reference. So without looking at the whole schema, if I see ThingID in a different table not called Thing, I know it points to the primary key of the Thing table.
In your case, I would say you should:

First decide what the correct solution should be for future maintainability and support of the schema alone without any other considerations. Imagine you are a new team member and you look at this new design with its naming structure, would you be able to understand it?
Determine what is preventing the team from executing the correct solution e.g. no one is comfortable with the code base or you didn't write it or you don't feel that anyone on the team has the wherewithal to modify the code and not break the application.
If #2 is insurmountable, then consider

a. Making the changes in #1 but add a layer like a view (add a "v_tablename") with the same old names and call the views instead of the tables from the code so the application works
b. Making the changes in #1 but add a layer or interface in the code base which allows some mapping of new field classes to old existing field classes so the application works
c. Isolate the schema and application in a separate parallel environment in which changes are made in both code and schema and verified thoroughly to ensure that the application works.

If you have zero access to the code base, then you just make the correct changes for the new tables - perhaps using a prefix-naming standard so you can identify old schema vs new schema.
If old schema has the correct naming, of course continue with it.

Overall, treating the codebase with undeserved trepidation can force inch-by-inch workarounds/accommodations and eventually, the team ends up with a design and system in both code and schema that no-one fully understands. This possible reality can add risk to your organization.


What's the best database design for implementing categories and tags?

If each article has at most one (or one and only one) category, then you would include the category information in the article table.

I would not include the text name of the category in the table. I would have a separate category table with the category name and an integer ID value. As there are presumably a relatively small number of possible categories, this shouldn't have a major impact on performance; and it would prove its value the first time someone had you change the name of a category.

You'll also probably want an index on article.category_id, if searches by tag and category will be very common (as I imagine they would be).


The right way of starting up a Galera Cluster

At MariaDB 10.1 where Galera included you need to start first node with key --wsrep-new-cluster

e.g.
/etc/init.d/mysql start --wsrep-new-cluster

All other nodes starts as usually:

/etc/init.d/mysql start

To determine which node needs to be bootstrapped, compare the wsrep_last_committed value on all DB nodes:

KVM-1> SHOW STATUS LIKE 'wsrep_%';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| wsrep_last_committed | 21568       |
...
| wsrep_cluster_status | non-Primary |
+----------------------+-------------+

KVM-2> SHOW STATUS LIKE 'wsrep_%';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| wsrep_last_committed |  1359       |
...
| wsrep_cluster_status | non-Primary |
+----------------------+-------------+

KVM-3> SHOW STATUS LIKE 'wsrep_%';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| wsrep_last_committed |   537       |
...
| wsrep_cluster_status | non-Primary |
+----------------------+-------------+

KVM-1 has the most up-to-date data. In this case, all Galera nodes are already started, so you don’t necessarily need to bootstrap the cluster again. We just need to promote KVM-1 to be a Primary Component:

KVM-1> SET GLOBAL wsrep_provider_options="pc.bootstrap=1";

The remaining nodes will then reconnect to the Primary Component (KVM-1) and resyncing back data based on this node.


Problem with the function UNIX_TIMESTAMP

My guess:

From: http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_unix-timestamp

  UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

  If called with no argument, returns a Unix timestamp (seconds since
  '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP()
  is called with a date argument, it returns the value of the argument
  as seconds since '1970-01-01 00:00:00' UTC

In the first query, you're comparing your date to the number of seconds 2013-08-01 00:00:00 and 2013-08-01 23:59:00 are after 1970-01-01, meaning you're comparing your dates to seconds.

In the second query, you're actually comparing date to other dates.


how to duplicate each row of a mysql table?

You can do it like this:

create new_table as
select name, 'italian' as language, property1_IT as property1 from old_table
union all
select name, 'english' as language, property1_EN as property1 from old_table
union all
select name, 'french' as language, property1_FR as property1 from old_table;


TSQL script to backup database using a driver table and stored procedure

There's a script out there that does almost the same thing and it's Ola's script. While the table is not programmable, the execution procedure is, so you can customize the job steps vs the table, it becomes more portable and easier to deploy in any environment. You'll find it hard to find another backup solution online that works as well to copy verbatim because solutions like Ola's already exist.

If they want an in-house built process utilizing T-SQL and you do not have the ability to write the T-SQL for the process, I would look at where you are now and how do you get to your end goal.

First, get your fundamentals together. Make sure you understand backups inside and out. Here are two good links to get started:

Backup ref 1

Backup ref 2

Next up on the list, T-SQL. You're in for a real bad time if you have to write all your code in T-SQL and if you don't know T-SQL. Check out Microsoft Developer Essentials. It comes with SQL Server Dev 2016 which is similar to Enterprise. It also comes with a 3 month trial of Pluralsite which you can and probably should immediately begin using to bolster your T-SQL. You can also check out these three books to help learn.

SQL in minutes

SQL Fundamentals

T-SQL Dev guide

MS Dev

In the end, you're going to have to pick a method for your process. This article  talks about methods of administration that you will have to use to complete this task. You may have to incorporate all of them to achieve your goal.

I'd start there and progress, unfortunately I don't see any shortcuts in this scenario if you want something done correctly. Backups and data accessibility are huge in the land of database. See Brent Ozar's hierarchy of DBA needs for more reference there. I cannot stress the importance of doing backups correctly, it's one of the foundations for DBA's and most IT roles.

Once you get into this for awhile, I would recommend to come back here with specific questions about the process you have already began to write and supply us with good sample code. See here for best practice asking questions.


How to structure different types of a model in MySQL?

Consider having all questions in a single table.  Have a column that is datatype SET with (currently) 4 "bits":  'A','B','C','D'.  10 questions would have all 4 bits set; the others would have one bit each.

(There are certainly other ways to solve this.)


Invalid object name 'SSISDB.catalog.customized_logging_levels'. (Microsoft SQL Server, Error: 208)

The first server is SQL Server 2014.

I discovered some additional information at http://radacad.com/ssis-catalog-changes-in-2016

There are a number of tables, views and stored procedures unique to SSIS 2016.  When I restored the SSIS database on the new server, these objects were overwritten.


Is it possible to write (A OR B) IN(X, Y, ..., W)?

No, there isn't a simple solution, unless the values are already in respective (normal or temporary) tables. If they are,

A IN(X, Y, ..., W) OR B IN(X, Y, ..., W)

is easy to rewrite. It's equivalent to:

EXISTS ( SELECT *
         FROM List1 JOIN List2 ON List1.item = List2.item )

If the A and B are not supplied values/parameters but columns/expressions from the tables involved in the query, we can use a mixed approach, having only the X, Y, ..., W values in a (temp) table:

A IN (SELECT item FROM List2)
OR
B IN (SELECT item FROM List2)

-- alternative 1
EXISTS (SELECT * FROM List2 WHERE List2.item = A)
OR
EXISTS (SELECT * FROM List2 WHERE List2.item = B)

-- alternative 2
EXISTS (SELECT * FROM List2 WHERE List2.item IN (A,B))

If the values are not already in tables, then a way to write this would be using VALUES clause but this has not been implmented in mySQL:

EXISTS ( SELECT *
         FROM (VALUES (A), (B)) AS List1 (item)
           JOIN
              (VALUES (X), (Y), ... (W)) AS List2 (item)
           ON List1.item = List2.item )

In MySQL, you'll have to rewrite the VALUES with rather long subqueries, using UNION

EXISTS ( SELECT *
         FROM (SELECT A AS item UNION ALL
               SELECT B
              ) AS List1
           JOIN
              (SELECT X AS item UNION ALL
               SELECT Y         UNION ALL
               ...
               SELECT W
              ) AS List2
           ON List1.item = List2.item )


SQL Server : which role grants permission to execute all stored procedures?

If you are using schemas other than the default dbo schema, create a database role per schema and grant EXECUTE on the schema to the role.

e.g.
For the default dbo schema:

CREATE ROLE role_exec_dbo
GO
GRANT EXECUTE ON SCHEMA::dbo to role_exec_dbo
GO

For a new schema:

CREATE SCHEMA mySchema
GO
CREATE ROLE role_exec_mySchema
GO
GRANT EXECUTE ON SCHEMA::mySchema to role_exec_mySchema
GO


You can separate the problem into multiple steps.

First lets create an example table:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Visitors(
  id INT,
  -- other columns
  country CHAR(2)
);

INSERT INTO Visitors(id,country)VALUES(1,'DE');
INSERT INTO Visitors(id,country)VALUES(2,'DE');
INSERT INTO Visitors(id,country)VALUES(3,'DE');
INSERT INTO Visitors(id,country)VALUES(4,'BE');
INSERT INTO Visitors(id,country)VALUES(5,'BE');
INSERT INTO Visitors(id,country)VALUES(6,'BE');
INSERT INTO Visitors(id,country)VALUES(7,'BE');
INSERT INTO Visitors(id,country)VALUES(8,'BE');
INSERT INTO Visitors(id,country)VALUES(9,'BE');
INSERT INTO Visitors(id,country)VALUES(10,'US');
INSERT INTO Visitors(id,country)VALUES(11,'US');
INSERT INTO Visitors(id,country)VALUES(12,'US');
INSERT INTO Visitors(id,country)VALUES(13,'US');
INSERT INTO Visitors(id,country)VALUES(14,'HR');
INSERT INTO Visitors(id,country)VALUES(15,'HR');
INSERT INTO Visitors(id,country)VALUES(16,'SE');
INSERT INTO Visitors(id,country)VALUES(17,'SE');
INSERT INTO Visitors(id,country)VALUES(18,'SE');
INSERT INTO Visitors(id,country)VALUES(19,'SE');
INSERT INTO Visitors(id,country)VALUES(20,'RU');

The first step now is to count the impressions per country using the GROUP BY clause:

Query 1:

SELECT country,COUNT(1) cnt
FROM Visitors
GROUP BY country

Results:

| COUNTRY | CNT |
-----------------
|      BE |   6 |
|      DE |   3 |
|      HR |   2 |
|      RU |   1 |
|      SE |   4 |
|      US |   4 |

From there we can get the top 5 countries using LIMIT:

Query 2:

SELECT *
FROM(
  SELECT country,COUNT(1) cnt
  FROM Visitors
  GROUP BY country
)V
ORDER BY cnt DESC
LIMIT 5

Results:

| COUNTRY | CNT |
-----------------
|      BE |   6 |
|      SE |   4 |
|      US |   4 |
|      DE |   3 |
|      HR |   2 |

Now we just need to join the result back to the visitors table:

Query 3:

SELECT V.*,c.cnt
FROM Visitors V
JOIN(
  SELECT *
  FROM(
    SELECT country,COUNT(1) cnt
    FROM Visitors
    GROUP BY country
  )V
  ORDER BY cnt DESC
  LIMIT 5
)C
ON V.country = C.country

Results:

| ID | COUNTRY | CNT |
----------------------
|  1 |      DE |   3 |
|  2 |      DE |   3 |
|  3 |      DE |   3 |
|  4 |      BE |   6 |
|  5 |      BE |   6 |
|  6 |      BE |   6 |
|  7 |      BE |   6 |
|  8 |      BE |   6 |
|  9 |      BE |   6 |
| 10 |      US |   4 |
| 11 |      US |   4 |
| 12 |      US |   4 |
| 13 |      US |   4 |
| 14 |      HR |   2 |
| 15 |      HR |   2 |
| 16 |      SE |   4 |
| 17 |      SE |   4 |
| 18 |      SE |   4 |
| 19 |      SE |   4 |


Executing same request from C# VS SSMS give different execution time

Is this consistent, time after time?

I see a CPU difference which could be compile time. Are there any LINQ settings that affect this?

Edit:

Capture the plans in Profiler
Are you sure the SQL is the same in Profiler?


Select children from hierarchy (/a/b/c/d/)

You need to 'walk the tree'.  This can be done using a recursive CTE, something like this:

USE tempdb
GO

CREATE TABLE dbo.Unit (
    id INT IDENTITY PRIMARY KEY,
    unitName        VARCHAR(20),
    hierarchyPath   hierarchyId UNIQUE,
    typeId          INT
    )
GO

CREATE TABLE dbo.UnitType (
    id      INT PRIMARY KEY,
    name    VARCHAR(20)
    )
GO

INSERT INTO dbo.Unit ( hierarchyPath, unitName, typeId )
VALUES
    ( '/42/', 'Team1', 1 ),
    ( '/42/1/', 'John', 2 ),
    ( '/42/2/', 'Joe', 2 ),
    ( '/42/3/', 'Johana', 2 ),

    ( '/43/', 'Team2', 1 ),
    ( '/43/1/', 'Bob', 2 ),
    ( '/43/2/', 'Bobek', 2 ),
    ( '/43/3/', 'wBobaFett', 2 )

INSERT INTO dbo.UnitType ( Id, name )
VALUES ( 1, 'Team' ), ( 2, 'Name' )
GO

--SELECT *, hierarchyPath.ToString() hierarchyPath_string
--FROM dbo.Unit

;WITH cte AS
(
SELECT 1 xlevel, Id, hierarchyPath, unitName AS teamName, CAST( '' AS VARCHAR(20) ) unitName
FROM dbo.Unit
WHERE typeId = ( SELECT Id FROM dbo.UnitType WHERE name = 'Team' )

UNION ALL

SELECT xlevel + 1, u.Id, u.hierarchyPath, c.teamName, u.unitName
FROM cte c
    INNER JOIN dbo.Unit u ON u.hierarchyPath.GetAncestor (1) = c.hierarchyPath

)
SELECT teamName, unitName
FROM cte
WHERE xlevel > 1
ORDER BY teamName, unitName
GO

DROP TABLE dbo.Unit
DROP TABLE dbo.UnitType
GO

HTH


Does schema binding views affect performance?

With schemabinding can improve performance in some cases.

I couldn't recall all the details, but found an excellent blog post that goes through this in detail:

  With user defined functions that may need to update data. You might
  benefit from improved performance that would prevent an Eager Table
  Spool.
  SQL Server SCHEMABINDING

Edit: Paul White indicated that this doesn't actually impact view table spools just used for joins.

Schema binding definitely adds some additional maintenance when changing schema. This could be considered a good thing, since it prevents underlying schema changes without handling the binding. This doesn't bother me as I'm using Red Gate SQL Compare and it handles this, but if you are not using a tool to generate scripts this could be a factor to consider... (solved by getting a tool to generate the scripts!)

See also Improving query plans with the SCHEMABINDING option on T-SQL UDFs on the Microsoft SQL Programmability & API Development Team Blog.

Additionally, the comment by Kin on your original question brings up a great additional point regarding the potential to index the view once you have schema binding included. This can be considered an additional benefit that might help.


question.noofanswers.  Why is it there?  Cant you just pull this from answer table at run-time?  Or are you enforcing rules on this?
Your student table looks like it has transitive functional dependencies in it but I can't be sure.  Is year dependent on courseid?  At any rate you are likely to want to break off student records and what courses they take since a student could take multiple courses over time.

Those are the only clear normalization/Codd's rules issues I see with this.  I could be missing something though.

Expanding on the second.  Take your student table.  Presumably there is a natural key somewhere but for now let's use StudentEmail as a candidate key because this can uniquely identify a given student (if there is a practical problem in your case, you can use StudentID as a substitute for argument's sake).

In this case StudentForename, StudentSurname depend functionally on StudentEmail.  For every email address there is one and only one forename and surname associated with it.  You might want to break away StudentAlias as well because that would give you an ability to have more than one alias per student, but if you will not have that, it breaks no normalization rules.

The problem is that Year depends on (StudentEmail, CourseID) meaning you have a 2NF violation here, where some attributes are dependent on part of the candidate key and others are dependent on the whole candidate key.  This really needs to be fixed before you can call it 3NF.


Halting a multi-step Agent job without resorting to "Quit the job reporting success"?

One solution is to use multiple job steps and branch depending on the success/failure condition of the business condition - but that means the job will produce that "dreaded" yellow triangle and red circle for the step.
A second solution is to use 3 jobs and call one of two jobs based on the result of the business condition.
STEP 1 : Do something
STEP x : Test for business condition
    IF dbo.fnBusinessCondition = 1
       EXECUTE msdb.dbo.sp_start_job 'Job2'
    else
       EXECUTE msdb.dbo.sp_start_job 'Job3'


You have the functions applied in wrong order. You need to do date arithmetic first (date_add, whatever) and apply the format last:

DATE_FORMAT( DATE_ADD(ss.week_startdate, INTERVAL (tt.day_id - 1) DAY),
             '%Y-%m-%e')

You also need %Y for a 4-digit year, not %y.

Also note that it's better to also change the first query, as it does a lot of redundant operations:

date_add(
    DATE_FORMAT(ss.week_startdate,'%y-%m-%d'),
                INTERVAL (tt.day_id- 1 )DAY
               )
as date

First, it calculates: DATE_FORMAT(ss.week_startdate,'%y-%m-%d')
which converts the datetime value to a string with format (example for current date, 2015 April 28): '15-04-28'.
Then, it does: date_add( previous_result,  INTERVAL (tt.day_id-1) DAY)
But the previous_result is a string so it has to be converted to date or datetime. It's only by accident that the 2-year format does not lead to a wrong date. After this conversion, the date_add() is calculated and because the first argument (after the conversion) is a date and you only add days, not hours or smaller unit, the result is also a date.
Then, the as day is considered but this does nothing, as the previous operation has resulted in a date, too.
Finally, the result is shown in the default format for dates, which (by one more accident!) is %Y-%m-%d, to get the wanted format.

In all, three conversions (datetime -> char -> date -> char), while you only need one.