ForeignKey setup clarification on updating related fields Considering these two tables in PosgtreSQL:
# report
+----+-------------+--------------------+
| id | report_name | report_description |
+----+-------------+--------------------+
| 1  | RPT         | ABCDEFGH           |
| 2  | TST         | LMNOPQRS           |
+----+-------------+--------------------+

# report_years
+----+--------------+-------------+------+
| id | report_id_fk | report_name | year |
+----+--------------+-------------+------+
| 11 | 1            | RPT         | 2019 |
| 12 | 1            | RPT         | 2020 |
| 13 | 2            | TST         | 2019 |
+----+--------------+-------------+------+

Given that report_id_fk is a foreign key in the report_years table, there wouldn't be a way to CASCADE and update the report_name based on report_id_fk?
They would have to be separate foreign keys?
report_name shouldn't even be in the report_years table in the first place since it is redundant and should just be queried over with a JOIN to report?
Is it possible to have a multifield foreign key constraint such that report_name and year would be required for a CASCADE?

For example:
# report
+----+-------------+------+
| id | report_name | year |
+----+-------------+------+
| 1  | RPT         | 2018 |
| 2  | RPT         | 2019 |
| 3  | RPT         | 2020 |
+----+-------------+------+

# report_details
+----+-----------+----------------+---------+---------+
| id | report_id | report_name_fk | year_fk | details |
+----+-----------+----------------+---------+---------+
| 11 | 1         | RPT            | 2018    | ABC     |
| 12 | 2         | RPT            | 2019    | DEF     |
| 13 | 3         | RPT            | 2020    | GHI     |
+----+-----------+----------------+---------+---------+

If I wanted to change the report_name for year = 2020 in report, it would CASCADE to report_details and update the report_name_fk based on the report_name_fk and year_fk?
Or (back to question 1), can I setup a CASCADE that would updated report_name_fk and year_fk in report_details?

I can see how to do this with a manual UPDATE, but seeing if it is possible with CASCADE. Or, like I think, this is redundant, should be avoided and if you need to know report_name and year for a report in report_details, you should just write a query an JOIN it.

You could solve this with a two-column foreign key and cascading update, but that requires a redundant two-column unique constraint on report, which will harm DML performance and use extra storage space.
The proper solution is to remove the redundant column from report_years. This concept of removing redundancy from a data model to improve consistency is so important that there is a name for it: normalization.


SP_WhoIsActive - RESOURCE_SEMAPHORE_QUERY_COMPILE Had an issue with one of our production servers earlier today. Many transactions were suspended with RESOURCE_SEMAPHORE_QUERY_COMPILE wait.
Have sp_whoisactive data logged to table every minute. Below is a snippet from it. It is ordered by USED_MEMORY.

Can someone help me understand the unit of measure for Used_Memory please or should i be looking at other measure to find the query that caused the memory pressure.

Updated Pic of the same session :

The sp_WhoIsActive documentation says:

  The [used_memory] column is also reported based on a number of 8 KB pages. The number a combination of both procedure cache memory and workspace memory grant.

However, I need to point out a few things in your question:

Queries that are waiting on compilation memory aren't shown in your screenshot
Those queries waiting for compilation memory are looking for a different kind of memory (gateways) - I'm not saying you don't have memory pressure, I'm just saying I probably wouldn't sort by memory_used to track the problem down
Your screenshot shows a serious blocking issue - queries have been waiting minutes (not seconds) because they're blocked by others

In your case, I'd start by troubleshooting the lead blocker. Don't worry about the queries waiting to compile - they haven't even started yet. In your scenario, the last thing you should be doing is starting even more queries, which are likely to just get blocked anyway and consume more workspace memory while they wait. Get the lead blocker out of the way instead.


Generate SQL scripts in powershell I want to be able to generate simple repetitive scripts in SQL using Powershell as the scripting language.
For example,  If I want to generate a SQL GRANT command for every table in a database, and for every category of user in the database,  the generic grant command might look like this:
grant $privs
   on $table
   to $user;

Here, $privs, $table, and $user are parameters to the Grant command,  expressed as powershell variables.
I want to step through a list of data that could be applied to this generic form to produce the actual script.  The list of data might look like this:
privs       table       user
-----       -----       ----
ALL         Employees   DBA
READ        Employees   Analyst
READ, WRITE Employees   Application
ALL         Departments DBA
READ        Departments Analyst, Application

This should generate five SQL GRANT commands.
Full Disclosure:  I am asking this question so as to be able to provide my own answer.

I have written a little function in Powershell that applies a driver table to a template in order to produce a simple repetitive result.  It was really a learning exercise to help me become proficient in Powershell.
Here is the function:
 <#
.NOTES
    Script: Expand-Csv    Rev:  3.2
    By:     DGC           Date: 2-21-19
.SYNOPSIS
    Generates multiple expansions of a template,
    driven by data in a CSV file.
.DESCRIPTION
    This function is a table driven template tool.

    It generates output from a template and
    a driver table.  The template file contains plain
    text and embedded variables.  The driver table
    (in a csv file) has one column for each variable,
    and one row for each expansion to be generated.
#>
function Expand-csv {
    [CmdletBinding()]
    Param (
       [Parameter(Mandatory=$true)]
       [string] $driver,
       [Parameter(Mandatory=$true)]
       [string] $template
    )
    Process {
       Import-Csv $driver | % {
           $_.psobject.properties | % {Set-variable -name $_.name -value $_.value}
           Get-Content $template | % {$ExecutionContext.InvokeCommand.ExpandString($_)}
       }
    }
}

Expand-Csv takes two inputs.  The first is a CSV file that represents the driver table.  In this  example,  the csv file might look like this.
privs,table,user
ALL,Employees,DBA
READ,Employees,Analyst
"READ, WRITE", Employees, Application
ALL,Departments,DBA
READ,Departments,"Analyst, Application"

The template might look like this:
grant $privs
   on $table
   to $user;

The result of running it through Expand-Csv might look like this:
grant ALL
   on Employees
   to DBA;

grant READ
   on Employees
   to Analyst;

grant READ, WRITE
   on Employees
   to Application;

grant ALL
   on Departments
   to DBA;

grant READ
   on Departments
   to Analyst, Application;

This result is fed down the pipeline,  but it could easily be written to a file for later execution by SQL.
A superior tool might be written that takes as input a table or view in a database instead of a .CSV file.  I wanted to do the easy stuff first.
I apologize for the fact that the code is not easy to read.  I was interested in minimizing the use of variables inside it,  so I used $_ in more than one context.  At least it's small.
You may be able to use this tool as a starting point to develop something that will make your workload easier.  I hope so.


String Function to Get Left 1000, But Only To Last Period or Carriage Return I have blocks of text, anywhere between 500 and 2000 characters long.

What I need to be able to do is take a maximum of 1000 characters from the description but only to the last full stop or carriage return (Char(13)), so that the text isn't cut off mid-sentence. Should there be both periods and Char(13)'s I would ideally like it to cut if off at the period.

I'm pasting some randomly generated text to try and show what I mean.

  *Up unpacked friendly ecstatic so possible humoured do. Ample ended might folly quiet one set spoke her. We no am former valley assure.
  Four need spot ye said we find mile. Are commanded him convinced
  dashwoods did estimable forfeited. Shy celebrated met sentiments she
  reasonably but. Proposal its disposed eat advanced marriage sociable.
  Drawings led greatest add subjects endeavor gay remember. Principles
  one yet assistance you met impossible. The who arrival end how fertile
  enabled. Brother she add yet see minuter natural smiling article
  painted. Themselves at dispatched interested insensible am be
  prosperous reasonably it. She boisterous use friendship she dissimilar considered expression. Sex quick arose mrs lived. Mr things do plenty others an vanity myself
  waited to. Always parish tastes at as mr father dining at. Led ask
  possible mistress relation elegance eat likewise debating.

  Or am nothing amongst chiefly address. The its enable direct men
  depend highly. Ham windows sixteen who inquiry fortune demands. Is be
  upon sang fond must shew. Really boy law county she unable her sister.
  Feet you off its like like six. Among sex are leave law built now. In
  built table in an rapid blush. Merits behind on afraid or warmly. In either so spring wished. Melancholy wayBy message*

I would want to be able to return all text up to "chiefly address." (990 characters).

Edit: This was posted to address an earlier version.

The left 1000 characters are easy.

Then find the first period in the reverse of the left 1000. That's how much you should subtract from the original 1000.

Like:

LEFT(@s,
    1001-
    CHARINDEX('.',
        REVERSE(LEFT(@s,1000))
    )
)

...I think - you may have to add one somewhere if it wipes the period or leaves a space.


Good way to restructure a table I have a number of tables in an existing database that i would like to migrate to a new database but with a little different structure.

For example if a current table has columns like below:

table1
id, a1, a2, a3, a4, b1, b2, b3, b4

And the new structure would be something like:

table1, table2 (new table)

table1 (patient names)
id, a1, a2, a3, a4, bfk

table2 (doctor names)
id, b1, b2, b3, b4

EDIT:

Adding hypothetical scenario of patients and doctors.

Lets say that the original table had 4 rows for 4 patients so
it would look like

 row1   id, pat1fname, pat1lname, pat1address...., doc1fname, doc1lname,....
 row2   id, pat2fname, pat2lname, pat2address...., doc2fname, doc2lname,....
 row3   id, pat3fname, pat3lname, pat3address...., doc1fname, doc1lname,....
 row4   id, pat4fname, pat4lname, pat4address...., doc1fname, doc1lname,....

Lets say that we only have two doctors and we want to put them in their own table 'Doctors'

so the tables would look like this

Original table structure
     row1   id, pat1fname, pat1lname, pat1address...., doc1fname, doc1lname,....
     row2   id, pat2fname, pat2lname, pat2address...., doc2fname, doc2lname,....
     row3   id, pat3fname, pat3lname, pat3address...., doc1fname, doc1lname,....
     row4   id, pat4fname, pat4lname, pat4address...., doc1fname, doc1lname,....

New table structure
'Patients' table
    row1    id, pat1fname, pat1lname, pat1address, 1, ....,
    row2    id, pat2fname, pat2lname, pat2address, 2, ....,
    row3    id, pat3fname, pat3lname, pat3address, 1, ....,
    row4   id, pat4fname, pat4lname, pat4address, 1, ....,
'Doctors' table
    row1    1, doc1fname, doc1lname,....
    row2    2, doc2fname, doc2lname,....

What would be a good way of linking these two new tables and converting each row from one-table-structure above to the two-table-structure i mentioned.

Methods of Linking:
1. Logically just left join doctors table and get doctor ID during patients data insert as showed below
2. Physically add foreign key constraint and during insert left join doctors table and get doctor ID during patients data insert

 /* Supposed your OriginalTable is with following structure */

    create table #OriginalTable
    (
    ID int,
    patfname varchar(100),
    patlname varchar(100),
    pataddress1 varchar(100),
    pataddress2 varchar(100),
    patcity varchar(40),
    patstate varchar(4),
    Patzipcode varchar(20),
    Docfname varchar(100),
    Doclname varchar(100),
    Docaddress1 varchar(100),
    Docaddress2 varchar(100),
    Doccity varchar(40),
    Docstate varchar(4),
    Doczipcode varchar(20),
    DocNPI varchar(40)
    )

    /*Dumpy data insert*/
    insert into #OriginalTable
    select '1', 'pat1fname','pat1lname','pat1address1','pat1address2','Pat1City','p1S','pt1zip'
    ,'doc1firstname','doc1lastname','doc1address1' ,'doc1address2' ,'doc1City','d1S','dt1zip','000001'
    union all
    select '2', 'pat2fname','pat2lname','pat2address2','pat2address2','Pat2City','p2S','pt2zip'
    ,'doc2firstname','doc2lastname','doc2address1' ,'doc2address2' ,'doc2City','d2S','dt2zip','000002'
    union all
    select '3', 'pat3fname','pat3lname','pat3address3','pat3address2','Pat3City','p3S','pt3zip'
    ,'doc1firstname','doc1lastname','doc1address1' ,'doc1address2' ,'doc1City','d1S','dt1zip','000001'
    union all
    select '4', 'pat4fname','pat4lname','pat4address4','pat4address2','Pat4City','p4S','pt4zip'
    ,'doc1firstname','doc1lastname','doc1address1' ,'doc1address2' ,'doc1City','d1S','dt1zip','000001'

    /* create patients table with all required fields*/
    create table Patients
    (
    ID int identity(1,1),
    FirstName Varchar(100),
    LastName Varchar(100),
    Adress1 Varchar(100),
    Adress2 Varchar(100),
    City Varchar(50),
    [State] Varchar(4),
    ZipCode varchar(20),
    DoctorID Int
    )

    /* create Doctors table with all required fields*/
    create table Doctors
    (
    ID int identity(1,1),
    FirstName Varchar(100),
    LastName Varchar(100),
    Address1 Varchar(100),
    Address2 Varchar(100),
    City Varchar(50),
    [State] Varchar(4),
    ZipCode varchar(20),
    NPI varchar(40)
    )

    /*Remove existing data from patients and doctors table*/
    delete from Doctors
    delete from patients

    /* insert all distinct doctors data from Orginal table */
    insert into Doctors( FirstName ,LastName ,Address1 ,Address2 ,City ,[State] ,ZipCode ,NPI )
    select distinct docfname,doclname,docAddress1,docAddress2, docCity, docState, docZipcode, docNPI
    from #OriginalTable

    /* Set Identity insert On to add OLD unique ID values as they are in #OriginalTable */

    SET IDENTITY_INSERT Patients ON

    insert into  Patients( ID , FirstName ,LastName ,Adress1 ,Adress2 ,City ,[State] ,ZipCode ,DoctorID )
    select old.ID ,old.patfname ,old.patlname ,old.pataddress1 ,old.pataddress2 ,old.patcity ,old.patstate ,old.Patzipcode ,doc.ID
    from #OriginalTable old left outer join Doctors doc
    on old.Docfname=doc.FirstName
    and old.Doclname=doc.LastName
    and old.Docaddress1=doc.Address1
    and old.Docaddress2=doc.Address2
    and old.Doccity=doc.City
    and old.Docstate=doc.[State]
    and old.Doczipcode=doc.ZipCode
    and old.DocNPI=doc.NPI

    /*Set Identity insert off */
    SET IDENTITY_INSERT Patients OFF

    /* Query table to verify inserted data*/
    select * from #OriginalTable
    select * from Patients
    select * from Doctors

    DROP Table #OriginalTable


Analyzing SQL Server query plan I ran the sp_BlitzCache script from the Brent Ozar toolkit on a server that I am monitoring (I am new at the company for about 3 weeks). The result of the procedure has the following values:
╔═════════╦═════════════╦═══════════════════╦══════════════════╦════════════════╦══════════════╦════════════╦═════════════════════╦═══════════════════╦═════════════════╦═════════════╦═══════════╦═════════════╗
║  Cost   ║ #Executions ║ Executions/Minute ║ Execution Weight ║ Total CPU (ms) ║ Avg CPU (ms) ║ CPU Weight ║ Total Duration (ms) ║ Avg Duration (ms) ║ Duration Weight ║ Total Reads ║ Avg Reads ║ Read Weight ║
╠═════════╬═════════════╬═══════════════════╬══════════════════╬════════════════╬══════════════╬════════════╬═════════════════════╬═══════════════════╬═════════════════╬═════════════╬═══════════╬═════════════╣
║ 47,4959 ║      212068 ║ 416,6365          ║ 10,9146          ║ 21883508.2710  ║ 103.1910     ║ 61,4143    ║ 47839177.6180       ║ 225.5841          ║ 73,0647         ║ 15372027688 ║     72486 ║ 66,6277     ║
╚═════════╩═════════════╩═══════════════════╩══════════════════╩════════════════╩══════════════╩════════════╩═════════════════════╩═══════════════════╩═════════════════╩═════════════╩═══════════╩═════════════╝

I took these values about 4pm. I have a column that indicates the plan was created about 8am (8 hours window).
I verified that we have the total reads value = to "15372027688". Is this possible? In the space of 8 hours, I have about 15 billion reads? I have the CPYU weight with the value = 61,4143 and the cost = 47,4959
Can anyone explain to me better the cost of the query, what it means? Is about the cost threshold of parallelism?
Also have the warnings running the procedure:
Warnings

Missing Indexes (3)
Parallel
Downlevel CE (the database is in Compatibility Level 110 while running on a SQL Server 2014 instance) and is running OUTSYSTEMS databases)

I also run performance counters on the server and have about 1400 connections into the SQL Server instance. On the network adapter traffic I have the total bytes in about 92 000 000, 00/sec.

Welcome to stack exchange. As a general rule, you should try to limit your questions to a single, well-defined question. Here it reads as if you just want general tuning help for your server, which isn't the best fit for the site. Ideally, questions and answers will be useful to a wide audience and will remain relevant over time. With all of that said, I'm going to answer the two discrete questions that you have about the number of logical reads and the significance of query cost.

It is certainly possible to have 15 billion reads on a server over an eight hour period. Those reads are logical reads which represents operations against memory instead of disk. I just ran a random query on my desktop that did about 2 million logical reads in a minute. Extrapolating that out would give me a total of about 58 billion logical reads over an eight hour period.

The query cost is a unitless measure of the expected amount of work that SQL Server will need to complete to execute a query. In general, the number doesn't mean much without context and you don't have to be concerned with it unless there's a problem. It is used by the engine in various ways if you're interested in that. Here's the list I know off the top of my head:

If the query cost is too low it won't be eligible for a parallel plan. This is controlled by the cost threshold for parallelism option.
The number of seconds that a query will wait for a memory grant before throwing an error. There are some configuration options here which can influence it, but the general formula is something like query cost / 25 (rounded in seconds) with a maximum of 86400 seconds (1 day).
The amount of steps taken by the optimizer during query plan creation. The optimizer may do more work in evaluating possible query plans for queries which are expected to be more expensive.
If the query is sent to a small query resource semaphore.
If the query is not able to run due to the query governor cost limit.

In general, you don't have to worry about those details unless something is going wrong. There are a few common cases where the query cost can be useful though. If certain queries aren't going parallel or if too many queries are going parallel then you may need to adjust CTFP based on your workload. If a query is slow and you're able to find a query hint that makes it faster than the difference in costs between two queries can provide useful clues as to why the optimizer didn't naturally select the query plan that you hinted. If a query is very slow with an absurdly slow cost (for example, < 1 unit) that suggests that the optimizer is working off bad information. For example, a cardinality estimate could be way off. If you're able to correct that bad information you might get a more efficient plan.


Replicating tables for reporting purpose We have 5 tables in a database on Server A that we would like to replicate to Server B for reporting purposes. We would like to create different indexes on the destination copy so we can get better reporting performance. We considered the following options:

Always On Availability group (Feature of SQL Server 2012): Cannot use this because it only allows to create a Read Only copy of the source database i.e. we cannot create different set of indexes on each server.
Transactional Replication: This looks good for implementing what we are looking to do but since our tables are going to be updated very frequently the performance impact on server is expected to be high and we are looking to avoid any performance degradation.
Snapshot each night: This could have been OK too since we don't need to keep the two databases in sync at all times. But this will cause to recreate the tables every night which means we will have to recreate the indexes as well each night. No guarantee that it will all finish in one night.

The other customized solution that we came up with was a two step process. First we replicate the full database on Server B using Always ON method. Then we will create another database on Server B which we will replicate to using the first destination database by Transactional Replication method. I understand that this is longer method to reach the same destination but this allows us to avoid the performance impact on Server A. We are not so concerned with the performance of Server B. Does this solution sound good or are we overlooking any possible issues with this solution?

I have implemented solution 2 multiple times in the past and it has proved extremely effective.

You are right in being concerned about the performance impact of replicating tables though.  You will have to invest time in making sure replication isn't causing issues.  Things to consider would be:

Where you will host the distributor - the DML and DDL performed on the replicated tables are passed from the publisher (where the original tables are) to the distributor.  The actual commands are effectively copied into a set of special tables in a separate "distribution" database.  The distributor has to be powerful enough to deal with this load.  This could be the same instance as the publishing database, but with high volume OLTP or OLAP you may want to scale that out to another machine.
How often do you make schema changes to the tables you are wanting to replicate?  Schema changes are possible, but they can be a nightmare in a replication topology.  This will make schema changes something you will need to be much more careful with in future.
How much data is inserted/updated/deleted in the replicated tables?  Replication can keep with with very high loads, but your underlying systems will need to be able to deal with that (it is very much a system architecture question).  As replication is effectively copying the DML and DDL into a second database you can basically see it as creating double the data (and I/O etc.) compared to the same DB without replication.

There are many more things to consider, but this should give you a quick intro to what needs considering if you went with solution 2.


How can client retrieve SQL Server public SSL certificate? Can a user retrieve the connection certificate for an SQL Server instance (similar to how someone can retrieve an HTTP certificate)? If so, how?
Context
If encryption is requested by the SQL Server client, the server certificate must be validated before allowing an encrypted connection. Development servers are often configured with self-signed certificates, which won't validate under default client setup.
To use a self-signed certificate to encrypt a connection, either:

the certificate must be added to the local store
the client must set "TrustServerCertificate=true"
the client must not request encryption, and the server must set "ForceEncryption" to "yes".

The latter two options are open to MitM attacks every time a connection is made. For the 2nd, some clients don't save the connection setting, so it must be re-set for each new connection. Thus the most desirable solution is to add the certificate to the local store (it takes a little more work initially, but after that is easiest & safest option). For users in a domain, the certificate is easy enough to distribute, but otherwise the certificate will need to be installed manually on each computer. An admin could provide the certificate to each user, either by sending directly or making it available as a network resource, but sometimes the admin may be too busy or unresponsive for other reasons to provide the certificate.
In such a case, it would be useful if the user could get the certificate from the SQL server directly, just as a certificate can be retrieved from an HTTP server using a browser or openssl s_client. This would still be vulnerable to a MitM attack when the certificate was retrieved, but provides a much narrower window than trusting the certificate every time there's a new connection.

Technically it is possible, both TDS and TLS/SSL are documented. But you are asking the wrong question.

There is no point in willy-nilly manually installing a self-signed certificate everywhere, it buys you nothing. You will only install the MitM's cert everywhere...

Focus on deploying a properly configured PKI infrastructure. Either use a trusted certificate (ie. Verisign et all), or install a PKI trust in your network and use a infrastructure signed certificate.

See this series: Designing and Implementing a PKI


PostgreSQL - is it possible to use a plpgsql function in a bash script? Target : What i'm trying to achieve is to restore a partial backup of a database (only certain rows that satisfied certain conditions have been backed up).

Problem : Since between the partial-backup and the restore may pass some time, and certain records could be deleted, I need to perform an Upsert(Update if record exist, Insert if record doesn't exists). This operation in Mysql would have be done with REPLACE, but in PostgreSQL it doesn't exist. After some research I've found a good substitute of it(correct me if i'm wrong) in this function :

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
        RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

The problem with this is that I need to make dynamic the input parameters of the function, and also the UPDATE and the INSERT statements, in order to adapt it for every record and every table i'm going to upsert.

Question :

is it possible to integrate plpgsql functions in bash scripts in order to makes them more dynamic?

As you were hinted in comments above, your RBAR approach might be very inefficient.  Consider the suggestions there.

Also, I am not going into details about the different approaches of UPSERT, as it is a very broad topic, especially when one wants to do concurrency-safe.  PostgreSQL 9.5 helps a lot in this regard.

So, to your actual question: it is not directly possible writing PL/pgSQL 'commands' interspersed with bash, but the task is definitely solvable.

For this, you need to define a function like this (left out the complexity to show my point more concisely):

CREATE OR REPLACE FUNCTION dynamic_upsert(
    table_to_upsert name,
    key_column name,
    value_column name,
    key integer,
    value text
)
RETURNS void
LANGUAGE plpgsql
AS $BODY$
BEGIN
    ...
    EXECUTE $$ UPDATE $$ || quote_ident(table_to_upsert) ||
            $$ SET $$ || quote_ident(value_column) || $$ = $2
               WHERE $$ || quote_ident(key_column) || $$  = $1 $$
        USING key, value;
    ...
END;
$BODY$;

Please note that this is fully dynamic in that you can choose any table, match against any column of it and modify any (other) column.  The type of the key (integer) and the value (text) are fixed, though.

Now you can call this from a shell script like

psql -h $DBHOST -U $DBUSER -d $DBNAME \
     -c "SELECT dynamic_upsert($TABLE, $KEY_COL, $VAL_COL, $key, $value)"

If you are still RBARing, this will produce an additional overhead of connecting to the database for every single change, so be patient (or set up a connection pool).

Notes:

using concatenation, one has to be absolutely sure to quote the object names properly.  This is here done using quote_ident(), which is the way to go if you are concatenating the dynamic statement.  In newer versions (from 9.1), one would use the format() function to avoid the mess:

EXECUTE format($$ UPDATE %I SET %I = $2 WHERE %I = $1$$,
               table_to_upsert, value_column, key_column)
USING key, value;

here I am using dollar quoting when building the dynamic query.  This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example).  This way most editors will highlight the statements nicely.


Most restricting permissions for querying view with linked server I've created view to query another server via linked server(SQL Server 2014).

I need to grant permissions to developers to use this view.
Granting only select is not enough and they got the following error:

  Access to the remote server is denied because no login-mapping exists.

What is the most restricted permission that I can grant them to be able to select from this view?

We use a SQL login to connect to linked server (be made using this security context), and I can query this view with my user. I can also query this view with my Active Directory user. I didn't try to connect to access the linked server with linked server login, but my AD user couldn't connect to linked server, if there were any issues with connecting login.

For testing purposes, I granted sysadmin to this user. It could query this view, so it is definitely a permissions issue. Prior to granting sysadmin, the user had only public, with no special permissions at the server level and only db_reader in the view's database.

The message you are getting is telling you that there is no mapping between the credentials you have supplied and credentials on the target machine. You need to ensure that the credentials you use to setup your linked server also has a login or can be mapped to a login on the linked server.

I suspect that when you set it up you specified the option Be made using the login's current security context which worked for you as you have a login on the remote machine - but it doesn't work for anyone else because they do not have sufficient credentials to query the linked server. (Just a hunch)

Here is a guide on how to set up a linked server and what all of the options are for, I suspect you may need to change the security context you are using to connect to the linked server or add the other accounts to it.

https://msdn.microsoft.com/en-us/library/ff772782.aspx


Query taking more time to execute due to lengthy column size When I am trying to execute below query in SSMS, the query is taking more time (more than 10 minutes) to execute due to lengthy column size (Column Name: Keywords, Column Size: nvarchar(4000)). Employee table is containing 6000 records.

Select EmployeeId, EmployeeName, Designation, Keywords
From Employees
Where StatusFlag = 'L'

I have created below non clustered covering index for the above table.

Create NonClustered Index NCI_Employees_StatusFlag On Employees(StatusFlag) Include (EmployeeId, EmployeeName, Designation, Keywords)

How to retrieve the data from the above table in a faster manner?

Even if all 6,000 rows are packed full of 4,000 Unicode characters (8,000 bytes), that's only 45 MB of data.  There's just no way it should take 10 minutes to read that much, even with a cold cache.

You're likely getting blocked by something, as Randi Vertongen mentioned in the comments.  The answer here isn't going to be indexing.  Run sp_WhoIsActive while your query is executing to see what's going on.  That should show you:

what the query is waiting on
if it's blocked (and what's blocking it)


How to reduce network latency during mirroring? We are using SQL Server 2008 R2 Standard edition for mirroring.

But we found out that network latency is too high. It takes nearly four times time to complete a transaction while mirroring is active. Is it possible to connect these two servers with a direct crossover cable and force sql server to use this link for mirroring?  Will that help?

We have already setup a crossover cable between these two servers. But how do we configure sql server to use this link for mirroring?

Any help will be appreciated.

For a weak network I wouldn't personally use Mirroring. But if you really want mirroring for the specific case when there might be a slow connection an option would be to use the Asynchronous mode (or High Performance mode). This would imply some data loss would be possible, but you will be able to finish transactions faster.

Details on MSDN: Async mirroring. Some details about it:

  This mode enhances performance at the expense of high availability.
  High-performance mode uses just the principal server and the mirror
  server. Problems on the mirror server never impact the principal
  server. On the loss of the principal server, the mirror database is
  marked DISCONNECTED but is available as a warm standby.

Now this comes with a caveat, the transaction on the Principal is just put in queue, it's not committed immediately on the Mirror, so in case of problems with the Principal, you may have data loss. But at least you won't wait for the transaction to finish on both servers. Check if it's an option.

Regarding specifying different NICs for the connections, please see these questions:

Using SQL Server on separate networks and
Running different instances of SQL Server from different networks on one box.

In theory it should be simple enough as to configure both NICs with different IP and use the IP to connect to the SQL Server (not the server name). Also I'd think @Nathan's answer might lead you to a fix. No idea if it can be done with the existing mirroring, my guess is that you need to redo it.

The first sentence is more important if it's an unreliable network. In that case I'd totally advice to use a different technology for HA (log shipping or maybe replication).

PS: Following @Nathan's mention about reading the real requirements (ty, @NathanJolly), then your option remains to configure the NICs so you can connect via the direct link cable. Follow the other answers and questions and see if it's good enough for your environment. If it's still too slow, then I'd suggest you move over to log shipping.


How to add foreign key "with more than one column" in SQL Power Architect In the following picture, I have a model that I'm building in SQL Power Architect:

I already have table 1 referencing a column (CDCONTR) from table 2. But I want to make the foreign key to reference two columns from table 2, I mean, both the column CDCONTR and CPF must come from table 2. How can I do this?

I'm using SQL Power Architect 1.0.7. About my RDBMS, I'm just using this power arch.

If the CPF value in TABASSASSINATES should always be the value from the related CONTRATOA table, it is redundant data.  Foreign keys won't enforce the relationship.

In other cases, the column will have applicable business rules, and you may not want to force the value in TABASSINATES to change when the CONTERATOA value is updated. The business rules won't appear in the model you have extracted above.

If it is possible to have multiple CPF values in CONTERATOA for the same CDCONTR value, the primary key should include CPF.  In this case, the foreign key relationship will include both columns.

It is possible, but highly discouraged, to model the CONTERTOA primary key with both columns and add a unique contraint on the CDCONTR column.


Regular Expression Difficulty I'm trying to extract 456 from the string :123:456: as follows:

select regexp_substr(':123:456:', ':(\d+):', 1, 2, 'i', 1) from dual

However, this query returns null. What am I doing wrong?

This works fine:

select regexp_substr(':123:456:', '(\d+):', 1, 2, 'i', 1) from dual;

I think yours fails because the opening and closing colons won't get matched by both occurrences (because the first match is greedy).


Requirement of SQL Server 2008 The software I am installing requires SQL Server 2008. I have SQL Server 2008 R2 Express Version. I am having problem in the setup and finally I am arriving on the conclusion that my SQL Express can be the problem. However I am highly skeptical.

I have asked the vendors and they we cannot confirm if their software works with the Express version.

Have you ever run into a problem where a product will work with full version (of Database) and not a free version?

Here is the Limitation of SQL Server 2008 R2

The following are the limitations of SQL Server Express:

4 GB max database size (10 GB for 2008 R2)
Single CPU socket
1 GB of RAM
No SQL Server Agent service

When you say you are having a "problem in setup", what is the problem?  The only thing in the above limitations I can think would error out is if you tried to restore a database that is greater than 4 GB (or 10 GB for R2), or if the setup application is trying to do something with SQL Server Agent.

Conversely, the setup application could potentially be just doing a basic check on the edition of SQL Server (through @@version) and then bombing out when it finds Express.  Hard telling not knowing your specific issue.


Reduce pre-allocated disk space We have a reporting SQL (2014 R2 Enterprise) system with a Reporting and Staging DB on it. The reporting DB is pre-provisioned at 4 TB across 8 data files. Currently it contains just shy of 1 TB of data and growth is marginally slow.

We need to reclaim 2 TB of that pre-allocated space on the SAN...

Since the data files are pre-grown, I assume I need to just change the file initial size and then perform a shrinkdb operation. I know the concerns and issues involved in a shrinkdb, and I just reviewed the article written by @BrentOzar (https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/) about it.

Given the pre-allocated file size versus the actual usage, should I be less worried about the fragmentation consequences?

On a side note: I love the tag descriptor when entering 'Shrink' as a tag...

In the end, as some have suggested, our only option was to incrementally shrink files w/out truncate only. The cost/benefit didn't really work out, so we are living with what we have.

Thank you all for the help and suggestions.


Automatically keep synchronized copy of database I want to create a working copy of my main database to hold it on a separate server to run queries and report generation on to take the load of the main server.

I'm looking into SQL Server Synchronization, but I'm not sure about it. If I use synchronization, does the slave database need to remain identical to the first? That is to say, I want to keep some new denormalized tables in the slave database for some frequently run, otherwise intensive queries and I'm worried these tables would disappear after every synchronization or would otherwise interfere with the process?

I only need the slave database to be updated once a day, none of the data I query off it needs to be up-to-the-hour.

Is SQL Synchronization the way to go or should I be looking at another alternative?

From everything you describe, I would think you would be better served by using Replication rather than Synchronization.

Replication allows you to have additional objects on the subscriber database that are not affected by the replication.

Your scenario fits a classic snapshot replication topology. I use that for reporting all time. Once it is setup it is fairly painless to replicate at whatever frequency suits you.

Here is a good article to cover setting it up: http://www.sqlservercentral.com/articles/Replication/snapshotreplication/819/


pg_restore warning column X has type unknown I'm doing a dump from PostgreSQL 9.1 and restoring on PostgreSQL 9.4 and getting this warning

pg_restore: WARNING:  column "foobar" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.

But after the restore I can verify the column "foobar" type is defined and correct.

What could be causing this warning?

CREATE VIEW or CREATE TABLE... AS SELECT have to infer the column types for the relation to create. Sometimes the context is not sufficient to guess a datatype, for example when it's just a string literal. In this case it's created as unknown.

Example :

test=> CREATE VIEW testview AS SELECT  'bla' AS foobar;
WARNING:  column "foobar" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW

Result:

test=> \d testview
    View "public.testview"
 Column |  Type   | Modifiers
--------+---------+-----------
 foobar | unknown |

If this view was pg_dumped and pg_restored, the column would be created as unknown with again the warning mentioned:

  pg_restore: WARNING:  column "foobar" has type "unknown"
  DETAIL: Proceeding with relation creation anyway.

The table/view should be created to begin with with explicit casts to columns when needed, as in

CREATE VIEW testview AS SELECT  'bla'::text AS foobar;

which we can assume is the point of that warning in the first place.


Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters I'm working on a tabular cube in SSAS 2012 SP1 CU4. I have 3 dimensions (Requisition, Requisition Status, Date) and 1 fact (Requisition Counts).  My fact table is at the grain of requisitionKEY, RequisitionStatusKEY, SnapshotDateKey.

I have calculated measures that essentially get the lastnonempty value (like a semi-additive measure) for the given period whether it is Year, Month, Or Date:

Openings:=CALCULATE(Sum('Requisition Counts'[NumberOfOpeningsQT]),
Filter('Date','Date'[DateKey] = Max('Requisition Counts'[SnapshotDateKEY])))

This works well until you throw Requisition Status into the mix.  I have rows for each requisition for every day in 2013.  For one of the requisitions, the Requisition Status was Pending for the first 6 months and then it changed to Approved on all data from July 1 to date.  When I summarize the number of openings for the requisition at the month level for July, users see two rows: the sum of the openings for the last populated day in July that it was pending and the sum of the openings for the last populated day in July that it was approved.

Although the total of 2 is correct, I need to change this calculation so that I only get the most current requisition status for the date period selected (Approved) and either show 0 or null or blank for the Pending approval line in the pivot table.

The Requisition Status table looks like this:

Update: Here is a link to a PowerPivot model I made that has some scrubbed data in it to help answer this question. This should better explain the table schemas. The NumberOfOpeningsQT field is basically the number of open positions they have for that job.  Sometimes it is 1, sometimes is is more than 1.  It doesn't usually change over time, but I guess it could. I'm trying to make the Openings calculation give me the desired answer. I threw some other calculations in there so show some things I had tried that had promise but that I couldn't get to work.

You can try the following calculation:

Openings:=
CALCULATE( SUM( 'Requisition Counts'[NumberofOpeningsQT] )
         , CALCULATETABLE( LASTNONBLANK( 'Requisition Counts'[SnapshotDateKEY] , 1 )
                         , ALL( 'Requisition Status' )
                         )
         )

The important part is in setting the correct filter context for the outer CALCULATE.

With CALCULATETABLE, we define the specific rows which we want the outer CALCULATE to consider: the last non-blank snapshot date for all requisitions (or for a specific requisition, considering the context of each requisition on a pivot table), regardless of any filters in the requisition status, which is achieved using the ALL function.

The measure doesn't work exactly as you want; you say that you would expect the total for Approved to be 2. Using the 2013-11059 requisition from the Power Pivot model as an example, since there is 1 Approved status in 2013-07-01 and another in 2013-07-02, it just considers the last status for the total, resulting in 1.


TSQL - SQL Server 2008 Maintenance Plan - anything else? I have a number of SQL 2008 R2 DBs that haven't had any maintenance plans run on them since birth, as my ISP's server provides a SQL version that doesn't have the Maintenance Plan option.

I've trawled through the numerous pointers, options, etc, but I just want to run something clean and simple on them. Having trawled, I've come up with the following simple TSQL script:

USE dbname

ALTER INDEX ALL ON tablename1 REBUILD WITH (FILLFACTOR = 80);
ALTER INDEX ALL ON tablename2 REBUILD WITH (FILLFACTOR = 80);
etc etc for each tablename in a db

EXEC sp_updatestats;

That's it. None of the SQL DBs are bigger than 300MB each at the moment (probably a lot of bloatware in there already) and each one is backed up by another process so have I covered the bases? Is there anything I've missed?

UPDATE: on this topic I've run the above and for some reason the frag % changes on the tables each time I do it???  It goes up and down erratically per table/index - is this normal???

Regards

Tom

First have a look on this brilliant solution by Ola Hallengren to build your own maintenance plan. It has been well recognised and trusted by many SQL Server pros.

If you are just after "one off" on dev/demo solution or want to get some insight please try the below. You will be interested in "Hint" column on this occasion:

DECLARE
@Minimal_Page_Count int = 1000,
@Max_allowed_Fragmentation decimal(5,2) = 5,
@Hours_Since_Last_Stats_Update int = 24
---------------------------------------------------------------------------------------------------

DECLARE @WorkingHours float = (SELECT DATEDIFF(mi, [sqlserver_start_time], SYSDATETIME()) FROM sys.dm_os_sys_info)/60.0

;WITH [index_usage_stats] AS
(
SELECT
[object_id],
[index_id],
[Reads] = [user_seeks]  + [user_scans] + [user_lookups] + [user_updates],
[Writes] = [user_updates],
[Last_Read] = [last_user_seek],
[Last_Write] = [last_user_update]
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID()
UNION ALL
SELECT [object_id], [index_id], NULL, NULL, [last_user_scan], NULL
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID()
UNION ALL
SELECT [object_id], [index_id], NULL, NULL, [last_user_lookup], NULL
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID()
), [ius] AS
(
SELECT
[object_id],
[index_id],
[Reads] = MAX([Reads]),
[Writes] = MAX([Writes]),
[Last_Read] =MAX([Last_Read]),
[Last_Write] = MAX([Last_Write])
FROM [index_usage_stats]
GROUP BY [object_id], [index_id]
), [Table_Stats] AS
(
SELECT
i.[object_id],
[Table_Row_QTY] = SUM(CASE WHEN i.[index_id] = 1 THEN p.[rows] ELSE 0 END),
[Total_Table_Size_(MB)] = CASE WHEN SUM(a.total_pages) > 0 THEN CAST(SUM(a.total_pages) / 128.0 as decimal(18,1)) ELSE 0 END,
[Not_Used_Table_Size_(MB)] = CAST(CASE WHEN SUM(a.total_pages) > 0 THEN SUM(a.total_pages) / 128.0 ELSE 0 END - CASE WHEN SUM(a.used_pages) > 0 THEN SUM(a.used_pages) / 128.0 ELSE 0 END as decimal(18,1))
FROM sys.indexes            i
JOIN sys.partitions         p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN sys.allocation_units   a ON p.[partition_id] = a.[container_id]
GROUP BY i.[object_id]
), [ALL] AS
(
SELECT
[DB_Name]   = db_name(),
[Schema] = OBJECT_SCHEMA_NAME(ips.[object_id]),
[Object_Name] = object_name(ips.[object_id]),
[Index_Name] = ix.[name],
[Allocation_Unit_Type] = ips.[alloc_unit_type_desc],
[Index_Type] = ips.[index_type_desc],
[Index_Size_(MB)] = CASE WHEN ips.[page_count]>0 THEN CAST(ips.[page_count] / 128.0 as decimal(18,1)) ELSE 0 END,
ts.[Total_Table_Size_(MB)],
ts.[Not_Used_Table_Size_(MB)],
ts.[Table_Row_QTY],
[AVG_Fragmentation(%)] = CAST(ips.[avg_fragmentation_in_percent] as decimal(5,2)),
[Is_Unique] = CASE ix.[is_unique] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Is_PK] = CASE ix.[is_primary_key] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Is_UC] = CASE ix.[is_unique_constraint] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Has_Filter] = CASE ix.[has_filter] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Is_Disabled] = CASE ix.[is_disabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Fill_Factor] = ix.[fill_factor],
[ius].[Reads],
[AVG_Reads_per_hour] = CAST([ius].[Reads] / @WorkingHours as decimal(16,1)),
[ius].[Last_Read],
[ius].[Writes],
[AVG_Writes_per_hour] = CAST([ius].[Writes] / @WorkingHours as decimal(16,1)),
[ius].[Last_Write],
[Reads_vs_Writes] = CASE WHEN [ius].[Reads] > 0 AND [ius].[Writes] > 0 THEN CAST(CAST([ius].[Reads] as decimal(18,5)) / ([ius].[Reads] + [ius].[Writes]) as decimal(6,3))END,
[Last_Stats_Update] = CAST(STATS_DATE(ips.[object_id], ips.[index_id]) as smalldatetime)
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) ips
JOIN sys.indexes ix ON  ips.[object_id] = ix.[object_id]
                    AND ips.[index_id]  = ix.[index_id]
LEFT JOIN [ius] ON  [ius].[object_id]   = ips.[object_id]
                    AND [ius].[index_id]    = ips.[index_id]
LEFT JOIN   [Table_Stats] ts ON ts.[object_id] = ips.[object_id]
WHERE   ips.[index_id] > 0
AND    ips.[page_count] >= @Minimal_Page_Count
)
SELECT *,
[Hint] = CASE WHEN [AVG_Fragmentation(%)] >= @Max_allowed_Fragmentation THEN N'ALTER INDEX [' + [Index_Name] + N'] ON [' + [Object_Name] + N'] REBUILD WITH (FILLFACTOR = ' + CAST((CASE [Fill_Factor] WHEN 0 THEN 100 ELSE [Fill_Factor]END) as varchar(3)) + N');' ELSE N'' END + nchar(13) + CASE WHEN DATEDIFF(hh,[Last_Stats_Update], SYSDATETIME()) >= @Hours_Since_Last_Stats_Update THEN N'UPDATE STATISTICS [' + [Object_Name] + N'] [' + [Index_Name] + N'] WITH FULLSCAN;' ELSE N'' END
FROM [ALL]
ORDER BY [AVG_Reads_per_hour] DESC


Backup Only Database Schema Everynight I have an instance with 15 databases on SQL Server 2012. I will set up to backup system which will work every night. I want to backup only the schema because several databases are very big. I haven't found any solution for this problem.

How can I backup only the schema with a job?

You can use a 3rd party tool like Red-Gate SQL Compare. It has a command-line interface that you can easily invoke using cmd or PowerShell (and you can do these from a SQL Server job, but you don't necessarily need to saddle SQL Server Agent with this task - you can just as easily do it using Windows scheduled tasks or a PowerShell job).

You can use your script to tell the tool to compare your source database with an intentionally empty database every night and either:

save the script somewhere, or
create a new database, apply the script, backup the new database and drop it.

(Depending on whether you want a script of the schema or an actual empty database that looks like your real database.)

That isn't the only tool to use, of course, but it's the one I have the most experience with. I know that it works and I know they invested in the command-line tool for exactly this purpose. You can check out some other potential offerings here:

Re-Blog : The cost of reinventing the wheel

You may also see if tools like Visual Studio SSDT (which replaces Visual Studio Team Edition for Database Professionals) does this - you may already have a workable solution in house. I haven't done much with SSDT yet so can't be sure its schema comparison features are scriptable.


Obtaining the next partition number For a partitioned table, how do I determine via T-SQL what is the next partition number that will be used?  For example, the switch-in today went into partition number 50.  I want to build the new switch-in table to contain the boundary values for partition number 51. I'm currently doing this manually and want to automate it via stored procedures. I'm using SQL Server 2012.

There are a few good DMVs (system views) where you can find this info: sys.partition_functions and sys.partition_schemes can be joined to sys.partition_range_values to get all existing ranges. The number of ranges should be the number of partitions.

The data_space_id of the partition scheme can be joined to that of the index or heap in sys.indexes on the table (sys.tables) that you're interested in.


List of all database without a specific index I am working on a Multi Tenant System. In which we have one Manager DB and Each client has a Company DB.

Now all of these database have the same Table Schema.

But some of them have a missing Index on a certain Table.

Is there a way to get a list of Databases with that missing index using SQL? We are using SQL-Server 2012

EDIT:

Let's say Company_1 has an index on TableAcalled IX_Missing_Index - but Company_2 and Company_3 DO NOT have the index 'IX_Missing_Index' - So all I need in the result set is Company_2 and Company_3

e.g. like this

    Database Without index IX_Missing_Index
    ----------------------------------------------
    Company_2
    Company_3

CREATE TABLE #dbs(db sysname);

DECLARE @cmd nvarchar(max) = N'INSERT #dbs SELECT ''?''
  WHERE EXISTS (SELECT 1 FROM ?.sys.tables WHERE name = N''TableA'')
  AND NOT EXISTS (SELECT 1 FROM ?.sys.indexes AS i
    INNER JOIN ?.sys.tables AS t
    ON i.[object_id] = t.[object_id]
    WHERE t.name = N''TableA''
      AND i.name = N''IX_Missing_Index'');';

EXEC master.dbo.sp_foreachdb @command = @cmd, @user_only = 1;

SELECT db FROM #dbs;

DROP TABLE #dbs;

You can get sp_foreachdb here (it is also being open sourced and added to Brent Ozar Unlimited's SQL Server First Responder Kit).


Schema for storing historical data on shared identifiers When user account is assigned an IP address, I create a table entry for the account/IP mapping. This data in the table is updated every 5 minutes. Right now I'm only keeping real-time data but I need to figure out how to store this historically.

So let's say at 09:00:00 I have the following:

10.10.100.20   dom\jsmith
10.20.5.30     dom\psanders
10.90.220.5    dom\kwest

This all stays the same for the next 45 minutes and then at 09:50:00

10.10.100.30   dom\jsmith
10.10.100.20   dom\jsmith
10.20.5.30     dom\bpeters
10.90.220.5    dom\sparker

In this case jsmith logged into a term server so his account maps to a second IP. The more difficult problem for me is bpeters shares a desk with psanders and the same for kwest and sparker.

Goals:

Recall who was logged in on 8/10 at 9:00.
Recall what IPs did jsmith have between two dates/time periods
Recall who was assigned a given IP on a given date

I thought about creating a table for each time period but over a long period of time that's a lot of tables and probably really inefficient. The number of IPs to track is probably 20k and individual accounts 15k.

As a novice to DB engineering I don't know if I'm missing a simple solution here or if I should invest a good amount of time into DB schema design.

Looking at the data you posted, what would be the expected result from your point of view?
The fact that users share the same desk/IP shouldn't be a problem as long as both users are reported while they're logged in.
You could either use your current table design (a single table with Row_Id, update_time, IP and User) or an aggregated table with Row_Id, IP, User, login_tim, and logout_time.

I'd decide based on the expected number of unchanged logins. For example, if users usually login in the morning and logout in the afternoon, then I'd go for the aggregated version. But if the scenario is more like a user to log in and log out within a short period of time and a high frequeny, then I'd go for the current design since aggragation wouldn't reduce the number of rows significantly.

How many rows do you expect per day (given your current design)?


Getting error when trying to use OPENQUERY Msg 7321, Level 16, State 2, Line 1 When I run this query:

SELECT *
FROM OPENQUERY([XXX], 'SELECT *
                       FROM Database.Table
                       WHERE (MBCONO=650) AND MBCUNO LIKE a%' )

I get the error:

  OLE DB provider "DB2OLEDB" for linked server "XXX" returned message
  "Token %ÂŸFOR SKIP WITH FETCH ORDER UNION EXCEPT OPTIMIZE SQLSTATE:
  42601, SQLCODE: -104".

  Msg 7321, Level 16, State 2, Line 1 An error
  occurred while preparing the query "SELECT * FROM Database.Table WHERE
  (MBCONO=650) AND MBCUNO LIKE a%'" for execution against OLE DB
  provider "DB2OLEDB" for linked server "LAWSON".

I can run the same query without AND MBCUNO LIKE a% successfully.

I would suggest you change your original SQL Statement as follows:

SELECT *
FROM OPENQUERY([XXX], 'SELECT * FROM database.table where left(MBCUNO, 1) = ''a'' and (MBCONO=650) ' )

This has the two variable swopped around, and the like % exchanged for a left(mbcuno,1).

Ensure that all your table & column references are correct


could not load library "/usr/pgsql-10/lib/postgis-2.5.so": /usr/pgsql-10/lib/postgis-2.5.so: undefined symbol: GEOSFrechetDistanceDensify I have installed postgresql 10 on centos 7.4 and installed postgis2.5 few months back. The installation was successful and Iam able to use postgis. All of a sudden Iam getting the following error.

test=# create extension postgis ;
ERROR:  could not load library "/usr/pgsql-10/lib/postgis-2.5.so": /usr/pgsql-10/lib/postgis-2.5.so: undefined symbol: GEOSFrechetDistanceDensify

And ldd on postgis-2.5.so is as follows

lib]# ldd postgis-2.5.so
    linux-vdso.so.1 =>  (0x00007ffcc03ef000)
    libgeos_c.so.1 => /usr/geos36/lib64/libgeos_c.so.1 (0x00007f7a7d766000)
    libproj.so.12 => /usr/proj49/lib/libproj.so.12 (0x00007f7a7d4fd000)
    libjson-c.so.2 => /lib64/libjson-c.so.2 (0x00007f7a7d2f2000)
    libxml2.so.2 => /lib64/libxml2.so.2 (0x00007f7a7cf88000)
    libm.so.6 => /lib64/libm.so.6 (0x00007f7a7cc86000)
    libSFCGAL.so.1 => /lib64/libSFCGAL.so.1 (0x00007f7a7c1b7000)
    libc.so.6 => /lib64/libc.so.6 (0x00007f7a7bdea000)
    libgeos-3.6.3.so => /usr/geos36/lib64/libgeos-3.6.3.so (0x00007f7a7ba3c000)
    libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f7a7b735000)
    libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f7a7b51f000)
    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f7a7b303000)
    libdl.so.2 => /lib64/libdl.so.2 (0x00007f7a7b0ff000)
    libz.so.1 => /lib64/libz.so.1 (0x00007f7a7aee9000)
    liblzma.so.5 => /lib64/liblzma.so.5 (0x00007f7a7acc3000)
    /lib64/ld-linux-x86-64.so.2 (0x00007f7a7dc56000)
    libCGAL.so.11 => /usr/lib64/libCGAL.so.11 (0x00007f7a7aa9b000)
    libCGAL_Core.so.11 => /usr/lib64/libCGAL_Core.so.11 (0x00007f7a7a862000)
    libmpfr.so.4 => /usr/lib64/libmpfr.so.4 (0x00007f7a7a607000)
    libgmp.so.10 => /usr/lib64/libgmp.so.10 (0x00007f7a7a38f000)
    libboost_date_time-mt.so.1.53.0 => /usr/lib64/libboost_date_time-mt.so.1.53.0 (0x00007f7a7a17e000)
    libboost_thread-mt.so.1.53.0 => /usr/lib64/libboost_thread-mt.so.1.53.0 (0x00007f7a79f67000)
    libboost_system-mt.so.1.53.0 => /usr/lib64/libboost_system-mt.so.1.53.0 (0x00007f7a79d63000)
    libboost_serialization-mt.so.1.53.0 => /usr/lib64/libboost_serialization-mt.so.1.53.0 (0x00007f7a79af7000)
    librt.so.1 => /usr/lib64/librt.so.1 (0x00007f7a798ef000)

The function GEOSFrechetDistanceDensify seems to be added in version 3.7 of Geos as in link below & I have geos 3.6. Is this the reason for this error?.
https://trac.osgeo.org/geos/browser/git/NEWS?rev=3.7.0

Please help me to get the issue resolved.

Had the same problem on CentOS after an update. I had 3.6 AND 3.7 installed.

$yum list installed | grep geos
geos36.x86_64                           3.6.3-1.rhel7                  @pgdg10
geos37.x86_64                           3.7.0-1.rhel7                  @pgdg10

I had to remove the 3.6:

$yum remove geos36

And restart PostgreSQL

$systemctl restart postgresql-10


Efficient SQL query to return parents that satisfy constraints on children I've got a schema something like having an Entity table, with a to-many relationship to Attribute, which has a to-many relationship to Value. Attribute has a column Name, and Value has a column Value:

Entity
------
Id
Name

Attribute
---------
Id
EntityId
Name

Value
-----
Id
AttributeId
Value

I need to be able to construct a query that will return only Entity rows that have certain combinations of attribute/value children. Currently I'm writing the query something like:

select
    *
    from Entity e
    where exists(
        select * from Attribute a
            join Value v on v.AttributeId = a.id
            where a.EntityId = e.id and a.Name = 'color' and v.Value = 'red')
    and exists(
        select * from Attribute a
            join Value v on v.AttributeId = a.id
            where a.EntityId = e.id and a.Name = 'size' and v.Value = 'small')

This works, but every extra attribute filter adds another exists clause, and I'm nervous about performance. Is there a more efficient way to write queries of this type, or is it likely to be perfectly performant if I use correct indexes etc?

You need something like that:

SELECT e.Id, count(1) AS cnt
  FROM Entity    AS e
  JOIN Attribute AS a ON a.EntityID = e.Id
  JOIN Value     AS v ON v.AttributeId = a.Id
 WHERE (a.Name = 'color' AND v.Value = 'red')
    OR (a.Name = 'size'  AND v.Value = 'small')
--  OR ( ... ) -- extended here
 GROUP BY e.Id
HAVING cnt = 2 -- exact number of desired attributes
;


Merge replication issue : conflicts with new data I set up merge replication with SQL Server 2008, I am having issues when creating new data on clients and trying to synchronize with the remote server.

Pulling data from the server works fine. But when adding new data from the clients and synchronizing, I get conflicts for the publication updates with error in conflict manager:

  Upload inserts failed.

  This failure can be caused by a constraint violation. Invalid object name 'dbo.MSmerge_repl_view_CCEBB5FB95354A40940D433C94AAAF3E_FF89BB83CC734065B74175EFC3B6E922'.

I found that dbo.MSmerge_repl_view_... is a system view but I am guessing it is from the client since I cannot find it on the server.

I don't get why this is happening. Is the data being uploaded to the view (which doesn't exist on the server) ? if someone can explain why this might happen it would be great !

That view is created by replication and used internally. If it is missing, either it got deleted accidentally, or sql servers replication metadata got corrupted.

Depending on how big you databases are and if you can afford some downtime, the easiest way to fix this is to drop and recreate the merge replication setup.


MySQL - Get counts of `car models` by `car make` I have a table with cars data, i.e. Makes, Models, & Year.

I want to get the count for each Car with how many models it has... something like the image below:

This is the SQL query I am using but it is giving me wrong counts. In fact, the number of rows against each car make is actually giving me my desired results.
SELECT `make`, COUNT(`id`) as `countmake`
FROM `cars_new`
WHERE 1
AND `make` IN ('Chrysler', 'Ferrari', 'Citroen', 'Dodge')
GROUP BY `model`

Help me to fix this!

You have to GROUP BY both make and model, to get the count
SELECT `make`,`model`, COUNT(*) as `countmakemodel`
FROM `cars_new`
WHERE `make` IN ('Chrysler', 'Ferrari', 'Citroen', 'Dodge')
GROUP BY `make`,`model`


How to check how the table structure effects the performance of my query I want to check how the table structure of a certain table effects the performance of a delete query.

This is not a generalised question, I have a table, with currently 2.2 million records approximately, i want to run delete query, I want to see how InnoDB stores data, in my disk, so i can change the structure of the table.

Is there any tool or command to check the structure of the table.

CREATE TABLE `chat_server` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `host` varchar(100) NOT NULL,
  `is_active` int(10) unsigned NOT NULL,
  `number_of_clans` int(10) unsigned NOT NULL,
  `weight` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELETE QUERY

DELETE c FROM `chat_history` c inner join ( select message_id from `chat_history` where     clan_id=1005 ORDER BY message_id DESC limit 30, 30000  ) x where c.`message_id` <= x.`message_id` AND c.clan_id = 1005

thanks in advance.

Procedure Analyse will be helpful command: http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

SELECT *
FROM `table_name`
PROCEDURE ANALYSE ( )


How to shrink over sized logs in Database Mirroring? I've searched the forums here at dba.se and other places on the Net and haven't found a specific answer.  I know this question looks similar to many, but the ones that I've found only address how to shrink a log file, which I already know how to perform.

I happen to be in one of those unique positions that a log shrink is necessary.

My specific question is:

When I perform a log shrink on the primary in a mirrored set, do I then failover and perform a log shrink on the "new" primary?

Or does this somehow replicate the changes to the secondary, making the manually failover unnecessary?

Just do the shrink on the primary:

Can I shrink the transaction log file on a mirror database?

No need to fail over and shrink the secondary.


Mariadb 10.1 / Galera - bootstrap node always doesn't lose quorum I'm using mariadb10.1.37, wsrep_provider_version 25.3.24(r3825). On my dev cluster (2node+1arbitrator) I found the following behaviour:

node1: bootstrap new cluster (using galera_new_cluster, with pc.weight=3)
node2: join cluster (pc.weight=3)
arbitrator: join cluster (pc.weight =1)

So, I have a three node cluster, with total pc.weight of 7. Then I do:

arbitrator: shutdown  garbd
node2: shutdown mysqld

At this point, I expect node1, being only node out of three still alive, should have lost wsrep_cluster_status=Primary status, and no longer accept writes.

Instead, I find wsrep_cluster_status = Primary, wsrep_cluster_size=1, and yet node1 will still accept writes.

Is there something I am missing?

Thanks in advance!

I don't think this is a bug. Its an expected behaviour interms of normal shutdown scenario. If you are practicing split-brain testcases then it makes sense for it to loose its Primary status.

In your case the clear information about their neighbouring voters exit status is communicated. And thus software lets it to serve the requests to the node1 that is alive, since the status of other nodes are shutdown and that is communicated to node1.

Try to execute Split-Brain scenario by isolating the node1 from node2+arbitor link and then you should find node1 losing its primary status and node2 wins while arbiter voting by its side.


How to search entire database to find tables that contain 2 columns? This database query doesn't work:
select table_schema,
table_name,
column_name,
column_name2
from information_schema.columns
where column_name = 'user_id' and column_name2 = 'organisation_id'
and table_schema = 'brand_production';

I would like to search for each table in the database to see if it contains "user_id" and "organisation_id"?

To include views:
SELECT TABLE_NAME, 'user_id' 'oganization_id'
    FROM
    INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME IN ('user_id', 'oganization_id') AND TABLE_CATALOG = '<your database name>'
    GROUP BY TABLE_NAME
    HAVING COUNT(COLUMN_NAME) =2

To only include tables:
SELECT c.TABLE_NAME, 'user_id', 'oganization_id'
    FROM
    INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t on t.TABLE_NAME = c.TABLE_NAME
    where c.COLUMN_NAME IN ('user_id', 'organization_id') AND t.TABLE_TYPE = 'BASE TABLE' AND c.TABLE_CATALOG = '<your database name>'
    GROUP BY c.TABLE_NAME
    HAVING COUNT(COLUMN_NAME) =2


Slow MySQL Database Import with mysqldump and USE .. SOURCE I have about 12 tables in MySQL innoDB, one of them has 11 million records in it.

I used this command to back things up:

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

And this command to import things on the new server:

USE [DBNAME];
SOURCE [/path_to_file/DBNAME].sql;

Here is the pain I am afflicted with (the time is going up with each query!):

What can I do to speed things up? Is something wrong with my mysqldump command?

For your dump command, you could use the following

mysqldump --single-transaction --extended-insert -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

Extended inserts are better.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction
The Single Transaction option is better for dumping InnoDB tables.

In your my.cnf file, make the following changes temporarily

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_doublewrite

  innodb_doublewrite=0

and also

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

  innodb_flush_log_at_trx_commit = 2

Restart MySQL and then remove these options after your import and restart MySQL again.

Memory Tables

If you're entire database could fit into memory, you could change the ENGINE=InnoDB to ENGINE=MEMORY in your dump sql file for every table or just the tables that are taking the longest to import, import the dump file back into your database and then change the engine back to innodb.

ALTER TABLE name_of_table ENGINE=InnoDB;

mysqlimport command

http://linux.die.net/man/1/mysqlimport

I have used mysqlimport to import data backed up using the tab format option in mysqldump. It has proven faster than importing via the mysql command.

Dump the database using mysqldump

mysqldump --tab=/some_directory/ -u [USERNAME] -p [DBNAME] [TABLENAME]

Import the data into your database.

mysqlimport -u [USERNAME] -p [DBNAME] /some_directory/tablename.sql


What database front end do you use? What database front end do you use?
It should let you manage databases, tables, indexes and ideally even data records.
And it should ideally be free so we can all use it too.

Databases that it could work with:

MySQL
MS SQL
Oracle
SQLite

One answer per frontend, to let voting do its trick.

I use HeidiSQL, its free and open source.

Only for MySQL, and only on Windows.

Its fast, and supports multiple servers, multiple databases, and lets you edit:

Users
Tables, columns, indexes
Views, procedures, triggers, events
Data

It also has good data management features:

Filtering, searching
Export to SQL
Export/import XLS

And it looks nice!


Is it possible to only display the largest calculated table column? Below is a code to sum all rows in each column where the value of each column c(x) equals 1. It gives me six columns containing each calculated sum. Is it possible to only display the largest calculated column (c4 as given by the image below) in a neat way?

SELECT DISTINCT
       (SELECT SUM(`vote_candidate_1`)
          FROM `votes`
         WHERE `vote_candidate_1` = 1) c1
     , (SELECT SUM(`vote_candidate_2`)
          FROM `votes`
         WHERE `vote_candidate_2` = 1) c2
     , (SELECT SUM(`vote_candidate_3`)
          FROM `votes`
         WHERE `vote_candidate_3` = 1) c3
     , (SELECT SUM(`vote_candidate_4`)
          FROM `votes`
         WHERE `vote_candidate_4` = 1) c4
     , (SELECT SUM(`vote_candidate_5`)
          FROM `votes`
         WHERE `vote_candidate_5` = 1) c5
     , (SELECT SUM(`vote_candidate_6`)
          FROM `votes`
         WHERE `vote_candidate_6` = 1) c6
  FROM `votes`;

Atleast for 8.0 there's the GREATEST function. I would also suggest that you do one scan against the table instead of six:

SELECT GREATEST(SUM(CASE WHEN vote_candidate_1 = 1
                         THEN vote_candidate_1 END)
              , SUM(CASE WHEN vote_candidate_2 = 1
                         THEN vote_candidate_2 END)
              , SUM(...)
               ) as ...
FROM votes;


Multiple rows vs Single row with JSON What is the most optimal way to do this?

In TV Show page I need to get all progress from all episodes in that TV Show
In Homepage I need to get the continue_watching
In Episode I need to get progress of the episode.
Some TV Shows have more than 100 episodes

Ex:

USER_ID | SHOW_ID | LAST_SEEN_ID | LAST_SEEN_TIME
10      | 5       | 12           | ---

USER_ID | EPISODE_ID | PROGRESS
10      | 10         | 15
10      | 11         | 20
10      | 12         | 95

OR

USER_ID | SHOW_ID | PROGRESS                     | LAST_SEEN_ID | LAST_SEEN_TIME
10      | 5       | {"10":15, "11":20, "12": 95} | 12           | ---

In PosgreSQL I can get the individual progress with:

SELECT progress->'10' as progress...

I think that the best method is the first but it will create a lot of rows and this could make DB slower?

I think that the best method is the first ...

Simply put, it is.

  ... but it will create a lot of rows ...

Yes, it will.
Don't worry.

  ... and this could make DB slower?

Relational databases are really, really good at running around large numbers of rows in many tables and putting things together.  With proper indexing, this structure will handle large numbers of rows with no problem at all and is the most flexible, so will support many different types of query well.


Reduce the number of queries to the same table I need to build some reports using two tables:

AuthorTitles: id(pk), author_id, title_id
AuthorReports: id(pk), author_id, title_id, items_sold, date

My report needs to output the fallowing: author_id, max_items_sold_wavg, items_sold_wavg, date

The below code might have some errors, please check the fiddle.

max_items_sold_wavg is the weighted average of the fallowing query:

WITH maxitems AS (
 SELECT
   at.author_id,
   at.title_id,
   max(ar.items_sold) as sold,
   ar.date::date as date
 FROM author_titles at
 LEFT JOIN author_reports ar ON ar.author_id = at.author_id AND at.title_id = ar.title_id
 GROUP BY 1, 2, 4
), totitems AS (
    SELECT
      mi.author_id
      count(ti.title_id) as total,
      count(CASE WHEN (mi.sold = 1) THEN mi.title_id END) AS sold1,
      count(CASE WHEN (mi.sold = 2) THEN mi.title_id END) AS sold2,
      count(CASE WHEN (mi.sold = 3) THEN mi.title_id END) AS sold3,
      mi.date,
    FROM maxitems mi
    GROUP BY mi.date
), itemsold AS (
   SELECT
      at.author_id
      count(at.title_id) as total,
      count(CASE WHEN (ar.items_sold = 1) THEN ar.title_id END) AS sold1,
      count(CASE WHEN (ar.items_sold = 2) THEN ar.title_id END) AS sold2,
      count(CASE WHEN (ar.items_sold = 3) THEN ar.title_id END) AS sold3,
      ar.date::date as date,
    FROM author_titles at
    LEFT JOIN author_reports ar ON ar.author_id = at.author_id AND at.title_id = ar.title_id
    GROUP BY at.author_id, ar.date::date
)
SELECT
  ti.author_id,
  ((ti.sold1 * 30 + ti.sold2 * 60 + ti.sold3 * 100) / at.title_id),
  ((is.sold1 * 30 + is.sold2 * 60 + is.sold3 * 100) / at.title_id),
  ti.date
FROM totitems ti, itemsold is
WHERE ti.author_id = is.author_id AND ti.date = is.date

You can notice that in maxitems I extract only the max sold for every title and in itemsold I extract all the sells for all the titles, because an author can sell many titles in one day.

Example report data:

http://sqlfiddle.com/#!15/d2efc/22

How can I improve this query, is there a better way of doing it, because you can see that I am accessing the same data multiple times.

You can cut this down quite a bit - the first thing to notice is that you don't need the information in the author_titles table at all to get your results:

select author_id
     , sum(case max_sold when 1 then 30 when 2 then 60 when 3 then 100 else 0 end)/count(*)
         max_items_sold_wavg
     , sum(sold)/count(*) items_sold_wavg
     , created_at
from ( select author_id
            , created_at
            , sum(case items_sold when 1 then 30 when 2 then 60 when 3 then 100 else 0 end)
                sold
            , max(items_sold) max_sold
       from author_reports
       group by author_id,title_id,created_at ) z
group by author_id,created_at;

SQLFiddle here


Database design for school management to keep track of current and previous school years What's the best way to design a students registration records and keep tracks of previous academic years?
I have a table to enroll students, something like this:
CREATE TABLE [dbo].[Students] (
    [StudentID]          INT             IDENTITY (1, 1) NOT NULL,
    [StudentNumber]      NVARCHAR (50)   NOT NULL,
    [EnrolmentDate]      DATE            NULL,
    [Class]              NVARCHAR (50)   NOT NULL,
    [StudentSurname]     NVARCHAR (60)   NOT NULL,
    [StudentFirstNames]  NVARCHAR (60)   NOT NULL,
    [FatherSurname]      NVARCHAR (100)  NULL,
    [FatherNames]        NVARCHAR (100)  NULL,
    [MotherSurname]      NVARCHAR (100)  NULL,
    [MotherNames]        NVARCHAR (100)  NULL,
    [SchoolYear]         NVARCHAR (50)   NOT NULL,
    [StudentPicture]     VARBINARY (MAX) NULL,
    CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ([StudentID] ASC),
    CONSTRAINT [IX_Students] UNIQUE NONCLUSTERED ([StudentNumber] ASC),
    CONSTRAINT [FK_Students_AcademicYear] FOREIGN KEY ([SchoolYear]) REFERENCES [dbo].[AcademicYear] ([SchoolYear]) ON UPDATE CASCADE
);

I also have other tables for SchoolFees, MarksEntry, SchoolYear, etc.
So far everything works well for current year, I an just select where SchoolYear = current year, whether it's list of students, ,marks or fees paid/unpaid.
The challenge is to access data from previous years, I haven't figured out how to design the database or add a column to keep track of previous year.
When a student passes the exam, he/she promoted to a different class, so what I update in the Students table is: Class and SchoolYear, the rest of data remain the same like student number and other details.
So if I have a student in class 1B in school year 2020-2021 and promote to class 2B in year 2021-2022, the records of this student in class 1B in 2020-2021 are still in database but no longer accessible because that student no longer exist, their records have been modified.
What's the best and easy solution not to duplicate a student records but somewhat keep track of previous classes and school years when promoting a student to a different class?
How to promote the student to a different class?
Thanks

The Student table is a master table; generally speaking, information about the student is static and would not change. I would recommend adding children tables to track the student activities/matriculation in or out of classes. All this detail data would be inserted or updated as time advances.
From the hip, I'd start with something like this:
/* Student demographic data */
CREATE TABLE Student
(
    StudentID  INT IDENTITY(1, 1) PRIMARY KEY
    ,FirstName VARCHAR(100)
    ,LastName  VARCHAR(100)
    ,DOB       DATE
    ,Address   VARCHAR(150)
    ,/* etc */
);

/* Information on classes */
CREATE TABLE Class
(
    ClassID       INT IDENTITY(1, 1) PRIMARY KEY
    ,AcademicYear VARCHAR(25)
    ,StartDate    DATE
    ,EndDate      DATE
    ,CourseNumber VARCHAR(50)
    ,/* etc */
);

/* What action was taken with the class */
CREATE TABLE ClassActivity
(
    ClassActivityID INT IDENTITY(1, 1) PRIMARY KEY
    ,Activity       VARCHAR(50)  /*Enroll, Drop, etc*/
    ,/* etc */
);

/* Classes students interacted with (took action in) */
CREATE TABLE StudentClass
(
    StudentClassID   INT IDENTITY(1, 1) PRIMARY KEY
    ,StudentID       INT
    ,ClassID         INT
    ,ClassActivityID INT
    ,EffectiveDate   DATE
    ,/* etc */
);

/* How students performed in class */
CREATE TABLE StudentClassGrade
(
    StudetnClassGradeID INT IDENTITY(1, 1) PRIMARY KEY
    ,StudentClassID     INT
    ,Grade              VARCHAR(10)
    ,EffectiveDate      DATE
    ,/* etc */
);

It would also be useful to create views to display the student's current/past enrollment as separate queries instead of trying to handle these questions in the data tables. Such as:
dbo.v_StudentCurrentEnrollment
dbo.v_StudentHistoricalEnrollment
dbo.v_StudentCurrentClassLevel

The logic for which class the student is in would be contained in the view, not in the data tables. Structure your tables with solid relationships, then you can ask well formed questions to answer myriad questions.


Select roads that are between FROM and TO I have a road table:

 create table road
   (road_id int,
    name_ nvarchar2(15),
    from_ nvarchar2(15),
    to_ nvarchar2(15),
    sort int
   );
insert into road (road_id,name_,from_,to_,sort) values (4,'Street A','Street 1','Street 2','1');
insert into road (road_id,name_,from_,to_,sort) values (6,'Street A','Street 2','Street 3','2');
insert into road (road_id,name_,from_,to_,sort) values (5,'Street A','Street 3','Street 4','3');
insert into road (road_id,name_,from_,to_,sort) values (3,'Street B','Street VI','Street VII','600');
insert into road (road_id,name_,from_,to_,sort) values (7,'Street B','Street VII','Street VIII','700');
insert into road (road_id,name_,from_,to_,sort) values (1,'Street B','Street VIII','Street IX','800');
insert into road (road_id,name_,from_,to_,sort) values (2,'Street B','Street I','Street II','100');
insert into road (road_id,name_,from_,to_,sort) values (9,'Street B','Street II','Street III','200');
insert into road (road_id,name_,from_,to_,sort) values (11,'Street B','Street III','Street IV','300');
insert into road (road_id,name_,from_,to_,sort) values (8,'Street C','Street X','Street Y','9');
insert into road (road_id,name_,from_,to_,sort) values (10,'Street C','Street Y','Street Z','10');
commit;
select * from road order by name_, sort;

   ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
         4 Street A        Street 1        Street 2                 1
         6 Street A        Street 2        Street 3                 2
         5 Street A        Street 3        Street 4                 3

         2 Street B        Street I        Street II              100
         9 Street B        Street II       Street III             200
        11 Street B        Street III      Street IV              300

    {Gap in Street B}

         3 Street B        Street VI       Street VII             600
         7 Street B        Street VII      Street VIII            700
         1 Street B        Street VIII     Street IX              800

         8 Street C        Street X        Street Y                 9
        10 Street C        Street Y        Street Z                10

Eventually, I would like to make a tool that allows users to select roads between a from_ and a to_ (the values would be entered in parameters).

To do this, I think I need to make a query that can do something like:

Select roads where the name_ is Street B, between Street II and Street VIII (inclusive).

   ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
         9 Street B        Street II       Street III             200
        11 Street B        Street III      Street IV              300
         3 Street B        Street VI       Street VII             600
         7 Street B        Street VII      Street VIII            700

How can I do this?

select *
from road
connect by (prior to_ = from_ and from_ != 'Street V')
start with name_ = 'Street B' and from_ = 'Street II'
;

   ROAD_ID NAME_      FROM_      TO_              SORT
---------- ---------- ---------- ---------- ----------
         5 Street B   Street II  Street III        200
         6 Street B   Street III Street IV         300
         7 Street B   Street IV  Street V          400

Answer for the updated question:

