How to model "Courses prerequisites" for a university? I am trying to model a courses structure and dependencies of a university.

For example, in the Computer Engineering department you need to do Data Structures before doing Artificial Intelligence. But you also need to finish Introduction to Logic OR Mathematical Logic and Proofs.

I was thinking about using a table for the courses (Id, Course Number, Course Name, Hours, Professor) and another table for the dependencies.

There lies the problem: How to model this dependencies/prerequisites? The only -viable- solution that I came up with was making this as a string, written in plain English; but that clearly doesn't seem to be a decent solution and I also believe that it violates the atomicity principle.

Notice that the prerequisites of Artificial Intelligence are of the form IA <- DS && (IL || MLP), but other courses will have a different structure like Calculus 2, that requires analytical geometry and calculus 1 done before: C2 <- AG && C1.

I don't know if this is relevant or not, but I will use MySQL.

You have some Courses:

COURSES
-------
course_id
course_number
course_name

You need to combine one or more of these Courses with a logical operator:

COMBINATIONS
------------
combination_id
logical_operator (AND, OR or NOT)

COMBINATION_COURSES
-------------------
combination_id
course_id

You may also need to combine some of these combinations (so changing the COMBINATION_COURSES table):

COMBINATION_COURSES
-------------------
combination_id
course_id
sub_combination_id

where one and only one of course_id and sub_combination_id is not null.

A Course may have one of the combinations as a prerequisite (so changing the COMBINATIONS table):

COURSES
-------
course_id
course_number
course_name
prerequisite_combination_id

EDIT

So, given your original examples your tables would be populated:

COURSES
=======
course_id course_name                    prerequisite_combination_id
--------- ------------------------------ ---------------------------
        1 Artificial Intelligence                                 12
        2 Data Structures
        3 Introduction to Logic
        4 Mathematical Logic and Proofs
        5 Calculus 1
        6 Calculus 2                                              13
        7 Analytical Geometry

COMBINATIONS
============
combination_id logical_operator
-------------- ----------------
            11 OR
            12 AND
            13 AND

COMBINATION_COURSES
===================
combination_id course_id sub_combination_id
-------------- --------- ------------------
            11         3
            11         4
            12         2
            12                           11
            13         7
            13         5

Course 1 - Artificial Intelligence has a prerequisite of the Combination 12. Combination 12 is Course 2 - Data Structures AND Combination 11. Combination 11 is Course 3 - Introduction to Logic OR Course 4 - Mathematical Logic and Proofs.

Hence, the prerequisite of Course 1 - Artificial Intelligence is Course 2 - Data Structures AND (Course 3 - Introduction to Logic OR Course 4 - Mathematical Logic and Proofs).

This structure should allow you to store any prerequisite that is the any logical combination of any number of courses.


Efficient way to handle multiple CASE statements in SELECT I run a report in which I have a situation where based on a column value which is basically a Key or Id, I need to fetch corresponding value from the mapping Id table. Something like below:

    SELECT
     (case when [column1='A'] then (select value from Table1)
           when [column1='B'] then (select value from Table2)
           when [column1='C'] then (select value from Table3)

           and so on uptil 35 more 'when' conditions ...

  ELSE column1 end) Value
from Table1

More precisely:

SELECT
(case when [A.column1='1']
then (select value from B where B.clientId=100 and A.column1=B.Id)
when [A.column1='2']
then (select value from C where C.clientId=100 and A.column1=C.Id)
when [A.column1='3']
then (select value from D where D.clientId=100 and A.column1=D.Id)
...
and so on uptil 30 more 'when' conditions
...
ELSE column1 end)
FROM A

In Tables B,C,D..so on, we maintain data for all clients. Each client has a specific ClientId and these tables B,C,D etc have index in place on Id and ClientId columns.

Is there an efficient way to handle this in SQL Server?

I'm assuming that you have appropriate indexes on the tables in the subqueries. I mocked up some quick test data and put 10 million rows in table A. I wasn't game to create 30 tables so I just created 3 for the CASE expression. I think that 3 is enough to show the general principles.

DROP TABLE IF EXISTS dbo.B;
CREATE TABLE dbo.B (
    ClientID INT NOT NULL,
    Id VARCHAR(20) NOT NULL,
    [Value] VARCHAR(100),
    PRIMARY KEY (ClientID, Id)
);

INSERT INTO B VALUES (100, '1', 'TABLE B');

DROP TABLE IF EXISTS dbo.C;
CREATE TABLE dbo.C (
    ClientID INT NOT NULL,
    Id VARCHAR(20) NOT NULL,
    [Value] VARCHAR(100),
    PRIMARY KEY (ClientID, Id)
);

INSERT INTO C VALUES (100, '2', 'TABLE C');

DROP TABLE IF EXISTS dbo.D;
CREATE TABLE dbo.D (
    ClientID INT NOT NULL,
    Id VARCHAR(20) NOT NULL,
    [Value] VARCHAR(100),
    PRIMARY KEY (ClientID, Id)
);

INSERT INTO D VALUES (100, '3', 'TABLE D');

DROP TABLE IF EXISTS dbo.A;
CREATE TABLE dbo.A (
    column1 VARCHAR(20) NOT NULL
);

