Oracle: How to create a not null column in a view

As I understand it now, you need the entity framework to see the table as having a NOT NULL BINARY_DOUBLE, but the data needs to be/is stored in a NOT NULL NUMBER.  This is a problem when using a view because the view does not pass the NOT NULL attribute through when it contains a CAST.

Your options include the two Alex Poole +1 mentioned on SO (1. Virtual Column, 2. New column of different type with a trigger) or 3. New table with a different type column and a trigger.  You should prefer these options in the order they are presented.

Previous Answer

Columns in views from columns in tables that are marked NOT NULL will inherently be NOT NULL as well.  There is nothing special you need to do to make this happen.

If instead you are trying to make the view accept nulls when the table is marked as NOT NULL, then you should look into INSTEAD OF triggers.  They will allow you to insert a null into a view and have it inserted as a zero into the table.  Here is an example:

SQL> drop table t1;

Table dropped.

SQL>
SQL> drop view v1;

View dropped.

SQL>
SQL> create table t1 (c1 number not null);

Table created.

SQL>
SQL> insert into t1 values (1);

1 row created.

SQL>
SQL> insert into t1 values (null);
insert into t1 values (null)
                       *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("LRIFFEL"."T1"."C1")

SQL>
SQL> create view v1 as select cast(c1 as binary_double) c1 from t1;

View created.

SQL>
SQL> insert into v1 values (2);
insert into v1 values (2)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here

SQL>
SQL> insert into v1 values (null);
insert into v1 values (null)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here

SQL>
SQL> CREATE TRIGGER TRIGGER1
  2  INSTEAD OF INSERT ON V1
  3  REFERENCING OLD AS old NEW AS new
  4  BEGIN
  5    INSERT INTO t1 VALUES (NVL(:new.c1,0));
  6  END;
  7  /

Trigger created.

SQL>
SQL> insert into v1 values (2);

1 row created.

SQL>
SQL> insert into v1 values (null);

1 row created.

SQL>
SQL> select * from t1;

        C1
----------
         1
         2
         0


Stop NLS settings in init.ora getting overridden by environment variable

The documentation clearly describes the precedence for NLS settings: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG188

1 (highest) Explicitly set in SQL functions
2           Set by an ALTER SESSION statement
3           Set as an environment variable
4           Specified in the initialization parameter file
5           Default

You can choose the first two from these.
A logon trigger can also issue the ALTER SESSION, and you don't need to modify the application code for that:

