How to perform strictly SQL query that uses multiple values and searches all fields I'm trying to create what I would call an "inclusive" search and by that, I'm not referring to "between". I have a snippet of code and it does what I need it to do but it's hacky and inefficient so I'm wanting to know if I can possibly do it with just SQL. I'm using a third party library called MysqliDb but if you want to forego phrasing your answer in that format, it's fine.
        if ($this->data != '') {
        $searchParams = explode(' ', trim($this->data));
        foreach ($searchParams as $s) {
            $this->db->orWhere('customer_name', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_address', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_city', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_zip', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_email1', '%'.$s.'%', 'like');
        }
        $this->db->having('customer_status', 'Active');
        $this->db->having('owner_id', $this->owner_id);
        $binaryArray = array_fill(0, sizeof($searchParams), 0);
        $results = $this->db->get('tblcustomers');
        $filtered = [];
        foreach ($results as $r) {
            $binaryArray = array_fill(0, sizeof($searchParams), 0);
            foreach ($binaryArray as $key=>$b) {
                if (strpos(strtolower($r['customer_name']), strtolower($searchParams[$key])) !== false ||
              strpos(strtolower($r['customer_address']), strtolower($searchParams[$key])) !== false ||
              strpos($r['customer_city'], strtolower($searchParams[$key])) !== false ||
              strpos($r['customer_zip'], strtolower($searchParams[$key])) !== false ||
              strpos($r['customer_email1'], strtolower($searchParams[$key])) !== false
            ) {
                    $binaryArray[$key] = 1;
                }
            }
            if (!in_array(0, $binaryArray)) {
                $filtered[] = $r;
            }
        }
    } else {
        $this->db->where('owner_id', $this->owner_id);
        $this->db->having('customer_status', 'Active');
        $filtered = $this->db->get('tblcustomers');
    }

$this->data is an input box with possible search words separated by spaces so my intent is to split the value of the input box up based on that and perform a query that has to include all of the parameters. By that I mean, I can easily do a query where it retrieves every row with $this->data[0] or $this->data[1] but I want to exclude rows that don't actually have have all of the words in $this->data in one column or another.
What I have does that but it uses a lot of PHP and I'd rather keep the db querying to SQL exclusively.

Well I looked into the Full Text Search but found it a bit cumbersome to perform it on joined tables, additionally, it's not that efficient so I just cleaned up the function I have

public function findByFilterQuery($request)
{
    $dbFields = ['first_name', 'last_name', 'email_address', 'tt_group.name', 'tt_position.name', 'tt_division.name'];
    $params = isset($request['search']) ? explode(' ', trim($request['search'])) : [];
    $users = User::ArrayBuilder()->with('group')->with('position')->with('division');
    foreach ($params as $param) {
        foreach ($dbFields as $field) {
            $users->orWhere($field, '%'.$param.'%', 'like');
        }
    }
    $results = [];
    $request['page'] = isset($reqest['page']) ? $this->data['page'] : 1;
    $users = $users->paginate($request['page']);
    if (sizeof($params) && $users) {
        foreach ($users as $user) {
            $binaryArray = array_fill(0, sizeof($params), 0);
            foreach ($params as $key=>$param) {
                foreach ($dbFields as $dbColumn) {
                    $val = $user;
                    foreach (explode('.', $dbColumn) as $subColumn) {
                        $val = $val[$subColumn];
                    }
                    $val = strtolower($val);
                    $param = strtolower($param);
                    if (strpos($val, $param) !== false) {
                        $binaryArray[$key] = 1;
                    }
                }
            }
            if (!in_array(0, $binaryArray)) {
                $results[] = $user;
            }
        }
    }
    return sizeof($results) ? $results : $users;
}


Getting most two recent date if I have a list of date.
eg. Jan 01,2017, Mar 02, 2017, Feb 09,2017, Jun 02,2017

How can I get the most two recent date? Mar 02, 2017 and Jun 02,2017

Using PostgreSQL this is pretty easy. The input formats support this form, and you can sort easily and limit to the two most recent dates.

SELECT mydate
FROM ( VALUES
  ('Jan 01,2017'::date),
  ('Mar 02, 2017'),
  ('Feb 09,2017'),
  ('Jun 02,2017')
) AS t(mydate)
ORDER BY mydate DESC
LIMIT 2;

If some of those dates are ahead of the current date, you may want to add some selectivity,

SELECT mydate
FROM ( VALUES
  ('Jan 01,2027'::date),
  ('Jan 01,2029'),
  ('Jan 01,2017'),
  ('Mar 02, 2017'),
  ('Feb 09,2017'),
  ('Jun 02,2017')
) AS t(mydate)
WHERE mydate <= CURRENT_DATE
ORDER BY mydate DESC
LIMIT 2;


How to partition an Oracle table with 65 million rows? The table that needs partitioning contains a list of Components and their Values.

So one table is tblComponents.  Another is tblFields.  Then this table, tblValues, contains the values of the Components/Fields, so it has a Component foreign key as well as a Field foreign key.  Right now this tblValues table contains 68,000,000 values.  Components table contains ~80,000 rows.  Each component has around 500-800 fields of inventory.

I was thinking about creating a partition by hash on the field id.  As the user will query, they will pick something like.. ."Show me the components where field id 5 = '03530'"  Something along those lines.

Sample queries are below that are common:

select * from tblComponents comp inner join tblValues v on comp.comp_id = v.comp_id
where v.field_id = 55 and dbms_lob.substr(v.value, 2000, 1) like '%brick%'

Also, the values are currently stored as CLOB's and can have numeric values or string values.

Any tips regarding how you would partition or index this would be great, especially for optimizing querying.

Partitioning is a licensable option for the Enterprise Edition.  This makes it expensive.  Unless you already have purchased it this is not the way to go.  And even it you have purchased it, it might not be the right approach.

Why not start with more straightforward tuning?  Obviously you have neglected to provide even cursory information regard data distribution (how many distinct values of FIELD_ID?) or database version, let alone something as useful as an explain plan, so the following are guesses.

  "Show me the components where field id 5 = '03530'"

That sort of query would most likely benefit from a compound index on:

tblValues(field_id, comp_id)

  dbms_lob.substr(v.value, 2000, 1) like '%brick%'

Is the sort of thing which is bound to suck.  You show those sorts of queries are common.  In which case you need to use Oracle Text.  That is designed for indexing and searching large free text fields.   Find out more.

In your case I think you need a CTXCAT index; in Oracle 11g we can build Composite Domain Indexes which mix CTXCAT indexes with regular columns.  You might get some good results from creating a CTXCAT index on value ad creating sub-indexes on (field_id) and perhaps (field_id, comp_id).  Find out more.


SQL Server 2008: how much RAM memory should SQL Server use in a 8GB RAM server? We have SQL Server 2008 and 8GB RAM in a customer, hosted in a Windows 2008 R2 SP1, and consider the following amount of free memory:

and consider the following running programs:

Scenario: Users use a website which saves data in this server in a SQL Server database. No more than 5 users.

Question: Considering scenario, that we have 8GB and we also have IIS hosted in this server (which I believe is a bad idea), in how much RAM should SQL Server use? Where can I configure the amount of RAM that SQL Server should really use without impacting Windows, IIS and any other running services?

  SQL Server version: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0
  (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft
  Corporation  Standard Edition (64-bit) on Windows NT 6.1  (Build
  7601: Service Pack 1)

  Windows version: Microsoft Windows [Version 6.1.7601]

Question: Considering scenario, that we have 8GB and we also have IIS hosted in this server (which I believe is a bad idea), in how much RAM should SQL Server use?

The answer to the question "how much memory SQL Server would use" is, as much as it can. For that reason its always advisable to put appropriate value for max server memory so that buffer pool is restricted.

Please don't use task manager to get SQL Server memory utilization it wont give correct value in case when SQL Server service account has locked pages in memory(LPIM) privilege. To find how much RAM SQL Server is using please use below query

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

  Where can I configure the amount of RAM that SQL Server should really use without impacting Windows, IIS and any other running services?

You can do that in Sp_configure using below query.

sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'max server memory (MB)' , xxx --value in MB which you need to set
go
reconfigure.

You can fix max server memory using the query but remember, SQL Server can still use memory outside buffer pool for third party DLL's and extended stored procs. This can lead SQL Server to use memory more that what is restricted in buffer pool

Please read this article. It would help you in setting starting value for max server memory. You should use below counters to set correct value for max server memory and monitor SQL Server memory usage.

SQLServer:Buffer Manager--Page Life Expectancy(PLE): PLE shows for how long page remain in buffer pool. The longer it stays the better it is. Its common misconception to take 300 as a baseline for PLE.   But it is not,I read it from Jonathan Kehayias book( troubleshooting SQL Server) that this value was baseline when SQL Server was of 2000 version and max RAM one could see was from 4-6 G. Now with 200G or RAM coming into picture this value is not correct. He also gave the formula( tentative) how to calculate it. Take the base counter value of 300 presented by most resources, and then determine a multiple of this value based on the configured buffer cache size, which is the 'max server memory' sp_ configure option in SQL Server, divided by 4 GB.   So, for a server with 32 GB allocated to the buffer pool, the PLE value should be at least (32/4)*300 = 2400. So far this has done good to me so I would recommend you to use it.

SQLServer:Buffer Manager--CheckpointPages/sec: Checkpoint pages /sec counter is important to know about memory pressure because if buffer cache is low then lots of new pages needs to be brought into and flushed out from buffer pool,  due to load checkpoint's work will increase and will start flushing out dirty pages very frequently. If this counter is high then your SQL Server buffer pool is not able to cope up with requests coming and we need to increase it by increasing buffer pool memory or by increasing physical RAM and then making adequate changes in Buffer pool size. Technically this value should be low if you are looking at line graph in perfmon this value should always touch base for stable system.

SQLServer:Buffer Manager--Freepages: This value should not be less you always want to see high value for it.

SQLServer:Memory Manager--Memory Grants Pending: If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article

SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.

SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.


How to get current time in Oracle, and use in interval partition? I'm trying to create an interval partitioned table in Oracle where the first partition is bounded to a week from now, but I just can't seem to figure out the syntax.

Here's what I have so far..

CREATE TABLE TRANSACTION
(
....
, TRANSACTION_DATETIME TIMESTAMP(6) NOT NULL
....
)
PARTITION BY RANGE(TRANSACTION_DATETIME)
INTERVAL (NUMTODSINTERVAL(7,'day'))
(PARTITION p_first VALUES LESS THAN (SELECT SYSDATE+7 FROM DUAL));

I've tried that and many other variations, such as..

(PARTITION p_first VALUES LESS THAN (SYSDATE+7));  //OR
(PARTITION p_first VALUES LESS THAN (SYSDATE+7 FROM DUAL));

But none of them seem to work

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.


Does MySQL 5.6 support fan-in replication? I've known for quite awhile that Continuent's Tungsten Replicator supports fan-in replication (example).

By fan-in replication I mean combining multiple datasources into a single instance (like a datawarehouse).

I recently came across this MySQL deck that seems to suggest that this could be done with MySQL 5.6. Specifically slide/page 35 says "Arbitrary Topology" under Topologies:.

Can someone confirm that fan-in replication is possible with MySQL 5.6?

Has anyone setup this topology in 5.6?

Thanks!

At present, MySQL does not support a native Multiple Master, Single Slave topology.

The phrase "Arbitrary Topology" is exact what it says. It is a topology of your making.

If you really want to create a Multiple Master, Single Slave topology, you came to the right place.

You will have to construct the topology based on the book

Pages 373-375 has two diagrams of three DB Servers (M1, M2, S1)

DIAGRAM #1

M1 has the following characteristics

Slave of M2
Database db1 has real data
Database db2 all tables use BLACKHOLE Storage Engine

M2 has the following characteristics

Slave of M1
Database db1 all tables use BLACKHOLE Storage Engine
Database db2 has real data

S1 has the following characteristics

Slave of M2
Database db1 has real data
Database db2 has real data

DIAGRAM #2

M1 has the Database db1
M2 has the following characteristics

Slave of M1
Database db1 all tables use BLACKHOLE Storage Engine
Database db2 has real data

S1 has the following characteristics

Slave of M2
Database db1 has real data
Database db2 has real data

WARNING : As long as you do not run ALTER TABLE statements against a database that is made up of all BLACKHOLE tables, this approach is the closest you can get to Single Slave/Multiple Master Replication. You will have to live with possible latency issues.

Give it a Try !!!

I have written about such topologies before

Apr 18, 2013 : Single slave - multiple master MySQL replication
Feb 03, 2012 : One slave, multiple masters MySql
May 16, 2011 : Is Multi Master Single Slave possible in mySQL DB?
Mar 11, 2011 : MySQL in star topology


How to monitor Mongo databases and Cloud servers? We have mongo databases installed in AWS and Azure based servers. We need to monitor the Mongo databases and Cloud servers on daily basis. Is there any way to healthcheck the databases and servers ?

Thanks,

As per MongoDB documentation here Starting in version 4.0, MongoDB (Community Edition) offers free Cloud monitoring for standalones and replica sets.

Free monitoring provides information about your deployment, including:

Operation Execution Times
Memory Usage
CPU Usage
Operation Counts

By default, you can enable/disable free monitoring during runtime using db.enableFreeMonitoring() and db.disableFreeMonitoring().

You can also enable or disable free monitoring during mongod startup using either:

the configuration file setting cloud.monitoring.free.state
or
the command-line option --enableFreeMonitoring

Once you enable free monitoring, it remains enabled until you disable it.


MySQL Matching records across tables discrepancies I have a database called match, within this database I have a number of tables. Of which in particular I need to marry up two datasets in different tables to identify email addresses that are not contained within the other. I recently had to marry some data so I built a Perl script in terminal to do this, sadly the file I was trying to match had email address as mixed case so I have a discrepancy of around 2,000 rows. Rather than rebuilding the script and waiting hours for it to run using Perl I opted to load it into tables to make my life easier.

The tables in question are called:

clickwithcust - customers I want to exclude - 18,978 records in total.
clickwithoutcust - contains all email addresses from both  clickwithcust and the remaining dataset that i need. - 20,932 records in total.

I ran a query which gives me everyone that has an email address and customer key:

SELECT *
FROM `click_withcust`
INNER JOIN click_withoutcust
ON click_withcust.`email`=click_withoutcust.`email`;

Resulting in 18820 unique rows

Then I wanted to find the inverse of this query so I created the following query:

SELECT *
FROM `Click_withoutcust`
WHERE NOT EXISTS (SELECT DISTINCT `email`
FROM click_withcust WHERE `Click_withoutcust`.`email`= click_withcust.`email`);

This should return the unique number of missing email addresses from the table click_withoutcust. Sadly I get a figure of 2,175 records when in theory I should get 1,954.

I'm at a bit of a lost end here and could really do with some help.

I think this could be a whitespaces issue. The problem is that MySQL ignores trailing whitespace when doing string comparison. See http://dev.mysql.com/doc/refman/5.6/en/char.html

  All MySQL collations are of type PADSPACE. This means that all CHAR,
  VARCHAR, and TEXT values in MySQL are compared without regard to any
  trailing spaces.

  ...

  For those cases where trailing pad characters are stripped or
  comparisons ignore them, if a column has an index that requires unique
  values, inserting into the column values that differ only in number of
  trailing pad characters will result in a duplicate-key error. For
  example, if a table contains 'a', an attempt to store 'a ' causes a
  duplicate-key error.

The section for the like operator gives an example for this behavior (and shows that like does respect trailing whitespace):

mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a ' |
+------------+---------------+
|          1 |             0 |
+------------+---------------+
1 row in set (0.00 sec)

Unfortunately the UNIQUE index seems to use the standard string comparison to check if there is already such a value, and thus ignores trailing whitespace. This is independent from using VARCHAR or CHAR, in both cases the insert is rejected, because the unique check fails. If there is a way to use like semantics for the UNIQUE check then I do not know it.

I made a test so you could see if there's some fields with whitespace that you're trying to matching.

Tables:

CREATE TABLE `click_withcust` (
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `click_withoutcust` (
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Data:

mysql> select * from test.click_withoutcust;
+--------------------+
| email              |
+--------------------+
| onaaree@gmail.com  |
| onare123@gmail.com |
| onare@gmail.com    |
+--------------------+
3 rows in set (0.00 sec)

mysql> select * from test.click_withcust;
+------------------+
| email            |
+------------------+
|  onare@gmail.com |
| onare@gmail.com  |
+------------------+
2 rows in set (0.00 sec)

mysql>

If you see there's a row with one whitespace onare@gmail.com.

SELECT click_withoutcust.*
FROM click_withoutcust
LEFT JOIN click_withcust
ON (click_withcust.`email`=click_withoutcust.`email`)
WHERE click_withcust.email IS NULL;

mysql> SELECT
    -> click_withoutcust.*
    -> FROM click_withoutcust
    -> LEFT JOIN click_withcust
    -> ON (click_withcust.email=click_withoutcust.email)
    -> WHERE click_withcust.email IS NULL;
+--------------------+
| email              |
+--------------------+
| onaaree@gmail.com  |
| onare123@gmail.com |
+--------------------+
2 rows in set (0.00 sec)

But if you count the tables rows:

mysql> select count(*) from test.click_withoutcust;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test.click_withcust;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

Try using COUNT and LIKE to find if there are rows with whitespaces.

mysql> select count(*) from test.click_withoutcust where email like '% %';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test.click_withcust where email like '% %';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)


MSSQL - Query had to wait for memory grant How do I go about troubleshooting an SP that times out every once in a while. When you include the actual execution plan, it says "The query had to wait for XXs for memory grant"

This does not happen on all the environments where the SP is running.

I have tried increasing the memory the on one of the machines where this occurs, it had no effect (8gb -> 16gb).

I am aware of the Memory Grant query hints, but this is not an option since the minimum requirement is SQL2012 (with no SP) and the hints were introduced in SP3.

Any guidance would be appreciated.

Query execution plan: https://www.brentozar.com/pastetheplan/?id=S1YAhUUbf

As sp_BlitzErik suggested, I used sp_Blitzcache to highlight the issues in the offending proc. I was able to address them and the problem went away. I highly recommend having a look at the SQL First Responders Kit. I wish I could give the credit for this answer to to Erik.


Is the same thing(internally) putting conditions in a query over a view that directly on the view? I'm having trouble understanding performance with queries on views. Given this two scenarios, the queries internally will be the same?

SCENARIO 1

View:

CREATE VIEW TEST.SIMPLEVIEW AS
SELECT
    TABLE1.*,
    TABLE2.*
FROM TABLE1
INNER JOIN TABLE2 ON
    TABLE1.id = TABLE2.fk_id;

Query:

SELECT * FROM SIMPLEVIEW WHERE status = 'P'; -- P in select

SCENARIO 2

View:

CREATE VIEW TEST.SIMPLEVIEW AS
SELECT
    TABLE1.*,
    TABLE2.*
FROM TABLE1
INNER JOIN TABLE2 ON
    TABLE1.id = TABLE2.fk_id
WHERE TABLE1.status = 'P'; -- P in view definition

Query:

SELECT * FROM SIMPLEVIEW;

QUESTION

The queries internally will be the same? Disregarding the fact that the condition of status would be or not permanent one.

Generally speaking a simple view is the same as a query, behind the scenes the view is expanded and passed along to the query optimizer as a normal query. Such a view is an organizational tool, not a performance enhancing one. Use these sort of views to limit what data some users can access directly (if users are accessing your DB directly instead of through Store Procedures) or to simplify referencing a commonly repeated query.

Indexed Views are a type of view for MSSQL Server implementations that can have performance implications as they possess their own unique clustered index on a subset of the underlying tables' data. This sort of view can improve performance as the clustered index is a copy of the data kept logically separate from the original tables, however it entirely depends on how the Indexed View is built. If you see references to views improving performance they may be talking about these and this option is only available on MSSQL Server to my knowledge.


What relevance does the table-level ANSI_NULL setting have? Apparently, SQL Server stores the current ANSI_NULL setting when creating a table (see related questions here and here).

However, when I execute

SELECT * FROM myVeryOldTable WHERE someFieldWithNullValues = NULL

I get zero results, even though the old table was created with ANSI_NULL OFF. So, apparently, SQL Server uses the current connection setting, which is ANSI_NULL ON.

If that is the case, what difference does the setting stored in the table make (except for making trouble in various cases)? Why is it stored at all, if the connection setting always overrides the table setting?

The table-level ANSI_NULLS setting matters for automatic table-level operations like when processing computed columns. This also applies to other objects such as triggers (as seen here).

For operations against the table (i.e. comparisons in queries) the connection applies though - this can be quite confusing if you are not expecting a difference in behaviour between what you actively do and automatic operations.


select group by with multiple AND conditions on the same columns I've an sql table like this:

SID|SERVICE_SID|ATTRIBUTE_SID|VALUE
1    2           5             "VAL1"
2    2           4             "VAL8"
3    2           3             "VAL4"
4    11          5             "VAL1"
5    11          3             "VAL4"

I'd like to group by service_SID where for the same service_sid:

AND (attribute_sid = 5 && Value="VAL1")

AND (attribute_sid = 4 && Value="VAL8")

So, in this case, only a row with service_sid = 2 should be returned.

Is this a query like this even possible? What would it look like?

You can use INTERSECT which is available in both HSQLDB and SQL Server

SELECT SERVICE_SID
FROM SQLTable
WHERE attribute_sid = 5 AND Value='VAL1'
INTERSECT
SELECT SERVICE_SID
FROM SQLTable
WHERE attribute_sid = 4 AND Value='VAL8';

You can then join the result of this query with your table and group by SERVICE_SID


Circular reference and multi-tenancy I'm have an issue with circular reference in my DB design. As see the ER diagram below have 2 circular reference that I highlighted with Red and Orange arrow.

Anyone know how to resolve this issue?

You do not have circular references. All those arrows point in the same direction, "up" to the tenant.

You can insert data to populate the tables without having a dependency on data not yet inserted.

Tenant
Group
Task
App
Group App
User
Group User

This would only be a circular reference if, for example, tenant had a user_id reference that pointed back down to the user table, which references back up to the tenant.

The arrows in your diagram have multiple paths back up to the "top" of the hierarchy (tenant, which is drawn to the bottom left). There is a clear tail and head of every path.

But the arrows don't create a circular cycle. In a circular reference, there is no clear head or tail in the path of the arrows.

So great job on your schema design! You did better than you thought.


Why isn't my new index/table being created on the filegroup I specify? Results from @@Version

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
Sep 21 2011 22:45:45
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Here is the database definition

CREATE DATABASE [Scratch] ON  PRIMARY
( NAME = N'Scratch_mdf', FILENAME = N'N:\MSSQL\TEST\Primary\Scratch_mdf.mdf' , SIZE = 17664KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
 FILEGROUP [DATA]  DEFAULT
( NAME = N'Scratch_dat1a', FILENAME = N'N:\MSSQL\TEST\Data\Scratch_dat1a.ndf' , SIZE = 14539584KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_dat1b', FILENAME = N'N:\MSSQL\TEST\Data\Scratch_dat1b.ndf' , SIZE = 12016128KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
 FILEGROUP [INDEX]
( NAME = N'Scratch_idx1', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx1.ndf' , SIZE = 92864KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_idx2', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx2.ndf' , SIZE = 84416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_idx3', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx3.ndf' , SIZE = 84416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_idx4', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx4.ndf' , SIZE = 92864KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
 FILEGROUP [PM_G0]
( NAME = N'PM_data0', FILENAME = N'N:\MSSQL\PDB1\Data\PM_data0.ndf' , SIZE = 768000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'Scratch_log1', FILENAME = N'N:\MSSQL\TEST\Log\Scratch_log1.ldf' , SIZE = 833024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO

ALTER DATABASE [Scratch] SET COMPATIBILITY_LEVEL = 100
GO

Here is the code to create the table and to add a clustered index.  In both cases I'm specifying filegroup PM_G0 but when I check its on filegroup DATA.

CREATE TABLE Scratch.dbo.STUPID_TEST (dummycol int) ON [PM_G0];
CREATE CLUSTERED INDEX ix_test ON stupid_test(dummycol) ON PM_G0;
-- Tried the filegroup name with/without brackets just in case

I'm checking the location of the first heap, then clustered index by using this query:

SELECT o.name objectname, fg.name filegroupname, sdf.name as datafile,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.tables t ON o.object_id = t.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
INNER JOIN sys.filegroups fg ON fg.data_space_id = i.data_space_id
INNER JOIN sys.database_files sdf ON sdf.data_space_id=fg.data_space_id
WHERE o.name = 'STUPID_TEST'

Interesting test.  I changed the default filegroup to [index].  Confirmed that [index] was in fact the default index in sys.filegroups then dropped and re-created my clustered index and it still went to the [DATA] filegroup.  Code below

ALTER DATABASE Scratch MODIFY FILEGROUP [index] DEFAULT;
GO
DROP INDEX dbo.STUPID_TEST.IX_test;
CREATE CLUSTERED INDEX ix_test ON stupid_test(dummycol) ON [PM_G0];
GO

First thanks to everyone who tried to help me with this here and on twitter.  We did however finally find the answer.  It turns out that someone created a database trigger on model.  The trigger parses every create index/table command and changes it to a DATA filegroup if it's a heap or clustered index and an INDEX filegroup for everything else.  We were then able to track the trigger back to a policy.


Select/print data from subquery In my application I have a table "user", a table "role", and a table "userroles". Userroles is simple table consisting of user, role, where user corresponds to the id column of the user table. As user can have multiple roles, and obviously multiple users can have the same role. Now I wish to find all users who do NOT have the specific role (private_account), and I wish to list them as (user_id, new-inserted-role-id) for further processing.

SELECT u.id, r.id
FROM public."user" AS u
WHERE u.id NOT IN (
    SELECT ur.user
    FROM public."userroles" as ur
    INNER JOIN public."role" as r
    ON ur.role=r.id
    WHERE r."roleDescription"='private_account'
)

However this complains that the table r does not exist.

ERROR:  missing FROM-clause entry for table "ur"

How can I do this?

So for clarity: if private_account would have id 2 and I a user 1 which does not have this ID the return would look like:

USER | role
1    | 2
2    | 2

The role reported is independent on what the user actually has. I could do this manually and do a select like below. However this has the huge disadvantage that I have to manually update the sql if I move to a different database (since the ID might not correspond to the actual role name).

SELECT u.id, 2
...

The further processing is basically using the return from the select as INSERT INTO:

INSERT INTO public."userroles"
SELECT u.id, r.id
FROM public."user" AS u
WHERE u.id NOT IN (
    SELECT ur.user
    FROM public."userroles" as ur
    INNER JOIN public."role" as r
    ON ur.role=r.id
    WHERE r."roleDescription"='private_account'
)

Create tables:

postgres=# create table "user" (
    id int primary key,
    name text unique);
postgres=# create table "role" (
    id int primary key,
    name text unique);
postgres=# create table "userroles" (
    "user" int references "user",
    "role" int references "role",
    primary key("user", "role")
);

Populate test data

postgres=# insert into "user" values (1, 'privateuser'), (2, 'publicuser');
postgres=# insert into "role" values (1, 'private_account'), (2, 'other_role');
postgres=# insert into userroles values (1,1), (1,2), (2,2);

Find all users and their roles:

postgres=# SELECT u.id, u.name as "user", r.id, r.name as "role"
    FROM "user" u
    LEFT JOIN "userroles" ur ON ur.user=u.id
    LEFT JOIN "role" r ON r.id=ur.role;

 id |    user     | id |      role
----+-------------+----+-----------------
  1 | privateuser |  1 | private_account
  1 | privateuser |  2 | other_role
  2 | publicuser  |  2 | other_role
(3 rows)

Find all users who do NOT have the specific role:

postgres=# select u.id, u.name as "user", r.id, r.name as "role"
    FROM "user" u
    LEFT JOIN userroles ur ON ur.user=u.id
    LEFT JOIN "role" r ON r.id=ur.role
    WHERE u.id NOT IN (
        SELECT "user" FROM userroles ur2, "role" r2
        WHERE r2.id=ur2.role AND r2.name='private_account');

 id |    user    | id |    role
----+------------+----+-------------
  2 | publicuser |  2 | other_role
(1 row)

This last query is your answer.


INSERT EXEC statement cannot be nested I have a stored proc spGetSites that returns three columns for SiteName, SiteId and UnitCount. The stored proc runs perfectly fine.
I'm trying to store the results of the stored proc to a temp table @Site to use for my report with below syntax. This time I got an error message: "INSERT EXEC statement cannot be nested"

Can I not use INSERT and EXEC statement at the same time?

DECLARE @Site TABLE (SiteName VARCHAR(100), SiteId INT, UnitCount INT)

INSERT INTO @Site
EXEC spGetSites @SiteId = 0

If you look at the code for your spGetSites procedure, somewhere in that procedure is another INSERT...EXEC. It may be directly in that procedure, or nested in the bowels of some other procedure it calls.

Ultimately, if a stored procedure uses INSERT...EXEC then if you try to call that stored procedure in the context of another INSERT...EXEC you will get the error you are seeing.

How do you fix it?

You could simply take the inner INSERT...EXEC and inline the code to this single stored procedure. Though, I suspect that other procedure may be there for a reason: ie to keep your code DRY.
Since this is a Get procedure, hopefully there's no data manipulation happening anywhere in the call stack. You could convert the child procedure into a table-valued function. This would allow you to convert that inner INSERT...EXEC into an INSERT...SELECT and resolve this issue.
Use temp tables scoped to the outer procedure to pass data between procedures. This solution gets complicated, so it's not my favorite, and I generally discourage this pattern when there's a better option--but for the sake of completeness, I'll include it here.  Basically, if you create your #temp table outside spGetSites you can use it inside spGetSites (without creating it inside there), and the table with data inserted will survive the procedure execution and continue to work.

I don't like option 3 because it's a coding pattern complex enough to ensure someone messes it up in the future, unless everyone is on board and familiar with the coding pattern:
* spGetSites will fail unless you create the table first. All callers need to remember to create the table exactly the same first.
* spGetSites can't assume the table is empty. It might have existing data from the outer call (or a prior execution from the same caller)
* Troubleshooting and debugging (and even getting a query plan) for spGetSites is more complex because of the table creation confusion.

What would I do?

Without knowing how complex the code is behind spGetSites, I'd look into creating an inline TVF that replaces the inner INSERT...EXEC with INSERT...SELECT or possibly all of spGetSites could be simplified/rewritten to make it self contained without the INSERT...EXEC


Wal position to wal archive number I want to manage the copy of archiving to remote location.
For that I want to delete all the archives I copied unless they are required by the standby.

So, for running pg_archivecleanup $ARCLOC $CHECKPOINT I need to find the most recent checkpoint which is ok to delete. I don't look on the recent .backup files as it is too old for my archive capacity. What I do want to look on is the replay_location \ replay_lsn(depend on the version) from pg_stat_replication.

However, I do not know how to convert from replay_location value to checkpoint value. I.e. from something like 7/2DBCED18 to 000000020000000700000029.

Answer:

Function is: pg_xlogfile_name

When you start a backup or stop a backup you'll get a WAL location, if you want to know it's physical location on disk use the following function: select pg_xlogfile_name('7/2DBCED18');


Change archivelog location in Oracle Oracle instance I set up is using USE_DB_RECOVERY_FILE_DEST for archivelog location.

log_archive_dest_1                   string

LOCATION=use_db_recovery_file_
dest, valid_for=(ALL_ROLES,ALL
_LOGFILES)

I want to specify the exact location. Old and new location is same, only need to specify the path. I cannot restart the database as it is production server. Is there any way I can do it without restarting the database

There are the manuals Specifying Archive Destinations,LOG_ARCHIVE_DEST_n. But they are unclear about if it is possible to change it online.

Try it out on a test instance. You always should try out thinks on a test instance. Especially if you are using some tips from the internet.
If you an alter system set = ... scope=both; and the parameter cannot be specified in memory, you get a

ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

error, but nothing bad happens. So you can try it out if the parameter is specified in memory.

I tried to set the parameter to the value already set and it worked

SQL> alter system set log_archive_dest_1=
  2  'location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES,ALL_ROLES)'
  3  scope=both;

System altered.

SQL>

If you change the destination for the archives and there are still archives in the old destination then the database knows this an therefore RMAN knows this and will not forget them but backup them when you do your next archive log backup.


Messaging Database - How to handle multiple recipients So I am trying to develop a relational database (I'm using MySQL, planning on a Java front end) where I have 3 tables (People, where I store names and relevant personal information... and Messages (where for every message sent, the relevant information is stored. Each message is given an ID# from which you can draw the date and time sent, the content of the message, the sender of the message, and the recipient of the message).

My problem is setting up the database such that each message can have just one sender but multiple recipients. For instance, I can easily handle message ID 73 being sent from User 6 to User 3. But how would I handle if message ID 74 was sent from user 4 to users 2,3, and 8? Is there a way to allow multiple recipients? Would I need to rethink how the database is structured? Anyone got some advice/tips?

You need to have a many-to-many table for your message recipients.  Think about it this way, each message can be sent to one or more recipients.  Also, each recipient can presumably receive zero, one or more messages.  This means you have a many-to-many and you need an intersection table to record this information in your database.

Consider this ERD:


SQL Server: Effect of Setting These Index Options A reindexing script was recently run in our dev environment that set ALLOW_ROW_LOCK = FALSE and ALLOW_PAGE_LOCKS = FALSE on all the indexes in the database.

This was done while testing new reindexing scripts--it was not done intentionally.

After this script was run, we immediately started seeing a large number of deadlocks where we normally never saw them.

Was setting the locking options on the indexes the direct cause?
Why exactly would those settings cause deadlocks?

The article linked to in the comments makes it clear, but let's prove it with an example.

CREATE TABLE t1(a int);
INSERT INTO t1(a) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);

CREATE CLUSTERED INDEX IX_t1
    ON t1(a)
        WITH(ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); /* Default */

BEGIN TRANSACTION;

    UPDATE t1
        SET a = 10
        WHERE a = 1;

    EXEC sp_lock;

ROLLBACK;

DROP TABLE t1;

Results (filtered to SPID and table of interest):

spid   dbid    ObjId       IndId   Type    Resource        Mode    Status
52      6       213575799   1       PAG     1:118           IX      GRANT
52      6       213575799   1       KEY     (de42f79bc795)   X      GRANT
52      6       213575799   1       KEY     (241332e1ddb0)   X      GRANT
52      6       213575799   0       TAB                     IX      GRANT <--
52      6       0           0       DB                       S      GRANT

(There are two KEY locks because SQL Server needs to protect both the "before" and "after" index values. If you want to see this, run SELECT a, %%lockres%% FROM t1 before and after the UPDATE statement.)

If I run the same script again with the clustered index definition having ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF, I get this:

spid   dbid         ObjId   IndId  Type    Resource    Mode    Status
52      6               0       0   DB                    S     GRANT
52      6       229575856       0   TAB                   X     GRANT <--

So it should be pretty obvious now why you suddenly started having deadlocks!


ORA-39097: Data Pump job encountered unexpected error -942 Would somebody know what is that error?

This is during the impdp command and there is nothing imported.
The full error is:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-00942: table or view does not exist
ORA-06512: at "SYS.KUPU$UTILITIES_INT", line 1579
ORA-06512: at "SYS.KUPM$MCP", line 2710

ORA-39097: Data Pump job encountered unexpected error -942

Known issue:

https://community.oracle.com/thread/4125750

ORA-39006, ORA-39065 and ORA-00942: table or view does not exist When Starting DataPump in 12.2 (Doc ID 2341607.1)

Rebuild the missing part by running:

catalog.sql
catproc.sql
utlrp.sql


Adding an index without a lag (PostgreSQL) PostgreSQL:
Adding an index to a table with big amount of data causes a lag for both SELECT and INSERT operations on that table, doesn't it?
While PostgreSQL does not support live (without lags) adding of indexes, how to solve the problem of adding an index to a table without lagging SELECT and INSERT (there is no UPDATEs on that table)?
My idea is to copy the table by small chunks to another table with the same structure, then in a transaction delete the original table and rename the new table to the original table name. How to ensure that the entire data is copied, while INSERT operations to the old table may happen?

See Building Indexes Concurrently in PostgreSQL documentation.
CONCURRENTLY option of CREATE INDEX solves the problem in an easy way. Just add it to CREATE INDEX.
Be aware that while CONCURRENTLY option avoids lags, it does slow down building the index and that CREATE INDEX with CONCURRENTLY cannot be used in a transaction.


Hot and Cold data Storage using partitioning I am working with a data-ware house and i want to partition the last three years of data into Hot data storage and any further than three years should be placed to cold. I want to be able to run a script monthly that would move data from Hot to Cold if data as gotten older I have currently Started by looking on line and have been testing the following script

Alter Database [Dev_PartionTest] Add Filegroup [HOT_DATA]       --------
Go                                                                  --------
Alter Database [Dev_PartionTest] Add Filegroup [COLD_DATA]       --------
Go                                                                  --------

Alter Database [Dev_PartionTest] Add FILE ( NAME = N'HOT_DATA', FILENAME = N'....\HOT_DATA.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB, MAXSIZE = 20000MB ) TO Filegroup [HOT_DATA]
Alter Database [Dev_PartionTest] Add FILE ( NAME = N'COLD_DATA', FILENAME = N'..\COLD_DATA.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB, MAXSIZE = 20000MB) TO Filegroup [COLD_DATA]

 Declare @Previousyear Date
 set @Previousyear = (SELECT DATEADD(year, -2, GETDATE()));

 Create Partition Function [MonthlyPartitionArchive] (datetime)
   as Range Right For Values (@Previousyear,  GETDATE())

Create Partition Scheme RangePartScheme as Partition
 [MonthlyPartitionArchive]
 To ([COLD_DATA], [HOT_DATA], [PRIMARY])

 Create Clustered Index IDX_Part On Tester(created)
 On RangePartScheme (created);

Could you guide me as to how i can develop such a a script.

    Create Partition Function [MonthlyPartitionArchive] (datetime)
   as Range Right For Values (@Previousyear,  GETDATE())

The issue i face is if i use the getdate to insert a record

 INSERT INTO Tester( Name, gender, created, description )
 VALUES ('Nicks', 'M',GETDATE(), 'Lloyds Office'),

The getdate function would return date higher than the date time used in the function above to set the upper bounds on the right in the create partition function. how can i set the current year without having to type it in manually everytime.
Can this script be running on a monthly process to update partition is that possible.

You want RANGE LEFT instead of RANGE RIGHT, and you only need a single boundary value at the three-year-mark.
Make the partition function like this:
Create Partition Function [MonthlyPartitionArchive] (datetime)
 as Range LEFT For Values (@Previousyear);

The scheme should then be:
Create Partition Scheme RangePartScheme as Partition [MonthlyPartitionArchive]
 To ([COLD_DATA], [HOT_DATA]);

There is no way to create a dynamic partition function.  You'll need to schedule a job to alter the partition function as needed.  Look at this answer for details about how to automate it.


Postgres pg_restore error: invalid byte sequence for encoding "UTF8": 0xe05c62 Lucky me, I get to play DBA today!

I am migrating a Postgres 8.4 DB from one server to another (both RHEL6).  I backed up the schema from the old server and restored it to the new server.  Then I did a full database backup and restored it to the new server, but I had two tables that were skipped during the restore.  In both cases I used the format=c option with pg_dump and pg_restore.

I am now focusing on those two tables, and i need help!  I do a backup on the old server:

pg_dump --format=c --file=mytable.sqlc -a --table public.mytable mydatabase

Now I go to the new server and attempt a restore:

pg_restore -d mydatabase -t mytable --format=c -a -c mytable.sqlc

I'm getting the following errors:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2241; 0 23420338 TABLE DATA mytable mydatabase_script
pg_restore: [archiver (db)] COPY failed for table "mytable": ERROR:  invalid byte sequence for encoding "UTF8": 0xe05c62
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT:  COPY mytable, line 231993
WARNING: errors ignored on restore: 1

The error seems to imply that I have an encoding difference between the old db server and the new one (even though I backed up/restored the schema).  I did a:

select * from pg_database where datname = "mydatabase"

And it shows that both databases are UTF-8 encoded.  What do I need to check next?  Thanks.

Your options are pretty simple, and the error message is explicit. This is the problem

  ERROR:  invalid byte sequence for encoding "UTF8": 0xe05c62

And this hint pretty much tells you what we would tell you,

  HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

So you have two options,

Remove the byte sequence (assumes you may not need it), there is no indicator that you know what it's actually supposed to be anyway.
Set the client_encoding to whatever encoding supports that sequence, as UTF-8 doesn't.

You may want to confirm the encoding of your database

SELECT pg_encoding_to_char(encoding)
FROM pg_database
WHERE datname = 'yourdb';

And confirm that the output encoding (--encoding to pg_dump) supports all of those characters (is the same or a superset). And, that also the new database is either the same encoding or a superset.

Try not to use --format=c, it only adds complexity. If you're having problems use plain, the default.


Column sort order in CREATE INDEX statement? When creating an index one can determine if an index column is sorted using ascending or descending order. BOL says that

[ ASC | DESC ]
Determines the ascending or descending sort direction for the particular index column. The default is ASC.

What is the primary reason for choosing between ASC or DESC?

Since indexes can be scanned in both directions most of the time there isn't much reason for choosing DESC.  Some TOP queries could be helped by the DESC on some of the columns of a covering index but only testing will show if this helps.

One other difference is that the forward order scan can be run in parallel but the reverse order scan is always single threaded.


Using oracle easy connect does it really connect to the remote DB? I tried connecting to remote host db using :

 sqlplus abc/abc@hostname1:1000/ABC

And tried printing

!hostname

it still shows me local hostname from which I am running and not HOSTNAME1.

So if I want statistics or run a script on that remote host .. Would it not do that..
i.e. Do I actually get data from remote host?

Is it bcoz !hostname is not a linux command and not a DB command?

The answer is very simple. The ! prefix in sqlplus executes the following text as a command in the local shell.

In your case it's executing the Unix hostname command locally.

Query v$instance to check the name of the machine you're connected to.

There are ways to execute local Unix commands via the database, but it's seen as a huge security hole, so I won't document them here.

ssh (assuming Unix) into the database host instead.


Postgres 10.5: Granting full schema access to a user that isn't the owner So the underlying problem I'm having is trying to migrate my on premise Postgres 10.5 instance to RDS. RDS does NOT give end users the SuperUser role so I'm hitting tons of permissions issues when trying to import from a pg_dump.

For example, logged in as user postgres - this is the user created when I created the RDS instance. What amazon calls the "root user":

CREATE SCHEMA f_site;
GRANT ALL ON SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON DATABASE mydb to postgres;

Those all get set without error. Now, the owner changes:

ALTER SCHEMA f_site OWNER TO schemaadmin;

Great, however if I try to immediately run another grant, just to test my permissions, still logged in as postgres user, same session:

GRANT ALL PRIVILEGES ON SCHEMA f_site TO postgres;
ERROR:  permission denied for schema f_site

Basically I am trying to create a 'pseudo-superuser' by giving the postgres user full grants on everything it creates, then transfering ownership of that object to another role.

This is because the very first lines in my pgdump backup are essentially creating schema and setting schema owners, then later (thousands of lines later) making changes/additions to it. Normally with a superuser this wouldn't be a problem.

Thanks for any advice.

Ok figured this out after doing a few more searches.

Essentially you need to grant same role that eventually will own the object to the user you're using.

So in my case: postgres is the user that I want to be my "pseudo-superuser" and schemaadmin is the user role that will own the objects.

GRANT schemaadmin TO postgres;

After that, everything that schemaadmin owns, postgres can modify.


Linked Server works on SSMS and isn't found on SSDT My Microsoft SQL Server has a linked server to another SQL Server. I have a simple query that runs on mine, deleting a remote table (truncate doesn't work) and inserting data to it.

This query works fine if it's run in SSMS. But I need it to be run on a schedule, so I add it in a dtsx as an EST. The problem is that when I run this dtsx, be it manually on SSDT or automatically scheduled on SQL Server Agent, it fails with the message:

  could not find server '-----' in sys.servers`.

This is very odd. Of course the server is in sys.servers and the linked server is working. As I said, the exact same query works when run on SSMS. I can't even start to imagine what may be causing this inconsistency. Other linked servers work fine.

Can somebody guess what may be causing it and what I could look for a clue? Googling this error only points me to explaining how to config linked server, but it's already there!

Could you share the linked object name along with the query?
Sometimes the problem is on the FROM part. There are a lot of situations where you can get this error, and many of them are covered on StackOverflow. However, one case which might especially be encountered on shared servers is simply that the database name has a period (dot) in it. For example, if its name is mysite.com_DB. This will automatically cause the problem.

The solution, if you can't rename the database, is to encapsulate the DB name in square brackets, so for example:

mysite.com_DB.Table_name

Will turn into:

[mysite.com_DB].Table_name


How can a weak entity be related to a weak entity? As we can see in the entity-relationship diagram (ERD) below, the ATTENDANCE entity is related to the TRANSCRIPT entity. But how can entities in TRANSCRIPT be identified if there is no primary key in ATTENDANCE? Is the ERD wrong?

All tables, according to Codd's definition, must have a primary key. The key can be simple, consisting of a single column, or compound, consisting of multiple columns. It is perfectly acceptable for those columns to be foreign keys. When FKs are part of the PK it is sometimes called a weak entity. I guess because the entity type cannot stand on its own - it requires another in order to make sense.

For the given diagram a valid implementation would be

Table: College
Columns: Name
Foreign Keys: none
Primary key: Name

Table: Attendance
Columns: CollegeName, StartDate, EndDate
ForeignKeys: CollegeName references College(Name)
PrimaryKey: CollegeName, StartDate

Table: Transcript
Columns: CollegeName, StartDate, CourseName, Semester etc.
ForeignKeys: (CollegeName, StartDate) references Attendance(CollegeName, Startdate)
PrimaryKey: CollegeName, StartDate, CourseName

These primary keys consist of attributes that are placed in the entity types "naturally" through analysis and normalisation. Hence they are called "natural keys." As you can see natural keys can quickly become unwieldy. That is why they are often replaced by a unique integer. This integer is called a surrogate key, because it stands in for the natural key.

Inserting a surrogate key in the database design typically converts a weak entity to a strong one i.e. the surrogate replaces the whole of the natural key. This is certainly the easiest to code and is supported by the SQL IDENTITY feature. It need not be so, however. Say we added a surrogate to Attendance - let's call it AttendanceId. It would still be legitimate to model Transcripts as (AttendanceId, CourseName). It would still be a "weak" entity.


Raw cast of arbitrary string to integer I have non-numeric string IDs in a table, and would like to cast them to unique integers. I need the mapping to be invertible / one-to-one. The string IDs are of uniform length.

I want to do this by literally aggregating the ASCII codes of each character in the ID. The ASCII function only returns the leftmost character. Is there an efficient way to cast the entire string?

Presuming you are using SQL Server, which you still haven't confirmed, I'd use an inline table-valued-function (TVF) that can be used to efficiently and safely convert character-data into an integer value, for strings of 8 characters or less.  The string must be a maximum of 8 characters in length since that is the maximum number of bytes for an integer in SQL Server.

This is the TVF:

IF OBJECT_ID('dbo.GetBigInt') IS NOT NULL
DROP FUNCTION dbo.GetBigInt;
GO
CREATE FUNCTION dbo.GetBigInt (@data VARCHAR(1000))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT BigIntResult = CASE WHEN LEN(@data) > 8 THEN 1/0
    ELSE CONVERT(BIGINT, CONVERT(VARBINARY(8), @data, 0)) END;
GO

To test the function, I created the following test-bed table with some sample rows:

IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t
(
    SomeText VARCHAR(100)
);

INSERT INTO #t (SomeText)
VALUES ('aaaaaaaa')
    , ('AAAAAAAA')
    , ('aaaaaaab')
    , ('ZZZZZZZZ')
    , ('ÿÿÿÿÿÿÿÿ')
    , (CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1))
    , (CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127))
    , (CHAR(128) + CHAR(127) + CHAR(128) + CHAR(128) + CHAR(128) + CHAR(128) + CHAR(128) + CHAR(128))
    , ('aaaaaaaba');

Run the TVF against the table like so:

SELECT *
FROM #t t
CROSS APPLY dbo.GetBigInt(t.SomeText) g;

The last row in the sample intentionally has 9 characters, which cannot be reliably converted into an 8-byte integer.  When the TVF attempts to convert that row, it will display the following error:

  Msg 8134, Level 16, State 1, Line 32
  Divide by zero error encountered.

The results for the other rows in the sample data are:

To test the speed of the TVF, I inserted 1,000,000 rows into the table, like so:

INSERT INTO #t (SomeText)
SELECT TOP(1000000) LEFT(c1.name, 8)
FROM sys.columns c1
    CROSS JOIN sys.columns c2
    CROSS JOIN sys.columns c3;

The execution plan for the query running against 1,000,000 rows:

If your "non-numeric string IDs" are longer than 8 bytes, I'd recommend simply converting them to BINARY(x) or VARBINARY(x) values, where x is then maximum length of your character data.

IF OBJECT_ID('dbo.GetBinary') IS NOT NULL
DROP FUNCTION dbo.GetBinary;
GO
CREATE FUNCTION dbo.GetBinary (@data VARCHAR(1000))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT BinaryResult = CONVERT(VARBINARY(1000), @data, 0);
GO

SELECT *
FROM #t t
CROSS APPLY dbo.GetBinary(t.SomeText) g;


Move database to another drive in same server I have installed MSSQL server 2008 in my server in C: drive and I want to move it to the D: drive completely. That means, database locations, installed location to move another drive with data. Is it possible to done this?

Note: Detach method is only for move database. but I want to move whole installation and DB files to another drive.

It is easy and well documented to move data files (including master, model, tempdb,...) to other locations in MS-SQL Server. There is no supported approach to move the instalation (SQL Server binaries) to another drive / folder. I think even with manual manipulation of registry entries the SQL Server wouldn't come up again.

You should consider uninstall and new installation.

backup all databases including system databases as master, model ( SSISDB)
uninstall SQL Server using SQL Server installation center
uninstall SQL Server 2008 Browser component by using the Add or Remove Programs
install SQL Server with same instance name using SQL Server installation center; select new locations for binaries and data
restore backups

This article describes how to uninstall a stand-alone instance of Microsoft SQL Server 2008. When you follow the steps in this article, you also prepare the system so that you can reinstall SQL Server 2008


How can we have IIS talk to SQL Server with TLS1.0 disabled? Background

We need PCI-DSS compliance, and so must disable TLS1.0.  In my test environment we have SQL Server 2014 SP1 with CU 1 (as per this post) and IIS 8 all on the same box.  I used the Nartac IIS Crypto tool to disable TLS1.0 and rebooted.  That's when my troubles began.

SQL Agent won't start, SSMS can't connect to the server, and the website stops working.  (Fortunately, I can still make a Remote Desktop Connection to my test server.)

Then I read Microsoft's article which says basically SQL Server just has to have TLS1.0.

Question

Can I fix this?  (Maybe I need a separate box in test for SQL Server?)

I've resolved my problem. Not sure if all of the hurdles are things DBAs would normally encounter, but since people are often interested in resolving the whole problem, and this seemed like a series of issues others might encounter (given the various sources of the problems), here goes:

Put IIS on a separate box. This allows IIS to have TLS1.0 disabled while SQL Server has it enabled.
Disable TLS1.0 on the IIS box using John Louros' PowerShell script (not NARTAC's IISCrypto tool).
At this stage I started getting System.InvalidOperationException: This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms..  FIPS-compliance is on by default for .NET apps, so others may well encounter this.  Since I didn't need FIPS compliance, I tried adding <enforceFIPSPolicy enabled="false"/> to web.config as per this post, which didn't fix the problem.  I then followed the accepted answer from this post.  I later ran into difficulties, and undid both these changes but the FIPS error didn't recur.  Sorry, that's not very enlightening.
Then I got System.Security.Cryptography.CryptographicException, which I fixed by changing web.config as suggested here. I replaced <machineKey validationKey="AutoGenerate,IsolateApps" decryptionKey="AutoGenerate,IsolateApps" validation="3DES" decryption="3DES"/> (which was suggested in this old Microsoft KB article) with <machineKey validationKey="AutoGenerate,IsolateApps" decryptionKey="AutoGenerate,IsolateApps" validation="HMACSHA256" decryption="AES" />.

Then - hurrah! - everything was working again.

Edit

I read Aaron Bertrand's post which refers to this answer.  For anyone coming here from there, I should point out that my testing was before Microsoft's late-January-2016 announcement of TLS1.2 support. I'll try to get around to re-testing with any updates I didn't previously have from that announcement and with IIS and SQL on the same box again, and post my findings here.

(I'm not especially optimistic about being able to co-locate IIS and SQL because from what I had read, I already had TLS1.1 and TLS1.2 support in SQL Server 2014 due to having SP1 CU1, so these latest updates aren't adding anything new in theory. Also, I have an un-tested hunch that since IIS and SQL are on separate boxes, I could have any old version of SQL Server, without needing TLS1.1 support.  If anyone has any thoughts on that I'd be glad to hear them.)


Select rows from table 1 only if all corresponding rows have certain status in the join table These are the tables

Orders

id
status

Order_lines

id
order_id

Invoice_lines

id
invoice_id
order_line_id

Invoices

id
status

I would like to retrieve only orders whose invoices are in approved or closed status. If any invoices are in open status, that order should not be selected

I tried this

 SELECT o.id FROM orders o INNER JOIN order_lines ol ON ol.orders_id = o.id
                     INNER JOIN invoice_lines il ON il.order_line_id = ol.id
                     INNER JOIN invoices i ON i.id = il.invoice_id
                     AND i.status IN ('approved', 'closed')

I keep getting orders which have invoices in open state. This is not the result I want. I want orders for whose invoices are only in approved and closed status

There are a couple issues I'll have to gloss over at this point due to lack of info:

no DDL; no sample data; no list of valid invoices.status values
"invoices are only in approved and closed status" implies an invoice needs at least 2 records ... one with status='approved' and one with status='closed'

Instead of trying to find all orders with invoices that are a) 'approved' and 'closed', or b) 'approved' or 'closed' ... just look for orders that have no invoices with a status other than 'approved' or 'closed':

select distinct
       o.id
from   orders o
where not exists(select 1
                 from   order_lines ol
                 join   invoice_lines il
                 on     il.order_line_id = ol.id

                 join   invoices i
                 on     i.id = il.invoice_id
                 and    ifnull(i.status,'UNDEFINED') not in ('approved','closed')

                 where  ol.order_id = o.id)


Will NVME SSD speed up heavy queries, e.g. selects at large offsets / table scans etc? If the database data will be on NVME SSD which let's assume is 4x faster than an ordinary SSD will queries like SELECT something OFFSET 100000 and the like typically also be much faster (of course when they are not cached/buffered etc)?

It is quite obvious, is it not?

If the IO side of the database is the bottleneck, then having a better IO side will speed things up.

Now, most of the time - outside of data warehouses - IO should not bottleneck that bad once you leave the really low IOPS side of Hard discs. A proper database server will have enough RAM (again, outside of data warehouses) hat a lot is cached - which means that no, you may not really need THAT much database bandwidth and it will likely NOT get you 4x the throughput.

But if you have like 5gb RAM and a 100GB table and you make a table scan, then yes, faster IO means faster query processing. Obviously.

Also Note: NVM does not necessarily mean the SSD is also faster. Not sure they are any, but you better make sure your NVM SSD is up to the task. There are some really cheap ones that are a: not optimized for the write performance needed and b: just too slow to use up the NVM ports.


Transaction log backup after full backup is too big Our current production database performs a full backup daily at 7AM. The transaction log backup takes place every 15 minutes (24/7).

Database version: Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
The .mdf file size: 3.9GB
The .ldf file size: 6.5GB
Recovery Model:Full
Full backup size at 7AM: 3.8GB

The transaction log files (.trn) are normally below 50MB but after the full backup (at 7AM) the next transaction Log is around 1500MB. This is an issue for me because transaction log files are being transferred across network and restore time reason.
This is the script of the full backup:
BACKUP DATABASE [DB_PROD] TO
  DISK = N'F:\SQL\DB_PROD_backup_2020_10_12_114830_1849966.bak'
WITH NOFORMAT, NOINIT,  NAME = N'DB_PROD_backup_2020_10_12_114830_1849966',
SKIP, REWIND, NOUNLOAD,  STATS = 10

There is a re-organize index step before the full backup. The command is like this:
ALTER INDEX [UC_LCRecipe_Name]
ON [dbo].[LCRecipe]
REORGANIZE WITH ( LOB_COMPACTION = ON )

My understanding is that full backups do not truncate transaction logs - only transaction log backups do (e.g., the full backup is fairly independent of the transaction logs).
However, my guess is that the re-organise all indexes does a lot of data processing - it is likely that these go into the transaction logs and are causing your issues.
You could test this by disabling or delaying the re-organise indexes one day, and see what happens to the transaction logs.


Subquery - return new row and only fill one column I'm trying to fetch information about a database and I have the following query that prints most of it out:

SELECT Serverproperty('ServerName') AS [Server],
       (SELECT Server_type = CASE
                               WHEN virtual_machine_type = 1 THEN 'Virtual'
                               ELSE 'Physical'
                             END
        FROM sys.dm_os_sys_info)
       AS [Virtual/Physical],
       (SELECT ( cpu_count / hyperthread_ratio )
        FROM   sys.dm_os_sys_info)
       AS [CPU(s)],
       (SELECT CASE
                 WHEN hyperthread_ratio = cpu_count THEN cpu_count
                 WHEN ( ( cpu_count - hyperthread_ratio ) / (
                        cpu_count / hyperthread_ratio )
                      ) = 0 THEN 1
               END
        FROM   sys.dm_os_sys_info)
       AS [Core(s)],
       Substring ((SELECT @@VERSION), 1, Charindex('-', (SELECT @@VERSION)) - 1)
       AS [Product Name],
       Serverproperty('ProductVersion') AS [Product Version],
       Serverproperty('ProductLevel') AS [Product Level],
       Serverproperty('Edition') AS [Edition]

Result:

This is where my issue comes in. I found a script that prints out all your instances on the server, too. Like this:

DECLARE @GetInstances TABLE
  (
     value         NVARCHAR(100),
     instancenames NVARCHAR(100),
     data          NVARCHAR(100)
  )

INSERT INTO @GetInstances
EXECUTE Xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

SELECT instancenames
FROM   @GetInstances

My issue is that I cannot seem to forge the first query with the second by including it in the SELECT-statement of the first script. I get the error:

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.

How can I fix this?
I would like the result to be printed out in the form of the image below, is that possible?

Only way to format like that is to UNION the queries:

DECLARE @GetInstances TABLE
  (
     value         NVARCHAR(100),
     instancenames NVARCHAR(100),
     data          NVARCHAR(100)
  )

INSERT INTO @GetInstances
EXECUTE Xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

SELECT Serverproperty('ServerName') AS [Server],
       (SELECT Server_type = CASE
                               WHEN virtual_machine_type = 1 THEN 'Virtual'
                               ELSE 'Physical'
                             END
        FROM sys.dm_os_sys_info)
       AS [Virtual/Physical],
       CAST((SELECT ( cpu_count / hyperthread_ratio )
        FROM   sys.dm_os_sys_info)
        AS VARCHAR(10))
       AS  [CPU(s)],
       CAST((SELECT CASE
                 WHEN hyperthread_ratio = cpu_count THEN cpu_count
                 WHEN ( ( cpu_count - hyperthread_ratio ) / (
                        cpu_count / hyperthread_ratio )
                      ) = 0 THEN 1
               END
        FROM   sys.dm_os_sys_info)
         AS VARCHAR(10)) AS [Core(s)],
       Substring ((SELECT @@VERSION), 1, Charindex('-', (SELECT @@VERSION)) - 1)
       AS [Product Name],
       Serverproperty('ProductVersion') AS [Product Version],
       Serverproperty('ProductLevel') AS [Product Level],
       Serverproperty('Edition') AS [Edition]

UNION ALL

SELECT '', instancenames, '', '', '', '', '', ''
FROM   @GetInstances

We return blank strings '' for most columns in the second query as both queries must have the same number of columns. Note I've also CAST [CPU(s)] and [Core(s)] columns in the top query to VARCHAR, otherwise the second query returns 0 in those columns ('' cast to INT = 0).


Using CREATE TYPE within Stored Procedure Call I have a SQL Server 2012 database, and I generally use stored procedures that belong to a role with grant execute permission. I just created a stored procedure (sp1) that calls another stored procedure (sp2).

In the called stored procedure (sp2) I have a CREATE TYPE statement. When I attempt to run it from a C# console job, I get:

  "CREATE TYPE permission denied in database  error"

This is using a SQL Server account. The job runs when I use sa. What permissions are required to perform this type of operation? I prefer to use the minimum permissions possible.

Ownership chaining from procedure does not work with DDL statements and TRUNCATE statement. You can use execute with impersonation (execute as) in your case.

The impersonation example for creating a stored procedure is -

Create procedure <your procedure>
with execute as owner
 as
   create type ...

Alternately, you can sign your procedure with certificates created with appropriate permissions on the database.


Database failed to process. Divide by zero error encountered running sp_BlitzIndex I am seeing a divide by zero message while running sp_BlitzIndex.  I am running Microsoft SQL Server 2008 (SP3) - 10.0.5775.0 (X64) Enterprise Edition (64-bit) on Windows NT 5.2  (Build 3790: Service Pack 2) (VM)

Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1535
SIOP database failed to process. Divide by zero error encountered.

That's my bug! It's in the statistics gathering portion. If you search for the line:

CASE WHEN si.rowmodctr > 0

Replace the whole case expression with this:

CASE WHEN si.rowmodctr > 0 THEN CAST(si.rowmodctr / ( 1. * NULLIF(si.rowcnt, 0) ) * 100 AS DECIMAL(18, 1))
                                     ELSE si.rowmodctr
                                END AS percent_modifications,

The best place to file bug reports for Blitz* scripts is over here.

Thanks!


Should 'FOR system_time ALL' include rows where the start and end times are equal? Querying a system-versioned temporal table with FOR system_time ALL appears to omit rows where the start and end times are equal.

For example:

/* Create a simple system-versioned table */
CREATE TABLE [dbo].[svt_test](
    [string] [VARCHAR](30) NOT NULL,
    [validFrom] [DATETIME2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [validTo]   [DATETIME2](7) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_svt_test] PRIMARY KEY CLUSTERED
(
    [string] ASC
),
    PERIOD FOR SYSTEM_TIME ([validFrom], [validTo])
)
WITH
(
    SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[svt_test_history] )
);

/*
Insert and immediately delete a row.
May have to try multiple times to get a row with the same start and end
timestamp.
*/
INSERT INTO dbo.svt_test (string) VALUES ('string');
DELETE dbo.svt_test WHERE string = 'string';

/* 0 rows */
SELECT * FROM dbo.svt_test;
/* 1 row */
SELECT * FROM dbo.svt_test_history;
/*
0 rows.
Should be the "union of rows that belong to the current and the history table"
  per https://msdn.microsoft.com/en-us/library/dn935015.aspx
*/
SELECT * FROM dbo.svt_test FOR system_time ALL;

/*
   CONTAINED IN does not find the row either, even though MSDN says the
   interval is defined as SysStartTime >= start_date_time AND SysEndTime <=
   end_date_time
*/
DECLARE @Start DATETIME2(7);
SELECT @Start = validFrom FROM [dbo].[svt_test_history] WHERE string = 'string';
DECLARE @End DATETIME2(7);
SELECT @End = validTo FROM [dbo].[svt_test_history] WHERE string = 'string';
SELECT * FROM dbo.svt_test FOR system_time CONTAINED IN (@Start, @End);

/* Clean up */
ALTER TABLE dbo.svt_test SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.svt_test;
DROP TABLE dbo.svt_test_history;

I've verified this on 2016 SP1 CU1 (12.0.4416) and 2016 CU3 (13.0.2186). I got the same result with datetime2(0) instead of datetime2(7).

Would anyone happen to know whether this is likely a bug or by design? Alternatively, perhaps I've misunderstood something?

I was not able to reproduce what you see. Probably because I have a slow computer. But on the other hand it seems that the SQL Server developers have built this with my slow computer in mind and not your fast one.

If I wrapped the insert and delete in a transaction they got the same start and end timestamp every time.

From Temporal Tables

  FOR SYSTEM_TIME filters out rows that have period of validity with
  zero duration (SysStartTime = SysEndTime). Those rows will be
  generated if you perform multiple updates on the same primary key
  within the same transaction. In that case, temporal querying surfaces
  only row versions before the transactions and ones that became actual
  after the transactions. If you need to include those rows in the
  analysis, query the history table directly.

So, if start and end timestamp are the same they assume it happened in the same transaction.


How to reset session context/variables in JDBC pooled connections - MySQL / Oracle In my application I have multithreads that needs to access database and I am using apache.tomcat.jdbc.pool.DataSource as a JDBC connection pool.
In some cases users execute stored procedures that might affect the database session context/variables before executing another query to retrieve some data.
Is there a way to enforce closing the connection instead of just returning it to pool. Or how can we clear/reset the connection session context/variables for MySQL or Oracle using a stored procedure.

DBMS_SESSION.CLEAR_ALL_CONTEXT

This procedure clears application context information in the specified
namespace.

DBMS_SESSION.RESET_PACKAGE

This procedure de-instantiates all packages in this session. It frees
the package state.


A database in MySQL is a schema in Oracle : I do not understand this statement Please help me understand this statement:

  A database in MySQL is a schema in Oracle.

I have just started to use Oracle and I find it different from other RDBMS softwares I have used like MSSQL, MySQL and Derby.

For example to create a database, when I use create database ghazals, it throws an error:

ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted

Also, commands like show databases do not work here.

It's simple.

MySQL has a single daemon that runs the database server. Within the server you can create any number of databases - these databases have no direct mapping to users.

Oracle has a single database. When you create a user in an Oracle database, it also creates a Schema with the same name as the user that created it. This is equivalent to a database in MySQL.

Having seen the edit to your question, you really need to forget about mysql while you learn Oracle. DDL and RDBMS concepts are completely different.

Start with the Oracle Server Concepts guide (link). The Oracle documentation home is here: http://www.oracle.com/pls/db112/homepage.

To create a user, the syntax would be:

create user newbie identified by yourpassword;

You'll then need to grant appropriate privileges to allow the user to connect:

grant connect to newbie;


Multiple schema or multiple databases for better performance I need to improve the performance of our entriprise search application(.net) from the database point of view.
The app already has 4 schemas on a separate sql server 2008.

2 schemas for front-end operations ( the tables are small)
2 schemas for back-end indexing, processing etc. (the tables are very
huge here).

Front and back schemas does not share any foreign key relation between each other.
To be highly responsive during the day time, the backend operation run in night time only.

Now the problem is the database server is heavely loaded at night time and often we get timeout errors. I want to spread the backend operations through out the day to overcome this issue with no impact on the front end operations.
when i see the performance graphs, during daytime the server and the DB in it is very minimally used.

My knowledge and exp on DB concepts is limited.
Following are the options which i think of.

I might not get a approval for 2 sql server machines.
Front schemas in a separate DB on the same sql instance. Place the
front DB in separate drive.
Front schemas is separate filegroup. and place the filegroup in
saperate drive.

Please let me know the pros and cons of my options and any other options which you think suits here.
Thanks in advance.

It's impossible to evaluate the options without knowing what the actual problem is.

Your first port of call should be to identify the reason the servers run slowly - it may be memory or CPU, rather than disk throughput. Perfmon will be able to tell you this; you can also run Microsoft's PAL tool to get a better insight. If it's not disk throughput that's limiting you, option 2 and 3 are pointless.

Secondly, it may well be possible to optimize the queries - this may be cheaper, quicker and less risky than changing the infrastructure. Find the query that's running slowly, work out what's constraining it, and see if you can optimize it.

If you do find out that the queries are optimally tuned, and that it is disk throughput that's the bottleneck, I'd put each of the two back-end DBs on separate drives - it doesn't sound like they are competing against the front DBs (as they run at different times), but they might be competing against each other if both DBs are used in overnight batch jobs.


Error in creating trigger I have 4 tables:

event:
event_id(p.k) | uid | circle_id(f.k)

activity:
uid | performed_activity_id(f.k->event_id) | activity_type_id

follow:
follower_id | circle_id(f.k)

notification:
sender_id | receiver_id(follower_id of follow table)

I want to create a trigger which inserts values into the activity and notification tables whenever there is an entry for event table.
I am able to insert values into the activity table because it is directly connected to event table.

However, I am not able to insert into the notification table because the receiver_id field in the notification table is coming from the follow table which is connected to event table by circle_id.

Here I am using select in trigger which is actually wrong.

DROP TRIGGER IF EXISTS  `InsertToActivity` ;

CREATE TRIGGER `InsertToActivity` AFTER INSERT ON  `event`
FOR EACH ROW
begin
    INSERT INTO activity( uid, performed_activity_id, activity_type_id )
        VALUES (new.uid, new.event_id,  '1');

    select follower_id from folow where circle_id=new.circle_id;

    insert into notification_table (sender_id,object_id,receiver_id)
        values (new.uid,new.event_id,new.follower_id);
end;

Is it good way to do this type of work using TRIGGERS?

I assume new.follower_id is supposed to be:

select follower_id from folow where circle_id=new.circle_id;

I don't think this will work. If you want to use this approach you need to declare a variable for follower_id and select into that. However, a better approach (IMO) is to do:

INSERT INTO activity( uid, performed_activity_id, activity_type_id )
    VALUES (new.uid, new.event_id,  '1');

insert into notification_table (sender_id,object_id,receiver_id)
    select new.uid ,new.event_id, follower_id
    from folow where circle_id=new.circle_id;


MongoDB ReplicaSet reconfig when primary and majority don't exist http://docs.mongodb.org/manual/tutorial/reconfigure-replica-set-with-unavailable-members/

Is there anyway to reconfigure a replica set in the event the primary as well as a majority of the servers are down ? For example if data center 1 goes does, a procedure can be run to reconfigure and set a new primary with the remaining servers?

I keep getting this "Command '$eval' failed: not master (response: { "note" : "from execCommand", "ok" : 0.0, "errmsg" : "not master" })"

We only have 2 data centers. (I we had 3 i realize this would solve my problem, but we don't.
)
I was thinking about running a reconfigure on a 5 member replica set every time the fail over happens and changing the host of the arbiter from one data center to another so that the active data center will always have the arbiter, but I can't do that if 3 of the 5 members of the replica set aren't available...because not new primary will ever be set.

Any suggestions?

Yes, you can do this, and in fact with just 2 data centers you really have no other choice.   Essentially, whenever you do not have a primary you can still do a reconfig but you must pass in {force:true} to make it happen on a non-primary node.

This is listed in the docs for the rs.reconfig() command and in detail (with examples) as part of a tutorial which describes exactly what needs to be done in the scenario you described:

http://docs.mongodb.org/manual/tutorial/reconfigure-replica-set-with-unavailable-members/

If you are interested, I cover this and other availability issues in the (free) M202: Advanced Deployment and Operations course on MongoDB University


Can SQL Server avoid subqueries or joins if other where clauses return enough results? Consider this as example:

SELECT TOP 1 FROM customers
WHERE firstname LIKE 'John%'
   OR id IN (SELECT id_customer
             FROM customer_emails
             WHERE email like 'john.doe@%' )

Does query optimizer manage to prioritize the first clause, avoiding to do the subquery if the simpler clause is true for at least 1 record (from using Top 1)?

I am mainly asking this because the above query can be rewritten as Join, which technically would be faster according to Join vs. sub-query. However if the subquery is avoided, the above example is clearly superior to a Join one in situations where the first clause is likely to be true.

Does query optimizer manage to prioritize the first clause, avoiding
  to do the subquery if the simpler clause is true for at least 1 record.

It can do

create table customers(id int identity primary key, firstname varchar(10)) ;
insert into customers values ('John'),  ('Johnathan'), ('George');
create table customer_emails(id_customer int, email varchar(100));
insert into customer_emails values (1, 'John@example.com'),  (2, 'Johnathan@example.com');

In the plan I got the first row matched the LIKE 'John%' predicate and the scan against customer_emails was not executed at all.

However your question is phrased as

  if the simpler clause is true for at least 1 record

That would imply that the simpler WHERE clause is evaluated in its entirety and only if that fails is the second one evaluated.

For

SELECT TOP 1 *
FROM customers
WHERE firstname LIKE 'George%'
   OR id IN (SELECT id_customer
             FROM customer_emails
             WHERE email like 'George.doe@%' );

Three rows were processed before one was found matching LIKE 'George%'and there were two ensuing scans on customer_emails

Rewriting as follows...

create index ix on customers(firstname) include(id)

SELECT TOP 1 *
FROM customers with(forceseek)
WHERE firstname LIKE 'George%'
   OR  firstname NOT LIKE 'George%' AND  id IN (SELECT id_customer
             FROM customer_emails
             WHERE email like 'George.doe@%' );

...  happens to give a plan where the operators in the shaded area for the IN part don't get executed if the simpler predicate returns a row but this plan isn't guaranteed.


Optimising MySQL database with read only tables I have a MySQL database with 15 tables. Three of them are used for website authentication and three other are tables that are frequently read and written to. Then other tables are set in stone and I'd never need to change them unless I make some significant changes to the web app.

Is there anything I can do to make my database more efficient and for performance, given that I don't need to write to these tables at runtime?

From Googling, I was able to see some settings for MyISAM, but would prefer to stick with InnoDB. Couldn't find any other info other than certain people having their entire database as read only. However, mine has some tables that I'd need to read/write.

EDIT:

Four tables are 4MB and below and one is 7.55MB. Also wouldn't I then need to adjust all my procedures to check whether the table in memory exists or not? Cause it may disappear when the instance is restarted or something.

Short answer:  You are making unnecessary work for yourself.  Use InnoDB for everything.  Forget about "readonly".

Long answer:

MyISAM is dying.
InnoDB is faster in some benchmarks than either MyISAM or MEMORY.
InnoDB "caches" things in its "buffer_pool", which is in RAM.  If you were using MEMORY to get things held in RAM, then "why bother".
Because the buffer_pool is a "cache", there is no requirement that all the data and indexes be held in memory.  Instead, only the "frequently" used blocks are kept in memory.  Since it is at a block level, it is even better than a human trying to second guess what to pin in memory (via MEMORY engine) and what not to.
Using space for MEMORY tables takes away from using RAM for the buffer_pool
Both MyISAM and MEMORY lock the entire table (when needed); InnoDB locks only rows.
True, MEMORY tables are not "persistent".  InnoDB tables are persistent.

Bottom line...

Use InnoDB for all of your tables.
Set key_buffer_size to 20M only because some system tables use that engine.
Set innodb_buffer_pool_size to 70% of RAM (or a smaller percentage if you have less than 4GB of RAM).


Memory Leak, safe to save? Would it be safe to save SQL data from memory if the program is having a memory leak and peeks 100% memory and 100% cpu usage?

a memory leak doesn't corrupt the data, it only grabs more and more memory without releasing it. so you would not have a problem with integrity, because the old data, are not  released, the new data are the current ones.
cpu time at 100 pro cent, will make it difficult to save any data, because you will not get any cpu time to do so.
Fix your problems, the<y are all manageable and then you will not have any bothers or see if there is an update


How can I avoid repeating the same condition in a "when matched then update" clause? I want to merge one table into another. I need to apply conditional logic in my WHEN MATCHED clause, which would ideally be done like this:

MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Do insert
WHEN MATCHED AND b.NeedsAdjustment = 1 THEN
UPDATE SET
    Col1 = b.Col1 + b.Adjustment
    ,Col2 = b.Col2 + b.Adjustment
    ,Col3 = b.Col3 + b.Adjustment
WHEN MATCHED THEN -- Default case (b.NeedsAdjustment <> 1)
UPDATE SET
    Col1 = b.Col1
    ,Col2 = b.Col2
    ,Col3 = b.Col3

This is not valid SQL. According to the MSDN documenation:

  If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE action.

This leads me to the following query:

MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Insert happens here
WHEN MATCHED THEN
UPDATE SET
    Col1 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col1 ELSE b.Col1 + b.Adjustment END
    ,Col2 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col2 ELSE b.Col2 + b.Adjustment END
    ,Col3 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col3 ELSE b.Col3 + b.Adjustment END

The conditional logic is moved inside of the update to get around the fact that merges can only have one WHEN MATCHED THEN UPDATE clause. Now, instead of one check per row, I have one check per row per column (and there are many more columns than the three in the example).

Can I avoid repeating this condition for every column that needs to be updated? Is there a better way to do conditional updates that perhaps don't involve merges?

I would recommend simply avoiding MERGE in the first place for all of the reasons listed here and just simply use a standard update statement.

UPDATE A
SET
    Col1 = b.Col1 + b.Adjustment
    ,Col2 = b.Col2 + b.Adjustment
    ,Col3 = b.Col3 + b.Adjustment
FROM ATable as A
inner Join TableB as B
    ON a.ID = b.ID
    AND b.NeedsAdjustment = 1
;
UPDATE A
SET
    Col1 = b.Col1
    ,Col2 = b.Col2
    ,Col3 = b.Col3
FROM ATable as A
inner Join TableB as B
    ON a.ID = b.ID
AND b.NeedsAdjustment <> 1
--Or INSNUL(NeedsAdjustment,0) If it's a nullable column
;

In the long run this would be a more stable approach than using MERGE. I've also added the <> because if that's not there your UPDATE will overwrite the first UPDATE.


How to Get WSFC Name Using T-SQL I know how to get the name of the current node, but is there a simple T-SQL query to use to get the Windows Server Failover Cluster name that the clustered instance is part of?

The SQL Server version is 2014 and the OS is Windows Server 2012

@Molenpad, you shall get the WSFC details through below mention TSQL query.

select * from master.sys.dm_hadr_cluster;
Go

Here cluster_name is the Name of the WSFC cluster that hosts the instances of SQL Server that are enabled for Always On availability groups.

I am also attaching the screen shot.

Note: I have highlighted the WSFC name due to my Prod server.

for your ref Here


Newly created user has rights on MySQL database I use the root to create a new user, like this:

I have no more operations on it. Then I use this user to log in:

I try to do some operations like this:

Here "test" is a database which I created before. Why can I do all the operations? I have not done any grant operation on the test user. When I come back to use the root user to log in, I also cannot revoke the privileges on test(database) for test(user), like this:

I am really confused by these things, and this is my first time to ask question on StackExchange. My English is also not very good. If you cannot understand what I am talking about just tell me, I will reply soon.

If I am not mistaken, the test Database is created by default.

  In addition, the mysql.db table contains rows that permit all accounts to access the test database and other databases with names that start with test_. This is true even for accounts that otherwise have no special privileges such as the default anonymous accounts. This is convenient for testing but inadvisable on production servers. Administrators who want database access restricted only to accounts that have permissions granted explicitly for that purpose should remove these mysql.db table rows.

http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html

Try to create a new DB with a different name and repeat your steps.

Hope this will help you.


Syntax to find text in string, then pull out following text until a comma is identified? I am trying to write a query that will extract partial information out of a longer string, but the string is not always consistent so I need to identify certain parts and extract based on that.

The string is legal description info on property parcels and is structured like this: "Lot D, Plan VIP12345, Section 70, Suchandsuch District".  I am trying to get the "VIP12345" part as a separate field.

Initially I considered exporting this field as a CSV table and then separate the info into unique sections that way, but this is a dataset that gets updated regularly by replacing the entire dataset, so every time it is updated I would have to export the table, make the field a CSV table, reimport that into separate fields and then export that into something my GIS can join to.

I would rather have my GIS link to the original table with an SQL query to sort out where the Plan# is (the "VIP12345" part).  However, the Plan# does not always start with VIP, sometimes it has no letters at start, sometimes it is VIS, sometimes EPP.  The Plan# is also not always the same number of digits, sometimes it is 4 sometimes 5, sometimes 6, so i can't just count # of characters to extract from a certain point.

So I am thinking I need an SQL query that in plain language would do this: In field [FIXLGL] Look for the string 'Plan ' then extract all characters following that until you see a ','

I am having troubles figuring out what SQL acrobatics to use to accomplish this.  I can identify records that have a plan# ([FIXLGL] LIKE '%Plan %') but then how to I tell the query that the important part is what is after that?

If you're using SQL Server, you can use this.

If you're not, I'll delete my answer.

DECLARE @s NVARCHAR(100) = N'Lot D, Plan VIP12345, Section 70, Suchandsuch District';

SELECT @s,
        SUBSTRING(@s AS string,
                 CHARINDEX('VIP', @s),
                 CHARINDEX(',', @s, CHARINDEX('VIP', @s))
                 - CHARINDEX('VIP', @s)
                 ) AS sub

How does this work?

We can use substring and the results of charindex to get just the parts of the string we're interested in.

Charindex is a better choice here than Patindex because it accepts a 3rd argument. The third argument tells charindex at which point in the string to start searching.

        SUBSTRING(@s, --The string
                 CHARINDEX('VIP', @s), --Charindex of VIP
                 CHARINDEX(',', @s, CHARINDEX('VIP', @s)) --Charindex of the first comma _after_ VIP
                 - CHARINDEX('VIP', @s) --Subtract the position of VIP
                 )

Which gives us:

string                                                  sub
Lot D, Plan VIP12345, Section 70, Suchandsuch District  VIP12345

Hope this helps!


Difference between MariaDB Server and Maria DB Enterprise? https://mariadb.com/pricing

From what I gather, the difference between "free" MariaDB and enterprise MariaDB, is solely around certification, support, and plugins. Is this correct? Is the server itself identical in these two instances?

MariaDB comes in a few different forms:

MariaDB is free and includes: Access all the features of MariaDB Server

MariaDB Enterprise Subscription includes a number of other features and is priced per server.

To look at the high-level features available see: https://mariadb.com/pricing


Issue when connecting to open shift via mysql workbench on Mac I am trying to connect to open shift with mysql workbench on an iMac. A colleague has shown me his setup on Windows which looks exactly as follows (other than the usernames / passwords changing)

However I keep running in to the following error and I have no idea how to fix it - I am 100% sure my password is ok, so don't really understand why it would say password yes.

Is there any special configuration differences needed between a Mac and PC setup, or any setting/config you can think of that might solve this issue?

The error message tells you that the MySQL server is not accepting a connection for user adminM4B1hN at the given host. You need to allow such a connection for that user (e.g. by adding an anyhost user entry (adminM4B1hN@%) for that user.

Also, keep in mind how the MySQL server is seen from the remote end of your SSH tunnel. If the tunnel ends on the same machine where the MySQL runs then the MySQL server address would be localhost not what you have given. The address is always resolved relative to the remote end of the tunnel.