select road_id, name_, from_, to_, sort
from
(
  select road_id, name_, from_, to_, sort,
  row_number() over (order by sort) as rn from road
  where name_ = 'Street B'
)
connect by (prior rn + 1 = rn and from_ != 'Street VIII')
start with from_ = 'Street II'
;

   ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
         9 Street B        Street II       Street III             200
        11 Street B        Street III      Street IV              300
         3 Street B        Street VI       Street VII             600
         7 Street B        Street VII      Street VIII            700


Using a join to return multiple values of the same column? I have two tables

user
Rank

In the Rank table, I saved rank_id and supervisor_id. In the Rank table, I stored the rank name. The rank_id and supervisor_id are foreign keys from the Rank table.

Since I have to select both rank_id and supervisor_id from same table(Rank), how do I join both tables to get the result with name, rank name and supervisor?

DDL

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `Name` varchar(90) NOT NULL,
  `rank_id` int(4) NOT NULL,
  `supervisor_id` int(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `Name`, `rank_id`, `supervisor_id`) VALUES
(1, 'john', 3, 2),
(2, 'james', 5, 1),
(3, 'geore', 4, 3),
(4, 'aby', 3, 2),
(5, 'john', 2, 1);

CREATE TABLE IF NOT EXISTS `rank` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `rank_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `rank`
--

INSERT INTO `rank` (`id`, `rank_name`) VALUES
(1, 'president'),
(2, 'vice president'),
(3, 'prime minister'),
(4, 'cabinet minister'),
(5, 'minister');

expected result

I need result like below mentioned table

CREATE TABLE IF NOT EXISTS `result` (
  `id` int(7) NOT NULL,
  `name` varchar(90) NOT NULL,
  `rank` varchar(50) NOT NULL,
  `supervisor` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `result`
--

INSERT INTO `result` (`id`, `name`, `rank`, `supervisor`) VALUES
(4, 'aby', 'prime minister', 'vice president');

You have to join the user table to the rank table twice

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.id = 4;

or

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.Name = 'aby';

Give it a Try !!!


how to run once a bunch of generated scripts with exception handling? Before I restore a database, I need to save the contents of a table called dbo.ref_systemSettings if it exists.

So I created a script that calls a procedure that will save the contents of this table in a DBA database, for all databases where this table exists.

That is working fine, but I would like to automate this process, avoiding the copy and paste - see below.

How it works:
When I run the following script I get a bunch of scripts to be executed:

--============================================================================
-- to save the table dbo.ref_systemSettings in all the databases where it exists
-- just run the generated script below at Radhe
--============================================================================
SELECT name, Radhe='exec sp_save ' + ''''
     + name + '.dbo.ref_systemSettings' + ''''
FROM sys.databases
WHERE OBJECT_ID(name+'.dbo.ref_systemSettings') IS NOT NULL

the query above produce the below:

which I copy and paste, in order to run:

exec sp_save 'my_db_01.dbo.ref_systemSettings'
exec sp_save 'my_db_02.dbo.ref_systemSettings'
exec sp_save 'my_db_03.dbo.ref_systemSettings'
exec sp_save 'my_db_04.dbo.ref_systemSettings'

when I run the whole of the query above, all my tables are saved into the DBA database. This is currently working fine.

Question:

I would like to eliminate the copy and paste.
what could be an effective way to run these scripts, while taking into consideration exception handling, lets say... there is no more space in the DBA database and the table cannot be saved... or any other expection??

The result should be stored in a variable and should execute that  SQL.
It's called dynamic SQL.
You can read more about this here

      BEGIN TRY

       DECLARE @sql varchar(max)
        SELECT @sql = COALESCE(@sql ,'')+'exec sp_save ' + ''''
             + name + '.dbo.ref_systemSettings' + ''''+';'
        FROM sys.databases
        WHERE OBJECT_ID(name+'.dbo.ref_systemSettings') IS NOT NULL

        EXEC (@sql)

       END TRY
       BEGIN CATCH

           DECLARE @ErrorMessage NVARCHAR(4000);
           DECLARE @ErrorSeverity INT;
           DECLARE @ErrorState INT;

          SELECT
             @ErrorMessage = ERROR_MESSAGE(),
             @ErrorSeverity = ERROR_SEVERITY(),
             @ErrorState = ERROR_STATE();

          -- Use RAISERROR inside the CATCH block to return error
          -- information about the original error that caused
          -- execution to jump to the CATCH block.
          RAISERROR (@ErrorMessage, -- Message text.
                     @ErrorSeverity, -- Severity.
                     @ErrorState -- State.
                    );

       END CATCH


using row data for column headers I have a table

  CREATE TABLE "AUTOMATION"."TESTRESULTS"
   (
    "BUILDNUMBER" VARCHAR2(30 BYTE),
    "TESTNAME" VARCHAR2(100 BYTE),
    "STATUS" VARCHAR2(100 BYTE)
   )

sample data would be

    buildnumber  testname   statusid
    1            testname1  pass
    1            testname2  fail
    1            testname3  warning
    2            testname1  fail
    2            testname2  fail
    2            testname3  fail
    3            testname1  fail
    3            testname2  pass
    3            testname3  fail

I would like to get a query to output:

               3         2           1
testname1      fail      fail        pass
testname2      pass      fail        fail
testname3      fail      fail        warning

What is the best approach to getting this output (buildnumbers as columns)?  I am using Oracle 11g and the test names are not known at the time of building the report.

11g has the pivot construct, which appears to meet your needs:

with pivot_data as (
select buildnumber, testname, status
  from testresults)
select * from pivot_data
 pivot (min(status)
        for buildnumber
        in ('1' as build_1, '2' as build_2, '3' as build_3)
        )
order by 1;

Note the min(status), the pivot requires an aggregate function, so we're working around that requirement.

TESTNAME    BUILD_1 BUILD_2 BUILD_3
testname1   pass    fail    fail
testname2   fail    fail    pass
testname3   warning fail    fail


Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands? I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote RDBMS and select them into a local table?

Sure, in SQL Server at least you can set up a linked server to other SQL Servers, Oracle, MySQL etc. and pull data from them. There are other approaches as well (such as OPENQUERY). An example would be:

SELECT * INTO dbo.localtable FROM OtherServer.database.dbo.TableName;

In Oracle I think the equivalent is database link but I confess that I have never used it.


I've got 16GB of ram, how should I configure MySQL Server? I've got a dedicated production server with multiple CPUs and 16GB of RAM.

How can I configure it to take advantage of server resources for better performance? Website traffic is increasing and MySQL starts using a lot of CPU.

This is my current MySQL server configuration.

I am also planning to optimize the database, and check the queries to improve the slow ones.

Please keep in mind that prior to MySQL 5.1.38, InnoDB was not designed to access multiple CPUs. You have one of two options to get MySQL to take advantage of multiple CPUs.

OPTION 1 : Install MySQL 5.5
OPTION 2 : Install MySQL 5.1 InnoDB Plugin

There are new options available since MySQL 5.1.38 (InnoDB Plugin Only) that is now fully available in MySQL 5.5

Rather rewrite what I wrote in past posts about this subject, please reads my past posts discussing those new settings:

About single threaded versus multithreaded databases performance
Possible to make MySQL use more than one core?
Multi cores and MySQL Performance

As for tuning all other aspects of MySQL, please refer to my past post about looking into disk space management and storage engine tuning : MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load


Sql Server 2012 data warehousing and different versions With Sql Server 2012 there are 3 flagship editions: Enterprise Edition, Business Intelligence, Standard.

The full comparison between the three: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

The business intelligence edition implies that the purpose of it is for data warehousing and covers what seems to be key concerns for that:

Self-Service Business Intelligence (Alerting, Power View, PowerPivot for SharePoint Server)
Advanced Corporate BI (Tabular BI Semantic Model, Advanced Analytics and Reporting, VertiPaq™ In-Memory Engine)
Advanced Data Integration (Fuzzy Grouping and Lookup, Change Data Capture, Advanced Data Mining)
Enterprise Data Management (Data Quality Services, Master Data Services)

However the enterprise edition is the only version that has:

  Data Warehousing (ColumnStore Index, Compression, Partitioning)

What functionality does this entail that is seperated between the BI and Enterprise editions?

Business Intelligence Edition

Business Intelligence edition has some useful features, like Master Data Services and non-additive aggregations (i.e. anything but sum/count).  EE has partitioning and the rest of the large database features.  The EE features are mostly relevant to users with large data volumes.  If you have less than (say) 100GB of data then you can probably get by with BI edition.  B.I. edition also has a limit on the number of CPU cores and memory that can be used by the database server, although this does not appear to apply to Analysis Services or Reporting Services.

A more detailed breakdown of the S.E., B.I. and E.E. features can be found here.

Some things that will be fine with B.I. edition

Most OLAP applications - B.I. edition seems to give you the clever aggregates (last non empty etc.) and other features that SE doesn't on the OLAP server.  By the look of the link, all SSAS features present in EE are present in B.I. edition, which makes it a bit more  of a contender for data marts.
MDM applications - B.I. edition comes with Master Data Services.
Moderate data volumes.  You can probably get away with (say) 100GB or so on BIE by applying brute force at the hardware level (fast disks).
B.I. edition supports distributed partitioned views, which gives you a basic read-only sharding capability.  However, the additional hardware and licensing may be no cheaper than biting the bullet and getting EE.
SSRS seems to be the same across B.I. and Enterprise editions.
Memory and CPU core limits do not apply to SSAS and SSRS servers.

Some things you will need Enterprise Edition for

If you have compliance requirements for physically secure data then the encryption and audit facilities of EE may be desirable.  Note that this is new in 2012.
Table partitioning is an EE only feature.  If you want to use table partitions to manage large data volumes you will need EE.
Star join transformations are only supported in EE.  If you have an application with lots of highly selective (<1% coverage) queries on a very large fact table you might get a win from star transformations.  This feature isn't really well documented in SQL Server circles, though, so it's hard to tell how well it works in practice.
Columnstore index - if you want to use this for fast ROLAP applications (using report builder or a 3rd party ROLAP tool such as Business Objects) then you may get significant mileage from this feature on EE.
Table compression may be useful for archiving old data.
B.I. edition only supports servers of a certain size - 64GB RAM, 4 sockets or 16 cores for the database server.  If you want to scale above a two socket machine then you will probably need EE.
B.I. edition is only licensed on a 'Server + CAL' basis.
Parallel DBCC and index builds are only supported in EE.  If you want to drop and recreate indexes for ETL loads this may reduce your run-times, particularly on incremental loads onto large incumbent data sets.
EE has a query re-write facility (called 'automatic use of indexed view by query optimiser').  If you want to use these to boost ROLAP performance you may want EE.  However, although this is quite a mature feature on Oracle I can't really vouch for how well it works on SQL Server in practice - although SQL Server does have a CUBE operator on GROUP BY, which is mostly indended for this application.
EE has fast Oracle and Teradata adaptors for SSIS, and adaptors for various other 'enterprisey' sources such as SAP BW.
Some of the MDM-ish featues of SSIS - e.g. fuzzy lookups - are only available in EE.
Change Data Capture is an Enterprise Edition only feature.