CREATE OR REPLACE TRIGGER SET_NLSCALENDAR_ONLOGIN AFTER LOGON ON DATABASE
BEGIN
  IF SYS_CONTEXT('USERENV','SESSION_USER') = 'SCOTT' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_CALENDAR=''Thai Buddha''';
  END IF;
END;
/

Check setting for SCOTT and other user:

SQL> connect scott/tiger
Connected.
SQL> select value from nls_session_parameters where parameter = 'NLS_CALENDAR';

VALUE
-----------
Thai Buddha

SQL> select value from nls_database_parameters where parameter = 'NLS_CALENDAR';

VALUE
-----------
GREGORIAN

SQL> conn hr/hr
Connected.
SQL> select value from nls_session_parameters where parameter = 'NLS_CALENDAR';

VALUE
-----------
GREGORIAN


Whether Cold backup method can be used to "migrate oracle DB"?

Can cold backup can be used to create replica/ DB migration?

Yes of course

  If i tried above process , shall i am able to start the db on target
  server without any errors ?

If you had cloned the machine or installed Oracle binaries in destination machine, only you need another steps:

Copy passwords file for your database, file $ORACLE_HOME/dbs/orapwSID
Copy line of /etc/oratab describing the database you are cloning into
destination machine.

  As i don't have any archive logs, will that create any problem here?

Yes its a cold backup, you don't need archivelog for nothing.


The Service Broker endpoint is in disabled or stopped state - AoAG - SQL Server 2012 - 15 minute interval

According to this link,

  It’s a default behavior. The message will show when AlwaysOn
  availability group Listener is created and SQL Server binds the
  Listener’s IP dynamically. There is no method to suppress the message.
  These messages are informational message and do not cause any harm and
  can safely be ignored.

That link also gives a query to see which databases do and do not have brokers enabled:

SELECT is_broker_enabled, name  FROM sys.databases


is there a GUI client that can execute multiple queries and show its multiple results?

Looks like Aqua Data Studio could be what I'm after.  To get multiple query results you have to separate your queries with a slash.  It's not automatic but it's a step in the right direction for me.

e.g.

select travelerId, name from travelers;
/
select dateCreated from carDealerships;
/


It is helpful if you post the table design, or at least what you will be joining on.  I created a sample that has 2 tables:

1) Student - Name (PK)

2) Training - Id (PK, Autonumber), Name, Class, Date

Create a relationship between Student.Name, Training.Name, enforce referential integrity.

There is a nice "Find Unmatched Records" query wizard in access that will get you going...  I used it as a base and came up with:

SELECT Student.Name
FROM Student
  LEFT JOIN Training ON Student.[Name] = Training.[Name]
WHERE (((Training.Name) Is Null));

From here you can add additional logic in your where clause to whittle it down.

Edit : I missed the part about a specific class - this is probably closer to what you are after:

SELECT Student.Name
FROM Student
where name not in (select name from training where class='your class here')


Starting an Oracle database without a spfile & pfile?

You can try it with a basic pfile file like this:

SID.__db_cache_size=180355072
SID.__java_pool_size=71303168
SID.__large_pool_size=4194304
SID.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
SID.__pga_aggregate_target=335544320
SID.__sga_target=1001088640
SID.__shared_io_pool_size=0
SID.__shared_pool_size=419430400
SID.__streams_pool_size=50331648
*.audit_trail='DB_EXTENDED'
*.compatible='11.2.0.0.0'
*.control_files='/u01/oradata/SID/control01.ctl','/u01/oradata/SID/control02.ctl','/u01/oradata/SID/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/SID/cdump'
*.db_domain='your.domain'
*.db_name='SID'
*.db_recovery_file_dest='/u01/oradata/SID/flash_recovery_area'
*.db_recovery_file_dest_size=1000G
*.db_unique_name='SID02'
*.pga_aggregate_target=335544320
*.sga_max_size=1005306368
*.sga_target=1001088640
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=900
*.undo_tablespace='UNDOTS02'

Change the values so that they reflect the values (at least replace SID with your SID) that you need.


How to change the default delimiter in PostgreSQL 9.1.9 using SET?

This:

`IF position ('|' in var) IS NOT NULL`

is assuming that the result of position('|' in 'nopipes') is NULL. It is not, it's zero. position returns null only on null input.

regress=> select position('|' in 'abc|def|ghi');
 position
----------
        4
(1 row)

regress=> select position('|' in 'abcdefghi');
 position
----------
        0
(1 row)

regress=> select position('|' in null);
 position
----------

(1 row)

... so if you instead wrote:

IF position ('|' in var) <> 0

it should work fine.

The field separator \f in psql is irrelevant to this. It's only used to control how psql displays result sets. The server and functions on the server cannot see or be affected by this value. \f is just shorthand for \pset fieldsep, it's a psql-local control to choose the field separator for unaligned output:

regress=> \pset format unaligned
Output format is unaligned.
regress=> SELECT 1,2,3;
?column?|?column?|?column?
1|2|3
(1 row)
regress=> \pset fieldsep #
Field separator is "#".
regress=> SELECT 1,2,3;
?column?#?column?#?column?
1#2#3
(1 row)


Access for multiple domain users to SQL Server using Windows Authentication

For troubleshooting purposes you could fine-tune your deployment after installing SQL Express by attaching this one-liner. Feel free to get more granular with security as needed--perhaps a more exclusive user group besides the local USERS group (and DB_OWNER--hopefully is overkill and can be something much less):

sqlcmd -E -S %COMPUTERNAME%\SQLEXPRESS -Q "CREATE LOGIN [BUILTIN\USERS] FROM WINDOWS; USE <your  database>;CREATE USER [BUILTIN\USERS];EXEC SP_ADDROLEMEMBER DB_OWNER,[BUILTIN\USERS]"

p.s. be sure and change  to the actual database name of the local SQL Express database. And if SQLEXPRESS is not the actual instance name--change that too. There are alternate syntax variants available as you progress up the SQL 20XX versions.


Constructing datetime from 2 fields: one integer and a datetime

So, you're going to be parsing strings and ints and doing all kinds of conversions, so I don't think you're going to really save anything by seeking out a "more efficient" way to turn crap data into good data - even on billions of rows. An alternative might be to avoid converting anything to a string at all (which is notoriously bad for performance):

DECLARE @dt DATETIME = '20130904', @t INT = 192423998;

SELECT DATEADD(MILLISECOND, @t % 100000 -- milliseconds
  + 60000 * (@t / 100000 % 100) -- minutes converted to milliseconds
  + 60000 * 60 * (@t / 10000000), -- hours converted to milliseconds
  @dt);

Of course, keep in mind that the milliseconds your trading system passes in can round in either direction when using datetime (.998 isn't possible and becomes .997, for example), so you may want instead:

SELECT DATEADD(MILLISECOND, @t % 100000 -- milliseconds
  + 60000 * (@t / 100000 % 100) -- minutes converted to milliseconds
  + 60000 * 60 * (@t / 10000000), -- hours converted to milliseconds
  CONVERT(DATETIME2(3), @dt));

I ran these each 100,000 times, and compared to your existing approach. The difference?

Your approach:               41.8 seconds
My approach (no convert):    40.5 seconds
My approach (with convert):  38.9 seconds

Now, this should not be extrapolated to mean that on 1 billion rows my method will save you (13,000 or 31,000) seconds, as the translation of 100,000 batches to operating on 100,000 rows in a single statement will not be linear. But it could be worth testing (you have the data necessary to test this; I would just be making stuff up, and I also probably have processors that have different performance characteristics when it comes to floating point operations etc., so anything else I could offer would not necessarily be relevant).

I still think you're better off taking the hit, fixing the design, and intercepting the incoming trading data and correcting it. As this system gets bigger, these translations and conversions are going to hurt more, and more, and more...


Can I tell mariadb which query plan to use for a given query?

There are al lot of passibilities to force mariadb

for example:

Forcing Join Order
Forcing Usage of a Specific Index for the WHERE Clause
USE INDEX: Use a Limited Set of Indexes
IGNORE INDEX: Don't Use a Particular Index
FORCE INDEX: Forcing an Index

how they work and how to use it

https://mariadb.com/kb/en/index-hints-how-to-force-query-plans/


This log just indicates that you didn't shut down correctly, so upon startup, InnoDB had to do a "crash recovery". It's not the crash recovery that would have lost data -- it's your operating system or hardware. Crash recovery is designed actually to avoid losing data due to crashes (that's its entire purpose).

The log message indicates a severe problem:

The log sequence numbers 5988825 and 5988825 in ibdata files do not match the log sequence number 6057379 in the ib_logfiles!

This likely means that data is not getting to disk effectively either because of a misconfiguration in MySQL, or a misconfiguration of your operating system or hardware.

If you are running with innodb_flush_log_at_trx_commit set to anything other than 1, this is your fault as this is a dangerous setting. If you are running it with 1, this would imply that InnoDB is requesting data to be written to disk and confirmed, but the system is lying to InnoDB about writing it, causing corruption later.

There is no way to get the data back. If you need data that was lost, you must restore it from backup.

MyISAM is definitely not safer, and in fact MyISAM would more than likely have suffered extreme corruption under the same circumstances.


How to compare current record with previous records

This seemed like a relational division problem to me:

DECLARE @results TABLE ( ID INT NOT NULL, PASSED BIT NOT NULL, LEVEL INT NOT NULL, CREATEDATE DATE NOT NULL, PRIMARY KEY ( ID, LEVEL, PASSED, CREATEDATE ) )
DECLARE @levels TABLE ( LEVEL INT PRIMARY KEY )

INSERT INTO @levels
VALUES ( 1 ), ( 2 ) --, ( 3 )
INSERT INTO @results
VALUES
    ( 100, 1, 2, '1 Oct 2016' ),
    ( 100, 0, 2, '1 Sep 2016' ),
    ( 100, 1, 2, '1 Aug 2016' ),
    ( 100, 1, 1, '1 Jul 2016' ),
    ( 101, 1, 1, '1 Oct 2016' ),
    ( 102, 1, 1, '1 Oct 2016' ),
    ( 102, 1, 1, '1 Sep 2016' ),
    ( 103, 0, 1, '1 Oct 2016' ),
    ( 103, 1, 2, '1 Sep 2016' )
    ,
    ( 200, 0, 1, '1 Sep 2016' ),        -- No pass at all
    ( 201, 1, 1, '1 Sep 2016' ),        -- 1 pass at level 1 only
    ( 202, 1, 1, '1 Sep 2016' ),        -- 1 pass at each level
    ( 202, 1, 2, '1 Sep 2016' ),        -- 1 pass at each level
    ( 203, 1, 2, '1 Sep 2016' ),        -- 1 pass at level 2 only
    ( 204, 0, 1, '1 Sep 2016' ),        -- No pass at either level
    ( 204, 0, 2, '1 Sep 2016' )         -- No pass at either level

    SELECT ID,
        CASE
            WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
              AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
            THEN 1
            ELSE 0
        END overallPass
    FROM @results
    GROUP BY ID

I have not included the CREATEDATE column in the calculation as it doesn't seem to add a lot.  Also, does it really make sense for someone to pass a course at the same level twice (as per ID 100 in your sample data)?  Does the order in which they pass the courses really matter?  If so, then this could cause a bug in my code where Sum of PASSED >= COUNT ( DISTINCT LEVEL ).  Pre-aggregating the data would solve this, eg

SELECT ID,
    CASE
        WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
          AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
        THEN 1
        ELSE 0
    END overallPass
FROM ( SELECT ID, LEVEL, MAX( CAST( PASSED AS INT ) ) PASSED FROM @results GROUP BY ID, LEVEL ) x
GROUP BY ID

If the date really is important, please post back and we'll need a slightly more complicated query, basically the PASSED status associated with the max date per ID.

HTH


Can independent applications cause roll back to each other's transactions?

I want to understand if there is a possibility that transaction being
  run via one application can affect the ones from an independent other
  application.

Directly, no. Each transaction is by definition is a "single unit of work" and is local to itself. That is, my transaction cannot become part of your transaction. I can't explicitly tell yours to roll back or commit.

However, if two transactions are working on the same data, they can influence each other. If a deadlock occurs between two transactions, one is a victim (killed) and the other succeeds. In this scenario, my transaction causes the database engine to issue a rollback on your transaction, but nothing in my transaction is directly impacting yours. While deadlocks can never be 100% avoided, there are ways to mitigate the risk and impact of them.

As El.Ham mentioned, separate transactions can also cause blocking, leading to a suspended query. This could lead to application slowness or even timeouts depending on how things are configured for your connections.

  Are these scenarios handled by the DBMS itself internally or have to
  be handled at the application level ?

It depends. If your concern is deadlocks (which I think it is), then you probably want the applications to have retry logic so that a deadlocked transaction will be re-tried automatically. This can be done with T-SQL or at the application level.


Performing those subqueries repeatedly for each distinct ID value is what's killing you. I ran a test of your method on a sample table of 10,000 rows and it took ~2.5 minutes to execute on my notebook. By comparison, the following code consistently returned the same results in less than 0.5 seconds:

Option Compare Database
Option Explicit

Sub UsingAggregateQuery()
    Dim cdb As DAO.Database, t0 As Single
    Set cdb = CurrentDb
    cdb.Execute "DELETE FROM FinalTable", dbFailOnError
    t0 = Timer
    cdb.Execute _
            "INSERT INTO FinalTable(ID, FID, KeyValue1, KeyValue2, Date1) " & _
            "SELECT InputTable.ID, " & _
            "       Last(InputTable.FID), " & _
            "       Last(InputTable.KeyValue1), " & _
            "       Last(InputTable.KeyValue2), " & _
            "       Last(InputTable.Date1) " & _
            "FROM " & _
            "    (" & _
            "        SELECT ID, FID, KeyValue1, KeyValue2, Date1 " & _
            "        FROM InputTable " & _
            "        WHERE Date1 <> '' " & _
            "        ORDER BY ID, Date2 " & _
            "    ) " & _
            "GROUP BY ID;", _
            dbFailOnError
    Debug.Print Format(Timer - t0, "0.0") & " seconds"
    Set cdb = Nothing
End Sub

Edit

I just ran another test on a sample table of 400,000 rows and the code above completed in under 7 seconds


First, photo_id should be unique on the table. Ignoring sound practice however, you can still do this with a composite PRIMARY KEY

CREATE TABLE photos (
  user_id  int,
  photo_id int,
  PRIMARY KEY (user_id, photo_id)
);

INSERT INTO photos (user_id,photo_id) VALUES (1,1),(1,2);

--duplicate already exists
INSERT INTO photos (user_id,photo_id) VALUES (1,1);

Composite natural PRIMARY KEY's are not bad, however I'm not a fan of having them on multiple surrogate keys. If you're going to create an ID, may as well make it unique.


How do I set up primary and foreign keys referencing two different tables?

The actual error message you are getting is because your CREATE TABLE statement is malformed as you cannot have a foreign key refer to more than one table. However you can still have the structure that you require by having two separate foreign keys. You can only use a composite foreign key if all columns that will be added to it belong to the same key in the target table.

CREATE TABLE ASSIGNMENT (
    ProjectID       INT             NOT NULL,
    EmployeeNumber  INT             NOT NULL,
    HoursWorked     NUMERIC(6,2)    NULL,

CONSTRAINT AssignmentPK PRIMARY KEY (ProjectID, EmployeeNumber),
CONSTRAINT AssignmentFK_Project FOREIGN KEY (ProjectID)
  REFERENCES PROJECT(ProjectID)
   ON UPDATE NO ACTION
   ON DELETE CASCADE,
CONSTRAINT AssignmentFK_Employee FOREIGN KEY (EmployeeNumber)
  REFERENCES EMPLOYEE(EmployeeNumber)
   ON UPDATE NO ACTION
   ON DELETE NO ACTION
);


How to add a PostgreSQL 10 identity column to an existing table?

You need to declare the type of the column, too (INT or BIGINT or
SMALLINT):

ALTER TABLE sourceTable
    ADD COLUMN ogc_fid int                -- int or bigint or smallint
        GENERATED BY DEFAULT AS IDENTITY;

Also be sure to use the latest point release. As the IDENTITY has only been recently added, there were bugs affecting this particular command before Postgres 10.2. See:
How to add a PostgreSQL 10 identity column to an existing table with rows?


I would use some proxy in front of postgresql servers.
I advise using HAProxy to proxy tcp traffic.

Example configuration might look more or less like this:

defaults
    mode tcp
    timeout connect 30s
    timeout client  90s
    timeout server  90s

frontend PGSQL-FRONT
    mode tcp
    bind *:5432
    default_backend PGSQL-BACK

backend PGSQL-BACK
    mode tcp
    balance leastconn
    option tcp-check
    tcp-check connect port 5432
    server s1 10.0.0.101:5432 check
    server s2 10.0.0.103:5432 check backup


Merge multiple rows into fewest number of rows of distinct values

Here are a couple ways you might approach the task by assigning an arbitrary row number to each of the non-NULL values for each column (avoiding extraneous sorts since the problem statement does not require ordered data) and then pivoting the resulting data into your final result set.

Create test data

CREATE TABLE #table (
    ID INT NOT NULL,
    Col1 VARCHAR(100) NULL,
    Col2 VARCHAR(100) NULL,
    Col3 VARCHAR(100) NULL,
    Col4 VARCHAR(100) NULL,
    Col5 VARCHAR(100) NULL
)
GO
INSERT INTO #table (ID, Col1, Col2, Col3, Col4, Col5)
VALUES  (37850, '1A', NULL, '3A', NULL, '5A'),
        (37850, NULL, NULL, '3B', NULL, NULL),
        (37850, NULL, '2A', '3C', '4A', '5B'),
        (37850, NULL, NULL, NULL, NULL, NULL),
        (37850, NULL, NULL, NULL, NULL, '5C'),
        (37850, NULL, '2B', NULL, NULL, NULL),
        (37850, NULL, NULL, NULL, NULL, '5D'),
        (37850, NULL, NULL, NULL, NULL, NULL),
        (37850, NULL, NULL, NULL, '4B', '5E'),
        (37850, NULL, NULL, NULL, '4C', NULL),
        (37850, NULL, NULL, NULL, '4D', NULL)
GO
CREATE CLUSTERED INDEX CI ON #table (ID)
GO

Perform the desired merge and flatten with one scan of the data per column and only a single sort (or hash group) on the final set of rows

SELECT ID,
        -- Pivot the data for each ID / row number pair,
        MIN(CASE WHEN colNumber = 1 THEN val END) AS Col1,
        MIN(CASE WHEN colNumber = 2 THEN val END) AS Col2,
        MIN(CASE WHEN colNumber = 3 THEN val END) AS Col3,
        MIN(CASE WHEN colNumber = 4 THEN val END) AS Col4,
        MIN(CASE WHEN colNumber = 5 THEN val END) AS Col5
FROM (
    -- Within each ID, assign an arbitrary row number to each non-NULL column value
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 1 AS colNumber, Col1 AS val FROM #table WHERE Col1 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 2 AS colNumber, Col2 AS val FROM #table WHERE Col2 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 3 AS colNumber, Col3 AS val FROM #table WHERE Col3 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 4 AS colNumber, Col4 AS val FROM #table WHERE Col4 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 5 AS colNumber, Col5 AS val FROM #table WHERE Col5 IS NOT NULL
) x
GROUP BY ID, rowNum
GO

In SQL Server 2017+, perform the desired merge and flatten with a single scan over the data and no sorting

SELECT s.ID,
    -- Pivot the data for each ID / row number pair,
    MIN(CASE WHEN v.colNum = 1 THEN v.value END) AS Col1,
    MIN(CASE WHEN v.colNum = 2 THEN v.value END) AS Col2,
    MIN(CASE WHEN v.colNum = 3 THEN v.value END) AS Col3,
    MIN(CASE WHEN v.colNum = 4 THEN v.value END) AS Col4,
    MIN(CASE WHEN v.colNum = 5 THEN v.value END) AS Col5
FROM (
    -- For each ID, build the list of non-NULL values, using
    -- a delimiter that will not exist in your data
    SELECT t.ID,
        STRING_AGG(t.Col1,CHAR(0)) as stringAgg1,
        STRING_AGG(t.Col2,CHAR(0)) as stringAgg2,
        STRING_AGG(t.Col3,CHAR(0)) as stringAgg3,
        STRING_AGG(t.Col4,CHAR(0)) as stringAgg4,
        STRING_AGG(t.Col5,CHAR(0)) as stringAgg5
    FROM #table t
    GROUP BY t.ID
) s
OUTER APPLY (
    -- For each ID, unpivot the list of non-NULL values,
    -- appending an arbitrary row number to each value
    SELECT value, 1 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg1,CHAR(0)) UNION ALL
    SELECT value, 2 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg2,CHAR(0)) UNION ALL
    SELECT value, 3 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg3,CHAR(0)) UNION ALL
    SELECT value, 4 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg4,CHAR(0)) UNION ALL
    SELECT value, 5 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg5,CHAR(0))
) v
-- For each ID, group together all column values with same row number
GROUP BY s.ID, v.rn
GO

Visualize the test data

Visualize the results


How can I restrict which computers can connect to an instance of SQL Server?

If you want to totally restrict certain servers, then the Windows Firewall should be configured to the restriction.

See: Configure the Windows Firewall to Allow SQL Server Access

If you need to prevent a specific login from connecting, then of course you would need to manage that login's rights to the server.

Depending on the granularity of what you need to do, you might need to use both approaches.


Does this medical database look correct?

As a very basic structure it is close.

The one glaring issue is that the relationship between trial and centre is the wrong way around.  You should have a CentreId on the Trial table, as I believe there can by multiple trials from 1 centre and not vice versa.

Also you may want to consider adding some extra details to Centre like name etc.

Lastly, the Patient -> Trial relationship may well be a many-to-many relationship and thus you  would need to add a table to encapsulate this relationship.


T-SQL - What's the most efficient way to loop through a table until a condition is met

You should try to avoid loops generally. They are normally less efficient than set based solutions as well as less readable.

The below should be pretty efficient.

Even more so if the name and weight columns could be INCLUDE-d in the index to avoid the key lookups.

It can scan the unique index in order of turn and calculate the running total of the Weight column - then use LEAD with the same ordering criteria to see what the running total in the next row will be.

As soon as it finds the first row where this exceeds 1000 or is NULL (indicating there is no next row) then it can stop the scan.

WITH T1
     AS (SELECT *,
                SUM(Weight) OVER (ORDER BY turn ROWS UNBOUNDED PRECEDING) AS cume_weight
         FROM   [dbo].[line]),
     T2
     AS (SELECT LEAD(cume_weight) OVER (ORDER BY turn) AS next_cume_weight,
                *
         FROM   T1)
SELECT TOP 1 name
FROM   T2
WHERE  next_cume_weight > 1000
        OR next_cume_weight IS NULL
ORDER  BY turn

Execution Plan

In practice it seems to read a few rows ahead of where is strictly necessary - it looks like each window spool/stream aggregate pair causes two additional rows to be read.

For the sample data in the question ideally it would only need to read two rows from the index scan but in reality it reads 6 but this is not a significant efficiency issue and it does not degrade as more rows are added to the table (as in this demo)

For those interested in this issue an image with the rows output by each operator (as shown by the query_trace_column_values extended event) is below, the rows are output in row_id order (starting at 47 for the first row read by the index scan and finishing at 113 for the TOP)

Click the image below to make it larger or alternatively see the animated version to make the flow easier to follow.

Pausing the animation at the point where the Right hand stream aggregate has emitted its first row (for gary - turn = 1).
It seems apparent that it was waiting to receive its first row with a different WindowCount (for Jo - turn = 2).
And the window spool doesn't release the first "Jo" row until it has read the next row with a different turn (for thomas - turn = 3)

So the window spool and stream aggregate both cause an additional row to be read and there are four of these in the plan - hence 4 additional rows.

An explanation of the columns shown in the above follows (based on info here)

NodeName: Index Scan, NodeId: 15, ColumnName: id base table column covered by index
NodeName: Index Scan, NodeId: 15, ColumnName: turn base table column covered by index
NodeName: Clustered Index Seek, NodeId: 17, ColumnName: weight base table column retrieved from lookup
NodeName: Clustered Index Seek, NodeId: 17, ColumnName: name base table column retrieved from lookup
NodeName: Segment, NodeId: 13, ColumnName: Segment1010 Returns 1 at start of new group or null otherwise. As no Partition By in the SUM only the first row gets 1
NodeName: Sequence Project, NodeId: 12, ColumnName: RowNumber1009 row_number() within group indicated by Segment1010 flag. As all rows are in the same group this is ascending integers from 1 to  6. Would be used for filtering right frame rows in cases like rows between 5 preceding and 2 following. (or as for LEAD later)
NodeName: Segment, NodeId: 11, ColumnName: Segment1011 Returns 1 at start of new group or null otherwise. As no Partition By in the SUM only the first row gets 1 (Same as Segment1010)
NodeName: Window Spool, NodeId: 10, ColumnName: WindowCount1012 Attribute that groups together rows belonging to a window frame. This window spool is using the "fast track" case for UNBOUNDED PRECEDING. Where it emits two rows per source row. One with the cumulative values and one with the detail values. Though there is no visible difference in the rows exposed by query_trace_column_values I assume that cumulative columns are there in reality.
NodeName: Stream Aggregate, NodeId: 9, ColumnName: Expr1004 Count(*)  grouped by WindowCount1012 according to plan but actually a running count
NodeName: Stream Aggregate, NodeId: 9, ColumnName: Expr1005 SUM(weight)  grouped by WindowCount1012 according to plan but actually the running sum of weight (i.e. cume_weight)
NodeName: Segment, NodeId: 7, ColumnName: Expr1002 CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005] END - Don't see how COUNT(*) can be 0 so will always be running sum (cume_weight)
NodeName: Segment, NodeId: 7, ColumnName: Segment1013 No partition by on the LEAD so first row gets 1. All remaining get null
NodeName: Sequence Project, NodeId: 6, ColumnName: RowNumber1006 row_number() within group indicated by Segment1013 flag. As all rows are in the same group this is ascending integers from 1 to  4
NodeName: Segment, NodeId: 4, ColumnName: BottomRowNumber1008 RowNumber1006 + 1 as the LEAD requires the single next row
NodeName: Segment, NodeId: 4, ColumnName: TopRowNumber1007 RowNumber1006 + 1 as the LEAD requires the single next row
NodeName: Segment, NodeId: 4, ColumnName: Segment1014 No partition by on the LEAD so first row gets 1. All remaining get null
NodeName: Window Spool, NodeId: 3, ColumnName: WindowCount1015 Attribute that groups together rows belonging to a window frame using the previous row numbers. The window frame for LEAD has max 2 rows (the current one and the next one)
NodeName: Stream Aggregate, NodeId: 2, ColumnName: Expr1003 LAST_VALUE([Expr1002]) for the LEAD(cume_weight)


This should work:

SELECT
    LEFT(fieldtosplit
        , CASE WHEN LEN(fieldtosplit) > 1000 THEN 1000 ELSE LEN(fieldtosplit) END
    )
    , CASE WHEN LEN(fieldtosplit) <= 1000 THEN '' ELSE
        SUBSTRING(fieldtosplit, 1001
            , CASE WHEN LEN(fieldtosplit) > 2000 THEN 1000
                ELSE LEN(fieldtosplit)-1000 END
        )
    END
    , CASE WHEN LEN(fieldtosplit) <= 2000 THEN '' ELSE
        SUBSTRING(fieldtosplit, 2001, LEN(fieldtosplit)-2000)
    END
FROM (SELECT fieldtosplit = CAST(fieldtosplit as varchar(3000) FROM data)

I am not sure why you need to do that and it may be a bad idea. Perhaps something should be changed in your DB design in order to avoid this logic and this type of string manipulation. But I don't have enough information to be sure.

You are talking about 3000 characters and 1000 characters but you are using DATALENGTH ans 1 CAST AS VARCHAR:

LEN seems to be better than DATALENGTH since you want a number of characters, not a number of bytes
CAST does not seem useful here or perhaps you have NVARCHAR data or something wrong in the design


Parse hyphenated word to unsigned integer

I think it would be better to work around your problem: simply replace hyphens with spaces in the strings to be ts_vectored.  If necessary, you can store the unhyphenated values in a separate column and run your searches against it.


By putting only my IP address in the scope means I've blocked everyone else apart from me connecting to the remote database?

Correct.

  Exceptions that you add to the firewall can restrict the opening of
  the port to incoming connections from specific computers or the local
  subnet. This restriction of the scope of the port opening can reduce
  how much your computer is exposed to malicious users, and is
  recommended.

https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver15#BKMK_additional_settings


Two potential workarounds:

Simulate the internal InnoDB table counter. That is, create work tables (with innodb_file_per_table enabled) until you have the internal pointer of table id equal to (1 – id_of_ibd_table_you_need_to_restore).
Manually hex edit the .ibd file, changing the table id.

See Recovering an InnoDB table from only an .ibd file for the full explanation and the detailed steps involved.


Maximum number of columns per index key in Ms SQL Server 2014

There are two limitations which you would probably have to consider.

The maximum number of columns in an index key is 16, unless you have an XML column

  Columns per index key

  Note: If the table contains one or more XML indexes, the clustering key of the user table is limited to 15 columns because the XML column is added to the clustering key of the primary XML index. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the limitation of a maximum of 16 key columns. For more information, see Create Indexes with Included Columns.

The maximum number of bytes in an index key is 900.

  Bytes per index key

  Note: The maximum number of bytes in any index key cannot exceed 900 in SQL Server. You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the maximum index key size of 900 bytes.

Maximum Capacity Specifications for SQL Server (Microsoft)


You can cascade action by defining a Foreign Key to those tables.
Here is an example from Postgresql documentation

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE CASCADE,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Here, deleting product or order deletes all corresponding items in order_items. You can apply the same for your case.

In case you want to delete all items in primary table and delete corresonding record, it is possible with Truncate cascade. Something like

TRUNCATE TABLE products CASCADE

This will delete all data in products plus all dependent tables.

Apart form these two, you have to somehow manage the deletion yourself. I would in that case use scripting language, and I don't know much about PL/SQL


Maintenance plan for creating transaction log backups

You might want to read up on the concept of Transaction Log Backups in the following Microsoft Technet Article: SQL Server - Understanding SQL Server Backups

If you set up a maintenance plan that creates a transaction log backup of all your user databases, then the backup job will create a *.TRN (default extension) file for each database and for each scheduled run.

Depending on the retention period you set in your maintenance plan, you will/can have literally thousands of transaction log backup files (*.trn) in your backup location.

E.g. Backing up a 10 user databases every 15 minutes and leaving the retention period for clean-up to 30 days, will leave you with 28'800 transaction log backup files lying around.

10 db * 1 trn/db/15min * 1440 min/d * 30d = 28'800 trn files

db = database
trn = trn backup file
min = minutes
d = day

So depending on the size of the databases and the amount of changing data, you could end up needing quite a lot of disk space for your transcation log backups. Then again, having a short retention period in your maintenance plan could result in lower disk space requirements.

Pssst, after reading the Microsoft article, have a look at some alternative backup solutions, like Ola's Maintenance script or other alternative solutions.


Just alter SID to SERVICE_NAME:

creds=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = aaa.cedd.net)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = creds)
    )
  )


A short way to execute a dynamic SQL string

I've found a rather straight way to do this:

CREATE OR REPLACE FUNCTION PARSE_(STR VARCHAR2)
RETURN DATE
IS
    c NUMBER;
    r INTEGER;
    val DATE;
BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(c, 'SELECT ' || str || ' FROM DUAL', DBMS_SQL.NATIVE);
    DBMS_SQL.DEFINE_COLUMN(c, 1, val);

    r := DBMS_SQL.EXECUTE(c);
    LOOP
        IF DBMS_SQL.FETCH_ROWS(c) > 0 THEN
            -- get column values of the row
            DBMS_SQL.COLUMN_VALUE(c, 1, val);
        ELSE
            EXIT;
        END IF;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(c);

    RETURN val;

EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
        DBMS_SQL.CLOSE_CURSOR(c);
    END IF;

    RETURN NULL;
END;
/

SELECT PARSE_('TO_DATE(''2016-01-01 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')') AS d FROM DUAL;
-- 01.01.2016

And if you want to use it with an all_tab_partitions.HIGH_VALUE, the HIGH_VALUE field has to be converted previously. I've made this in 2 step:

create temp table with TO_LOB(HIGH_VALUE) AS HV field
select from this temp table with PARSE_(TO_CHAR(HV)).


Change database file path with SQL not running

Do not restart sql server service or try to offline the db

What you can do is ADD a secondary log file using

ALTER DATABASE [dbname] ADD LOG FILE ( NAME = N'dbname_log2', FILENAME = N'M:\dbname_log2.ldf' , SIZE = 20480KB , FILEGROWTH = 1024KB )

Check log file usage :

DBCC SQLPERF(LOGSPACE);

This way the db can be online and you can start taking log backups (or if point in time restore is not required, then change to simple recovery) to truncate the log file.


How can I tell if a SQL Server database is still being used?

You would have to be concerned with items that have been purged from the cache and that you have missed, or for databases that have infrequent usage.

Rather than dropping the databases out of hand put them either OFFLINE to prevent access without dropping them or in RESTRICTED_USER mode to limit access. Doing this you can leave them in that state for a month or two to check and see if there is occasional usage.

You could also look to use a server side profiler trace filtering on that database.


Slow connection to SQL Server both locally and by client

There is no way to see performance in the past unless you have a monitoring system in place during the event in question.

Assuming you don't have a monitoring solution in place, you could be prepared in case this happens in future.

For instance, you could look at wait stats over a time period using my wait stats capture script.  The script shows what SQL Server actually waited on over a short period of time.  For instance, if you happened to run that script while the problem in your questions was occurring, it's possible the output might show thread starvation via the THREADPOOL wait.  Check Paul Randal's site for details about that wait.


Performance difference between SQL Server Linked Server and direct query

A few quick ideas:

The local server initiates a connection to the remote server, runs a query and fetches appropriate rows from the remote server. This all takes time. Worse, if you trace such activity with SQL Profiler, you may find that rows are fetched from the remote server to the local server one at a time, or in very small batches. This is very inefficient, and the more rows it fetches the worse and worse it gets.

If all data is local, none of that happens and the data can be retrieved much more quickly, especially if it is sitting in the data cache.

Even though there may be indexing on tables on the remote server, SQL may not be able to take advantage of them while it can build a local query plan that does take advantages of indexing.

For very large remote result sets (in terms of data size), you may run into issues where SQL needs to grow data or log files to accommodate the data. 7 seconds probably doesn't qualify, since it probably isn't bringing back much data.

Lastly, if the remote query is to a production system and the local query is to a dev or test system, load on the production server (which does not exist on the dev server) may cause blocking of the remote query. This would also slow down the observed performance of the remote query.


How to list all constraints of a table in PostgreSQL by schema?

Specifically for the "not null" part, thanks to psql -E to see what happens when you do \d+, and adapted/simplified from it, the following works:

test=# CREATE TABLE nonull (id integer NOT NULL);
CREATE TABLE
test=# \d+ nonull
                        Table "public.nonull"
 Column |  Type   | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
 id     | integer | not null  | plain   |              |

test=# SELECT a.attname, a.attnotnull FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='nonull') AND a.attnum > 0 and NOT a.attisdropped;
 attname | attnotnull
---------+------------
 id      | t
(1 row)


PostgreSQL: Another seq scan where an index scan is expected

Since you are on 9.3, can you try this rewriting:

SELECT
    participants.contest_number,
    points.track_id,
    points.lat,
    points.lon,
    points.alt,
    points.timestamp,
    points.h_speed,
    points.v_speed,
    points.distance
FROM
    ( SELECT track_ptr_id, participant_id
      FROM tracks_competitiontrack
      WHERE task_id = 24
        AND online = true
    ) AS tracks
  LEFT JOIN
    competitions_contestparticipant AS participants
      ON tracks.participant_id = participants.id
  JOIN LATERAL
    ( SELECT p.*
      FROM tracks_competitiontrackpoint AS p
      WHERE p.track_id = tracks.track_ptr_id
        AND p.timestamp <= 1409663400
      ORDER BY p.track_id, p.timestamp desc, p.id
      LIMIT 1
    ) AS points ON TRUE
ORDER BY
    points.track_id ;

I think that with indexes on tracks (task_id, online, track_ptr_id, participant_id) and on pooints (track_id, timestamp desc, id), it would preform better, assuming that the condition (WHERE task_id = 24 AND online = true) restricts to a small number of rows.


MariaDB handling composite unique indexes that are foreign keys at the same time

This is documented behaviour:

  MySQL requires indexes on foreign keys and referenced keys so that
  foreign key checks can be fast and not require a table scan. In the
  referencing table, there must be an index where the foreign key
  columns are listed as the first columns in the same order. Such an
  index is created on the referencing table automatically if it does not
  exist. This index might be silently dropped later if you create
  another index that can be used to enforce the foreign key constraint.
  index_name, if given, is used as described previously.

Multiple indexes over the same columns are allowed, but deprecated (as it is redundant- there is almost nothing that the non-unique key can do that the unique index cannot), and may error out in the future (although probably not for different index types like this case).

My suggestion, to prevent confusion, is to just create the unique index (or any explicit index that is adequate with a custom name) from the beginning. I agree with you, that this could be confusing, so looking at the manual is always a great idea, and I believe the current implementation make sense from a compromise between control and optimization perspective. :-)


Recovering InnoDB database from a full physical backup?

Put all the files back where they were.
Start mysql/mariadb.

Caveat:  If MySQL/MariaDB was running when you took the physical dump, there may be issues.


How do you increase MySQL performance using current server resources?

Here is your original query from the slow log

SELECT COUNT(inboxentities.id) FROM inboxentities
WHERE (active=true)
AND (deleted=false)
AND ((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0'))
AND (notification=true);

The most effective way to index for a query like this is to generate an index that covers as many of the fields in the WHERE clause as possible. An index produced with that concept in mind is called a covering index.

Look at the WHERE clause

'active' is true or false (2 values). An index of just that field is lopsided.
'deleted' is true or false (2 values). An index of just that field is lopsided.
'notification' is true or false (2 values). An index of just that field is lopsided.
'to_' is a values worth indexing and narrowing down.

Although you have idx_complex defined, there is one more element to the query causing the idx_complex index not be used: the COUNT(inboxentities.id) clause.

You are counting something in a table. The index has no reference point to the table except to_. The MySQL Query Optmizer would choose idx_to, the simplest index. To force the MySQL Query Optimizer to choose the covering index you want (idx_complex) just COUNT from the index rather than the table. My suggestion is to change the query slightly:

SELECT COUNT(to_) FROM inboxentities
WHERE (active=true)
AND (deleted=false)
AND ((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0'))
AND (notification=true);

Give it a Try !!!

UPDATE 2011-08-15 15:16 EDT

Some have seen marginal-to-significant performance increases scaling up by changing my.cnf to satisfy Storage Engine performance needs.

You need to set the MyISAM Key Cache and InnoDB Buffer Pool.

This will recommend the right size MyISAM Key Cache for your given data set:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will recommend the right size InnoDB Buffer Pool for your given data set

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

BTW

(SELECT 0 PowerOf1024) generates the answer in Bytes
(SELECT 1 PowerOf1024) generates the answer in KB
(SELECT 2 PowerOf1024) generates the answer in MB
(SELECT 3 PowerOf1024) generates the answer in GB

If you go beyond this, email me.

Once you have these settings, make sure that the combined number for recommended_key_buffer_size and recommended_innodb_buffer_pool_size does not exceed 75% of installed RAM.

UPDATE 2011-08-15 15:35 EDT

You may want to run mysqltuner.pl on the server to let it tell you how to tweek memory useage for DB Connections. Settings governing memory usage per DB Connection include

join_buffer_size
sort_buffer_size
read_buffer_size

These are usually multiplied by max_connections.

You can get mysqltuner.pl get downloading it as follows from the Linux command line:

wget mysqltuner.pl

The output from it is something like this:

$ perl mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: username
Please enter your MySQL administrative password: (password hidden)

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.47-community-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 7G (Tables: 6)
[--] Data in InnoDB tables: 2G (Tables: 382)
[!!] Total fragmented tables: 84

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12d 20h 24m 6s (54M q [49.462 qps], 555K conn, TX: 287B, RX: 95B)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 9.3G global + 48.2M per thread (1250 max threads)
[!!] Maximum possible memory usage: 68.2G (291% of installed RAM)
[OK] Slow queries: 0% (647/54M)
[OK] Highest usage of available connections: 5% (69/1250)
[OK] Key buffer size / total MyISAM indexes: 256.0M/19.4M
[OK] Key buffer hit rate: 100.0% (51M cached / 10 reads)
[OK] Query cache efficiency: 89.3% (47M cached / 52M selects)
[!!] Query cache prunes per day: 60670
[OK] Sorts requiring temporary tables: 0% (239 temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 47% (1M on disk / 2M total)
[OK] Thread cache hit rate: 99% (103 created / 555K connections)
[OK] Table cache hit rate: 21% (722 open / 3K opened)
[OK] Open file limit used: 0% (70/32K)
[OK] Table locks acquired immediately: 99% (12M immediate / 12M locks)
[OK] InnoDB data size / buffer pool: 2.4G/8.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Increasing the query_cache size over 128M may reduce performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 1G) [see warning above]
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)


INSERT-ing to a table if the the data does not already exits - how do indexes peform in this scenario?

A clustered index IS the table - so if the rows you are adding are already ordered there is not much work to do. A non-clustered index is an extra structure that you will have to maintain - so Inserts, Updates & Deletes will all have an overhead, the more indexes the higher the overhead. Generally, there are some selects for every table (otherwise what is the point of storing the data?), The balance between the improved SELECT performance versus the maintenance overhead can really only be determined for your particular workload - so measure twice & cut once!


SQL Server 2008 R2 error: 15023, user, group, or role already exists

There are a number of things you can try, and the success of each will probably depend on what server role you have.

For starters, if it is just one or two users then honestly, the easiest way is to drop the database user from the restored database, and providing there is an existing server login already, remap the database user to the server login using SSMS. If the server login does not exist then just create it, map the user, and presto! Away we go.

Next option: If you are migrating a large number of users, use sp_help_revlogin. sp_help_revlogin is a Microsoft supplied stored procedure that will help migrate logins from one server to another, including passwords and SIDs. Here is a good article about it SP_HELP_REVLOGIN


how to get a schematic diagram/layout of the an existing database

You can use Reverse Engineer under Database menu:

You just need to rearrange the tables.


How to get 5min date interval from stock data

For recent versions of SQL-Server that support CTEs (and not the obsolete 2000):

Using a CTE and copying a trick of @Paul White in this answer (to get TOP in the recursive part of the CTE): How to recursively find gaps where 90 days passed, between rows

WITH CTE AS
(
    -- Anchor:
    -- Start with the earliest date in the table
    SELECT TOP (1)
        T.id, T.update_time, T.price
    FROM tableX AS T
    ORDER BY
        T.update_time

    UNION ALL

    -- Recursive part
    SELECT
        SQ1.id, SQ1.update_time, SQ1.price
    FROM
    (
        -- Recursively find the earliest date that is
        -- more than 5 minutes after the "current" date
        -- and set the new date as "current".
        -- ROW_NUMBER + rn = 1 is a trick to get
        -- TOP in the recursive part of the CTE
        SELECT
            T.id, T.update_time, T.price,
            rn = ROW_NUMBER() OVER (
                ORDER BY T.update_time)
        FROM CTE
        JOIN tableX AS T
            ON T.update_time >= DATEADD(minute, 5, CTE.update_time)
    ) AS SQ1
    WHERE
        SQ1.rn = 1
)
SELECT id, update_time, price
FROM CTE ;

A cursor solution, that works in older versions (I don't have a 2000 instance to test and the following will probably need adjustments). Modified from another answer, in the same question How to recursively find gaps where 90 days passed, between rows:

(first, some needed tables and variables):

-- a table to hold the results
DECLARE @cd TABLE
(   id INT NOT NULL,                        -- adjust types
    update_time DATETIME PRIMARY KEY,       -- according to
    price DECIMAL(10,2) NOT NULL            -- your columns
);

-- some variables
DECLARE
    @id INT,
    @update_time DATETIME,
    @price DECIMAL(10,2),

    @diff INT,
    @previous_update_time DATETIME = '1900-01-01 00:00:00' ;

The actual cursor:

-- declare the cursor
DECLARE c CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    SELECT id, update_time, price
      FROM tableX
      ORDER BY update_time ;

-- using the cursor to fill the @cd table
OPEN c ;

FETCH NEXT FROM c INTO @id, @update_time, @price ;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @diff = DATEDIFF(minute, @previous_update_time, @update_time) ;

    IF @diff >= 5
      BEGIN
        INSERT @cd (id, update_time, price)
            VALUES (@id, @update_time, @price) ;

        SET @previous_update_time = @update_time ;
      END ;

    FETCH NEXT FROM c INTO @id, @update_time, @price ;
END

CLOSE c;
DEALLOCATE c;

And getting the results:

-- get the results
SELECT id, update_time, price
    FROM @cd
    ORDER BY update_time ;

Tested at SQLfiddle (in 2008 version).


Wrap all the changes in a transaction and then abort it.

How To Use Transactions in SQL Server Integration Services SSIS


Extracting Company from Multi-tenant Shared Schema Database

Sounds like a job for SSIS  and the Transfer SQL Server Objects Task https://msdn.microsoft.com/en-us/library/ms141134(v=sql.120).aspx

Make sure you install and configure SSIS, and then you should be able to do this with ease.


Why won't PostgreSQL 9.3 start on Ubuntu?

My locale settings were not properly configured when PostgreSQL was installed. Purging and reinstalling didn't help. I followed the instructions here and that did the trick for me.

Essential parts of the linked information reproduced below:

The problem showed itself in the following manner:

warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
...
are supported and installed on your system.

The first one was very easy to solve by executing:

#dpkg-reconfigure locales

...and choosing the preferred locales.

But after that PostgreSQL still refused to start. This is due to the fact that the installation process tried to create a cluster at installation time but because of the bad locales this wasn't done. So we have to redo this step by executing:

#pg_createcluster 9.3 main --start

(For the 9.3 version of PostgreSQL)

After that step PostgreSQL starts flawlessly via

#/etc/init.d/postgresql start


MySQL no longer starting "An attempt was made to access a socket in a way forbidden by its access permissions." (Windows 10)

Found the issue. Even though I rebooted (turned off, turned on), my ethernet connection (via dell dock) was not active. So only wifi was active and apparently windows prevents services from listening to ports on that. Even though there's an exception present for public networks in the firewall.
Anyway, after disconnecting and reconnecting the ethernet cable it started working again.


What is causing my Transaction log to grow

You can review events from the default trace to identify the growth events you care about:

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 93 -- log autogrow event
ORDER BY StartTime DESC;

Combined with a separate trace or extended event session you set up yourself (e.g. to capture all updates with > x duration or > y reads or writes) you can hopefully find some likely culprits around the same time from the same SPID. You won't be able to do that going back in time, of course, but you could set something up to help identify them in the future.

With that all said, your next instinct might be to fight with this process and constantly run up behind it and shrink the log file. This is not a good idea because, really, what's the point? You're going to shrink it to free up space temporarily, only so the same file can grow and fill up the space again? Please read this Q & A in full:

Why Does the Transaction Log Keep Growing or Run Out of Space?


Centralized Backup report for SQL server databases

Use your central repository where you keep information for all servers (Most likely another SQL server which is used as a central repository for DBA stuff) in the firm. Use below tsql query as a start and modify as necessary.  You can loop thru all your servers and collect backup history. That can be another tsql script called via SQL Agent job or PowerShell. Then you will need to set up a threshold for each type of backup and get alerted if the threshold exceeds.

For example if you do a full backup once a week, differential every other days, and transaction log at xx minute interval; you can set up thresholds of 168 hours for full backup, differential 24 hours + some buffer, transaction log xx minute + some buffer.

-- Get Backup History for required database
SELECT
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1048576 AS INT) AS VARCHAR(14))  AS bkSizeMB,
CAST(CAST(s.compressed_backup_size / 1048576 AS INT) AS VARCHAR(14))  AS Compressed_bkSizeMB,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(12)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.backup_start_date>'2017-04-01'  --adjust your date
--Uncomment below lines if you want a one or more type of backup
--AND (s.type='D' OR s.type ='I')
--AND (s.type='L' )
--Uncomment below line if you want to filter by database name
--AND database_name ='security'
ORDER BY backup_start_date DESC, backup_finish_date

As an alternative you can leverage these two PowerShell function to achieve the same result.

Get-DbaBackupHistory
Get-DbaLastBackup


How to do something like UPDATE DELAYED in MySQL

While there is no such command as UPDATE DELAYED, you can create this mechanism in a rather strange way. Please follow my rather crazy train of thought...
SCENARIO : Let's say you have just a single DB Server called M1

You want to fire UPDATEs at a single table called mydb.timing
You don't want UPDATEs to log jam the table

SOLUTION : Serialize UPDATEs
MECHANISM : Would you believe MySQL Replication ?
That's right, I said MySQL Replication.
You are probably saying right now

How in the world would having a Slave create UPDATE DELAYED ?

Here is how you do it:
STEP 01 : Using another DB Server (call it M2), create a MySQL Instance with binary logging enabled.
STEP 02 : mysqldump all user databases with no data, just structures (called it MySQLSchema.sql)
STEP 03 : Edit MySQLSchema.sql, change all ENGINE=MyISAM into ENGINE=BLACKHOLE
STEP 04 : Edit MySQLSchema.sql, change all ENGINE=InnoDB into ENGINE=BLACKHOLE
STEP 05 : Run source MySQLSchema.sql on M2.
STEP 06 : Run RESET MASTER; on M2
STEP 07 : Add replicate-do-table=mydb.timing to /etc/my.cnf on M1
STEP 08 : service mysql restart on M1
STEP 09 : Setup replication from M2 to M1
STEP 10 : In your application, fire your code
set @value=4;
set @name="myAverageValue";
UPDATE `mydb.timing` SET
  `max` = greatest(`max`,@value),
  `average` = `average` + ((@value - `average`) / (`count` + 1)),
  `count` = `count` + 1
WHERE `name` = @name

only at M2
That's it. No locking can ever happen on M2 since mydb.timing on M2 uses the BLACKHOLE Storage Engine (a.k.a /dev/null Storage Engine). Having MySQL Replication from M2 to M1 forms a queue that serializes UPDATEs. The delay appears in the form of MySQL Replication's asynchronous operation. Replication lag (in this instance only) is totally acceptable since you are looking for UPDATE DELAYED.
Give it a Try !!!
CAVEAT
This turns MySQL Replication upside down because the production server becomes a Slave to a BlackHole Server. With this strange topology, you can also implement DELETE DELAYED.
Make sure you include this option
[mysqld]
expire-logs-days=1

on M2. Otherwise, lots of UPDATEs makes for lots of binlogs


Big table with simple queries, but with a lot of text, is it bad for performance?

MySQL itself can handle primary key selects very efficiently. However cached content does not require transactional engine so it would be much faster to store this kind of data in various key-value storages. Memcached is the first one to consider.


How to install MongoDB database as a service in ESxi?

Answer is simple, you cannot do that! MongoDB cannot operate without OS, period!


Trouble producing a query that groups a selection of groups

Wouldn't this work?

SELECT
  sale_value AS "Sale Value",
  EXTRACT(hour FROM sale_time) AS Hour,
  COUNT(sale_value) AS "Total Sales"
FROM t_sale
GROUP BY sale_value, EXTRACT(hour FROM sale_time)
HAVING sale_value IN ('HIGH', 'MED', 'LOW');


Sql get results only containing all keywords

I have found the answer and I will write it here as it may be useful for someone.

First I have altered tables by marking it up on Mysql database and clicking "Fulltext".

To use multiple keywords, use this code (which will get ) before mysql query (in php):

$search="ACDC 2000"; // any keyword
$search=explode(' ', $search );
$Asearch = array();
foreach($search as $result) {
    $Asearch[] .= '+'.$result.'*';
}

Then use this mysql query:

SELECT * FROM songs WHERE MATCH(artist,featuredartist,year) AGAINST('".join(' ',$Asearch)."' IN BOOLEAN MODE)

You will get only results which include all entered keywords.


At a guess, your user isn't the owner of the tables and hasn't been granted rights to them WITH GRANT OPTION so it can delegate them.

The database administrator must do something like:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser WITH GRANT OPTION

and should probably also GRANT similar rights on SEQUENCEs, on the schema its self, etc.

The WITH GRANT OPTION gives the user myuser the right to in turn GRANT the assigned rights to others.

See the manual for details.

You might also be interested in ALTER DEFAULT PRIVILEGES.


Impact on Recovery Time Using A Single Perpetual Base Backup and WAL Archives

I think the answer is "it depends".
In my experience, the thing it depends upon mostly is the amount of data you are restoring, bearing in mind that the amount of data is the base backup plus all of the WALs since that base backup.
This means that:

if you have a large database (base backup) with few UPDATEs/INSERTs, then there will be very little WAL data to restore. In this case you can have a large period between base backups, with little impact on the restore time.
if you have a small database which is heavily UPDATEd/INSERTed then a large amount of your restore time will be spent replaying WALs. In this case having a large period between base backups, will lead to much increased restore times.


moving data from one db to another using SSIS

Create regular data flow with 2 components - OLE DB Source and OLE DB Destination (I assume you are using MS SQL Server, in general, use whatever components your company uses to connect to the DB).

In case of 2 DBs, create 2 connection managers, each pointing to its DB. Point OLE DB Source to first connection manager configured to point to source of data, and OLE DB Destination to second connection manager configured to point to destination DB.

Now point OLE DB Source to the source table in source DB, leave all the fields intact. Connect source and destination components with green arrow originally going out of source component. Now point OLE DB Destination to the destination table in target DB. Double-click destination, go to mappings and make sure they are correct (SSIS tries to map automatically using strick name matching), otherwise (in case names are different) connect source and destination fields manually. That's it, you just don't provide mappings for the fields which cannot be accommodated by destination table.

Alternatively, you can leave out the columns you don't need at source component - double-click it, go to Columns and uncheck columns you don't need.


How to get SQL Server CPU and Memory read and write usage within two weeks?

You can query sys.dm_os_ring_buffers DMV to extract information from it. Please note sys.dm_os_ring_buffers is Undocumented Command but safe to use.

NOTE: These queries are tested on from SQL Server 2008 R2 to 2014. I have not tested them on 2008 or 2005.

To get information about SQL Server memory usage  you need to query sys.dm_os_ring_buffers and filter out for "RING_BUFFER_RESOURCE_MONITOR". Below is the sample query. If the query only produces results for current date and you want for previous date you can use GETDATE() -1 instead of GETDATE() in the query

;with RingBufferMemoryMonitoring
as
(SELECT

 x.value('(//Record/MemoryRecord/MemoryUtilization)[1]',
       'bigint')
       AS Memory_utilization_Percentage ,
       x.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]',
       'bigint') as [TotalPhysicalMemory_KB] ,
       x.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]',
       'bigint') as [AvailPhysicalMemory_KB] ,
x.value('(//Record/@time)[1]', 'bigint')
 as [Record_Time]
FROM  (SELECT Cast (record AS XML)
 FROM   sys.dm_os_ring_buffers
 WHERE  ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS R(x)
 )
 select RingBufferMemoryMonitoring.*, Dateadd (ms, RingBufferMemoryMonitoring.[record_time] - SI.ms_ticks, Getdate()) AS
       Notification_time
      from  RingBufferMemoryMonitoring
 cross join sys.dm_os_sys_info SI

Below is another query,I took the query from This Blog, to get CPU utilization history. If you want for previous date you can use GETDATE() -1 instead of GETDATE() in the query

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info

SELECT top 20 record_id, EventTime,

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization

FROM

(

  SELECT

    record.value('(Record/@id)[1]', 'int') AS record_id,

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2

  FROM (

    SELECT timestamp, CONVERT (xml, record) AS record

    FROM sys.dm_os_ring_buffers

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'

      AND record LIKE '%<SystemHealth>%') AS t

) AS t

ORDER BY record_id desc

If you want to read more about how to extract information from  Sys.dm_os_ring_buffers please read Inside Sys.dm_os_ring_Buffers


Joining two tables with different fields through a link-table

People and Events are related thorugh a many-to-many relationship, because an event can have many people related to it, and one person can be related to several events.

This is represented in a SQL database by means of a link-table, which contains (at least) two columns (fields), one indicating the unique id representing the Event, the other one indicating the unique id representing the Person (People). Let's call those columns EventId and PeopleId. The table might contain more columns if they should provide some relevant extra information (let's say you want to know which role the person had with regard to this event, such as "was the organiser", "was an attendant", "was a sponsor", "was the inducer", "led the event", "was one of the lead roles", "just was a passer-by", "was the victim", ...).

The primary key of this table is actually a compound one, comprising both (EventID, and PeopleID). So your table should be defined in a fashion similar to the one depicted in the following image:

You can let Access know how this table is related to the other ones by means of the Relationships Diagram:

The corresponding SQL definition is:

CREATE TABLE [People_x_Events]
(
    [PeopleID] LONG NOT NULL REFERENCES [People] ([ID]),
    [EventID]  LONG NOT NULL REFERENCES [Events] ([ID]),
    PRIMARY KEY ([PeopleID], [EventID])
) ;


Is there a way that we could backup the database in number of smaller parts or change the backup path?

Sure - Have a look at Backup to multiple files for faster and smaller SQL Server files.

Problem

Have you ever wished you could get your backups to run faster? Well there may be a way, by writing to multiple files. Creating SQL Server backups is pretty simple to do; you can use the SQL Server management tools or you can use T-SQL commands to issue the backup. But sometimes with large databases it takes a long time to run and it is difficult to copy this very large file across the network or even to your backup tapes.

Solution

Write your database backup to multiple files. In addition to writing your database backup to one file you have the ability to write to multiple files at the same time and therefore split up the workload. The advantage to doing this is that the backup process can run using multiple threads and therefore finish faster as well as having much smaller files that can be moved across the network or copied to a CD or DVD. Another advantage to writing to multiple files is if you have multiple disk arrays you can write your backup files to different arrays and therefore get better I/O throughput. One thing to note is that to maintain the I/O throughput you should keep the writing of your backup files and the reading of the database file on different disk arrays.

Backup to multiple files using T-SQL

BACKUP DATABASE [Northwind] TO
DISK = 'C:\Northwind_file1.bak',
DISK = 'D:\Northwind_file2.bak',
DISK = 'E:\Northwind_file3.bak',
DISK = 'F:\Northwind_file4.bak'
WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT

In reference to your question

  Or If there is any way that I could change the database backup path to
  my local drive.

It's important to note that when you are running backups against a particular SQL Server instance, the backup drives you use in the BACKUP command are drives known by the server that the SQL Server instance is running on.  Now, you could also use a fileshare (assuming you have the proper permissions to write to it).  As far as writing to your local drive, you might be able to achieve that if you create a fileshare on your computer and use that fileshare in your BACKUP command.

I ran a quick BACKUP on one of our 100+gb play databases using 4 backup files.  It appears that BACKUP certainly tried to divide the data as evenly as possible.

An important reminder is

  All of the files must be available, and all must be referenced in a
  restore session.

Some additional valuable information can be found at Improving the Performance of Backups


Optimal query to show active records by date

So I happened to have stumbled on a more efficient pattern for this sort of operation (based on how much faster this is in testing).

Given the above, here's what it would look like (I've excluded fields beyond what are necessary to point to a compoent_data record):

Updated Query:

WITH cd_start_end AS (
    SELECT
        id,
        owner_rel_id,
        generated_date AS start_date,
        COALESCE(removed_date, now()) AS end_date
    FROM
        component_data
    ORDER BY
        id
), active_dates AS (
    SELECT
        id,
        generate_series(start_date::date, end_date::date, '1 day'::interval)::date AS active_dates
    FROM
        cd_start_end
)

SELECT
    *
FROM
    active_dates
WHERE
    active_dates >= '2013-01-01'
    AND active_dates < now()::date;


Dynamic Data Masking (DDM) can fulfil the requirement as long as the Least Privilege Principle followed, since by default members of sysadmin server role and members of db_owner database role can always see UNMASKED (Including Encrypted columns).
If the requirement as such that even sysadmin role members must only see the encrypted data then the better fit will be Always Encrypted feature...
Or, if the goal is just to protect some sensitive data within database when it restored into DEV/UAT environments, better look for reliable third-party solution, or you can also develop you're own stored procedure/script that can be executed (manually) in DEV DB every time a restoration happened.


How can I select records which are between 2 dates based on a date in a field in PostgresSQL?

If I understand your problem correctly, you have to join the other table to historical_adresses based on the unique ID and that the visit time falls between startdate and enddate.

If this is so, the query could look like:

SELECT [address_elements]
FROM historical_adresses AS ha
JOIN visits AS v
    ON ha.user_id = v.user_id
    AND v.visit_time BETWEEN ha.startdate AND ha.enddate
WHERE [find the visit somehow that you are interested in];

Note: you usually don't need all columns of a table (and definitely not from a join), so use the actual column names rather than *.


Table aliasing is a common and helpful practice.

It saves you keystrokes when referencing columns anywhere in your query.
It improves the readability of your SQL when you are referencing many tables. Aliases let you give those tables a short name plus a little meaning to how they are being used.
It is even required when you join a table to itself or when you join to the same table multiple times. This is so the query optimizer knows which table you are referencing when you mention a column.

The following reporting extract illustrates all of the above points nicely:

INSERT INTO reporting.txns_extract
SELECT
    -- 30+ columns snipped
    --
    -- Would you want to type out full table names for each
    -- column here?
FROM
    -- ... and in the JOIN conditions here?
                billing.financial_transactions  ft_cdi   -- alias required here
    INNER JOIN
                billing.cash_application_links  cal
            ON  ft_cdi.key_num = cal.applied_ft_key_num
    INNER JOIN
                billing.financial_transactions  ft_pmt   -- alias required here
            ON  cal.owner_key_num = ft_pmt.key_num
    LEFT OUTER JOIN
                billing.invoice_lines           invl
            ON  ft_cdi.key_num = invl.invoice_key_num
    LEFT OUTER JOIN
                billing.charges                 chrg
            ON  invl.creator_key_num = chrg.key_num
    LEFT OUTER JOIN
                billing.customer_services       cs
            ON  chrg.cs_key_num = cs.key_num
    INNER JOIN
                billing.billers                 bil
            ON  ft_cdi.biller_account_key_num = bil.biller_account_key_num
    INNER JOIN
                billing.formal_entities         fe
            ON  bil.frml_key_num = fe.key_num
WHERE
    -- ... and in the WHERE conditions here?
        ft_cdi.transaction_type <> 'Payment'   -- alias tells me this table is not for payments
    AND ft_cdi.status = 'Approved'
    AND ft_pmt.transaction_type =  'Payment'   -- alias tells me this table is for payments
    AND ft_pmt.status = 'Approved'
    AND ft_cdi.last_user_date >   ft_last_user_date_begin
    AND ft_cdi.last_user_date <=  ft_last_user_date_end
;


How to create a unique column in SQL Azure Federation

You can only have uniqueness validation within a single federation, same as partitioned table allow for aligned uniqueness validation only within a partition (federations don't have an 'unaligned' equivalent). The solution is trivial: create a separate, non-federated,  Azure DB used only to validate email uniqueness.


Will creating partitions reduce locking and how do we implement this in sql-server?

Software can't fix this if the underlying IO/disks are opaque to you.

If you add partitions, you won't spawn 30 threads
If all your partitions are on the same volume you'll decrease throughput

I've worked on similar systems where we had

a staging DB
SIMPLE recovery on staging
deferred deletes (eg a new or updated run is an INSERT) that run out of hours
each run has a header row to track this status
flush to real DB when client is ready

We've also

removed FKs and other tweaks to the main table
put the staging DB onto separate volumes.


Is there a specific SQL Server 2008 R2 Permission to run DBCC CHECKIDENT?

I think that MS-Docs on DBCC CHECKIDENT are really clear:

Permissions

  Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.


Optimize MySQL Query with multiple subqueries

Add an index to (post_id) in postlocks, remove the subquery against that table and the reference to that column in HAVING, and add WHERE NOT EXISTS (SELECT * FROM postlocks pl WHERE pl.post_id = p.id).

...for a start.

You want that condition to be evaluated early since it will eliminate some of the other lookups, and this should help ensure that the optimizer does that.  And the column needs to be indexed, otherwise you're scanning the table, as indicated by type = ALL in dependent subquery 2.


Should I expect better performance out of MySQL?

You need an index beginning with UserId.  It sounds like you have INDEX(Events, UserId).  That was somewhat helpful, but it still had to scan the entire index.

More on building optimal indexes.


AWS RDS MySQL some time get slow performance when update table1 set field='any word' where primarykey=1234

Finally was the auto commit as mustaccio told me in a comment:

  Looks like your "sleep connections" are not committing their transactions and thus keeping locks. Revise your application logic.


MySQL - reformat a column value dependant on existing value

In Mysql you can use CONCAT function to concatenate values ,you can use your logic in if() to show yes/no according to the criteria.if you want to show the value from column when its not empty then replace 'Yes' with your column name

SELECT
   `d`.`FirstName` AS `First Name`,
   `d`.`LastName` AS `Last Name`,
   `o`.`Name` AS `Organisation`,
    IF (`d`.`PhoneWork`  IS NOT NULL AND `d`.areacode IS NOT NULL,CONCAT(`d`.`PhoneWork`,' ',`d`.areacode),'your value') AS `concat_colum`,
    IF (`d`.`PhoneWork`  IS NOT NULL,'Yes','No') AS `WorkPhone`,
    IF (`d`.`areacode`  IS NOT NULL,'Yes','No') AS `areacode`,
   `d`.`EmailAddress` AS `Email Address`,
FROM `delegate` d
JOIN `organisation` o ON(`d`.`OrganisationID` = `o`.`ID`)

You can do the same by using CASE too

SELECT
   `d`.`FirstName` AS `First Name`,
   `d`.`LastName` AS `Last Name`,
   `o`.`Name` AS `Organisation`,
    CASE WHEN
    `d`.`PhoneWork`  IS NOT NULL AND `d`.areacode IS NOT NULL
    THEN CONCAT(`d`.`PhoneWork`,' ',`d`.areacode)
    ELSE 'your value' END  AS `concat_colum`,
    CASE WHEN `d`.`PhoneWork`  IS NOT NULL THEN 'Yes' ELSE 'No' END AS `WorkPhone`,
    CASE WHEN `d`.`areacode`  IS NOT NULL THEN 'Yes' ELSE 'No' END  AS `areacode`,
   `d`.`EmailAddress` AS `Email Address`,
FROM `delegate` d
JOIN `organisation` o ON(`d`.`OrganisationID` = `o`.`ID`)


Changing a variable using SET GLOBAL doesn't impacts sessions variable?

In order to change wait_timeout we should also change interactive_timeout variables.

The definition of wait_timeout : The number of seconds the server waits for activity on a noninteractive connection before closing it. On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value.

Here is the test

Query without changing any parameter , all the values are set to 8 hours by default

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                 28800 |                  28800 |                        28800 |                         28800 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)

Change the parameter at session level

SET wait_timeout = 10 , interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Results , all looks good as expected , global setting stay same and session settings are changed

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                 28800 |                     10 |                        28800 |                            10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)

Change the parameter at global level

SET GLOBAL wait_timeout = 10 ;
Query OK, 0 rows affected (0.00 sec)

SET GLOBAL interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Results , all looks good as expected

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                    10 |                     10 |                           10 |                            10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)

Disconnect MySQL thread and connect again , and all looks good again

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                    10 |                     10 |                           10 |                            10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)


Copy tables to another SQL Server keeping the identity property

It might be easiest for a one-off exercise to use the Generate Scripts SSMS feature to script out the tables. Right-click the database, choose Tasks, then Generate Scripts.
Be sure to check the advanced options so you script exactly what you need. That will create the tables (and, optionally, things like indexes) including the IDENTITY property.
You can then load the tables using any convenient method, including the Import and Export Data Wizard you already have experience with. You will need to select the "enable identity insert" option because the destination tables will have that property now.
Yes, it would be nice if the import and export wizard handled identity, but it is a general tool designed for multiple types of data source, and identity is proprietary.


Select one single row from table b for each row from table a

How about this:

Select *
from
  tableB b
 inner join
  (select a.*, row_number() over (partition by a.type order by a.type) rn from tableA a) a1
    on b.type=a1.type and a1.name='SEARCHED_NAME' and a1.rn=1

PS. Not tested, as you didn't provide a script to create and populate the tables.


So far as I know, you can't do a full outer join in MySQL, so just run it as the UNION of a LEFT JOIN and a RIGHT JOIN as

SELECT * FROM
cajas LEFT JOIN almacenes ON almacenes.codigo = cajas.almacen
UNION
SELECT * FROM
cajas RIGHT JOIN almacenes ON almacenes.codigo = cajas.almacen;

I think this would fix your problem.

EDIT: After a quick Googling, I realized that the above may not be perfect for all cases.  Instead you could do

SELECT * FROM
cajas LEFT JOIN almacenes ON almacenes.codigo = cajas.almacen
UNION ALL
SELECT * FROM
cajas RIGHT JOIN almacenes ON almacenes.codigo = cajas.almacen
WHERE cajas.almacen IS NULL;

See this blog post for reference.


How to connect MS sql database and php using xamp

You don't have the MS SQL Drivers installed. You can check this with phpinfo();

On Linux you need mssql.so or sybase.so With debian its apt-get install php5-sybase

For windows take a look here: http://msdn.microsoft.com/en-US/library/cc793139%28v=SQL.90%29.aspx

Drivers need to be configured for PHP to find the function mssql_...

You could also look at PDO DB classes as they can connect to any DBS, you need the drivers installed.


Wiki answer generated from question comments by SDillon

No; at least in versions prior to 2.6. After 2.6 I am not 100% sure.

I've had this happen in development, thankfully a while ago. I dropped the collection and recovered the data to it. Here is a MongoDB email group post regarding this topic and your exact challenge.

Another mistake I made, once, was to monkey around in the config database and attempt to remove the shard info that way. I ruined that dev cluster.


The right way to model multiple FACTs schema

So the first question is: Are you absolutely sure you need to use a star schema?  Having worked in a number of warehouses built to varying levels of quality, I have found star schema to be: 1. Inflexible 2. Difficult to use (tons of joins) 3. Really bad for time series.
The way you have described things (Product, Product State, Test) along with the need to track points in time would generate something like this (slight embellishment for party/test):

If you need to get the state as of a certain test this is attainable by a simple join, which can be encapsulated in a view so users don't have to worry about getting the logic correct:
SELECT
  ProductTest.ProductId
 ,ProductTest.TestDtm
 ,ProductTest.TestCd
 ,<Other relevant columns>
 ,ProductState.OwnerId
 ,ProductState.<Location info> --Don't know if this is address or lat/long, use whatever works best for you
FROM
  ProductTest ProductTest
LEFT JOIN
  ProductState ProductState
    ON ProductState.ProductId = ProductTest.ProductId
         AND ProductState.UpdateDtm =
               (
                 SELECT
                   MAX(UpdateDtm)
                 FROM
                   ProductState
                 WHERE
                   ProductId = ProductTest.ProductId
                     AND UpdateDtm <= ProductTest.TestDtm
               )


Copying my Oracle 10g Express database to another PC

First Install XE to another PC.

Then clone your database: Backing Up and Restoring the Database

Then transfer clone to another PC and restore the clone there.

This post may help you also: How to clone Oracle Express 10g Edition?


MySQL Replication query - Primary key need

Your assumption is incorrect.

The hidden clustered index does nothing to help, here, because the server can't use that index to find rows in a way that replication needs.

This causes some problems when the master is logging in STATEMENT mode, different problems in ROW mode, and all of the problems, combined, in MIXED mode.  Here's the tl;dr on the biggest pitfall:

Every replication event against a table without a primary or unique key whose value is accessible through the SQL interface has the potential to require a full table scan for each row replicated.

The inaccessibility of the hidden clustered index to the SQL layer excludes it from use... and it wouldn't help, even if it were not as hidden, since its value is not deterministic from server to server, and so its value is never written to the binlog.

Simply enough... you should not create tables without accessible primary keys. It's bad design.

I would argue that a valid table cannot have two identical rows -- every relation has at least one candidate key, by definition, and candidate keys must be unique, also by definition. Choose one and make it primary, or create a surrogate (auto-increment) so that it's visible.  If you have unique constraints, you need to define them in the schemata, not just impose them in code.


For a table called cristian, here is the needed query:

select name,SUM(value) "1s" ,SUM(1) "total"
FROM cristian GROUP BY name;

Here is your sample data:

mysql> use test
Database changed
mysql> drop table cristian;
Query OK, 0 rows affected (0.01 sec)

mysql> create table cristian
    -> (
    ->     id int not null auto_increment primary key,
    ->     name varchar(25),value int not null
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into cristian (name,value) values
    -> ('nameA',1),('nameA',0),('nameB',0),
    -> ('nameC',0),('nameC',1),('nameC',1);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from cristian;
+----+-------+-------+
| id | name  | value |
+----+-------+-------+
|  1 | nameA |     1 |
|  2 | nameA |     0 |
|  3 | nameB |     0 |
|  4 | nameC |     0 |
|  5 | nameC |     1 |
|  6 | nameC |     1 |
+----+-------+-------+
6 rows in set (0.00 sec)

and here is the query's output

mysql> select name,SUM(value) "1s" ,SUM(1) "total"
    -> FROM cristian GROUP BY name;
+-------+------+-------+
| name  | 1s   | total |
+-------+------+-------+
| nameA |    1 |     2 |
| nameB |    0 |     1 |
| nameC |    2 |     3 |
+-------+------+-------+
3 rows in set (0.14 sec)

mysql>

These would also work

select name,SUM(value) "1s" ,COUNT(1) "total"
FROM cristian GROUP BY name;

select name,SUM(IF(value=1,1,0)) "1s" ,COUNT(1) "total"
FROM cristian GROUP BY name;

Give it a Try !!!


Is it better to use FREETEXT or CONTAINS Full Text Searches when searching by single characters

I'm not sure I'm understanding your question.  FREETEXT evaluates not only on the string, but also the meaning.  Whereas CONTAINS is used for precise or fuzzy searching on a string.


Difficulty getting sp_executesql to work

I think you are attempting to get a list of tables that contain rows referenced in another table.  I think you want to encapsulate this into a re-usable object, such as a stored procedure.

I created a simple schema to test your code, as such:

USE TempDB;
CREATE TABLE dbo.T1
(
    ID INT NOT NULL PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(255)
);
CREATE TABLE dbo.T2
(
    ID INT NOT NULL FOREIGN KEY REFERENCES dbo.T1(ID)
    , SomeOtherData VARCHAR(255)
);

INSERT INTO dbo.T1 VALUES (1, 'Test');
INSERT INTO dbo.T2 VALUES (1, 'Test2');

The following shows the table T2 as output, which I think is what you are attempting to do.

CREATE PROCEDURE dbo.GetReferencedTables
(
    @TableName SYSNAME
    , @RowID INT
)
AS
BEGIN
    DECLARE @OutputTable TABLE
    (
            TableName SYSNAME
    );
    DECLARE @Command nvarchar(max);

    SELECT @Command = isnull(@Command + ' union all ', '') + '
        SELECT ''' + object_name(parent_object_id) + '''
        WHERE EXISTS(SELECT * FROM ' + object_name(parent_object_id)
        + ' WHERE ' + col.name+ ' = ' + cast(@RowId as varchar) + ')'
    FROM sys.foreign_key_columns fkc
        JOIN sys.columns col ON
            fkc.parent_object_id = col.object_id AND fkc.parent_column_id = col.column_id
    WHERE OBJECT_NAME(referenced_object_id) = @TableName;

    --Totally unnecessary to put the results into a table variable
    --unless you want to further manipulate or use the results
    INSERT INTO @OutputTable
    EXEC sp_executesql @Command;

    SELECT * /* NEVER use SELECT * unless you're me.
            see http://bit.ly/1nRziYq for reasons.
            */
    FROM @OutputTable
    ORDER BY TableName;
END
GO
EXEC dbo.GetReferencedTables 'T1',1;

Output:


That sounds like the DBMS_ADVANCED_REWRITE package.  Tim Hall has an excellent walk-through of using that package to point an application's queries against a different table or view.

If you merely want to change the query plan but not point the query at a different table, you can use stored outlines or SQL profiles.

For example, I have tables FOO with 1 row and BAR with 2 rows

SQL> select * from foo;

      COL1
----------
         1

SQL> select * from bar;

      COL1
----------
        66
        77

I can declare a rewrite equivalence saying that queries against FOO should instead hit BAR

begin
  sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE(
    'Rewrite_Foo',
    'select col1 from foo',
    'select col1 from bar',
    false,
    'TEXT_MATCH' );
end;

Now, if I set query_rewrite_integrity to trusted, queries against FOO end up hitting a completely different table.

SQL> alter session set query_rewrite_integrity=trusted;

Session altered.

SQL> select * from foo;

      COL1
----------
        66
        77

That can create some rather interesting query plans where the object you're querying is nowhere to be found in the plan

SQL> select * from foo;

      COL1
----------
        66
        77

Execution Plan
----------------------------------------------------------
Plan hash value: 4224476444

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    26 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| BAR  |     2 |    26 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        584  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed