Global Read Lock for Snapshot of MySQL Data

When you ran SET GLOBAL READ_ONLY=ON; you stop users that do not have the SUPER privilege.

If every user has the SUPER, then setting read_only does not help.

Run this query

SELECT user,host FROM mysql.user WHERE super_priv = 'Y';

Whatever rows appear are the users that can still make changes even with read_only on.

SUGGESTIONS

What you really need to do is run

FLUSH TABLES WITH READ LOCK;

and hold that DB Connection open until you run your snapshot to completion.

How can you do so ? In my post How can I optimize a mysqldump of a large database?, I mention doing this in conjunction with a SLEEP command. Here is an except from that post under the heading "Option 4 : USE YOUR IMAGINATION" Point #2:

  Using separate process, run "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" before launching mysqldumps. Kill this process after mysqldumps are complete. This is helpful if a database contains both InnoDB and MyISAM

Here is some code from my 3-year-old post Writing transactions to .MYD file to take Backup

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

In your particular case, just replace the mysqldump with your LVM snapshot code.

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

*** Snapshot Code Goes Here ***

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

GIVE IT A TRY !!!


Optimize MariaDB query with table that self-joins

What the heck is the intent?  It seems to deliver the ids of all pairs of individuals with the same first_name.

It looks like a "group-wise" max, but not quite.

weight seems to be irrelevant since it is the constant "5".

INDEX(contact_type, first_name, id)

may speed it up, even with 99.9% of contact_types being the desired value.  The speedup is due to the index being "covering".  The order of the columns is important.

It is likely to return a million rows.  That, in itself, will take time.  And possibly choke the client.

Just looking for dups?

Perhaps you want

SELECT GROUP_CONCAT(id), first_name
    FROM t
    WHERE first_name != ''
      AND contact_type = 'Individual'
    GROUP BY first_name
    HAVING COUNT(*) > 1;

Alternatively, SELECT MIN(id), MAX(id), first_name ..., except that it does not give you all the ids when there are more than 2.

Optimal:

INDEX(contact_type, first_name, id)


Recipe Health risk Database ERD structure

You probably want to have some risk information in your NUTRITION table.  Perhaps a text column to describe the risk and a threshold quantity column, which might be optional, to describe what is a safe and an unsafe amount.

The thing you have to decide is whether you want to try to normalize out some other information from your nutrition risk descriptions.  For example, do you want to have a MEDICAL_CONDITION table that allows risks to be categorized in terms of what type of person is at risk (e.g. diabetes, nut allergy, lactose intolerance,...)

From a design perspective, we can't answer your question about what is needed.  That depends on your business rules and what you want to achieve.  We can help with technical design questions based on specific statements of your business rules.


MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

I can understand why this happening
You only have one row in the table.
MySQL Query Optimizer will execute a plan around this rule-of-thumb : If the number of rows needed to examine the fulfillment of a query through an index exceeds 5% of the total of number of rows, the Query Optimizer will not use the index and will go with a table scan or an index scan instead.
How many rows in the table ? One
How many rows are indexed ? One
Since you will be examining 100% of the rows, a keyed lookup will not chosen. The query will be executed. You simply do not have enough rows to justify getting an index involved in the search. This applies in general for anything indexed. FULLTEXT indexes tends to get thrown the Query Optimizer under the bus at the most inconvenient times. I wrote about that back in Jan 26, 2012.
Try loading more rows into the table, at least 21 rows, and try again.


Generate All days in moth report using PIVOT of SQL Server

The result you requested won't help you if you want to use this in any type of data set, cause the column names change with every call, a better solution is to use static column names from 1 - 31 (You might not need dynamic sql in this case), but that is up to your need, maybe you just want to export them directly.

And one more thing, you didn't specify the conditions for scan numbers, what if a user scanned more than 2 times a day, what if he didn't scan the whole month, he won't even appear in the results (and to show him, it requires a different query).

USE [YOUR_DATABASE];

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Ahmad Magdy
-- Create date: 2014-10-10 13:10
-- Description:
-- =============================================
CREATE PROCEDURE [PROCEDURE_NAME]
    @Year INT = NULL,
    @Month INT = NULL
AS
BEGIN
SET NOCOUNT ON;

SET @YEAR = CASE WHEN @Year IS NULL THEN YEAR(GETDATE()) ELSE @Year END;
SET @Month = CASE WHEN @Month IS NULL OR @Month NOT BETWEEN 1 AND 12 THEN MONTH(GETDATE()) ELSE @Month END;

DECLARE @FirstDayOfMonth DATE = CAST(@Year AS VARCHAR(4)) + '-' + CASE WHEN @Month < 10 THEN '0' ELSE '' END + CAST(@Month AS VARCHAR(2)) + '-' + '01';
DECLARE @FirstDayOfNextMonth DATE = DATEADD(MONTH, 1, @FirstDayOfMonth);
DECLARE @LastDayOfMonth DATE = DATEADD(DAY, -1, @FirstDayOfNextMonth);

DECLARE @SQLCode NVARCHAR(MAX) =
'SELECT *
FROM
(   SELECT ap.EMPLOYEE_ID, ap.ScannedTime, CONVERT(DATE, ap.ScannedTime) AS Date, ''SCN'' + CAST(ROW_NUMBER() OVER (PARTITION BY ap.EMPLOYEE_ID, CONVERT(DATE, ap.ScannedTime) ORDER BY ap.ScannedTime ASC) AS VARCHAR) AS Type
    FROM tblEmployeeScan ap
    WHERE ap.ScannedTime >= ''' + CAST(@FirstDayOfMonth AS VARCHAR) + ''' AND ap.ScannedTime < ''' + CAST(@FirstDayOfNextMonth AS VARCHAR) + ''' -- You can use YEAR(ap.ScannedTime) = @Year AND MONTH(ap.ScannedTime) = @Month, but that will not make this query Sargable
) pv
PIVOT
(
    MAX(ScannedTime)
    FOR Date IN (';
DECLARE @i DATE = @FirstDayOfMonth;
WHILE(@i <= @LastDayOfMonth)
BEGIN
    SET @SQLCode = @SQLCode + '[' + CAST(@i AS NVARCHAR) + '], ';
    SET @i = DATEADD(DAY, 1, @i);
END
SET @SQLCode = LEFT(@SQLCode, LEN(@SQLCode) - 1);
SET @SQLCode = @SQLCode + ')
) p
--WHERE Type = ''SCN1'' OR Type = ''SCN2'' -- TODO You can use this query to limit the scans to 2
ORDER BY EMPLOYEE_ID, Type; --OPTION (RECOMPILE)';

PRINT CAST(@SQLCode AS NTEXT);

EXECUTE SP_EXECUTESQL @SQLCode;
END
GO


I was able to resolve this by doing a batched insert of 100m records at a time. The insert completed successfully without error, and I was able to insert 2.88bn records.

The other solution I tested was to insert the records into a table with an identity column but no clustered index, then add the clustered primary key afterward. That also worked.

Not sure why this was occurring inserting into a clustered table. I only tested this on Azure SQL MI, not our on-premise SQL Server 2017. So I don't know if there is any difference there.


Error Code: 1239. Incorrect foreign key definition for 'foreign key without name': reference and table don't match

You need to add column name along with table name.

  CREATE TABLE Department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key(dept_name));

        CREATE TABLE Course (course_id varchar(7), title varchar(50), dept_name varchar(20),credits numeric(2,0), primary key(course_id),
         foreign key(dept_name) references Department(dept_name));

        CREATE TABLE Instructor (ID varchar (5),name varchar (20) not null,dept_name varchar (20),salary numeric (8,2),primary key (ID),
        foreign key (dept_name) references Department(dept_name));


raise an error in sql server severity higher than 18

WITH is an optional clause that goes at the end of the RAISERROR. A few examples:

RAISERROR('Oops', 10, 1)
RAISERROR('Oops', 10, 1) WITH NOWAIT
RAISERROR('Oops', 10, 1) WITH SETERROR

RAISERROR('Oops', 19, 1) -- Fails with "Error severity levels greater than 18..."
RAISERROR('Oops', 19, 1) WITH LOG -- OK

Remember that severities of 20 and above will terminate the connection (won't even jump to a CATCH).


Windows logins are tied to Windows users/groups based on the SID. In other words, you're still able to log in since the Windows user's SID matches, even though the login name still indicates the system name rather than the domain name. If you're worried about the mismatch, you can drop and recreate the login. Since it'll have the same SID, it should map to all the existing database users, and privileges will be preserved. Also, Management Studio seems to think you can hit F2 to rename a login, but I don't have a test system handy to confirm that works.

Of course, all the database users will still have the old name too. You can either script/recreate them, or try the F2 method.

After you've done all this, you'll probably need to change the owner on the jobs. I usually keep all the jobs owned by SQL Server logins (typically sa) so that they don't all fail if there's a temporary problem with the DC.


UPDATE SET REPLACE() matches but does not change

Look carefully at the query's output

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The expression Rows matched is based on WHERE bug_id=452;. That matched.

When you executed it, the change evidently failed because the short_desc column didn't change because short_desc does not equal 'I don\'t know why my previous comment means.'

To verify that run this:

SELECT REPLACE(short_desc,'I don\'t know why my previous comment means.',
'I don\'t know what my previous comment means.') replace_result
FROM bugs_fulltext WHERE bug_id=452;

If you getback the old value, then the old value was not in the row to begin with.

You should just directly update it (overwrite it):

UPDATE bugs_fulltext
SET short_desc = 'I don\'t know what my previous comment means.'
WHERE bug_id=452;


SSDT publish multiple referenced database projects to new instance

I've been able to get all databases in my solution to publish by doing the following:

Setting the correct connection string and publish options under Project Properties > Debug for each project in my solution.
Assigning multiple startup projects in Solution Properties
Debugging the solution will then build/deploy all databases in the correct order

Though this doesn't feel like it is the correct way to achieve this as it now means I need to modify all my projects if I want to publish to a different server.


PROBLEM #1
Your major problem is having at least 10000 tables
Here is the issue I see
RDS Instance was All InnoDB
How many file handles are open if you have 10000 InnoDB tables ?

One file handle for the .frm file
One file handle for the .ibd file

This would be a maximum of 20000 open file handles
RDS Instance was All MyISAM
How many file handles are open if you have 10000 MyISAM tables ?

One file handle for the .frm file
One file handle for the .MYD file
One file handle for the .MYI file

This would be a maximum of 30000 open file handles
PROBLEM #2
You are using m1.medium. That only has 3.75 GB RAM.
PROBLEM #3
Having 10000 tables can be very memory intensive because of the amount of metadata to maintain
See my old posts about INFORMATION_SCHEMA's memory consumption

Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory?
Apr 21, 2014 : Adding new tables -- memory usage increases

PROBLEM #4
Your Production Server probably has too many open DB Connections that are dormant but consuming memory (See my post How costly is opening and closing of a DB connection?)
ANALYSIS
Your problem could simply be having too many open tables at the moment you are trying to create a table (which needs 2-3 file handles) or an alter table (which needs 2-3 file handles). I have seen this happen to me trying to create a partitioned table and running out of file handles. I wrote about this in my old post What are the possible risks involved in partitioning large database tables? where I raised the ULIMIT on the OS (bare metal server) to address it. I know changing ULIMIT does not help you because you are in RDS.
SUGGESTIONS
Simply run
mysql> FLUSH TABLES;

and it will close all tables that have open files handles.
If you prefer to close specific open tables, you can list the open tables with
mysql> SHOW OPEN TABLES FROM <database>;

Then, you must close the tables explicitly like this
mysql> FLUSH TABLES db1.tb1,db2,tb2,db3.tb3;

After running FLUSH TABLES;, then you can run CREATE TABLE or ALTER TABLE.
You may also need upgrade to another server model with much more RAM.
You could reduce the number of tables but archiving the data and removing inactive databases. This will reduce RAM consumed for the INFORMATION_SCHEMA.
You should also close all your DB Connections that are not being pooled.
GIVE IT A TRY !!!


Designing Simple Schema for Disaggregation of Demand Forecast

Okay, so here's the data model I came up with.

PRODUCT -- to store product information and maintain parent-child hierarchy

id  NUMBER  "Primary Key Not Null"
level_code  VARCHAR2    Not Null
name    VARCHAR2    Not Null
description VARCHAR2
parent_id   NUMBER  Foreign Key references PRODUCT(id)

ORDERS -- to store orders for products

id  NUMBER  "Primary Key Not Null"
prod_id     NUMBER  "Foreign Key references PRODUCT(id) Not Null"
order_type  VARCHAR2    "Not Null Default 'Default'"
order_qty   NUMBER  Not Null
order_date  NUMBER  Foreign Key references DATE_INFO(date_key)
delivery_date   NUMBER  "Foreign Key references DATE_INFO(date_key)
Check delivery_date >= order_date"

FORECAST -- to store forecast value for products (store value for higher levels, store value for lower levels after disaggregation from a parent)

id  NUMBER  "Primary Key Not Null"
product_id  NUMBER  "Foreign Key references PRODUCT(id) Not Null"
forecast_value  NUMBER  Not Null
week    NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null"

DISAGGREGATION_RULES -- to store which method was used for disaggregating a value from a higher level to lower level and how much percentage got distributed to lower level

id  NUMBER  "Primary Key Not Null"
parent_product_id   NUMBER  "Foreign Key id references PRODUCT(id) Not Null"
child_product_id    NUMBER  "Foreign Key id references PRODUCT(id) Not Null"
method  VARCHAR2    Not Null
from_week   NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null"
to_week NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null Check end_week >= start_week"
percent_distribution    NUMBER  Not Null

DATE_INFO -- date dimension, has information about start date (has to be Saturday) and end date corresponding to the week in which a particular date falls

date_key    NUMBER  "Primary Key
Not Null"
full_date   DATE    Not Null
week_begin_date DATE    Not Null
week_end_date   DATE    Not Null

As for the bucket number .. I am calculating week start date (date on Saturday, in my case) with the following function

CREATE OR REPLACE FUNCTION get_week_start_date(v_bucket_num IN NUMBER)
  RETURN DATE
IS
  week_start_date DATE;
BEGIN
  SELECT (TRUNC(SYSDATE+2, 'IW')-2) + ((v_bucket_num-1) * 7)
  INTO week_start_date FROM dual;
  RETURN week_start_date;
END;


What can be done, if anything, to tune a SharePoint database?

There is not a lot you can do to tune the database it self.  You aren't allowed to create indexes, or statistics.

Keep your content databases small.  If they get to 100 Gigs you'll want to add another content database (there's ways to do this through the SharePoint UI).

As the tables grow you will want to be sure that you are doing index rebuilds and updating stats regularly.

You can look at using the EBS feature if you are using SharePoint 2010 (might be in SharePoint 2007 as well) which will allow you to move the blob data out of the database and store it on the file system using SQL Server's FILESTREAM feature.

Short of that there isn't much you can do besides throwing more hardware at the problem.


Given my issues, is shrinking so bad? I will rebuild all my indexes. I did a dry run, and brought my database down to 6gb

Most of the advice given on the Internet is copied and is disseminated, while not reading the whole topic carefully. There is no doubt that shrinking of data files is bad, but if you ask any SQL guru he would always say, "yes I have shrunk the data file". He will also tell you the scenario that "forced" him to do so. Like if you have a huge database and you are archiving old records. So you moved data out and deleted the old data from the database. Now a lot of free space is there and you need it immediately; in this case you have no option but to shrink.

In your case you would have ended up paying a large amount for files which grew because of a silly code issue. In your case you can shrink it. Its also good that you are aware that shrinking causes fragmentation and you need to rebuild the fragmented indexes.

The whole motto is do not make shrinking a habit, it should always be your last resort to reclaim space

I suggest you read Paul Randal's Article on why shrinking is bad. He has pointed out in his article other way around to shrinking, which you can follow if you face similar issues later.


Auditing - parameter values not being written to log

I can confirm in SQL Server 2008 R2 Service Pack 3 (v10.50.6000.34), simple parameterized queries do result in the parameterized values being recorded in the audit log.

I setup a simple test rig, as follows.

Create a server audit:

USE master;
IF NOT EXISTS (SELECT 1 FROM sys.server_audits sa WHERE sa.name = 'AuditTest')
BEGIN
    CREATE SERVER AUDIT AuditTest
    TO FILE (FILEPATH = 'D:\SQLServer\MV\Audits', MAXSIZE = 2MB, MAX_ROLLOVER_FILES = 1, RESERVE_DISK_SPACE = ON)
    WITH (
        QUEUE_DELAY = 0
        , ON_FAILURE = CONTINUE
        , AUDIT_GUID = N'B126B4DD-3973-4993-9ADF-4D324A15A8A4'
    );
END
ALTER SERVER AUDIT AuditTest WITH (STATE = ON);
GO

Create a database where we can play with auditing:

IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'AuditTest')
BEGIN
    ALTER DATABASE AuditTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE AuditTest;
END
CREATE DATABASE AuditTest;
ALTER DATABASE AuditTest SET RECOVERY SIMPLE;
BACKUP DATABASE AuditTest TO DISK = 'NUL:';
ALTER DATABASE AuditTest SET PARAMETERIZATION SIMPLE;
GO

Create a table in the database:

USE AuditTest;
CREATE TABLE dbo.TestTable
(
    id int NOT NULL
        CONSTRAINT FK_TestTable
        PRIMARY KEY
        CLUSTERED
        IDENTITY(1,1)
    , someVal varchar(30) NOT NULL
);

Create a database audit spec:

CREATE DATABASE AUDIT SPECIFICATION AuditTestSpec
FOR SERVER AUDIT AuditTest
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.TestTable BY dbo)
WITH (STATE = ON);

Create a stored procedure and trigger an audit action:

CREATE PROCEDURE dbo.AuditTest
(
    @t varchar(50)
)
AS
BEGIN
    INSERT INTO dbo.TestTable (someVal) --stored procedure
    VALUES (@t);
END
GO
EXEC dbo.AuditTest @t = 'runtime value not audited - procedure';
GO

Perform a direct parameterized test:

DECLARE @t varchar(50);
SET @t = 'runtime value not audited - direct insert';
INSERT INTO dbo.TestTable (someVal) --direct insert
VALUES (@t);

SELECT *
FROM dbo.TestTable tt
WHERE tt.someVal = @t;

Finally, run a simple-parameterization test:

INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt1');
GO
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt2');
GO

The execution plan for the above two simple-parameterized statements shows simple parameterization is working as expected:

Read the audit log, using a cursor and PRINT to preserve formatting:

USE master;
DECLARE @stmt nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT af.statement
FROM fn_get_audit_file('D:\SQLServer\MV\Audits\AuditTest_B126B4DD-3973-4993-9ADF-4D324A15A8A4_*', default, default) af;
OPEN cur;
FETCH NEXT FROM cur INTO @stmt;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @stmt;
    FETCH NEXT FROM cur INTO @stmt;
END
CLOSE cur;
DEALLOCATE cur;

Results:

INSERT INTO dbo.TestTable (someVal) --stored procedure
    VALUES (@t);
INSERT INTO dbo.TestTable (someVal) --direct insert
VALUES (@t);

SELECT *
FROM dbo.TestTable tt
WHERE tt.someVal = @t;

INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt1');
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt2');

As you can see in the above output, the simple-parameterized statement run-time values were captured in the audit log.


2NF decomposition + database normalization

There's a little aphorism that goes:

The key (1NF)
The whole key (2NF)
And nothing but the key (3NF)

. . . So help me Codd

In your example we can assume 1NF to begin with as the relational structure doesn't imply any repeating groups within the row (i.e. no D1, D2, D3  etc.).

  R = {a, b, c, d, e, f, g} F = {AB --> C, A --> DE, B --> F, F --> GH,
  D --> IJ }

2NF deals with 'the whole key' - if you have a composite key and some members of the relation are dependent on a part of that key then they should be split off into their own relations.  In your case:

R1 = AB --> C is in 2NF as 'AB' is 'the whole key'
R2 = A --> DEIJ is 2NF but not 3NF as A --> DE, but A --> IJ is not 3NF as the real dependency of I and J is D --> IJ.  D is not a key of this relation (A --> DEIJ), so A --> IJ violates the 'nothing but the key' princple of 3NF.
R3 = B --> FGH is in 2NF but not 3NF as B --> F is a correct relation and F is dependent on the entirety of the key 'B'.  However, B --> GH is not in 3NF as the correct relation is F --> GH.  B --> GH violates the 'nothing but the key' principle, as G ahd H are correctly dependent on the non-key attribute F.

Your decomposition to 2NF is correct.  Decomposition to 3NF requires taking the non-key attributes that have their own dependencies into separate relations.

The relation in 3NF would look like:

R1 = AB --> C
R2 = A --> DE (I and J are dependent on the non-key attribute D)
R3 = B --> F (G and H are dependent on the non-key attribute F)
R4 = D --> IJ
R5 = F --> GH


How to get current time in Oracle, and use in interval partition?

You can not do that. You need to specify the date yourself, as that clause accepts only literals or MAXVALUE, and not expressions.

So you could just use:

(PARTITION p_first VALUES LESS THAN (DATE'2015-12-12'));

If you really want to calculate it dynamically, you need to use dynamic SQL.


How to check if current SQL session has uncommited changes?

You could check V$TRANSACTION. Here is an example:

create table t(a number);

Table created.

insert into t values (1);

1 row created.

select t.status
from v$transaction t
join v$session s
on t.ses_addr = s.saddr
where s.sid = sys_context('USERENV', 'SID');

STATUS
----------------
ACTIVE

After commiting:

commit;

Commit complete.

select t.status
from v$transaction t
join v$session s
on t.ses_addr = s.saddr
where s.sid = sys_context('USERENV', 'SID');

no rows selected

For this you have to grant SELECT privilege on SYS.V_$TRANSACTION and SYS.V_$SESSION though.


I can't open my Pharmacy management software"based on sybase database"

You can use FreeTDS or anything that links in with it like DBD::Sybase to connect to the database.


Real time info charts - Updating via trigger or via schedule

How many times will be a row updated ?
How many rows in percentage are updated per Aggregation ?
Trigger is a possible solution for a reduced number of updates , aggregation is a much better solution if large percentage of the table is updated .


Equivalent of include clause while creating non clustered index in sybase?

No. Sybase doesn't have any equivalent to the INCLUDE statement.


count multiple occurences of attribute values for each row in sql

select
  m1.id,
  m1.attribute,
  (select count(*) from mytable m2 where m2.attribute = m1.attribute)
from
  mytable m1
;

Another version:

select
  m1.id,
  m1.attribute,
  m2.c
from
  mytable m1
  join (SELECT attribute, COUNT(attribute) as c FROM mytable GROUP BY attribute) m2
  on (m1.attribute = m2.attribute)
;

A better version for databases with analytic/window functions:

select
  m1.id,
  m1.attribute,
  count(*) over (partition by m1.attribute)
from
  mytable m1
;


why computed columns slowing down performance all the sudden?

I'm going to throw this out there, since no one else has mentioned it.

Having a scalar valued function in a computed column is an awful idea.

Forces ALL queries against the table to run serially (even if you don't select the computed column)
Forces maintenance (Index, CHECKDB) to run serially, even if you're
on Enterprise
Executes row by row
If used in the WHERE clause, can cause issues with cardinality
estimates

Here are a few blog posts I've written about similar subjects.

Still Serial After All These Years

Scalar Functions In Computed Columns

Scalar Functions In Check Constraints


Is automatic failback on Postgres 8.4 possible via log shipping/PITR?

In general, no, not possible to turn the original master into a standby of the new master without making a copy of the new master, replacing all the data files of the old master with said copy, and resuming log shipping.

This is because, in general, there will be lost transactions on the old master that didn't make it to the standby.

You could use rsync to make the copy go faster -- as the contents of the two databases will likely be very similar. The tool pg_rewind was invented for this purpose: it will "rewind" the old master to the point at which the failover happened.

See Heikki Linnakangas' excellent presentation about the whole topic at http://hlinnaka.iki.fi/presentations/NordicPGDay2015-pg_rewind.pdf


How to return a CTE as REFCURSOR from an Oracle stored procedure?

Since the CTE is part of the same SQL statement, it should not contain a semicolon.

So, there should be no semicolon on the second line of the third block or the third line of the fourth block.  The fifth block does not have a contiguous SQL statement.


How to move a table to secondary filegroup without losing data?

How to move a table to another filegroup?

You need to rebuild your clustered index to new filegroup

  How to move a Heap table to another filegroup?

You need to organize it as a clustered index to new filegroup

  Do we need to move the Non-Clustered indexes to secondary filegroup after moving the table?

Yes, you can move nonclustered indexes to new filegroup.
Ent Edition Of SQL allows you to even rebuild indexes online.
Some links:
https://msdn.microsoft.com/en-us/library/ms175905(v=sql.105).aspx


Finaly got it working. Not quite sure of explanation, but I think it had something to do with Kerberos / NTLM authentication.

SSIS was running under domain account that was not registered with SPN. Changing to "Log On as Built-in Account" as Network Service and restarting SSIS service solved the problem.

But also I'm (almost) sure that I've tried it earlier when I found his thread: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/bba0d02d-67d0-42d4-af20-5ea8a4ae443b

Also these articles explaining Kerberos, NTLM and checking SPN settings were helpful:

http://sql-articles.com/articles/dba/linked-server-part-2/
http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx


Rolling up multiple rows into a single row and column from two tables

The GROUP_CONCAT allows this form of transformation:

SELECT
              register_cr.id,
              register_cr.first_name,
              register_cr.father_name,
              register_cr.surname,
              register_cr.address,
              GROUP_CONCAT(DISTINCT cr_record.charge SEPARATOR "\n"),
              GROUP_CONCAT(DISTINCT cr_record.date SEPARATOR "\n"),
              GROUP_CONCAT(DISTINCT cr_record.charge_type SEPARATOR "\n"),
              GROUP_CONCAT(DISTINCT cr_record.fir_no SEPARATOR "\n")
FROM
             register_cr as register_cr
INNER JOIN
             cr_record as cr_record ON register_cr.id = cr_record.cri_reco_id
GROUP BY register_cr.id


Why scalar valued functions need execute permission rather than select?

Most likely the primary reason is that Table-Valued Functions return a Result Set, just like Tables and Views. This means that they can be used in the FROM clause (including JOINs and APPLYs, etc) of SELECT, UPDATE, and DELETE queries. You cannot, however, use a Scalar UDF in any of those contexts.

Secondarily, you can also EXECUTE a Scalar UDF. This syntax is quite handy when you have default values specified for input parameters. Take the following UDF, for example:

CREATE FUNCTION dbo.OptionalParameterTest (@Param1 INT = 1, @Param2 INT = 2)
RETURNS INT
AS
BEGIN
    RETURN @Param1 + @Param2;
END;

If you want to treat any of the input parameters as "optional", you still need to pass in the DEFAULT keyword when calling it like a function since the signature is fixed:

DECLARE @Bob1 INT;

SET @Bob1 = dbo.OptionalParameterTest(100, DEFAULT);

SELECT @Bob1;
-- Returns: 102

On the other hand, if you EXECUTE the function, then you can treat any parameters with a default value as truly optional, just like you can with Stored Procedures. You can pass in the first n parameters without specifying parameter names:

DECLARE @Bob2 INT;

EXEC @Bob2 = dbo.OptionalParameterTest 50;

SELECT @Bob2;
-- Returns: 52

You can even skip the first parameter by specifying parameter names, again, just like with Stored Procedures:

DECLARE @Bob3 INT;

EXEC @Bob3 = dbo.OptionalParameterTest @Param2 = 50;

SELECT @Bob3;
-- Returns: 51

UPDATE

Why might you want to use the EXEC syntax to call a scalar UDF just like a Stored Procedure? Occasionally there are UDFs that are great to have as UDFs since they can be added to a query and operate over the set of rows returned, whereas if the code were in a Stored Procedure then it would need to be placed into a cursor in order to iterate over a set of rows. But then there are times that you want to call that function on a single value, possibly from within another UDF. Calling a UDF for a single value can be done as either:

SELECT dbo.UDF('some value');

in which case you get a return value in a result set (a result set won't work). Or it could be done as follows:

DECLARE @Dummy INT;

SET @Dummy = dbo.UDF('some value');

in which case you need to declare the @Dummy variable;

HOWEVER, with the EXEC syntax, you can avoid both of those annoyances:

EXEC dbo.UDF 'some value';

ALSO, scalar UDFs have their executions plans cached. This means that it is possible to run into parameter sniffing issues if there are queries in the UDF that have execution plans. For scenarios where it is feasible to use the EXEC syntax, then it is possible to also use the WITH RECOMPILE option to ignore the plans compiled value for that execution. For example:

SETUP:

GO
CREATE FUNCTION dbo.TestUDF (@Something INT)
RETURNS INT
AS
BEGIN
   DECLARE @Ret INT;
   SELECT @Ret = COUNT(*)
   FROM   sys.indexes si
   WHERE  si.[index_id] = @Something;

   RETURN @Ret;
END;
GO

TEST:

DECLARE @Val INT;

SET @Val = dbo.TestUDF(1);
SELECT @Val;

EXEC @Val = dbo.TestUDF 0 -- uses compiled value of (1)
SELECT @Val;

EXEC @Val = dbo.TestUDF 0 WITH RECOMPILE; -- uses compiled value of (0)
SELECT @Val;

EXEC @Val = dbo.TestUDF 3 -- uses compiled value of (1)
SELECT @Val;


Well , its simple from the word itself db_owner:

  Members of the db_owner fixed database role can perform all
  configuration and maintenance activities on the database, and can also
  drop the database.

So yes , user with db_owner role can change the recovery model.

Also, for complete understanding please read Database-Level Roles


Select statement with dynamic columns where these columns are rows in another table

Looking at your table structures, I would suggest that you change the design to something that is normalized. For example:

create table tbl_subjects
(
  sub_id int,
  subject_name varchar(25)
);

create table tbl_cmarks
(
  c_id int,
  examid int,
  rollno int,
  sub_id int,
  mark int
);

Using something similar to above will allow you to add new subjects without having to alter your table.  Then you will just join the tables on the sub_id to get the list of subjects for each class.

select ta.rollno,ta.StdNm,tc.C_Name,
  tm.mark,
  s.subject
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
  ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
  ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
  on tm.sub_id = s.sub_id
where tm.Cid = 22
  and tm.Examid=9;

See SQL Fiddle with Demo. The above will give you a result in rows, but you could easily apply an aggregate function with a CASE expression to pivot the data into columns. Similar to the following:

select ta.rollno,
  ta.StdNm,
  tc.C_Name,
  max(case when s.subject = 'English' then tm.mark end) Emglish,
  max(case when s.subject = 'Physics' then tm.mark end) Physics
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
  ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
  ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
  on tm.sub_id = s.sub_id
where tm.Cid = 22
  and tm.Examid=9
group by  ta.rollno, ta.StdNm, tc.C_Name;

See Demo.

But if you don't change your current table structure and if you don't know the columns that you are going to return, then you will have to implement a prepared statement to generate dynamic SQL.

First, you will create the list of the classes:

set @sqlList = null;
set @query = null;

SELECT
  GROUP_CONCAT(concat('tm.', subject))
INTO @sqlList
FROM tbl_subjects
where C_Id=22;

See Demo. This will give you the list of classes for each c_id. Once you have the list of classes, then you can add this to the rest of the sql string so the full code will be:

set @sqlList = null;
set @query = null;

SELECT
  GROUP_CONCAT(concat('tm.', subject))
INTO @sqlList
FROM tbl_subjects
where C_Id=22;

SET @query
  = CONCAT('SELECT ta.rollno,ta.StdNm,tc.C_Name, ', @sqlList, '
            FROM tbl_cmarks tm
            INNER JOIN tbl_classes tc
              ON tm.Cid = tc.C_Id
            INNER JOIN tbl_admission ta
              ON ta.rollno = tm.rollno
            where tm.Cid = 22
              and tm.Examid=9');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo


When did a specific database come online?

As @SQLLearner specificed in his answer, the easiest way without auditing in place or an XE session running would be to just look in the SQL Server Log.  The below code will get the most recent startup of a particular database (using YourDatabaseName in this example, but obviously change it for your environment):

create table #error_log
(
    LogDate datetime not null,
    ProcessInfo nvarchar(128) not null,
    Text nvarchar(512) not null
)
go

insert into #error_log
exec xp_readerrorlog 0, 1, N'Starting', N'YourDatabaseName'
go

select top 1
    LogDate,
    Text
from #error_log
order by LogDate desc

As per @usr's comment an Connect link, in SQL Server 2012 Unicode parameters are required.


Postgres pgpool increase num_init_children

You're increasing num_init_children, but there's no sign you've set the PostgreSQL server backend's max_connections high enough to allow that number of connections from PgPool.

Please see the PgPool-II documentation to learn more about configuring PgPool-II. You might find this article useful.

You really don't want thousands of parallel queries to the backend database. What you need is for PgPool to queue them up and send them a few hundred at a time to PostgreSQL. Otherwise you'll tend to see terrible performance; see number of database connections in the Pg wiki. So you need to limit the number of back-end connections PgPool-II creates, while allowing lots of front-end connections. That means you want a low num_init_children. Personally I'd use something like:

num_init_children = 100
max_pool = 1

because, per the documentation:

  If more than num_init_children clients try to connect to pgpool-II, they are blocked (not rejected) until a connection to any pgpool-II process is closed.

This is what you want for good performance.

That said, I use PgBouncer for this. I find it much simpler to work with, and it tends to perform very well. It also offers transaction-pooling mode, which can be really useful when you have applications that don't close connections, instead leaving them idle for ages. If you're not using PgPool's cluster features I strongly recommend just using PgBouncer, it's way simpler.


How to simply add ' and " to numeric values for height(inch) format?


mysql duplicate entry error 1062 when restoring backup

I would suggest that this is indeed indicative of a problem with db3.  By default, mysqldump generates "extended" insert statements, containing more than one row's worth of insert per line.

INSERT INTO table_name VALUES (...), (...), (...), ...;

This is an optimization, since multiple inserts in a single statement are much faster than executing individual insert statements.

But, you can disable this behavior using the --skip-extended-insert option.

Using that option for backups isn't a good idea, since they restore much more slowly, but this option does make dump files that are much easier to read with your eyeballs and much easier to search through for a specific record using grep or a similar tool.

Dump the database using this option and then search through the file for the duplicate keys that are throwing errors and it seems likely that you'll find duplicate rows or rows with duplicate keys that should have been unique... which means underlying table trouble in db3.

I can't think of a way this could happen with InnoDB but with MyISAM it's distinctly possible.

There are a couple of other mysqldump options that might also be useful:

--replace generates a file writing the statements as REPLACE INTO instead of INSERT INTO which would result in the duplicate key records occurring later in the file replacing the conflicting records that occurred earlier in the file, without generating an error
--insert-ignore generates a file with the statements written as INSERT IGNORE INTO instead of INSERT INTO, which would cause the duplicate key records occurring earlier in the file to be persisted in the restored tables, as the later conflicting records would be ignored, not inserted, and would not generate an error.


Suppress repeated performance condition alerts

You basically created an alert that will get fired when the log space used percent is above 25%.  That's pretty low, in my opinion.

  Please let me know what action should I take so that I did not get this alert again Hard drive where transaction log get saved have enough space.

If what I'm understanding from you is correct, you're saying that the volume your transaction log sits on has plenty of space, and you think you may be getting false alerts?  If that's correct, this perfmon counter isn't looking at that at all.  It could be a 2 GB log file, living on a 500 GB volume.  Even if that 500 GB volume has 400 GB of free space, if your 2 GB log file has 500+ MB space used you'll get that alert.  Volume free space and database file free space are two different things.

You need to determine what you care about to be alerted on.  It's a great strategy to alert on log file space used if it is getting high (in my opinion, the upper bounds would be 70% - 80%, but you will need to tailor that to your environment), but I think you may be confused with the above two points.

If that hasn't answered your question, please clarify either through comments here or editing your own question.

Edit after your edit

You need to see what your log space used percent is.  By your alert, if it is greater than 25%, then you should be alerted.  You can find that out a couple of ways.

The quickest is to run the following:

dbcc sqlperf(logspace);

That will give you a rundown of all log space used for every database.  If you want to break that down for a particular database (for whatever reason) it could be accomplished this way:

use YourDatabase;
go

;with file_cte as
(
    select
        name,
        size,
        fileproperty(name, 'SpaceUsed') as space_used
    from sys.database_files
    where type_desc = 'log'
)
select
    name,
    size,
    space_used,
    convert(decimal(5, 2), space_used * 1.0 / size * 100) as space_used_percent
from file_cte;

The reason why you didn't get alerted after that is simple:  Because your log space used didn't breach 25%.  That could have been through log reuse due to a transaction log backup, or maybe somebody grew the file making that 25% space used a larger number.  As for tempdb's database file sizes, that shouldn't affect when you get alerted, unless you are being alerted specifically for tempdb log file space used (in your question it says "my database name" so I'm assuming you're not alerting off of tempdb with this alert).


I know of two ways to copy timestamp data:

A SELECT..INTO will insert explicit values into a timestamp column, but it also requires that the destination table does not exist. That will not help in your situation.
Convert it to a VARBINARY or VARCHAR value. This is the equivalent of the SSIS conversions to DT_BYTES/DT_WSTR you mentioned.  In both cases, you can copy the values, you can manipulate the values, but you cannot reinsert them into a TIMESTAMP column.

In short, no, I don't know of a way to copy the exact same timestamp values between databases. If you need the exact value, you'd need to change the column type on the destination.

In most cases I've seen, though, the value is much less important than the sequence provided by the timestamp column.  If you insert the values into the destination table in the order of the timestamp values in the source table, you should end up with the same sequence, even if the values are different.


Postgres command failed: No space left on device

when a command fails like this, are the results of the command not immediately discarded?

You need to understand the difference between the logical and physical structures of the database. When you insert 350m rows to a table and it fails, the logical changes are rolled back.

But when the underlying datafiles have to be increased in size to accomodate the 350m rows, this physical change is not rolled back.

  Why when no actual changes have been made to the table, is the tablespace still full?

While your 350m rows were being added, the tablespace grew its datafiles to accomodate the new data. When the data was deleted, the space remains. Deletions will not shrink the datafile, it will merely mark the space as unused and leave it there to be reused later. The space will only be returned to the operating system if the DBA issues a VACUUM FULL command.

  And finally, what is the best way to free up space again when postgres will not allow to me execute any other commands e.g. VACUUM?

Multiple valid strategies for fixing this problem are provided in these two older questions:

I need to run VACUUM FULL with no available disk space

VACUUM returning disk space to operating system


PostgreSQL/Psycopg2 indexing array in the size of thousands

I dont' think indexes will help. I am not sure why, but I am just being experimental...

First, we create a table with your structure, and fill it with random data (10.000 rows, and a vector of 1.000 columns).

 CREATE TABLE t
 (
     id integer /* PRIMARY KEY */,
     stuff integer,
     large_array_of_values float[]
 ) ;

 CREATE OR REPLACE FUNCTION random_vector() RETURNS float[] AS
 $$
     select
        array_agg(random())
     from
        generate_series (1, 1000)
 $$
 LANGUAGE SQL ;

 INSERT INTO t
    (id, stuff, large_array_of_values)
 SELECT
    id, random()*10000,  random_vector()
 FROM
        generate_series(1, 10000) AS i(id) ;

At this point we create one sample index for values at index [32] of the vector (plus the id!):

 CREATE INDEX
    idx_32 ON t(id, (large_array_of_values[32]));

Now, we ask PostgreSQL to analyze the following query and explain it:

 EXPLAIN ANALYZE
 SELECT
    avg(large_array_of_values[32])
 FROM
    t
 WHERE
    id BETWEEN 5000 and 7500
     AND (large_array_of_values[32]) > 0.32 ;

 | QUERY PLAN                                                                                |
 | :---------------------------------------------------------------------------------------------------------------------- |
 | Aggregate  (cost=46.94..46.95 rows=1 width=8) (actual time=54.871..54.871 rows=1 loops=1)                               |
 |   ->  Bitmap Heap Scan on t  (cost=4.91..46.89 rows=17 width=32) (actual time=0.392..1.204 rows=1732 loops=1)           |
 |         Recheck Cond: ((id >= 5000) AND (id <= 7500) AND (large_array_of_values[32] > '0.32'::double precision))        |
 |         Heap Blocks: exact=20                                                                                |
 |         ->  Bitmap Index Scan on idx_32  (cost=0.00..4.91 rows=17 width=0) (actual time=0.364..0.364 rows=1732 loops=1) |
 |               Index Cond: ((id >= 5000) AND (id <= 7500) AND (large_array_of_values[32] > '0.32'::double precision))    |
 | Planning time: 0.405 ms                                                                                |
 | Execution time: 55.013 ms                                                                                |

dbfiddle here

The query plan is not using the index (idx_32) to perform an index-only scan, which I guess is what you wanted, even if idx_32 is a covering index for such a query.

VACUUMing, to make sure that the visibility map was up-to-date didn't have any effect. I couldn't find any explicit restrictions on index-only scans having to refer to columns (and not Indexes on Expressions), but it appears that they aren't used in this second case.

Comparison with a non-vector column

 CREATE TABLE t
 (
     id integer /* PRIMARY KEY */,
     stuff integer,
     a_value float
 ) ;

 INSERT INTO t
    (id, stuff, a_value)
 SELECT
    id, random()*10000,  random()
 FROM
    generate_series(1, 10000) AS i(id) ;

 CREATE INDEX idx_value ON t(id, a_value);

 VACUUM ANALYZE VERBOSE t ;

In this case, the covering index is actually used, and you get an "index-only scan".

 EXPLAIN ANALYZE
 SELECT
    avg(a_value)
 FROM
    t
 WHERE
    id BETWEEN 5000 and 7500 AND (a_value > 0.1)

 | QUERY PLAN                                                                                |
 | :---------------------------------------------------------------------------------------------------------------------------- |
 | Aggregate  (cost=103.67..103.69 rows=1 width=8) (actual time=1.139..1.140 rows=1 loops=1)                                     |
 |   ->  Index Only Scan using idx_value on t  (cost=0.29..98.05 rows=2251 width=8) (actual time=0.026..0.655 rows=2247 loops=1) |
 |         Index Cond: ((id >= 5000) AND (id <= 7500) AND (a_value > '0.1'::double precision))                                   |
 |         Heap Fetches: 0                                                                                |
 | Planning time: 0.184 ms                                                                                |
 | Execution time: 1.179 ms                                                                                |

dbfiddle here


It's a thread_id as per https://dev.mysql.com/doc/refman/8.0/en/error-log-format.html :

This log writer produces the traditional error log output. It writes messages using this format:

  timestamp thread_id [severity] [err_code] [subsystem] message

The [ and ] square bracket characters are literal characters in the message format. They do not indicate that fields are optional.


Estimating impact of indexes SQLite database size

At least some of the details of index B-trees are documented.

It might be easier to just create a test database, populate it, and measure the file size before and after creating an index.


deadlocks are an applicative error and should be treated as such.
Look through the application to find the 2 flows which change this row and decide on the best course to avoid collisions.

The link you shared is related to changes locking foreign keys- this is not the case.

You just need to find & fix the app - happy hunting :)


First let's answer your initial question then your follow up question.

I would go further and:
change the SQL Server port to a non standard one, turning off the SQL browser service, renaming SA, turning on the firewall on the local machine not to accept any connections on the SQL Server port outside of your internal IPs, and have all of your traffic hit the web server which serves as a secured abstraction layer for outside connections.

Follow up question:
How about HyperV and setup one server for SQL internally and a second one for the web server in the DMZ?  Why does the web server need to be in the DMZ?  Are you able to just forward all 80/8080/443/etc. ports over to that web server?

BTW, since you have both on 1 machine, have you set 'max memory to use' options in SQL Server and 'lock pages in memory' so IIS doesn't steal precious RAM?


You can have 2 strategies:

Stop MySQL Server and backup (just copy all files to some location) - folder c:/ProgramData/MySQL/data
Make a full dump of all databases
mysqldump -u root -p --all-databases > c:/backups/all_db.sql

p1 - if You plan rollback exactly for 5.6.30,

p2 - if You plan go till the win, best to have both.

then You must have old installer for revert back at any time, You can download it from official archive - https://downloads.mysql.com/archives/community/

Then You if You decide revert back:

Uninstall latest MySQL - by windows tools
Install 5.6.30 from previous step
Copy folder c:/ProgramData/Mysql/data saved on previous step
Start MySQL

If You also make a sql dump - You can use normal restore procedure

mysql -u root -p < c:/backups/all_db.sql

, but it work much slower than simple file copy.


AlwaysOn Primary Showing as Question Mark

That is normal. It is just indicates you that you are on the secondary server and the role is taking care of by the primary node (the one without the question mark). If it was a 3 node or more cluster you might see the question mark on all node expect the primary. When it failover you will see the reciprocal of this on the other server.


Check if a primary key value is referred in any other table

You can check dba_constraints:

Get the primary key constraint name:

select constraint_name
from dba_constraints
where owner = '<SCHEMA_NAME>' and table_name = 'STUDENT' and constraint_type = 'P';

Then check where that constraint is used:

select owner, table_name, constraint_name
from dba_constraints
where r_constraint_name = '<result from previous query>' and constraint_type = 'R';

To get the column name of the referential constraint, you can use dba_cons_columns:

select owner, table_name, column_name
from  dba_cons_columns
where constraint_name = '<constraint name from previous query>';

Plugging all of this together, you can do something like:

  SELECT fk.owner, fk.table_name, col.column_name
    FROM dba_constraints pk
    JOIN dba_constraints fk
      ON pk.constraint_name = fk.r_constraint_name
     AND fk.constraint_type = 'R'
    JOIN dba_cons_columns col
      ON fk.constraint_name = col.constraint_name
   WHERE pk.owner = :owner AND pk.table_name = 'STUDENT' AND pk.constraint_type = 'P';


Deleting Record from Linked Table Slow in MS Access

It could be a mechanism within MS Access when it comes to the linked table. How ???

MSAccess sees a linked table and you issue this:

DELETE FROM [mytable];

This will not run over to SQL Server and do a deletion of all records in a single transaction. What MSAccess does is pick up every PRIMARY KEY and issues an individual DELETE FROM [mytable] WHERE pk = ???, probably as individual transactions (explains the delay before the confirm). I remembered this wonky behavior going back 15 years because I saw this happen when having linked tables to SQL Server, MySQL, and Oracle.

If this is still what MS Access does to a linked table, you are better off connecting to SQL Server via isql and issuing the DELETE on the server side, especially if there are a lot of rows to delete. At the very least, you should minimize executing bulk operations against SQL Server from MSAccess.

Although I have never worked with it, MSAccess can allow "pass through" queries where you issue a SQL Command and the ODBC Driver simply passes the query to the Server Side for parsing and execution rather that trying to parse and execute the SQL the "ODBC" way.

If things have changed over the years with linked tables, I'll leave it to the SQL Server community to address this if I am way off base.


Netezza LIKE operator not finding subsequent backslashes

I had to avoid the escape character in the pattern matching string (after the LIKE), and append ESCAPE '' to avoid using the backslash as an escape character.  In this example, I had _ characters in the literals, as well as \ characters.


https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/tuning-SQL-Plus.html

8.1.2 Execution Plan
The Execution Plan shows the SQL optimizer's query execution path. Execution Plan output is generated using EXPLAIN PLAN and DBMS_XPLAN.

Yes, it is possible to have a different actual plan. Example:
create table t1 (c1 number);
insert into t1 select 1 from dual connect by level <= 100000;
insert into t1 values (2);
create index i1 on t1(c1);
variable B1 number
exec :B1 := 2;
set autotrace on
select * from t1 where c1 = :B1;

        C1
----------
         2

Execution Plan
----------------------------------------------------------
Plan hash value: 955340196

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   933 | 12129 |    59   (2)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| I1   |   933 | 12129 |    59   (2)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1"=TO_NUMBER(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

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

But the actual plan was:
SQL> set autotrace off
SQL> select sql_id, sql_text from v$sql where sql_text = 'select * from t1 where c1 = :B1';

SQL_ID        SQL_TEXT
------------- ----------------------------------------
9677k5kzddy78 select * from t1 where c1 = :B1

SQL> select * from table(dbms_xplan.display_cursor('9677k5kzddy78'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID  9677k5kzddy78, child number 0
-------------------------------------
select * from t1 where c1 = :B1

Plan hash value: 1816312439

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| I1   |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:B1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

22 rows selected.


Turn off (suppress) Msg 3013 "BACKUP DATABASE is terminating abnormally"

There is no way to "suppress" the error, nor would I personally want to. The error is letting you know something happened and it's up to you to go figure out what that something was.

In your example, it says the database doesn't exist. Let's say you lost connectivity to disks, or there was corruption, or ... ad nausea. In this way, you can key off of the 3013 error as a general "backup database issue" (alert) and then investigate further.

Assuming you could suppress the error message, you'd either have to have an alert or line item for each individual error that could possibly happen or just go about your day until someone or something complains. Either way, not so good.


Reporting Services TempDB keeps going into restoring mode

Why are you running a log backup job on [ReportServerQA5TempDB]? It should be in simple mode.

From MSDN:

  ReportServerTempdb is created with the primary report server database and is used to store temporary data, session information, and cached reports.

and

  Use the simple recovery model to backup the reportservertempdb database.

Now the real question becomes, "What are you using for your backups?" Check to make sure that it's not changing the recovery model and that ReportServerTempDB is still in SIMPLE recovery model.

    SELECT name, recovery_model_desc
        FROM sys.databases;

I wonder if your solution is trying to switch back and forth between recovery models and is failing somewhere along the way. I have a vague recollection of this having happened to me in the past.

Try changing the recovery model to Simple and removing it from your hourly log backup job.

ALTER DATABASE ReportServerQA5TempDB SET RECOVERY SIMPLE;
GO


Is it necessary to re-insert all the data in specific order, when I'm going to use the BRIN index?

As with everything related to databases, the answer will be “it depends”.
BRIN indexes are efficient if the ordering of the key values follows the organization of blocks in the storage layer. In the simplest case, this could require the physical ordering of the table, which is often the creation order of the rows within it, to match the order of the key(s). Keys on generated sequence numbers or created data are best candidates for a BRIN index.
You mentioned that the index will he made in a date/timestamp column so, as long as these records are already stored sequentially in the table, you will not need to do a dump and reload. If the values are all over the place, then it would be in your best interest to export the data in the new order, drop the table, and reload.
BRIN indexes use far less storage compared to a normal B-Tree index, but they can be quite “lossy”, which will reduce their effectiveness. You may be able to tweak this by specifying a pages_per_range value like this:
CREATE INDEX idx_date_brin ON my_stats_db USING BRIN(transaction_date) WITH (pages_per_range = 32);

BRIN indexes store entries for a range of pages in a corresponding table. The larger the range of pages, the smaller the index. The smaller the index, the lossier it becomes.
Because indexes can be created and destroyed far more quickly than a complete table load (mist of the time), i would suggest playing around on a non-production system to see whether a BRIN is better than a B-tree for your use case and if your table data is sequential enough to not require a reload.
Hope this helps


Adding another Secondary Log Shipping location

Yes, you should apply the latest FULL and all the applicable Transaction log backups in sequence after that full, manually on the secondary with no recovery, prior to setting up the log shipping jobs and turning them on (using the wizard with no initialization).  When the log shipping wizard is used, it will, as you said, create the backup job on the primary and the copy and restore agent jobs on the secondary which looks for the next LSN to apply after the last one you applied manually when it starts applying the logs it needs for the log shipping process to begin.


Confused by GRANT ALL PRIVILEGES causing Access Denied for database 'mysql'

You only have SELECT privilege on mysql.user and mysql.db.
You need to have more than that. If you could be granted
GRANT UPDATE,INSERT,DELETE on mysql.user TO 'dbadmin'@'10.%';
GRANT UPDATE,INSERT,DELETE on mysql.db TO 'dbadmin'@'10.%';

This will allow you to CREATE USER as it must be able to write to those two tables.
With you current grants on mysql.user and mysql.db, you cannot create any user.


As you have correctly identified, NadÃ¨ge is the UTF-8 representation of    Nadège incorrectly decoded as ISO-8859-1 ("latin-1"). Then, in your case, re-encoded to UTF-8 for storage in the DB.

To fix it you need to:

Take the current representation and decode the UTF-8 to latin-1 as a byte string
re-interpret the byte string, decoding it as utf-8

So:

test=> SELECT convert_from(convert_to('NadÃ¨ge', 'latin-1'), 'utf-8');
 convert_from
--------------
 Nadège
(1 row)

The Python equivalent would be close to what you wrote, but starts with a unicode representation to illustrate that PostgreSQL stores everything in the database encoding. Something like:

>>> print u"NadÃ¨ge".encode("latin-1").decode("utf-8")
Nadège

The problem with all your attempted solutions is that the cast from text to bytea uses the database encoding. So you're starting with the bytes for the utf-8 representation of utf-8 mis-decoded as latin-1. With the cast you'd have to write:

test=> SELECT convert_from(convert_to(convert_from((TEXT 'NadÃ¨ge')::bytea, 'utf-8'), 'latin-1'), 'utf-8');
 convert_from
--------------
 Nadège
(1 row)

because you have to explicitly decode the utf-8 representation produced by the cast before re-interpreting as latin-1 and decoding again.

You just needed to use convert_to(mycol, 'latin-1') instead of mycol::bytea


How to manage many-to-many relationships when there is also a hierarchy among the many?

Martin,

If I am understanding correctly, the employee can be associated with one or more tasks, phases, and projects.  This implies a many to many relationship between an employee and a task, phase, or project.  If this is the case, throw out the idea of the assignment table completely.  Instead, create three different junction tables that can be used to establish the many to many relationships:  EmployeeTask, EmployeePhase, and EmployeeProject.

The Task, Phase, and Project tables will allow for relationships to be established as you have defined in your post.  The three junction tables will enable an employee to be included in the hierarchy at any of the three levels.

Your SELECT statements will require LEFT OUTER JOINS with the junction tables to pull back the appropriate employee information, which is fine.  Adding the junction tables will provide a much more normalized database design.


Pull every other row, starting at 0, and concatenate them together

DECLARE @x TABLE(ID INT, [Text] NVARCHAR(32));

INSERT @x VALUES (0,N'This'), (1,N'Do'),  (2,N'is'),   (3,N'not'),
                 (4,N'the'),  (5,N'use'), (6,N'text'), (7,N'text');

SELECT LTRIM((SELECT ' ' + [Text] FROM
  (SELECT rn = ROW_NUMBER() OVER (ORDER BY ID), [Text] FROM @x) AS x(rn, [Text])
  WHERE rn % 2 = 1 ORDER BY rn FOR XML PATH, TYPE).value('.[1]','nvarchar(max)'));

Result:

This is the text

If you know your strings can't contain characters like <, > and & then it should be slightly more efficient to leave out the TYPE).value bit:

SELECT LTRIM((SELECT ' ' + [Text] FROM
  (SELECT rn = ROW_NUMBER() OVER (ORDER BY ID), [Text] FROM @x) AS x(rn, [Text])
  WHERE rn % 2 = 1 ORDER BY rn FOR XML PATH('')));


Anyone understand why this query no longer works?

rows is a reserved keyword in MariaDB.

https://mariadb.com/kb/en/library/reserved-words/

Unless you single- or double- or backquote it:

select id as 'rows' from tbl;
select id as "rows" from tbl;
select id as `rows` from tbl;

db<>fiddle here

As commented by dbdemon, rows is now a reserved word in MySQL too, specifically starting from version 8.0.2. See another db<>fiddle demo here.


Strange blocking on readable secondary after reboot

It sounds like you experienced the behavior that's described in this post on the PFE blog:

AlwaysOn Availability Groups unable to query against readable secondary replica database: Wait Type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING

Essentially, there happened to be a long-running transaction on the primary when the secondary was made readable, and thus queries will be blocked in the secondary until the snapshot-related row versions are available.  I imagine removing and re-adding the database was coincidental with the long-running transaction finally completing.

So this behavior, as described in that blog post, is by design.

However, if there was not a long-running transaction, then this could be a bug.  There is a comment on that blog that indicates others have had this problem:

  I am facing the issue after Secondary SQL Server reboot after OS patching. Do not see any open transactions in primary prior to the reboot. There are two databases in the AG having this issue. And we have been waiting for more than 15 hours but still readable replica is not able to process any select query for those databases.

If you are able to repeat the behavior, it would be good to report it on the feedback site and / or engage Microsoft support if you have a support agreement.


Clustering a heap by date in stead of unique composite key

Non-clustered indexes use the clustered index key value as a pointer back to the data.  If you create a compound clustered index, you are making your non-clustered indexes that much larger.

When choosing a clustered index, especially on tables with hundreds of millions of rows, try to choose a single narrow column if possible.  i.e. choose an integer over a varchar field.

The Datetime field is 8 bytes with is relatively narrow.  If your data is inserted by date in ascending order (e.g. as time progresses) the records will be inserted at the tail end of the table.  Pages and extents will be added as necessary.

If your data data is spread out all over the place however, you will end up with page splits as new records are shoved in between existing records.  Make sure your incoming date data is accurate.

Be warned that SQL will add an internal 4 byte "uniquifier" to your clustered index if it is not unique.

So, go for the single column clustered index.  Queries that use the datetime column will benefit greatly, and the other indexes will not suffer.


In MongoDB serverStatus(), what is the meaning of "connection data handles currently active"?

Although I can't find any evidence for that in the documentation, it is probably the open file-handles that Mongo holds against the file storage.

I did a test that supports this idea:

Creating new mongodb instance, "connection data handles currently active" minimum value is 11 (sometimes it becomes 12,13 and then back to 11).
db.coll.insert({"foo":"bar"}) - "connection data handles currently active" min value = 13.

I did the same with other operations that cause mongo to create new files on disk, such as index creation, and I have noticed the same behavior.
We can conclude that this value, "connection data handles currently active" is related to the number of file-handles that mongo holds.

As for the question if it has an impact on the instance's performance, in some cases it could be true according to the next discussion at Jira:
https://jira.mongodb.org/browse/SERVER-27700


BLObs – Should I Store In Database Or in Directory Structure?

I've seen it done both ways. If you want to bulk up your database size just start using BLOBS.

Pro's for storing documents in your database

you're already storing your data there so why not a little more? (Only it's a lot more)
a certain simplicity of access.  Any request to read only has to go one place to access the document or thing

Cons for storing BLOBS in your database

datafiles grow quickly, backups are slower.  As soon as it takes longer to backup the database than you have available quiet time more sophisticated and expensive options are required
in Oracle you cannot easily access BLOBS/CLOBS across a database link so your data in those fields cannot be accessed by other databases

Pro's for using a file system

system admins feel backing up a directory is a lot easier than backing up a database
same for adding more disc space
separation of concerns (although this is illusory: if the database is down you can't find the document anyway and same if the application server is down)


You don't have to drop the database, it should be enough to drop all the objects in the database. This can be done using

drop owned by adminuser

If you then create the SQL dump including the create table statements (so without the --data-only option) everything should be fine.

You can also remove the --column-inserts then, which will make the import a lot faster.

However, if you do want to delete everything, you can do that with a little dynamic SQL:

do
$$
declare
  l_stmt text;
begin
  select 'truncate ' || string_agg(format('%I.%I', schemaname, tablename), ',')
    into l_stmt
  from pg_tables
  where schemaname in ('public');

  execute l_stmt;
end;
$$

This will truncate all tables in the schema public with a single statement which will also work even if there are many foreign key constraints connecting all tables. If your tables are spread over multiple schemas, you need to add them in the where condition.


MySQL Workbench - question about creating FK relationships

It looks like you've used the option to create an Identifying relationship (this is the one with the unbroken line in the toolbox).

An Identifying relationship assumes that the primary key in the referenced table should be part of primary key of the referencing table.

Because Company_id is now part of Department's Primary Key, it must also be part of the foreign key of Employee, hence your problem #1.

What you're probably wanting to do is create a Non-identifying relationship. This can be done by either selecting the dashed line from the toolbox, or unticking "Identifying relationship" in the Foreign Key tab of the Relationship properties.

A Non-identifying relationship is a classic foreign key constraint, and simply ensures that any value in the referencing table exists in the referenced table.

Technically this should resolve your problems, but there are still potential issues with the underlying database design.

For example, there's nothing to stop multiple companies existing with the same name, or multiple departments with the same name, even within the same company.

This could be solved by adding unique key constraints, but another way to tackle the same problem would be to use the Company and Department names as primary keys.

If you were doing this, you'd actually want to use an Identifying relationship, so that the Company name becomes an integral part of the Department.

(You may wish to read up on database theory and database normalisation, as it will help you avoid a lot of traps that you're likely to come up against when building a database)

I'm not suggesting that this is a better database design, simply that this is one where an Identifying relationship is valid / useful.


Pulling from Two Tables with Different Number of Records

Sounds like you just need to do an INNER JOIN. And it also sounds like you don't know what one is? Recommend you read up: http://www.mysqltutorial.org/mysql-inner-join.aspx

In your case it would be a simple:

SELECT EventID, CONCAT(m.Title, " ", m.LastName) AS Member
FROM Events e
INNER JOIN Tasks t ON e.EventID = t.EventID
INNER JOIN Members m ON t.MemberID = m.MemberID
WHERE EventID = <event ID>

Here, I am assuming you wish to get the information for a particular event? If not, remove the WHERE statement.


Databases design decision - Mobile & Web

You can consider the web and mobile as just delivery interfaces to the functionality/services for your application. With this regard the backend functionality (server side - processing etc), should be the same for both interfaces, so yes they should have the same database.

You may want to add data to identify the sources of data addition or changes or traffic, but essentially its the same app.

UPDATE:
Think of a mobile app just like a separate UI to be supported, I am not going to exhaust the possibilities but look at potential list of possible interfaces below:

Web Application - available
Mobile App - is this HTML 5 web app, Android or
IOS? each may require its own 'different infrastructure'
Desktop app - for a subset, administration, operations
Web Services (REST, SOAP) - for machine to machine communication which does not have a definite UI per se, but may be provided to 3rd parties to develop their own apps (even the 3 UIs above can be considered as such)

The way to think about it is that there is one application with a single database which is being accessed through multiple channels, whether from the same server or different servers


Mysql update table using ubuntu terminal

MySQL has built-in support for importing data from CSV files, just use the command-line client :

  mysql -u <user> -p

(replace <user> with your MySQL username and enter the password when asked to)

Then, tell MySQL to use your database :

  USE <database>;

(replace <database> with the database name)

And finally, use this to import everything from your CSV file :

  LOAD DATA INFILE '<path>' INTO TABLE <table>;

(replace <path> with the full path to the CSV file such as /home/user/file.csv and <table> with the table name).

Look at this question and this one that may be related to your question.


How to model a database to retain driver related information?

Assuming there is a 'company' table as well that drivers can be associated to?

If so, one option is as follows:

Driver:

Driver_ID (PK)
Vehicle_ID (FK to Vehicle... nullable)
Company_ID (FK to Company... nullable)
solo_driver (boolean value [kind of optional, but could be useful])
other Attributes...

Vehicle:

Vehicle_ID (PK)
Company_ID (FK to Company... nullable)
Other Attributes...

Company:

Company_ID (PK)
Other Attributes...

If the driver is a solo-driver, then they have an associated Vehicle_ID
If the driver is a company-driver, then they have an associated Company_ID
- The company_ID would have associated (one-to-many) vehicle_ID's


How to List All Permissions for SQL Server Fixed Database and Server Roles

Neither of these roles has an enumerated list of permissions.  db_owner has all permissions in a database, and sysadmin is exempt from permissions checking on both the server and every database.  There are a handful of system procedures that are hardcoded to check for sa or perhaps dbo.


Create unique DB_NAME and DB_DOMAIN for an Oracle 11g instance

No, you do not have a name collision.  Your DB_DOMAIN, DB_NAME, and INSTANCE_NAME can all be identical as long as the databases are on different hosts (as you have indicated).  However, as others have stated using the same DB_NAME is a bad idea for anything other than perhaps a recovery operation.  A policy enforcing distinct passwords will be broken.  Connections will be confused.  Most things you can do to ensure that changes are not inadvertently made on production are probably worth the hassle.

Here is the relevant documentation:

DB_DOMAIN

  In a distributed database system, DB_DOMAIN specifies the logical
  location of the database within the network structure. You should set
  this parameter if this database is or ever will be part of a
  distributed system. The value consists of the extension components of
  a global database name, consisting of valid identifiers (any
  alphanumeric ASCII characters), separated by periods.

  Note: Oracle recommends that you specify DB_DOMAIN as a unique string
  for all databases in a domain.

  This parameter allows one department to create a database without
  worrying that it might have the same name as a database created by
  another department. If one sales department's DB_DOMAIN is
  JAPAN.ACME.COM, then their SALES database (SALES.JAPAN.ACME.COM) is
  uniquely distinguished from another database with DB_NAME = SALES but
  with DB_DOMAIN = US.ACME.COM.

  If you omit the domains from the name of a database link, Oracle
  expands the name by qualifying the database with the domain of your
  local database as it currently exists in the data dictionary, and then
  stores the link name in the data dictionary.

DB_NAME

  DB_NAME specifies a database identifier of up to 8 characters. This
  parameter must be specified and must correspond to the name specified
  in the CREATE DATABASE statement.

  If you have multiple databases, the value of this parameter should
  match the Oracle instance identifier of each one to avoid confusion
  with other databases running on the system. The value of DB_NAME
  should be the same in both the standby and production initialization
  parameter files.

INSTANCE_NAME

  In a Real Application Clusters environment, multiple instances can be
  associated with a single database service. Clients can override
  Oracle's connection load balancing by specifying a particular instance
  by which to connect to the database. INSTANCE_NAME specifies the
  unique name of this instance.

  In a single-instance database system, the instance name is usually the
  same as the database name.


Why does a MySQL MEMORY-backed table take so much more space than on disk?

VARCHARs are stored with a fixed length in memory, so your max length of 255 will be using a lot of space.  Your InnoDB disk table will be storing the rows with a dynamic length.

http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html says...

  MEMORY tables use a fixed-length row-storage format. Variable-length
  types such as VARCHAR are stored using a fixed length.


Benefits of using WITH TABLOCK on an INSERT

I know of a few benefits but they're mostly situational.

Using TABLOCK will reduce concurrency but will immediately take a table lock on the target table. As long as you can guarantee that just one session will insert into the table this will avoid unnecessary row or page locks and will prevent lock escalation. After all, if you are inserting so much data that you'll get lock escalation anyway why not do it upfront?
If you are inserting into an empty page-compressed heap without TABLOCK all of the pages will have row compression instead of page compression:

The newly inserted row is page-compressed:

if new row goes to an existing page with page compression

if the new row is inserted through BULK INSERT with TABLOCK

if the new row is inserted through INSERT INTO ... (TABLOCK) SELECT FROM

Otherwise, the row is row-compressed.

In SQL Server 2016 the TABLOCK hint is required to get parallel insert into heaps, CCIs (Clustered Columnstore Indices), and local temp tables. There are lots of restrictions, some of which aren't documented. There can't be an IDENTITY column, the insert can't be done through an OUTPUT, etc.

See also The Data Loading Performance Guide


Still i am not satisfying with Logfile Shrinking in SQL Server?

In order for you to manage the transaction log  size of your database(s) which is in full recovery mode you should be taking regular and frequent transaction log backups. The frequency should be according to RPO and RTO defined

Do NOT change the recovery mode and shrink the log. You stated in your question that you didn't want any data loss by doing this you would lose point in time recovery. In the full recovery mode this is done by taking regular transaction log backups so that in the event of a failure, the backups can be used to restore the database to a point in time.

By changing the recovery mode, you are breaking the log chain which will prevent your ability to restore the database to a point in time.

Log backups will free space in the log by marking virtual log  inactive or reusable. This allows space within the log to be re-used and prevents growth (if the log is big enough initially).

You should also be aware that putting the database in the simple recovery mode will not prevent any transaction log growth. The log will record all active transactions and will grow accordingly. The difference is that in the simple recovery model, the virtual log files are marked for re-use after a database checkpoint.

Here's a couple of links for more information on the transaction log:-

Overview of the transaction log:-  http://msdn.microsoft.com/en-us/library/ms190925.aspx
Transaction log backups:- http://msdn.microsoft.com/en-us/library/ms191429.aspx


Partitioning a Table Referenced by a Foreign Key

From BOL (http://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx)

Source and target tables must have the same FOREIGN KEY constraints. If the target table has any FOREIGN KEY constraints, the source table must have the same foreign keys defined on the corresponding columns, and these foreign keys must reference the same primary key as those of the target table. The foreign keys of the source table cannot be marked is_not_trusted (viewable in the sys.foreign_keys catalog view), unless the corresponding foreign key of the target table is also marked is_not_trusted.

So if you want to partition a table referenced by foreign key, then both tables need to have same FK.
Also for fast partitioning, below rules apply:
you cannot use fk's

No primary key/foreign keys defined if source table has the primary key. There can be no active primary key/foreign key relationship between the source table and the target table in which the source table holds the primary key.
No primary key/foreign keys defined if target table has the foreign key. There can be no active primary key/foreign key relationship between the source table and the target table in which the target table holds the foreign key.
No foreign key from another table can reference the source table. The source table cannot be referenced by a foreign key in another table.

So, you have to drop the constraint, partition the table and the recreate the costraint. Better to do as a transaction, so on success it can be committed or on failure it can br rolled back.
Step by step tutorial can be found at : http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/
And
http://www.confio.com/logicalread/sql-server-primary-key-vs-clustered-index,-part-3/#.Ui1lNGIpDFo


The backup hangs because the SSAS database is corrupt, which was the result of a forced reboot of the server.

Some questions this raises:

If an SSAS database has an estimated size of 0, does that always indicate a corrupted database?
Are there any other ways to detect SSAS db corruption by using SSMS dialogs or AMO?


DB2 MQT not rewriting role playing columns

You may wish to try creating indexes over the foreign keys.


Find Transactions in Time Range, Ignoring Date

I think you will be fine using DATEPART.

Assume the following columns exist, and assume dates and times don't test for daylight saving time.

This will select all the UserIDs from a table where the time is between 1am and 5pm, since January 1st.

SELECT
    UserID,
    LoginDate
FROM
    [dbo].[LoginDates]
WHERE
    [LoginDate] > '20160101'
    AND DATEPART(HOUR,[LoginDate]) >= 1
    AND DATEPART(HOUR,[LoginDate]) < 5

Notes:

The LoginDate > '20160101' will check for all logins after midnight on the 1st of January 2016.
I'm using >= and < instead of BETWEEN, because it's more accurate.


looking for missing indexes in the cached execution plans

You can try to execute below script and find for missing indexes in cached execution plans from here

SELECT qp.query_plan
, total_worker_time/execution_count AS AvgCPU
, total_elapsed_time/execution_count AS AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads
, execution_count
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]' , 'decimal(18,4)') * execution_count AS TotalImpact
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]' , 'varchar(100)') AS [DATABASE]
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]' , 'varchar(100)') AS [TABLE]
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
WHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex[@Database!="m"]') = 1
ORDER BY TotalImpact DESC

It has sort of worked for me in sql server 2008 and 2012, but still you will need to check if that works the way you looking in sql server 2014.

Also,

In addition to above i would suggest you read Finding what queries in the plan cache use a specific index , a great script from Jonathan, which really helps in understanding the usage of those indexes getting used for the current execution plans in cache.


How can I use an environment variable in a Postgres function?

You can not access a environment variable directly; but you can access a custom configuration setting and set that to a value from the environment at startup.  For example, you can pass the follow startup option using PGOPTIONS:

PGOPTIONS="-c 'custom.some_string=${SOME_STRING}'"

Note: the dot (.) in the name is important.

Then retrieve the value using current_setting:

SELECT current_setting('custom.some_string');

(I have tried this on Linux and found it to be working, it should be the same for Windows, but may need some slight modification to cater for that particular platform.)


Cannot Add "Active Directory - Password" as Linked Server into my SSMS

First, you need to use the new Microsoft OLEDB Driver for SQL Server, aka "MSOLEDBSQL", not the old Microsoft OLEDB Provider for SQL Server, aka "SQLOLEDB".
The old driver doens't support Azure Active Directory Authentication.
Second you have to set the "Authentication" connection string keyword:

Then when you map the login using AAD credentials, the driver will interpret them correctly, and not try to use SQL Auth.

Or in script form:
USE [master]
EXEC master.dbo.sp_dropserver @server=N'AZURE', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'AZURE', @srvproduct=N'', @provider=N'MSOLEDBSQL', @datasrc=N'xxxxxxx.database.windows.net', @provstr=N'Authentication=ActiveDirectoryPassword'

GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AZURE', @locallogin = NULL , @useself = N'False', @rmtuser = N'xxxxxxx@xxxxxxx.onmicrosoft.com', @rmtpassword = N'xxxxxxxxx'
GO
exec ('select 1 a') at AZURE


•Dismounting the storage that the database files reside on the primary server will not fail the server over. I thought the witness would notice that the files do not exist anymore and fail over?

Database mirroring does not look at the individual database, even though that's the unity of failover. It's the same as an availability group in 2012/2014 or 2016 without the database failover option.

This is expected.

  •Throttling the network down to 1kbps on the primary server disconnects the mirror on both the primary and secondary. I would think that the witness would lose connectivity to the primary because the network is so slow and fail over but instead both servers go to disconnected status.

If all are disconnected because the network is exceeding sluggish or gone, then it won't be able to talk between the servers for a failover - the databases need to be synchronized, the primary is online and the session timeout for the mirror elapsed so it looks like a mirror disconnect rather than a primary issue. Even if something were to happen, after the disconnect the databases would not be in a state for automatically fail over.

Reference of the required conditions for automatic failover for mirroring.

Also, with Mirroring completely gone at this point, I'd suggest migrating away from using it... though you probably already know this :)


Calculate percent of roads (by length) where condition is >=15

There will be other solutions - but how about ...

select
  length_15_og
, total_length
, round( length_15_og / total_length * 100, 2 ) as percent_15_og
from (
  select
    ( select sum( length ) from road_condition where condition >= 15 ) as length_15_og
  , ( select sum( length ) from road_condition ) as total_length
  from dual
) ;

-- result
LENGTH_15_OG  TOTAL_LENGTH  PERCENT_15_OG
135           275           49.09

Dbfiddle


how azure sql geo replication works internally?

As you were told by Support, Azure SQL Database's geo replication uses a special flavor of Always On Availability Groups to replicate data. The "special flavor" doesn't matter much in the context of your question, so I am going to respond in terms of "Replication vs Availability Groups" since it applies to your case, too.

Replication

SQL Server Transaction Replication is a way to replicate specific data between databases. By default, it will replication data changes between the Published and the Subscriber. The Distributor has a Log Reader Agent that reads the transaction Log of the Publisher, and converts that into separate update statements, which are sent to the Subscriber via a Distribution Agent.

A single update statement that updates 1,000 rows on the Publisher will generate 1,000 separate update statements on the Subscriber, doing row-by-row updates based on the PK.

There are alternatives, but in most cases, this is the configuration people use, as it is the default behavior. (For example, you can configure stored procedure executions to replicate, so that rather than replicating row-by-row data updates, the Subscriber executes the stored procedure again... You probably aren't doing that.)

As you can imagine, this row-by-row update on the subscriber can be inefficient. Each individual update on the Subscriber will be quick because it is a single-row update based on a Primary Key--but the row-by-row vs set-based operation isn't always ideal.

Availability Groups

Availability Groups (AGs) handle things differently. With AGs, the entire database is replicated, not just specific data. The replication method involves writing to the transaction log on the secondary server, and then redoing the transaction on the secondary server. These two steps are generally referred to as "send" and "redo" phases.

A simplified version of how it works: The overhead on the Primary server for "send" is essentially just that it has to write to the transaction log locally, and also send that same exact information to the Secondary for it's copy of the transaction log. The overhead on the Secondary server for "redo" is essentially just processing that transaction the same way it was handled on Primary.


How do I synchronize parallel imports with COPY?

Parallel DB operations

  Is there any good strategy to coordinate this multiple build?

Well, the right way to run a task that requires multiple connections to the database is to use fork-exec (spawn on Windows). Then you wait in the parent for the children to return. For a good review check out this video on YouTube. It brings up fork-exec about 27 min in. That whole video is probably watching if you're new to Unix.

It's unlikely though that your loads are substantially faster in parallel, because it's not likely that the CPU is the bottleneck.

Loading from Files

If it really does matter though, and you're loading from file rather than query check out pg_bulkload rather than doing this yourself.

Materialized Views

In your case you're batching up CREATE TABLE AS SELECT (CTAS). A CTAS is useful for test data. If what you're doing is not test data, and you're dropping these tables and recreating them or the like then what I highly suggest you check out is MATERIALIZED VIEWS which can even be REFRESH MATERIALIZED VIEW CONCURRENTLY,

  REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. The old contents are discarded. [...]

  CONCURRENTLY Refresh the materialized view without locking out concurrent selects on the materialized view. Without this option a refresh which affects a lot of rows will tend to use fewer resources and complete more quickly, but could block other connections which are trying to read from the materialized view. This option may be faster in cases where a small number of rows are affected.


Is it possible to copy mounted database without RMAN?

If you change file's permissions during copy operation, tar will throw "file changes as we read it".


How to set the sequence to the last used id for all tables in Oracle?

First you have to find out what the current sequence value is and then reset it. I would write a script which does the following tasks:

Get the max value from the table
update/recreate the sequence

The script could look like this:

declare
  maxval number;
begin
  select max(id) into maxval from <table_1>;
  execute immediate 'ALTER SEQUENCE <sequence_name_1> START WITH '|| maxval+1 ||';';

  select max(id) into maxval from <table_2>;
  execute immediate 'ALTER SEQUENCE <sequence_name_2> START WITH '|| maxval+1 ||';';
end;
/

I'm pretty sure there are better scripts but if you have to do this task frequently this might do the job without much development time.


As was pointed out in the comments, you could go with mysqlslap.

If you are a little more adventurous, you may want to setup you own by deliberately dummying down the MySQL configurations or just collecting query profiles. Here are some of my posts and some of the others that address doing stress testing and query profiling. I hope the ideas help.

Apr 04, 2013 : Stress test MySQL with queries captured with general log in MySQL

Accepted Answer
My Answer

Mar 10, 2013 : Stress Test to Saturate Memory for MySQL InnoDB

Accepted Answer
My Answer

Jun 08, 2012 : How to improve MySQL Server Performance..??
Dec 28, 2011 : Is there a tool like Microsoft's "SQL Server Profiler" for MySQL?
Jul 09, 2011 : Testing Query Speed (My Answer was MySQL-specific)

Give it a Try !!!


While returning rows from a loop directly, the sort order is determined by the loop. PL/pgSQL builds up the result set and you can still raise an exception to prevent the function from returning anything until the last moment. (There is a code example in the manual.) But there is no way to re-order the result set before finally returning. You would have to add ORDER BY to the function call for that:

SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar')
ORDER  BY match;

FALSE sorts before TRUE, so non-matches come first.

  But I'm really only interested in the cases where the output's match column is false.

So don't do more work than required. This function does what you need, efficient and safe against SQL injection (unlike your original):

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(_schema1 text, _schema2 text)
  RETURNS TABLE(tablename text, schema1_ct bigint, schema2_ct bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR tablename IN
      SELECT quote_ident(t.tablename)  -- must be table-qualified, due to dupe in OUT parameters!
      FROM   pg_catalog.pg_tables t
      WHERE  t.schemaname = _schema1
      ORDER  BY 1
   LOOP
      EXECUTE format(
         'SELECT (SELECT count(*) FROM %1$I.%3$s)
               , (SELECT count(*) FROM %2$I.%3$s)
          WHERE  EXISTS (SELECT FROM %1$I.%3$s)
              <> EXISTS (SELECT FROM %2$I.%3$s)'
       , _schema1, _schema2, tablename)
      INTO schema1_ct, schema2_ct;

      CONTINUE WHEN schema1_ct IS NULL; -- happens when both or none have values
      RETURN NEXT;
   END LOOP;
END
$func$;

Call:

SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar');

Counting can be expensive with big tables, so this function only counts when required.

Identifiers may or may not require double-quoting and have to be treated like user input to be safe. See:

SQL injection in Postgres functions vs prepared queries