INSERT INTO dbo.A WITH (TABLOCK)
SELECT CAST(1 + t.RN % 3 AS VARCHAR(20))
FROM
(
    SELECT TOP (5000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

I disabled result sets and ran the following query in SSMS:

SELECT A.column1
FROM A;

It took around 0.723 seconds. I'm doing pretty unscientific tests because I don't know anything your data. In any case, with serial queries we can't expect a better result than 0.7 seconds. That's our baseline.

The most efficient way to write this query is without joins at all. The key is that the CASE expression is only ever going to return 3 (or 30) unique values if it finds a match. You can save off the results into local variables and just use those in the query. The query below finishes in around 1.044 seconds:

DECLARE @B_VALUE VARCHAR(100) = (select value from B where B.clientId=100 and B.Id = '1');
DECLARE @C_VALUE VARCHAR(100) = (select value from C where C.clientId=100 and C.Id = '2');
DECLARE @D_VALUE VARCHAR(100) = (select value from D where D.clientId=100 and D.Id = '3');

SELECT
(case when A.column1='1' then @B_VALUE
      when A.column1='2' then @C_VALUE
      when A.column1='3' then @D_VALUE
      -- omitted other columns
else column1 end)
FROM A;

The plan is very simple:

For another option you can write the query with joins (where we can rewrite the CASE expression in a more compact form, using COALESCE(). This finished in around 2.314 seconds:

SELECT
    COALESCE(B.column1, C.column1, D.column1, -- omitted other columns
             A.column1)
    -- (case A.column1
    --    when '1' then B.value
    --    when '2' then C.value
    --    when '3' then D.value
    --    -- omitted other columns
    -- else A.column1 end)
FROM A
LEFT JOIN B ON B.clientId=100 and B.Id = '1'
LEFT JOIN C ON C.clientId=100 and C.Id = '2'
LEFT JOIN D ON D.clientId=100 and D.Id = '3';

Here's the plan:

You can get a nearly identical runtime and query plan by writing the query like this:

SELECT
(case A.column1
   when '1' then (select value from B where B.clientId=100 and '1'=B.Id)
   when '2' then (select value from C where C.clientId=100 and '2'=C.Id)
   when '3' then (select value from D where D.clientId=100 and '3'=D.Id)
-- omitted other columns
else column1 end)
FROM A;

The original query in the question has an issue: SQL Server is doing a useless sort before the nested loop join. That query finishes in around 5.838 seconds on my machine.

Trace flag 8690 eliminates the sort as well as the table spools. The query runs in about 7.479 seconds with trace flag 8690, so I suppose that the spools are helpful for this query.


How to sort data using 2 conditions and keep the first set of data always at the top? I have a simple table:

id, status, date

When status is null, the date is also null. Question: how do I get my data that's always sorted by status = null and then sorted by date? The tricky part is, I would like to paginate through results, so I need to be able to use LIMIT and OFFSET with this query.

Use the ISNULL() function

SELECT * FROM mytable
ORDER BY ISNULL(status) DESC,date;

or

SELECT * FROM mytable
ORDER BY 1-ISNULL(status),date;

For pagination, you can reference id

SELECT B.* FROM
(
    SELECT id FROM mytable
    ORDER BY ISNULL(status) DESC,date
    LIMIT xxx OFFSET yyy
) A INNER JOIN mytable B USING (id)
ORDER BY ISNULL(B.date) DESC,B.date;


Tabled Value Function returns incorrect or no results I recently learned of TVF's and it seemed like it was exactly what I was looking for. I did my first attempt at one and it is not going well.

Here is my code:

USE [RMC_Tracker] GO
/****** Object:    UserDefinedFunction [dbo].[TFV_Dashboard]
9:08:08 AM ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO

Script Date: 4/13/2020

--------------------------------------------
Author:    John Clark
Create date: 4/12/2020
Description: Returns a recordset for the RMC Tracker

ALTER FUNCTION [dbo].[TVF_Dashboad]
(
-- Add the parameters for the function here
@CompDate datetime = NULL

)
RETURNS TABLE AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT    dbo.ACTIVE.RECORD_ID, dbo.ACTIVE.CMD_ID, dbo.ACTIVE.EQUIPMENT, dbo.ACTIVE.EquipDes,
          dbo.ACTIVE.CASREP,dbo.ACTIVE.[UPDATE], dbo.ACTIVE.TYAST,
          dbo.ACTIVE.STATUS,dbo.ACTIVE.JCN, dbo.COMMANDS.Trigrpah

FROM      dbo.ACTIVE INNER JOIN dbo.COMMANDS ON dbo.ACTIVE.CMD_ID = dbo.COMMANDS.CMD ID

WHERE    (dbo.ACTIVE. RECCLS = @CompDate)
)

I call this Function from within MS Access via a Pass Through Query:

SELECT * FROM dbo.TVF_DashBoard(default)

It returns an empty set. What I am trying to do is create a simple Function that allows me to return a table that can be toggle based on whether a field (RECCLS) is either Null or Not Null.

Any assistance would be appreciated.

The default value of @CompDate is NULL. I suppose you actually want to find rows where dbo.ACTIVE.RECCLS IS NULL. So you need to modify your function code:

ALTER FUNCTION [dbo].[TVF_Dashboad]
(
-- Add the parameters for the function here
@CompDate datetime = NULL

)
RETURNS TABLE AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT    dbo.ACTIVE.RECORD_ID, dbo.ACTIVE.CMD_ID, dbo.ACTIVE.EQUIPMENT, dbo.ACTIVE.EquipDes,
          dbo.ACTIVE.CASREP,dbo.ACTIVE.[UPDATE], dbo.ACTIVE.TYAST,
          dbo.ACTIVE.STATUS,dbo.ACTIVE.JCN, dbo.COMMANDS.Trigrpah

FROM      dbo.ACTIVE INNER JOIN dbo.COMMANDS ON dbo.ACTIVE.CMD_ID = dbo.COMMANDS.CMD ID

WHERE    (dbo.ACTIVE.RECCLS = @CompDate)
    OR (@CompDate IS NULL AND dbo.ACTIVE.RECCLS IS NULL)
)

Notice: such function may suffer from parameter sniffing issue.


The simplest way to copy sql tables from one database to another? So I have been logged into two different database. I want to copy one table from the first database to the other one with its content. I want to overwrite the content of the destination one (Because I have the same table name but it has just few entries) So what is the simplest way to do it?

PS: I am using Microsoft SQL Server Management Studio 2019 and each database located on different server.

This article lists out three simple ways to copy data from one table to another. To summarise, you can use the Import/Export Wizard in SSMS to copy data from one table to another. You can use the "Generate Scripts" option to generate a script of schema and data from the source then drop your target table and run the script against your target. Lastly, you can use INSERT INTO ... SELECT ... to transfer data from one table to another.

For your scenario, the easiest option is the Import/Export Wizard for a one-off transfer. If you want to repeat this transfer, a simple solution would be to create a linked server on your target SQL Server connecting to your source SQL Server and use the INSERT INTO SELECT option.


Planning and Optimizing the table structure InnoDB MySql I am planning to have an InnoDB MySql table with tens of millions of rows.

Each row represents a record that can belong to AT LEAST one of 200+ categories. Most of the records will belong to 2 or more categories. All the categories represented by a numeric id, from 1 to 200+.

What is the most efficient way to store these multiple categories within each record to get the fastest look up speed when querying to return all the records belonging to one of the categories?

I think the fastest way will be to have a separate column for each of the categories and then just query that column. But the problem is I will have to make 200+ columns and will be new categories added in the future. Also, I don’t think having 200+ indexes will do any good for my server.

Then I thought of creating a separate record for each of categories, so there would be only one column for categories and the index will work just fine. The problem with this approach is that the table is already in tens of millions and it will keep on growing over time. If I will throw in duplicate records for each of the current multi-category records, the table size will quickly become unmanageable plus it will affect the performance.

The other approach I can think of will be to store all the categories related to a record in one column separated by comma or any other separator and then do a LIKE %% search. But If I understand correctly, this method doesn’t use an index and will result in the slow lookups.

So what is the correct way to approach it? I was even thinking of creating a separate table for each category, but that would be unacceptable when I will need to do some other types of queries across whole data.

Recommendation

"all the records belonging to one of the categories" --> Build a many:many mapping between records and categories.  To have that table optimal, follow the tips here .  Be sure to use a 2-byte SMALLINT UNSIGNED for the category_id.  (You should not trust that you won't go beyond 255.)

Then the query is something like

SELECT r.*
    FROM RecordsCategories AS rc
    JOIN Records AS r  USING(record_id)
    WHERE rc.category_id = 123;

This approach will do an index scan of a portion of rc to find the record_ids, then randomly reach into r via the PK.  Yes, there will be "hundreds of millions of rows" in rc, but it will scan only a small part of it, because all the entries for category_id=123 will be 'clustered' together (either in the data or the secondary index).

Critique of alternatives

If you have a column with a commalist of categories, then every row will need to be fetched.  That is a full table scan ("tens of millions of rows" = a few gigabytes) of Records.  (BTW, don't use LIKE %123%, use FIND_IN_SET() -- not faster, but less messy to handle edge cases.)

"a separate table for each category" -- This is a very common question on this (and other) forum.  The answer is "NEVER!".

"separate column for each of the categories" -- Gag me with a spoon!  The table will be fatter and, again, you will need a full table scan.

"200+ indexes" -- The hard limit is 64.  The practical limit is more like 5.

Building on Akina's and Acidon's comments:  With 8.0, you can use TINYBLOB or BINARY(..) and set bits.  This is about as clumsy as having multiple SETs.  Before 8.0, bitwise ORs (etc) were limited to 64 bits; now BLOBs work.  SET/BLOB/BINARY/BIGINT save the most space.  But your entire dataset is only tens of GB, so I don't see 'size' as a problem.  Even the many:many table I recommend will be only several extra GB.


What does "ALTER DATABASE ... SET NEW_BROKER" do? Is there anything to worry about? I'm having an issue with sql broker service. Is there a reason to worry about setting a new sql broker service? I'm really worried about doing this, but I'm kind of at my wits end since I know it will clear out the message queue to send out, but is this the only thing I need to worry about? Is there anything else that I should be looking for or that has to be done when I do run this statement?

Only if your database participates in distributed conversations. NEW_BROKER changes your service_broker_id. If you created any route with service name and service instance then it will become invalid as the route service instance value no longer reflect the destination service_broker_guid. It goes w/o saying that doing NEW_BROKER on a database that does have running conversations that span isntances (or even DBs in the same conversation), these conversations will have one half 'nuked' and the other half will be left stranded, unable to make any progress (will have to be cleaned up with END ... WITH CLEANUP or the other database will also have to be NEW_BROKER-ed).

Be aware that NEW_BROKER affects hidden conversations like the ones created by Event Notifications.

If everything you have is local, in the same db, then you have basically nothing to worry about.


SQL Cluster Resources Disappear After Removing Disk Resource In the process of removing an usused disk resource from cluster, I would follow these steps: 1) remove dependencies on the disks by the SQL Server resource, 2) take the disk resource offline, 3) delete the disk resource from the SQL resource group, 4) delete the disk resource from the Available storage pool.

I was proceeding to do this on a two node cluster with two SQL 2008 R2 named instances (running Win2K8R2 SP1 build 6701, 64-bit). On deleting a resource in step 3 above, SQL would go offline, all the disk resources would go back to the Available pool, and the SQL resources (Net Name, SQL Server, SQL Server Agent) would disappear. SQL services do restart OK after via the services console as all the disks are still on the same node.

I ran cluster res from the command prompt and I see the SQL service resources are still there, in Available Storage, and offline.

To bring the resources back to the appropriate group, can I move them via cluster commands?

The link below has the likely answer.  Premier Support agrees that the steps to fix should fix my similar issue.  I plan on going through the steps during my next outage window.

http://blogs.technet.com/b/askcore/archive/2013/01/14/removing-a-mount-point-disk-from-a-cluster-group.aspx


Update using PK from a lookup table, should I use the PK directly or select it using the associated lookup value? - best practice? This is not asking for an opinion, simply what DBA's who are more professional than me tend to do in this situation.
Assume I have a simple lookup table status_type  such as this, where status_id is an autoinc integer PK and status_value is a varchar.
status_id  status_value
---------  ------------
        1  Active
        2  Lapsed
        3  Resigned
        4  Deceased
        5  Demurred
        6  Terminated
        7  Pending
        8  Redacted

and another table person has a field the_status INT as a FK linked to status_type from which I can deduce the status string pertaining to that person. This is easy to maintain and I can alter the text of the status_value if necessary without altering the person record and can add new status values as needed.
If, inside a stored procedure, I want to update table person and change someone's status, say to 'Resigned', is it best practice / more common to say
(a) UPDATE person
    SET the_status  = 3
    WHERE personID = 1234;

or
(a) UPDATE person
    SET the_status  = (SELECT status_id
                       FROM status_type
                       WHERE status_value = 'Resigned'
                      )
    WHERE personID = 1234;

Option (a) assumes that the ID of 3 will always mean the same as 'Resigned', so it won't matter even if I change the string contained in status_value to 'Left the company' but it needs a magic number hard coded in the stored proc, which I don't really like.
Option (b) does not require me to hard code 'magic numbers' into the stored proc but requires me to know what status_value string is currently held in table status_type to indicate someone voluntarily no longer works for us. Consequently the stored proc will need changing if the status_value in table status_type is ever edited.
Is either way more widely used than the other?
I did look at this and this

This is a little close to an opinionated question, but in general, I think you'll find option a to be the mostly widely used one from the perspective of the database layer. For the purpose of this answer, I'm using the term enum as synonymous with lookup table, for all intents and purposes.
Enums are inherently meant to numerically give meaning to a concept with the added benefit of being able to verbalize that meaning with a name / string value. But it should be a rare event that the meaning of a specific instance of an enum changes from the original meaning for that numerical representation (in fact, if it did, it would likely logically break any existing references to that enum's numerical value). With that in mind, they shouldn't exactly be considered the same as magic numbers (despite being rather close), and it is the string value / name that is more flexible to change.
Should you use option b, you'd run into two issues:

There's a performance difference (though likely negligible in this case) by doing a second lookup on the status_type table as opposed to your UPDATE statement in option a.

If the name / string value of your enum were to change (remember this is very well more possible than the underlying meaning of the instance of that enum's numerical value to change) then your stored procedure would now be broken.

I would go with option a all day.


How to combine three table to find Avg,High,Low value in mysql? I have three different tables such as

  prac_test
UID | net_wpm
5        58
5        67

 multi_test
UID | net_wpm
5        88
5        97

 shadow_test
UID | net_wpm
5        8
5        77

How to combine these three tables and get avg,High,Low net wpm for UID  5

Thank in advance

try use union all + avg + max + min

select avg(`net_wpm`) `avg`,max(`net_wpm`) High,min(`net_wpm`) Low from (
  select * from prac_test union all
  select * from multi_test union all
  select * from shadow_test
) T
group by `UID`

Demo Link

Result:

| UID |     avg | High | Low |
|-----|---------|------|-----|
|   5 | 65.8333 |   97 |   8 |


How Uniqueidentifier in SQL Server is always a unique value globally? As per microsoft documentation on UniqueIdentifier, This value is always a unique globally beacuse it's based on network clock and CPU clock time and on the other hand the same documentation says

  uniqueidentifier columns may contain multiple occurrences of an
  individual uniqueidentifier value, unless the UNIQUE or PRIMARY KEY
  constraints are also specified for the column.

I'm not able to come to a conclusion how UniqueIdentifier (GUIDs) can be unique globally, as Network address (Mac address) can be same on two different networks, How GUIDs can be unique globally with which combinations and also why Microsoft says there should be primary or unique constraint in order to ensure we've unique UniqueIdeitifer value always.

https://technet.microsoft.com/en-us/library/ms190215(v=sql.105).aspx

Problem #1

  As per Microsoft documentation on UniqueIdentifier, This value is always a unique globally because it's based on network clock and CPU clock time... (emphasis added)

The main problem here is that you are confusing two different things as being two terms that refer to one thing: UNIQUEIDENTIFIER and GUIDs.

UNIQUEIDENTIFIER is a datatype. Datatypes define the nature of the data that they (i.e. columns and variables of this type) can contain (e.g. min / max values, etc) and certain behaviors of the data (e.g. how to handle comparisons). This particular datatype merely holds GUID / UUID values. But it is not data, so the concept of uniqueness does not apply to it. And the word "Unique" in the name "UniqueIdentifier" is not a promise, or even statement, regarding actual uniqueness.
GUIDs / UUIDs are actual values that can be stored as UNIQUEIDENTIFIER, but could also be stored as VARBINARY / BINARY, (N)VARCHAR / (N)CHAR, and maybe some others. While the UNIQUEIDENTIFIER datatype is the best choice (in SQL Server) for storing these values, storing the values in the other types does not make the values any more or less unique.

Problem #2

  I'm not able to come to a conclusion how UniqueIdentifier (GUIDs) can be unique globally, as Network address (Mac address) can be same on two different networks

The second problem here is that you are accepting, as fact, a technical error in the documentation that you linked to. I assume you are referring to this statement:

  A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value.

That statement is referring to functions like NEWID() in T-SQL and Guid.NewGuid() in .NET that create new GUID / UUID values, and the intention of them to always generate unique values. However, that is not reality: newly generated GUIDs are not guaranteed to be unique. As you already pointed out, MAC Addresses aren't necessarily unique (they can even be spoofed; more info in the "Related info" section below). Also, from other Microsoft documentation:

MSDN page for .NET Guid Structure states (emphasis added):

  A GUID is a 128-bit integer (16 bytes) that can be used across all computers and networks wherever a unique identifier is required. Such an identifier has a very low probability of being duplicated.

The .NET Guid.NewGuid() method (which is used to generate new GUID / UUID values) calls Win32Native.CoCreateGuid. The documentation for that function states (emphasis added):

  To a very high degree of certainty, this function returns a unique value – no other invocation, on the same or any other system (networked or not), should return the same value.

Please note that the non-SQL Server documentation doesn't even mention MAC Address. And the documentation for CoCreateGuid points to the real function that does the generation: UuidCreate. The documentation for that function states:

  For security reasons, it is often desirable to keep ethernet addresses on networks from becoming available outside a company or organization. The UuidCreate function generates a UUID that cannot be traced to the ethernet address of the computer on which it was generated. It also cannot be associated with other UUIDs created on the same computer. If you do not need this level of security, your application can use the UuidCreateSequential function, which behaves exactly as the UuidCreate function does on all other versions of the operating system.

The implication here is that MAC Address is specifically not used (unless using NEWSEQUENTIALID()). And in fact, generating a few GUIDs in SQL Server via NEWID() indicates that they are RFC 4122, Version 4 UUIDs, which are extremely likely to be unique. There is a chart here, Random UUID probability of duplicates, that shows just how unlikely it is to have duplicates. However, even a very, very low probability of duplicates is not a guarantee of uniqueness.

And so...

There is no guarantee that newly generated GUID / UUID values are unique. And, even if there was a guarantee, the UNIQUEIDENTIFIER datatype would still have nothing to do with actual uniqueness (as is shown in Brent's answer). Uniqueness, for one or more columns (i.e. data, not datatypes) can only be enforced by Unique Indexes / Constraints.

Related info:

How is the Uniqueness of MAC Addresses Enforced?
Are MAC addresses really unique?
MAC address
Globally unique identifier


Unable to pass scema.object to the query shortcut in SSMS 2016 I am having a query shortcut that is generating create table in SQL Server Management Studio 2016. This works fine when I am not trying to select schema.table, but once I am trying to select such object I am getting error Incorrect syntax near '.'.

My query shortcut looks like:

exec sp_executesql 'huge sql oneliner', N'@table_name SYSNAME', @table_name =

Any idea how I can pass objects like that to the query shortcut?

update 1:

The only workaround I kind of found is to wrap the table name with the single quotes, which is a bit annoying.

update 2:

I simplified my shortcut a bit for debugging purposes:

exec sp_executesql N'select @table_name;', N'@table_name varchar(max)', @table_name =

and when I ran it this is what I captured in the Profiler:

exec sp_executesql N'select @table_name;', N'@table_name varchar(max)', @table_name = dbo.Categories

which makes sense for the workaround I found earlier, but still would love to make it work without surrounding it in single quotes by myself

The result of a shortcut must still be valid SQL. As the documentation makes clear, the parameter values must be surrounded by quotes. You could use trace or Profiler to capture the actual code submitted to see exactly what's going wrong.

As the additional text seems to be appended to the shortcut rather than embedded in it there's no way to pre-wrap the additional text in quotes.

Perhaps a custom snippet would suit your needs better? Though it does take a little work to establish and you're obviously sensitive to gratuitous keystrokes.

If you're feeling very brave you could change the user's default schema inside the dynamic SQL and omit the schema from the appended text, removing the need for quotes.


Plan Cache Pages by Database in SQL Server 2008R2 Is it possible to get a break down of how many plan cache pages are being used per database? Perfmon seems to only have the total for the server, but maybe there is a DMV with more detail?

Also, does the buffer pool (sys.dm_os_buffer_descriptors) include the plan cache or is that separate?

Here are queries that will give you buffer breakdown and plan cache breakdown per database. Note that plan cache is broken down by MB, not by pages - I don't know of a handy way, off the top of my head, to calculate the pages used by the plan cache (you'd think it's simple division, but not necessarily).

-- buffer pool breakdown

SELECT
  db = DB_NAME(database_id),
  c = COUNT(DISTINCT page_id),
  size_MB = CONVERT(DECIMAL(12,2), COUNT(DISTINCT page_id)*8.0/1024)
FROM sys.dm_os_buffer_descriptors
WHERE database_id < 32767
GROUP BY database_id
ORDER BY c DESC;

-- plan cache breakdown

SELECT
  db = DB_NAME(t.[dbid]),
  size_MB = CONVERT(DECIMAL(12,2), SUM(p.size_in_bytes)/1024.0/1024)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
WHERE t.[dbid] < 32767
GROUP BY t.[dbid]
ORDER BY size_MB DESC;

On my test instance there are cases where plan cache is larger than the buffer pool for a given database.

The buffer pool contains both the data cache and the plan cache. But AFAIK sys.dm_os_buffer_descriptors only contains information about the data cache (index and data pages), so it does not necessarily reflect the entire buffer pool. The cases I saw above seem to support that theory.


How to list all constraints of a table in PostgreSQL? How to list all constraints (Primary key, check, unique mutual exclusive, ..) of a table in PostgreSQL?

Constraints can be retrieved via pg_catalog.pg_constraint.

SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema name>'
             AND rel.relname = '<table name>';

Replace <schema name> with the name of your schema and <table name> with the name of your table.


When is the SGA created in an Oracle database environment? When is the SGA created in an Oracle database environment?

A.) When the database is created

B.) When a user process is started

C.) When the database is mounted

D.) When the instance is started

E.) When a server process is started

Answer given is D.) When the instance is started

But oracle can have multiple database and system global has shared pool+ log file+control file for each and every database.

I am Confused and expect the correct answer as A.) When the database is created.

Please explain if I'm wrong.

https://docs.oracle.com/cd/B28359_01/server.111/b28310/create003.htm#ADMIN11073

To create a database first you must connect to an instance. SGA is a part of an instance so it is created before the database, thus A is a wrong answer.


ER model between video, tagbundle and tag I have the following situation:
There is a video platform (like youtube). Each video is described by a number of video tags. How to model the situation?
I realized it as follows:

Each video, is described by a 'tagbundle' and each 'tagbundle' can describe 1..N videos..But what about the relationsship between 'tagbundle' and 'tag'..Each 'tag' can be contained in 1..N 'tagbundles' but each 'tagbundle' can also contain 1..N 'tags'..how do I resolve this?

What is purpose of tagbundle? For such kind of problems you should put something between tags and tagbundle tables.
Consider table (could be called tag_mapping) that has 2 foreign keys and allows to join tags with bundles together (it has even fancy name - associative_entity).


What is a connection in MySQL Workbench? I've searched so much and still found no answer to what exactly a connection is. I'm a beginner and don't know how connecting to databases/servers works, so I can't move forward yet.

What does the connection connect to and from?

What do I put in the "hostname" and "port" if I'm making a schema for my website at "localhost/magento" in my browser?

Thanks

Are you familiar with "server versus client"?  Your program (Workbench, PHP, magento, etc) is the "client".  MySQL's program (mysqld) is the server.

If both are on the same computer, the connection can be made via a "socket" and you use "localhost", but no "port".

If they are on separate computers, the client needs to specify where the server is (via hostname or IP address) and port number (or use the default of 3306).

Database products other than MySQL have similar connection protocols.

More

The typical beginner installs WAMP or LAMP.  They contain Apache, MySQL/Workbench/phpmyadmin, and PHP/Perl/Python on Windows or Linux.  Everything you need for building and publishing, say, web pages.  In that context, everything is on the same computer.  Under the hood, there are a lot of moving parts.

As far as connecting, you simply follow the "socket" or "localhost" path.

Meanwhile, the "big guys" have thousands of servers -- some with MySQL, some with web servers (Apache+PHP or whatever), some with "clients" (with WordPress or whatever) and they talk over the network (hostname+port).  But you are not a "big guy" yet, so don't worry about this detail.


Is automatic failback on Postgres 8.4 possible via log shipping/PITR? I have successfully setup continuous archiving and recovery of a master and slave node with PostgreSQL 8.4 via log shipping. I'm using keepalived to monitor both nodes and I can successfully trigger failover.

If I immediately bring the old master back online after the failover, such that log shipping immediately resumes, the master just sits there waiting for a WAL file that never arrives. It appears to be a timeline issue where the old master is  looking for WAL files from what it knew the last timeline to be, but by design, the new master advanced the timeline.

I've seen some related discussions that seem to indicate that automatic failback isn't possible but they seem to involve later versions of postgres and streaming replication.

Is automatic failback possible, assuming the log shipping resumes as it should, or would one have to redo the process of making a base backup, copying it to the old master, then resuming log shipping?

EDIT: It occurred to me that I didn't mean automatic failback so much as I want the old master to rejoin the cluster as a standby.

Thanks

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

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

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

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


Migration DB postgresql in windows to Red Hat(linux) I would like to know how I could migrate a postgresql database which is currently in a Windows operating system to Red Hat? I'm a beginner and I don't know where to start.

Thanks .

pg_dump will create a file with the queries that are needed to recreate the state of your database. The file will look like a transaction or write ahead log.

pg_dump databasename > dumpfilename

pg_restore then will use this file to recreate your database.
It's also useful to migrate to a newer database version

you could also use

psql newdatabasename < dumpfilename


Analysis services access through MS Excel- permissions issues I have an instance of Analysis Services set up with my cube and am attempting to access it through MS Excel.

This works for one of my accounts but not for another. My goal is to get the second account to be able to access the cube just like the first. However, I am not sure what permissions I am missing for the second.

I have added the second user as an administrator on Analysis services and even on the SQL Server instance and yet can't get past the below error.

How does my second account need to be set up to access the cube?

The server name looks like: https://[server]/OLAP/msmdpump.dll

My issue here was that the accounts had an Active Directory policy that was set to expire the password at first log in. In other words, the user was meant to reset their password when they first use the account.

Taking this off the password policy made the accounts work.


Is it possible to query value of SQLNET.EXPIRE_TIME parameter? Can somebody advice whether it is possible to query for current value of SQLNET.EXPIRE_TIME parameter in Oracle 11 DB?

Should

select * from v$parameter

return value of SQLNET.EXPIRE_TIME (if it has been configured)?

No, it can't be queried. These values are stored in sqlnet.ora on either or both of the client and the server.

In either case, none of the contents of the network configuration files sqlnet.ora, tnsnames.ora, listener.ora etc (eg protocol.ora, snmp.ora, cman.ora) can be queried.

If you really need to read the contents of these files and can't solve this problem in some other way (programatically via some OS level program or even administratively), you could read the file via external tables or via a Java stored procedure.

I would prefer to solve this outside of the database.


Is there a reason I should not set my default data location to google drive when setting up PostgreSQL? I'm brand new to working with databases and am setting up a PostgreSQL database which will be used for personal projects. I'm setting it up with the postgis extension and will use both for some basic geospatial projects. I use the google drive back up and sync utility on my mac and use my google drive directory for everything. I'm not too worried about storage capacity on either my local disk or my google drive. When installing postgresql, I'm required to specify the install locations for both PostgreSQL and PostgreSQL data. Is there any reason I wouldn't want to set at least the data location on google drive? What about putting the whole install in that directory? Many thanks in advance

Don't know if it matters at all but I'm running mac os x 10.13

There are two good reasons not to use Google drive for your data directory: data integrity and performance.

The second is obvious because of the latency.

For the first: Google drive is a file server. I am sure that it does not offer the guarantees required by a relational database, particularly that you can reliably sync data to disk. Even a Windows file share does not offer these guarantees.


How can I determine how many IOPs I need for my AWS RDS database? I am migrating part of a mysql database into AWS.  The data in question is 99% write, and each row has about 1k of varchar fields, a datetime and 4 ints.

I estimate that we will need between 20-25k records inserted/hour during peak times.

I ran iostat -h on the current database, and it reported around 40 tps.

How do I figure out what type of IOPS I'll need?

You'll have to test it.

You could do some back of the envelope calculations to approximate the number of I/Os per insert, multiply it by the number of transactions per second, add in some buffer room etc, but it's much easier to just test it.

The easiest thing to do is to allocate a best guess, then go back and increase or decrease it to match you're real world testing. This is one of the luxuries of using a cloud based environment, hardware changes are low in capital costs and such changes usually require only config updates. With EBS volumes you can't just increase the number of IOPS, you'd have to scale up the size of the volume as well1. You can always just create a new volume and copy your data over. There will be some downtime but if you're data isn't huge it shouldn't be much as it'd be a raw copy.

Here's a guess at the number of I/Os necessary. Again it's just a guess though as the specifics depend on the number of indexes and whether your traffic flow will be smooth or spikey. At 25K tx/hour you've got ~7 tx/sec. The size of each row isn't particularly relevant as it's less than the size of a single I/O (4K). Each transaction will do somewhere between 1-5 IOP (primary insert plus a couple index tree inserts) so let's say ~35/s.

I say start out with the bare minimum of  100 IOPS and scale up if necessary.


How to join at least two tables with different column names but same data, and only show the matching data I am trying to combine two (possibly more) tables that has different column names but the same data within the columns I am trying to line up.  For example, I have a table called dbo.member and within this table is a column called UID. On another table called dbo.tasklist_data, there is a column called TaskID which holds some of the same data as UID.  What I am looking for is to combine the two tables with the result giving me ONLY the UID's on the member table, that matches up with the data in the TaskID column on the tasklist_data table.

I do hope I explained that well enough.  Please bear with me as I am new to this but eager to learn.  Any help or advice will be greatly appreciated.  Thank you.

From a high level, you'd simply do this:

SELECT *
FROM dbo.member m
    INNER JOIN dbo.tasklist_data tld ON m.UID = tld.TaskID;

The INNER JOIN will take rows from dbo.member where the UID column values match values contained in the TaskID column from the tasklist_data table.  If you wanted ALL the rows from dbo.member, and only matching rows from dbo.tasklist_data, you'd rewrite that as:

SELECT *
FROM dbo.member m
    LEFT JOIN dbo.tasklist_data tld ON m.UID = tld.TaskID;

Steve Stedman has an excellent resource for understanding how the different types of JOIN statements work.

If you need to do this on more than two tables, you simply add JOIN clauses as necessary:

SELECT *
FROM dbo.member m
    INNER JOIN dbo.tasklist_data tld ON m.UID = tld.TaskID
    INNER JOIN dbo.some_other_table sot ON m.UID = sot.some_column_name;

I have broken a (arguably) cardinal sin above by using SELECT * to return all columns from all of the tables mentioned in the FROM clause of the query.  It is generally considered good practice to explicitly specify the columns required, for instance:

SELECT m.UID
    , m.SomeColumn1
    , m.SomeColumn2
    , tld.TaskID
    , tld.SomeColumn3
FROM dbo.member m
    INNER JOIN dbo.tasklist_data tld ON m.UID = tld.TaskID;


What is the difference between NULL and \N while loading data from a csv using load data local infile I have a table which has four fields

create table test_hc1(cola int,colb int,colc varchar(20),cold int);

Now, I am trying to load following data

1,2,\N,4

and the third field in table is coming out as NULL, which is the expected result.

In another case, I am trying to load following data

1,2,NULL,4

and it is also loading the third field as NULL.

Here is my question : What is the difference between using the two(\N, NULL) ?

I am using

LOAD DATA LOCAL INFILE 'PATH_TO_DATAFILE'
INTO TABLE TABLE_NAME FIELDS TERMINATED BY x'01';

Any help is appreciated.

Thanks

Depends on your options FIELDS and LINES used in the LOAD DATA statement.

MySQL documentation

From MySQL doc:

  Handling of NULL values varies according to the FIELDS and LINES
  options in use:

  For the default FIELDS and LINES values, NULL is written as a field
  value of \N for output, and a field value of \N is read as NULL for
  input (assuming that the ESCAPED BY character is “\”).

  If FIELDS ENCLOSED BY is not empty, a field containing the literal
  word NULL as its value is read as a NULL value. This differs from the
  word NULL enclosed within FIELDS ENCLOSED BY characters, which is read
  as the string 'NULL'.

  If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.

  With fixed-row format (which is used when FIELDS TERMINATED BY and
  FIELDS ENCLOSED BY are both empty), NULL is written as an empty
  string. Note that this causes both NULL values and empty strings in
  the table to be indistinguishable when written to the file because
  both are written as empty strings. If you need to be able to tell the
  two apart when reading the file back in, you should not use fixed-row
  format.


SQL Server restore sequence from multiple full, diff and log backups Suppose I have a lot of backups (may include full, differential and log backups) of a SQL Server database. I want to write a program that can generate a SQL script to take the database to its most recent state. Now I have difficult in finding the correct restore order of these backup files. I read some pages in MSDN and got some basic rules. They are:

Full backups not only include data, but also include enough log records. This makes the full backup can be used by itself (after restore, use the log records in the backup to roll forward the database to a consistent state)
Every backup file (include full, diff and log) has some common property

first lsn: specify the first log record that included in the backup
last lsn - 1: specify the last log record that included in the backup
checkpoint lsn: specify the log record that corresponds to the most recent checkpoint operation
database lsn: specify the log record that corresponds to the most recent full backup

The log backups that need to be restored consecutively may have overlap log records, that is to say, if log backup A needs to be restored just before log backup B, A.last_lsn may be greater than or equal to B.first_lsn
You need to restore the most recent full backup (we have just one for sure), then the most recent diff backup (we may or may not have this), last, restore the logs backups that were taken after the full or diff backup in turn

Now my question is, how to determine which full and diff backups is the most recent one? How to determine which log file should start to restore after the full or diff restore? I guess these information can be found from those lsns, but I'm not sure of the algorithm. Can anyone help to write a piece pseudocode to describe the algorithm? Thanks.

Personally, I like my backups to be timestamped something like MyDB-yyyymmddhhnn-Full.bak or MyDB-yyyymmddhhnn-Log.bak in the file name. I've never actually read the LSN from a backup file, neither have I read the LSN information from the system tables.

You can sort the files and pick out which ones you need quite easily.


SQL Server account permissions for New ReportServer database from wizard I'm trying to create a ReportServer database on a different server (Server_AT) other that my Reporting Service server (Server_RT).
ON Server_AT, I created the RSExecRole as per the script here. Then created a login 'Reporter' with user 'Reporter' and assigned the RSExecRole.
Now when I use the configuration wizard -> create a new ReportServer database-> enter the SQL Server credential for Reporter as prompted-> and finish.

Now the process fails - constantly with on error after another that one of permissions in the RSExecRole cannot be found.
The ReportServer database does gets created on Server_AT but the user is dbo- but I am not able to connect to this database either (when I try next time).

What am I missing here?

@Kin- Thanks for your answer. I was able to refer to this from your answer and get it resolved.
https://social.technet.microsoft.com/Forums/en-US/7541b1ce-aff8-4385-9544-8f9422d01ba7/cannot-alter-the-role-rsexecrole-because-it-does-not-exist-or-you-do-not-have-permission

The fix is weird- this basically covers all the permissions that are collectively included in the RSExecRole but somehow works when executed invidually. Weird!!


Why shouldn't we allow NULLs? I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL.  I don't remember why this was the case though.

All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings).

But what do you do in the case of dates, datetime, and time (SQL Server 2008)?  You'd have to use some historic or bottomed-out date.

Any ideas on this?

I think the question is poorly phrased, as the wording implies that you've already decided NULLs are bad. Perhaps you meant "Should we allow NULLs?"

Anyway, here is my take on it: I think NULLs are a good thing. When you start preventing NULLs just because "NULLs are bad" or "NULLs are hard", you start making up data. For example, what if you don't know my birth date? What are you going to put in the column until you know? If you're anything like a lot of anti-NULL folks, you're going to enter 1900-01-01. Now I'm going to be placed in the geriatric ward and probably get a call from my local news station congratulating me on my long life, asking me my secrets to living such a long life, etc.

If a row can be entered where it is possible that you don't know the value of a column, I think NULL makes a lot more sense than picking some arbitrary token value to represent the fact that it is unknown - a value which others will have to already know, reverse engineer, or ask around to figure out what it means.

There is a balance, though - not every column in your data model should be nullable. There are often optional fields on a form, or pieces of information that otherwise don't get collected at the time the row is created. But that doesn't mean you can defer populating all of the data. :-)

Also the ability to use NULL can be limited by crucial requirements in real life. In the medical field, for example, it can be a life-or-death matter to know why a value is unknown. Is the heart rate NULL because there wasn't a pulse, or because we haven't measured it yet? In such a case, can we put NULL in the heart rate column, and have notes or a different column with a NULL-because reason?

Don't be afraid of NULLs, but be willing to learn or dictate when and where they should be used, and when and where they shouldn't.


What data type for a column which can have three states: yes, no, unknown I have an application where the user can decide between:

yes
no
unknown

I started to use a boolean column which is nullable.

But things get quite complicated since my framework (django) does not provide a input field for coding a search form out of the box.

In the search form I need four states:

yes
no
unknown
show all (ignore this column in the search)

Related question: https://stackoverflow.com/questions/38307866/search-form-for-nullbooleanfield

An old rule of thumb comes to my mind again:

  Avoid nullable db columns.

I want to change the data type from a nullable boolean field to something different.

What data type for a column which can have three states: yes, not, unknown?

I use Postgres 9, but AFAIK this should not matter for this question.

You want boolean.

It can have true (=yes), false (=no) and null (=unknown)

More details in the manual:
https://www.postgresql.org/docs/current/static/datatype-boolean.html

"Avoid nullable columns" is only a recommendation. If you need an "unknown" state, then NULL is exactly what you are looking for. It was specifically created to represent "unknown".

Postgres even support the keyword unknown for boolean expressions: where some_column is unknown is equivalent to where some_column is null if some_column is defined as boolean


When do I need to install Oracle database client? I know Oracle database client provides basic network components and in order to connect to an Oracle database, I have to install it on the client machine. I noticed for Oracle's SQL Developer, I haven't installed any client and it works quite well and can connect to the database without any issues. For Toad, it seems I need to install a client to make it work. My question is, when should I install the  Oracle client? How do I know 32bit or 64bit version is needed?

You haven't installed any client because SQL Developer is a Java application, and it uses the Oracle JDBC driver by default, which is located under jbdc/lib inside the directory of SQL Developer and it is included in the zip file you downloaded. SQL Developer is able to use an Oracle client as well.

Yes, you need to have something for connecting to Oracle databases.
It can be a JDBC driver, a .NET provider, or the collection of libraries that is called the "client".

32 or 64 bit depends on the platform and application you use. For modern tools and application you typically go for 64 bit.


Cardinality Estimation for >= and > for intra step statistics value I am trying to understand how SQL Server try to estimate for 'greater than' and 'greater than equal to' where clauses in SQL Server 2014.

I think I do understand the cardinality estimation when it hits the step for example if I do

    select * from charge where charge_dt >= '1999-10-13 10:47:38.550'

The cardinality estimation is 6672 which can be easily calculated as
32(EQ_ROWS) + 6624(RANGE_ROWS) + 16 (EQ_ROWS) = 6672 (histogram in below screenshot)

But when I do

    select * from charge where charge_dt >= '1999-10-13 10:48:38.550'

(increased the time to 10:48 so its not a step)

the estimate is 4844.13.

How is that calculated?

The only difficulty is in deciding how to handle the histogram step(s) partially covered by the query predicate interval. Whole histogram steps covered by the predicate range are trivial as noted in the question.
Legacy Cardinality Estimator
F = fraction (between 0 and 1) of the step range covered by the query predicate.
The basic idea is to use F (linear interpolation) to determine how many of the intra-step distinct values are covered by the predicate. Multiplying this result by the average number of rows per distinct value (assuming uniformity), and adding the step equal rows gives the cardinality estimate:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * F * DISTINCT_RANGE_ROWS)

The same formula is used for > and >= in the legacy CE.
New Cardinality Estimator
The new CE modifies the previous algorithm slightly to differentiate between > and >=.
Taking > first, the formula is:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * (F * (DISTINCT_RANGE_ROWS - 1)))

For >= it is:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * ((F * (DISTINCT_RANGE_ROWS - 1)) + 1))

The + 1 reflects that when the comparison involves equality, a match is assumed (the inclusion assumption).
In the question example, F can be calculated as:
DECLARE
    @Q datetime = '1999-10-13T10:48:38.550',
    @K1 datetime = '1999-10-13T10:47:38.550',
    @K2 datetime = '1999-10-13T10:51:19.317';

DECLARE
    @QR float = DATEDIFF(MILLISECOND, @Q, @K2), -- predicate range
    @SR float = DATEDIFF(MILLISECOND, @K1, @K2) -- whole step range

SELECT
    F = @QR / @SR;

The result is 0.728219019233034. Plugging that into the formula for >= with the other known values:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * ((F * (DISTINCT_RANGE_ROWS - 1)) + 1))
            = 16 + (16.1956 * ((0.728219019233034 * (409 - 1)) + 1))
            = 16 + (16.1956 * ((0.728219019233034 * 408) + 1))
            = 16 + (16.1956 * (297.113359847077872 + 1))
            = 16 + (16.1956 * 298.113359847077872)
            = 16 + 4828.1247307393343837632
            = 4844.1247307393343837632
            = 4844.12473073933 (to float precision)

This result agrees with the estimate of 4844.13 shown in the question.
The same query using the legacy CE (e.g. using trace flag 9481) should produce an estimate of:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * F * DISTINCT_RANGE_ROWS)
            = 16 + (16.1956 * 0.728219019233034 * 409)
            = 16 + 4823.72307468722
            = 4839.72307468722

Note the estimate would be the same for > and >= using the legacy CE.


Confusion about charset for connected tables from MS Access I run 10.4.12-MariaDB on Windows10 and I use 64-bit ODBC driver to connect tables from MS Access to MariaDB database. I am simply glad that it works (!).
But going deeper and working more with connected tables (yes, this way to link mdb and open source db world is too complicated, but no other way for me...), I found some constraints.

Now I am confused with charsets.
My configuration of MariaDb is as follows:

version: 10.4.12-MariaDB on Windows10 64-bit
[mysqld]
...
character-set-server=utf8

My table connected to Access ?2003? mdb file:

CREATE TABLE pohoda.xfa (
  ID INT(10) NOT NULL,
 ... list of fields
)
ENGINE = CONNECT,
TABLE_TYPE = ODBC,
TABNAME = 'Fa',
BLOCK_SIZE = 10,
CHARACTER SET utf8,
COLLATE utf8_general_ci,
CONNECTION = 'DSN=pohoda_64b;DBQ=c:/path/to/mdb;';

and further connection details from information_schema.tables.CREATE_OPTIONS:

`TABLE_TYPE`='ODBC' `TABNAME`='DJjizdy' `DATA_CHARSET`='cp1250' `BLOCK_SIZE`=10

To summarize, when I created the connected tables, I tested that I should set DATA_CHARSET='cp1250' to display non-ascii characters right.

As for browsing the tables, it works great. But when I came to the query, a problem appeared.

Simple SELECT with non-ascii character in WHERE clause does not return appropriate and really existing rows:

MariaDB [pohoda]> SELECT  id, Firma FROM  xfa WHERE Firma LIKE 'Adam Vič%'
Empty set (0.120 sec)

MariaDB [pohoda]> SELECT  id, Firma FROM  xfa WHERE Firma LIKE 'Adam Vi%';
+-------+--------------+
| id    | Firma        |
+-------+--------------+
| 14443 | Adam Vička   |
| 14488 | Adam Vička   |

Thus, while  WHERE Firma LIKE 'Adam Vič%' returns an empty set, just removing the character "č" from where clause  Firma LIKE 'Adam Vi%' gives the right recordset.
I tested it from command and from HeidiSQL with same results.

Could you please help me to set a charset for connected table?

I'm going to guess that the problem might be the difference between your table collation and the data_charset. Maybe try using a compatible collation, so how about this? Use CHARACTER SET cp1250, COLLATE cp1250_general_ci:

CREATE TABLE pohoda.xfa (
  ID INT(10) NOT NULL,
  ... list of fields
)
ENGINE = CONNECT,
TABLE_TYPE = ODBC,
TABNAME = 'Fa',
BLOCK_SIZE = 10,
CHARACTER SET cp1250,
COLLATE cp1250_general_ci,
CONNECTION = 'DSN=pohoda_64b;DBQ=c:/path/to/mdb;';


Subselect dependent on first select value I wish to do a select of all data matching a particular id.  I would also like to return a selection of all data matching the alt_id field in the first select.

This example works fine for the main query of two tables and their data based on the contact_id I feed it, in this case '1':

SELECT e_contacts.*, e_credentials.*
FROM e_contacts, e_credentials,
WHERE e_contacts.contact_id = '1' AND
e_credentials.contact_id = e_contacts.contact_id
ORDER BY e_contacts.contact_id

However, one of the fields in e_contacts is called alt_contact_id (wife or friend) which when holds a value, requires I get both contacts data.

So, I would like to have a second row of data returned for that alt_contact_id's data which exists in the same table.  In other words we'll be requerying the same select statement but feeding it e_contacts.contact_id = alt_contact_id found in the first select (if alt_contact_id has a value of course).

This is the table structure:

I'm completely uncertain how to accomplish it.

You can use 2 subqueries, one joining with the contact_id and another joining with the alt_contact_id. Then UNION them and ORDER BY:

SELECT cont.*, cred.*, 'main' AS contact_type
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.contact_id
WHERE cont.contact_id = 1

UNION ALL

SELECT cont.*, cred.*, 'alternate'
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.alt_contact_id
WHERE cont.contact_id = 1

ORDER BY contact_id ;

If you want this for all contacts, just remove the two WHERE clauses.


Inheritance and performance in database I have 3 table linked by an "inheritance" with Entity Framework.
Sadly, I have poor performance with those query because I can't make any good index.
Here is the tables :

CREATE TABLE [dbo].[PlannedElements](
    [IdPlannedElement] [int] IDENTITY(1,1) NOT NULL,
    [ActualDate] [datetime] NULL,
    [Order] [int] NOT NULL,
    [PlannedDate] [datetime] NULL,
    [TimeSpan] [time](7) NULL,
    [IsCompleted] [bit] NOT NULL,
    [PlanningLineIdPlanningLine] [int] NULL,
    [AddressIdAddress] [int] NULL,
    [CrewIdCrew] [int] NULL,
    [PlannedElementIdPlannedElement] [int] NULL,
    [SuppressionDate] [datetime] NULL,
    [LastUpdateDate] [datetime] NULL,
    [IsActualDateEstimated] [bit] NOT NULL,
    [TrackerIdTracker] [int] NULL,
    [HospitalRoom] [nvarchar](4000) NULL,
 CONSTRAINT [PK_PlannedElements] PRIMARY KEY CLUSTERED
(
    [IdPlannedElement] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[PlannedElements_PlannedFleetElement](
    [IdPlannedElement] [int] NOT NULL,
    [TypeOfFleetElement] [int] NOT NULL,
 CONSTRAINT [PK_PlannedElements_PlannedFleetEl] PRIMARY KEY CLUSTERED
(
    [IdPlannedElement] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[PlannedElements_PlannedBusinessFleetElement](
    [IdPlannedElement] [int] NOT NULL,
    [TypeOfBusinessFleetElement] [int] NOT NULL,
    [JourneyIdJourney] [int] NULL,
    [IsCertified] [bit] NOT NULL,
 CONSTRAINT [PK_PlannedElements_PlannedBusines] PRIMARY KEY CLUSTERED
(
    [IdPlannedElement] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I would need an index on  JourneyIdJourney, AddressIdAddress but when I use a materialized view SQL Server is never using it;
Do you have any recommendations ?

Thanks !

Paul White explains in his answer to the question below amongst other things that automatic indexed view matching won't even be considered if a low enough plan is found first.

What factors go into an Indexed View's Clustered Index being selected?

also
Consider having a look at what statistics you have in each table
and keeping them updated

and
when using indexed views WITH(NOEXPAND) query hint

From Query Hints (Transact SQL) on MSDN:

  The indexed view is not expanded only if the view is directly
  referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH
  (NOEXPAND, INDEX( index_value [ ,...n ] ) ) is specified.


Are there any performance benefits to using a hash table with no clustered index? I have this table:

CREATE TABLE [dbo].[relatea] (
   [mid] [varchar](16) NOT NULL,
   [sid] [varchar](16) NOT NULL
)

It stores hash matches. Is there any benefit to having this as a heap? It has around 7 million rows, and the values are not unique in either column. I know heaps are generally never good for any large tables.  This table currently has no indexes.

I was thinking of altering the table to:

CREATE TABLE [dbo].[relatea] (
   [mid] [varchar](16) NOT NULL,
   [sid] [varchar](16) NOT NULL,

   CONSTRAINT [pk_relatea] PRIMARY KEY CLUSTERED (
      [mid] ASC,
      [sid] ASC
   )
)

Table queries are generally but not always on sid, and the combination of mid and sid should always be unique.

Is it better to create a clustered index like this, as opposed to leaving the table as a heap?

I would put a clustered index (not a primary key) on the sid column.  If you do lookups against the mid column sometimes as well then add an index to the mid column with the sid column as an included index.

If you don't want to create a clustered index, then two non-clustered indexes one on each column with the other column as an included column should give you the same basic read performance.


Monitor delete statements on a specific table on DB2 LUW Background

User is claiming that records are deleted from a specific table, but nobody knows why. They are not sure if it's an application error or done by a user.

Question

Is it possible to monitor / catch / trace delete statements executed against a specific table?

My first idea was to create an event monitor, but I can't limit it to a specific table or a specific kind of statement.

Use the AUDIT TABLE statement on this table using an AUDIT POLICY with the EXECUTE category.
Or create an AFTER UPDATE trigger.


How do I limit the resources that pg_dump uses? Is it possible to limit the system resources used up by pg_dump?

I have a fairly resource intensive server that uses a PostgreSQL database. and it is running on FreeBSD 10.2. The server has 8 GB of memory.

I have a backup run with the following command.

pg_dump -h <address> -U <user> -Fc <database> -f <location>

As the backup is being completed the associated postgres process keeps rising in memory usage. It gets to the point of 2.2 GB for that process alone. At this point memory is all but used up and FreeBSD starts killing processes.

I've tried to change how the backup is done. For example, I tried using multiple jobs (and thus changed the format to directory).

pg_dump -j 4 -h <address> -U <user> -Fd <database> -f <location>

No differences. Eventually the memory usage will cap and FreeBSD will kill the database backup (among other processes).

I've also tried tuning some system variables. There was Managing Kernel Resources and also Resource Consumption. These have affected the running postgres processes that are used up by the server, but it had no impact on the process pg_dump uses. It still rose to 2.2 GB before FreeBSD started killing processes.

I've read through the pg_dump manual and haven't really seen anything that will help besides the multiple jobs.

At this point I'm not really sure what to do. Is there a way to cap the resources that pg_dump is allowed to use? I don't mind if the backup is slower. Just don't run the system out of resources where the OS has to start killing processes.

My assumption is that RAM isn't your problem. But that you're so IO-bogged down that you can't write to a file as a quick as you can read from the database.

There is no solution here, pg_dump is made it work as fast as it can. If it can read data faster than it can write it, your ram usage may go up. Until the table is flushed to disk. You may want to enable compression, see man psql and look for -Z,

   -Z 0..9
   --compress=0..9
       Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data
       segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file
       to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support
       compression at all.

As far as I know, processes don't know how much ram a system has or how much they should take. Normally, setting a limit just does what happened here and kills the process. If it didn't kill the process, they process may just keep drinking ram until it starts paging, and then beyond.


What is a standard or conventional name for a column representing display order? For example, a junction table associating a product and its pictures:

create table product_picture (
  product_id bigint not null references product(id),
  picture_id bigint not null references picture(id),
  some_column int not null
);

What is a common/conventional, short, general, name for some_column if it represents the display order of the photos?

order, sort, and sequence are out, as they are keywords.

It depends on what your philosophy is around column naming.

If you go for very descriptive long column names, then display_order is perfectly fine, since that is exactly what your column contains.

If you are comfortable with shorter names that may seem a little cryptic, but which can be figured out pretty easily, then you could use seq or ord.  For English speakers, seq is pretty unambiguous as there aren't a lot of words that this could reasonably represent.  I would pick that over ord for that reason.


What are the benefits of using a different tablespace for a new user schema? The tables in the schema  are intended to be used read only and is only updated once each three month.

My question refers to
performance
maintenance (backup/restore, export/import)

What about the temporary tablespace, is it better to use a different one in this case?

If you are planning to drop schemas, having a separate tablespace which can also be dropped will reduce fragmentation in whatever tablespace you would otherwise use (unless you are using uniform extents, but that is not the default).

RMAN recovery options are different as you would be able to do TSPITR on each individual schema.

Fragmentation is not an issue for temp tablespaces because "All extents of temporary tablespaces are of uniform size". Obviously there are no backup/restore, export/import implications for temp. By giving each user their own temp tablespace you'd be imposing a kind of resource limit on them, but there are less wasteful ways of doing that if it is your aim.


mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES I'm trying to backup a drupal site database but I'm having some issues. When I ran the following command:

mysqldump -uroot -p  drupaSite > drupaSite.sql

I get the following error:

mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES

if I tried to query the table I get the same error:

mysql> select * from drupal_install_test;
ERROR 1017 (HY000): Can't find file: 'drupal_install_test' (errno: 2)

I check the status of the table in the database:

show table status from drupaSite;

I get the following output:

| drupal_install_test     | NULL   |    NULL | NULL       |   NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL                | NULL            |     NULL | NULL           | Can't find file: 'drupal_install_test' (errno: 2) |

I ran the following query:

SELECT * FROM information_schema.tables WHERE table_name='drupal_install_test'\G

I get the following output:

*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: drupaSite
     TABLE_NAME: drupal_install_test
     TABLE_TYPE: BASE TABLE
         ENGINE: NULL
        VERSION: NULL
     ROW_FORMAT: NULL
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: NULL
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: NULL
       CHECKSUM: NULL
 CREATE_OPTIONS: NULL
  TABLE_COMMENT: Can't find file: 'drupal_install_test' (errno: 2)

I ran the following query:

CHECKSUM TABLE drupal_install_test;

I got the following output:

+-------------------------------+----------+
| Table                         | Checksum |
+-------------------------------+----------+
| drupaSite.drupal_install_test |     NULL |
+-------------------------------+----------+
1 row in set, 1 warning (4.34 sec)

I ran the following query:

CHECK TABLE drupal_install_test;

and I get the following output:

   +-------------------------------+-------+----------+---------------------------------------------------+
    | Table                         | Op    | Msg_type | Msg_text                                          |
    +-------------------------------+-------+----------+---------------------------------------------------+
    | drupaSite.drupal_install_test | check | Error    | Can't find file: 'drupal_install_test' (errno: 2) |
    | drupaSite.drupal_install_test | check | error    | Corrupt                                           |
    +-------------------------------+-------+----------+---------------------------------------------------+

2 rows in set (0.02 sec)

My question for you guys is how can I fix this in a way I can backup the database and restore it in another server. The site is working just fine I need to migrate the server. I would really appreciate your help guys.

You apparently have a missing drupal_install_test in your database schema. As the name says, it's a test table that is created at installation time to test the performance of the database and of the database user you provided for your site. At the end of the installation process, it gets dropped from the database.

The problem here is that the table has been dropped but mysql still thinks it exists. The reason why that table has remained in information_schema.tables should definitely be investigated and may reflect some issues in the database or in your server's configuration.

That said, if drupal_install_test is the only corrupted table in your database, you can try and drop it and proceed with your backup. I recommend that you make a full backup of the mysql data directory (in a typical linux installation /var/lib/mysql) and issue a DROP TABLE drupal_install_test command. Mysql should be able to restore the integrity of the schema tables and you should be able to proceed with your backup.


Sort results by number of matching words I have a table (called sentences) in which I have some sentences. I want people to be able to search it with multiple words, which would make the SQL query look like this:
SELECT content FROM sentences WHERE content LIKE "%<word1>%"
                                 OR content LIKE "%<word2>%"
                                 OR content LIKE "%<word3>%" etc... ;

How can I sort the results by the number of occurrences of different words, the most matching sentence comes first, etc...?

Add a fulltext index on content and use "match", as in
SELECT *, match(content)
  against ('you surround' IN NATURAL LANGUAGE MODE) as score
  FROM Sentence
  Order by score desc

See this fiddle


vertica/postgres copy text to binary column Until now I used Oracle to store xml file in blob data type column.

Is there any option in Vertica to copy xml file into binary column?

I saw there is varbinary data type but, I haven't succeeded to load this file into one row in this column.

If someone is familiar with this process in PostgreSQL can maybe be helpful (share your knowledge)

As you asked for it: here is a Postgres solution if you can upload all the files to the Postgres server (or at least a directory accessible from there).

do
$$
declare
  l_rec record;
  l_data bytea;
  -- change this to the server directory where all XML files are stored
  l_dir text := '/data/import';
  l_fname text;
begin
  for l_rec in select * from pg_ls_dir(l_dir) as t(name) loop
    l_fname := l_dir ||'/'||l_rec.name;
    l_data := pg_read_binary_file(l_fname);

    insert into the_table (the_xml_column)
    values (l_data);
  end loop;
end;
$$

This must be run as the superuser (postgres)  because the functions pg_ls_dir() and pg_read_binary_file() have restricted access (for security reasons)


What is wrong in my jdbc config pool? I am using clearDB with max_connections plan being 40.

This is my jdbc pool configuration:

<bean
   id="dataSource"
   class="org.apache.tomcat.jdbc.pool.DataSource"
   destroy-method="close"

   p:driverClassName="com.mysql.jdbc.Driver"
   p:url="#{ @dbUrl.getJdbc() }"
   p:username="#{ @dbUrl.getUser() }"
   p:password="#{ @dbUrl.getPassword() }"
   p:initialSize="2"
   p:maxActive="10"
   p:maxIdle="5"
   p:minIdle="2"
   p:maxWait="60000"
   p:timeBetweenEvictionRunsMillis="5000"
   p:minEvictableIdleTimeMillis="60000"
   p:testOnBorrow="true"
   p:validationQuery="/* ping */ SELECT 1"
   p:validationInterval="30000"
   p:testWhileIdle="true"
  />

Today we had 197 concurrent users on site and received a lot of

  com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: User 'yyyyy' has exceeded the 'max_user_connections' resource (current value: 40)

which meant that other users were not able to log in and use the site.

It was very frustrating. Might it be because of our maxActive being only 10? What you would propose to refine for this configuration in order to get rid of these error?

Go into my.cnf (or my.ini).  Increase (or set) max_user_connections in the [mysqld] section.

Also check max_connections; the 197 may be bumping up against that.

Do you really have a single user connecting more than 40 times?  Or is the "connection pooling" inflating things?


Converting a 50+ column table into 1 much smaller table I'm currently dealing with a legacy PLC device which is spitting out data in an annoying MYSQL table. The table structure is:

PK | FK | FK | FK | Result1 | Result2 | ... | ResultN

As you can imagine, performing any sort of query or join on this is quite cumbersome to write and takes a long time to resolve.

I'd like to setup an automatic select -> insert statement to pull the data and put it into a more appropriate table

PK | FK | FK | FK | Result Number | Result Value

Unfortunately, I'm stuck on how to pull the column names so I know what to populate the Result Number column with, as well as then how to cross-multiply across the table to make sure each new row has all the required keys for each value.

You need N INSERT ... SELECT ... statements.  A one-time tedious task.


Updating data from subpartition when the subpartition name is unknown Thanks to JSapkota who helped me pull the subpartition name as per below query

select subobject_name from user_objects where data_object_id IN
(select dbms_rowid.rowid_object(rowid) from TABLE
WHERE DOCID = 'S00102981655537O')

Which gives me a result

SUBOBJECT_NAME
--------------
MAR16_IN_UK

Now if I update that ID using below statement it works

UPDATE TABLE SUBPARTITION (MAR16_IN_UK)
SET FLAG = 'Y'
WHERE DOCID = 'S00102981655537O';

1 rows updated

But the same query by dynamically pulling the subpartition name would throw an error

UPDATE TABLE
SUBPARTITION (select subobject_name from user_objects where
data_object_id IN
(select dbms_rowid.rowid_object(rowid) from TABLE
WHERE DOCID = 'S00102981655537O'))
SET FLAG = 'Y'
WHERE DOCID = 'S00102981655537O';

Error

Error report -
SQL Error: ORA-00971: missing SET keyword
00971. 00000 -  "missing SET keyword"
*Cause:
*Action:

Version: Oracle 11g

you can not do in dynamically like this. partition name has to be known and the time when statement is compiled.

But you can use PARTITION FOR clause:

UPDATE TABLE T PARTITION FOR(TO_DATE('31-JAN-2007','dd-MON-yyyy')
...


SQL Server - Linked Server - Using OPENROWSET with windows integrated security I have seen many articles regarding OPENROWSET using integrated security (Windows Authentication), but I could not make it work for me.

It is working fine using SQL Server authentication:

 select *
 FROM
 OPENROWSET('SQLOLEDB',
 'myserver';'monitor';'#J4g4nn4th4#',
 'SELECT GETDATE() AS [RADHE]')

But the same query I am trying to run using windows authentication, and it is not working:

select *
 FROM
 OPENROWSET('SQLOLEDB',
'myserver';'Integrated Security=SSPI',
 'SELECT GETDATE() AS [RADHE]')

Can someone please post an example that works?

Here is a good article that describes OPENROWSET usage.

Examples of working scripts using OPENROWSET - please read comments

 ----------------------------------------------------------------
 -- this works - linked server REPLON1
 ----------------------------------------------------------------

select *
 FROM
 OPENROWSET('SQLOLEDB',
'Server=REPLON1;Trusted_Connection=yes;',
 'SELECT GETDATE() AS [RADHE]')

 select *
 FROM
 OPENROWSET('SQLOLEDB',
'Server=REPLON1;Trusted_Connection=yes;',
 'SET FMTONLY OFF select * from sys.dm_exec_requests')

 SELECT a.*
FROM OPENROWSET('SQLOLEDB', 'server=replon1;Trusted_Connection=yes;', 'SET
FMTONLY OFF select * from sys.dm_exec_requests') AS a
WHERE a.session_id > 50
ORDER BY a.start_time desc

  ----------------------------------------------------------------
 -- this does not work - when using windows authentication
 -- apparently because windows server 2003 and windows server 2012 have problems connecting - related to SID
 -- it works fine using SQL Server Authentication
 ----------------------------------------------------------------

 select *
 FROM
 OPENROWSET('SQLOLEDB',
'Server=SQLREPLON1\REP;Trusted_Connection=yes;',
 'SELECT GETDATE() AS [RADHE]')

-- Msg 18456, Level 14, State 1, Line 1
--Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

 ----------------------------------------------------------------
 -- this works - linked server SQLREPLON1\REP
 ----------------------------------------------------------------

 select *
 FROM
 OPENROWSET('SQLOLEDB',
 'SQLREPLON1\REP';'monitor';'#J4g4nn4th4#',
 'SELECT * from SAReporting.DBO.tblStockLedgerMovement')

Declare variable to get current instance name and pass value to OpenROWSet.

Enable Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

OPENROWSET using windows authentication to fetch data

DECLARE @InstanceName VARCHAR(200),
    @sql NVARCHAR(MAX)
SELECT  @InstanceName = ( SELECT @@servername  )

SELECT  @sql = 'select a.* from openrowset(''SQLNCLI'', ''Server='
        + @InstanceName
        + ';Trusted_Connection=yes;'', ''select * from Databasename.dbo.TableName'') as a'

EXEC sp_executeSQL @sql


mysql workbench *reverse engineer* is not connecting tables graphically. How to fix? I thought that previously when I reverse engineered databases, mySql Workbench figured out the relationships from the id names and connected the tables leading to an awesome ERD, even when the foreign key constraint clauses don't exist and they are just used through the fields (xxxx_id) have the foreign key values.

However currently, when I pick various databases (I create them in rails) and try to import them I end up with this nasty looking smodge:

Instead of:

MySQL Workbench does not have the capability to "infer" the relationships from table column names, or MyISAM tables. It requires that the constraints be defined in the database or SQL script being reverse engineered.


How to get combination results I have 36 data of box with weights:

24.8
24.7
24.6
24.6
24.65
24.75
25.1
24.7
24.7
24.9
24.8
24.65
24.85
24.85
24.7
24.75
25
24.6
24.55
24.6
24.5
24.55
24.5
24.85
24.7
24.65
24.4
24.4
24.55
24.45
24.35
24.95
24.65
24.85
24.75
25

Which combination of 12 boxes to get results of 296.35? Can I can provide this with stored procedure or cross join in MySQL?

The query to insert are:

insert tbl(weight) values(24.8);
insert tbl(weight) values(24.7);
insert tbl(weight) values(24.6);
insert tbl(weight) values(24.6);
insert tbl(weight) values(24.65);
insert tbl(weight) values(24.75);
insert tbl(weight) values(25.1);
insert tbl(weight) values(24.7);
insert tbl(weight) values(24.7);
insert tbl(weight) values(24.9);
insert tbl(weight) values(24.8);
insert tbl(weight) values(24.65);
insert tbl(weight) values(24.85);
insert tbl(weight) values(24.85);
insert tbl(weight) values(24.7);
insert tbl(weight) values(24.75);
insert tbl(weight) values(25);
insert tbl(weight) values(24.6);
insert tbl(weight) values(24.55);
insert tbl(weight) values(24.6);
insert tbl(weight) values(24.5);
insert tbl(weight) values(24.55);
insert tbl(weight) values(24.5);
insert tbl(weight) values(24.85);
insert tbl(weight) values(24.7);
insert tbl(weight) values(24.65);
insert tbl(weight) values(24.4);
insert tbl(weight) values(24.4);
insert tbl(weight) values(24.55);
insert tbl(weight) values(24.45);
insert tbl(weight) values(24.35);
insert tbl(weight) values(24.95);
insert tbl(weight) values(24.65);
insert tbl(weight) values(24.85);
insert tbl(weight) values(24.75);
insert tbl(weight) values(25);

SELECT  t1.weight,
        t2.weight,
        t3.weight,
        ...
        t12.weight
    FROM tbl t1
    JOIN tbl t2  ON t2.weight > t1.weight
    JOIN tbl t3  ON t3.weight > t2.weight
    ...
    JOIN tbl t12  ON t12.weight > t11.weight
    WHERE t1.weight +
          t2.weight +
          t3.weight +
          ...
          t12.weight  = 296.35;

Possible issues:

What I provided does not allow for duplicate weights.  If that can happen, I need an id or something instead of weight for avoiding dups.
If you want the closest, not exact, match:

Replace the WHERE clause with something like:

    ORDER BY ABS( t1.weight + ... + t12.weight - 296.35 ) ASC
    LIMIT 1

This would probably run a lot faster in any programming language then SQL.

I worry that this query will not complete in my lifetime.  I think it would take billions, maybe trillions of iterations.

OR

The code above is "brute force".  With some more thinking, it should be possible to 'prune' the list to cut back significantly (down from trillions to billions??) the amount of work.  But that would need a recursive CTE (upgrade to MySQL 8.0) or, as I say, use a real programming language.


How do others know what is delivery address and invoice address if they are in the same table? When I searched around, the recommended way is to use the invoice address and delivery address in the same table and use a field "type" to show what it is. For example
Addresses(Company, address, city, type)

Type = 1: The address is a delivery address
Type = 2: The address is a invoice address
Type = 3: The address both delivery address and invoce address (same address)
If someone does not look at my code that uses the database, they can not know what Type 2 is. Is it delivery, invoice or both? Do you usually clarify this and how? For example, if someone wants to search the database (outside my code that calls the database) for all billing addresses, they do not know if they should use type 1, 2 or 3 to get all invoice addresses from a database client.
Compared to if you had two tables, then the name of the table says what it is and whoever can easily pick out all the invoice addresses.
Delivery(Company, address, city)
Invoice(Company, address, city)

An address is an address. There’s no need to store the same address details twice because it’s used for a delivery address and also invoicing.
In your orders table you would have columns like delivery_address_id and invoice_address_id, they could have the same value in them.
If you wanted to list all addresses that were used as a delivery address you can do a simple semi-join
Select *
from address
where address_id in (select delivery_address_id from orders)


Postgresql - UPDATE with different WHERE clasules I've written a long Query for updating a table, with some large WITH selects. I want to update some columns during some condition and other if other conditions. I tried to write an easy example

WITH sub_select(SELECT column1, column2, column3 FROM my_large_table)
UPDATE my_second_table
SET first_col = column1
WHERE column3 > 1
SET second_col = column2
WHERE column3 = 1
FROM sub_selection;

What I can do is write it as two separate queries, but I prefer to solve it with one query, any suggestions?

with sub_select(SELECT column1, column2, column3 FROM my large_table)
UPDATE my_second_table
SET first_col = column1
WHERE column3 > 1
FROM sub_selection;
with sub_select(SELECT column1, column2, column3 FROM my large_table)
UPDATE my_second_table
SET second_col = column2
WHERE column3 = 1
FROM sub_selection;

It would be helpful if you gave tables and columns, but something like this might work. Basically, move the WHERE into a case statement and set the column equal to itself if the condition is not met.

WITH sub_select(SELECT column1, column2, column3 FROM my_large_table)
UPDATE my_second_table
SET first_col = case when column3 > 1 then column1 else first_col end
, second_col = case when column 3 = 1 then column2 else second_col end
FROM sub_selection;


MS-SQL stored procedure to insert into one-to-many-to-many relationship I have three tables to represent a single record in a one-to-many-to-many relationship. I would like to create a stored procedure in MS SQL Server to create the record all at once. I have seen examples on how to do this with just two tables with a table-valued parameter to populate the child table values, but can't find how to do the same with one more table deep.

Table 1 - request(req_id, requester, date)
Table 2 - reqitems(line_id, req_id, descript, qty)
Table 2 - itemcharge(charge_id, line_id, dept, percent)

So a request can have 1 or more items & each item can have 1 or more charges, adding up to 100%.

I appreciate any help you have. Thanks

Example to populate Tables 1 & 2

CREATE TYPE dbo.reqlineTable AS TABLE
(
    descript nvarchar(100),
    qty int
);
GO

CREATE PROCEDURE InsertRequest
    @requester nvarchar(50),
    @date datetime,
    @lines reqlineTable READONLY
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @req_id int;

    INSERT INTO dbo.request
           (requestor
           ,date)
     VALUES
           (@requester
           ,@date);

    SET @req_id = SCOPE_IDENTITY();

    INSERT INTO dbo.reqitems
    SELECT qty
        , descript
        , @req_id
    FROM @lines;

    SET NOCOUNT OFF;

END
GO

You can use the OUTPUT clause of the INSERT to capture the identity values that were generated like so:

DECLARE @output TABLE (
   line_id int,
   qty int,
   descript nvarchar(100),
   req_id int
);

INSERT INTO dbo.reqitems
OUTPUT INSERTED.line_id, INSERTED.qty, INSERTED.descript, INSERTED.req_id
  INTO @output
SELECT qty
    , descript
    , @req_id
FROM @lines;

Then you can use the @output table variable to perform the insert into your third table:

INSERT itemcharge
SELECT /* whatever values you need go here */
  FROM @output;

OUTPUT Clause (Transact-SQL)


Get two weeks of data but group by a period of 7 days I am looking to run a query that collects the number of results over the last 14 days, this is fairly easy and achieved like so:

SELECT COUNT(id)
FROM table_name

What I'd like to do is then group the results so that I can show this week and the previous week. Using the WEEK() option in MySQL allows me to get the previous two weeks worth of data, but grouping by weeks can return either 2 or 3 results depending on which week of the year the results fall into:

For example:

SELECT WEEK(date_logged) as week, COUNT(id) as rows
FROM table_name
WHERE date_logged > DATE_SUB(NOW(), INTERVAL 14 DAY)
GROUP BY WEEK(date_logged)

produces:

week 38: 10 result
week 39: 20 results
week 40: 3 results

rather than:

this week: 13 results (10 from week 38 and half of week 39)
previous week: 20 results (half of week 39 and all of week 40)

Hopefully that dataset illustrates what I'm after.

If possible, I'd like to avoid doing a select on a select and hopefully I'm not too far what I'm looking for.

SELECT 'last week' AS week, COUNT(*) AS rows
    FROM table_name
    WHERE date_logged >= CURDATE() - INTERVAL  6 DAY
      AND date_logged  < CURDATE() + INTERVAL  1 DAY
  UNION ALL
    SELECT 'previous week' AS week, COUNT(*) AS rows
    FROM table_name
    WHERE date_logged >= CURDATE() - INTERVAL 13 DAY
      AND date_logged  < CURDATE() - INTERVAL  6 DAY ;


How do I shrink the MASTER log file in SQL Server 2008? This is for a non-system database:

USE AdventureWorks
GO
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (AdventureWorks_Log)
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL
GO

But should I do something different for the MASTER database?

The first question that is going to come to mind for many readers is, "why is your master database log so big that you are thinking of shrinking it?"  If user tables were accidentally placed in master that caused log file growth, they need to be removed.

If the master log file has become overly large for some reason and there is now free space in it, you can shrink it like any other database log file.  Master should already be in simple recovery mode, so your shouldn't need to change it.

USE master
GO
DBCC SHRINKFILE (N'mastlog', 0, TRUNCATEONLY)
GO

You may want to resize it after this if it shrunk down to less than a couple MB.  Not sure what the default is, but I'm looking at a later version of SQL Server and it is 3 MB, so anything in that range should be a sufficient.


How to bring all replicas on a SQL availability group to an earlier state / point in time? I have a SQL Always on availability group, with 4 replicas. One of them is the primary. Lets say someone accidentally drops a table on the primary. This transaction would go to all of the secondary replicas.

Now if I want to restore the state of the entire group to before the table was dropped, what would be the correct (time efficient) sequence of steps to take? Assuming I have all the full and transaction backup files needed.

From my current understanding, restoring a back up on the primary alone would cause a data conflict (or corruption issues) on the secondary replicas - so I have to suspend data movement and drop the availability databases on the secondary before I restore a back up, then re-seed the databases from scratch on the secondary replicas. Is this is the best approach? Thanks.

Yes, that is the correct approach.

From my current understanding, restoring a back up on the primary alone would cause a data conflict (or corruption issues) on the secondary replicas

Unless you remove the database from the Availability Group, you will not be able to restore the same database on the primary node.

On a side note:
If it is just dropping one table (depending on the size of the table compared to the database size) I might consider restoring the backup with a different name and recreate that table in the primary. There are many ways to do that which is not the scope of this question.


Truncate Table locking sys views We have a proc that runs Truncate Table inside an Snapshot transaction.  This seems to be causing a LOCK_M_S lock that blocks the sys view sys.partitions.

Is there a convenient work around for this?  I like the efficiency of not taking excess logs that happens with truncate, but don't want to lock up my sys.partitions.

I'm happy to post the code upon request, but I'm pretty sure this is some behavior of Truncate being inside a Snapshot transaction. that i'm just unaware of.

Locking for truncate and delete is different. As for locking TRUNCATE behave more like 'ALTER TABLE' than DELETE.

From Microsoft documentation:
TRUNCATE TABLE (Transact-SQL)
"When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row".

As you can see, there is a schema lock on the whole table.


Partial unique constraint spanning multiple tables in postgres To enforce partial uniqueness in postgres, it is a well known workaround to create a partial unique index instead of an explicit constraint, like so:

CREATE TABLE possession (
  possession_id serial PRIMARY KEY,
  owner_id integer NOT NULL REFERENCES owner(owner_id),
  special boolean NOT NULL DEFAULT false
);
CREATE UNIQUE INDEX possession_unique_special ON possession(owner_id, special) WHERE special = true;

This would restrict each owner to having no more than one special possession at the database level. It obviously isn't possible to create indices spanning multiple tables, so this method cannot be used to enforce partial uniqueness in a supertype & subtype situation where the columns exist in different tables.

CREATE TABLE possession (
  possession_id serial PRIMARY KEY,
  owner_id integer NOT NULL REFERENCES owner(owner_id)
);

CREATE TABLE toy (
  possession_id integer PRIMARY KEY REFERENCES possession(possession_id),
  special boolean NOT NULL DEFAULT false
);

As you can see, the earlier method does not allow for restricting each owner to no more than one special toy in this example. Assuming each possession must implement exactly one subtype, what is the best way to enforce this constraint in postgres without substantially altering the original tables?

Not an answer for postgres, but may be useful nevertheless to someone using Oracle who stumbles across this:

Oracle allows partial uniqueness to be enforced across multiple tables using a fast refreshing materialised view. Tom Kyte describes it here. In short, if a join produces any rows on commit, it violates a constraint on the materialised view.

Untested, but in principle it should work like so:

create materialized view log
  on possession with rowid;

create materialized view log
  on toy with rowid;

create materialized view one_special_toy_per_owner
  refresh fast
  on commit
  as select p.owner_id, count(t.special) as special
     from possession p, toy t
     where p.possession_id = t.possession_id
     group by p.owner_id
     having count(t.special) > 1;

alter table one_special_toy_per_owner
  add constraint owner_has_only_one_special_toy
  check (owner_id is null and special is null);