Is it a good to move data out of the original table when it's deleted or archived? I have a table with offers where I perform typical CRUD actions.

Each offer can has one out of the following statuses: active, deleted, archived, withdrawn, etc.

Most actions I perform only for active offers, so is it a good idea to move offers with other statuses to other tables, like offers_archived, offers_withdrawn?

No.

Data has a place to be based on what it is. Moving it somewhere else such as to an archive table or a deleted table is an implementation detail -- a workaround of sorts for performance.

Some databases, like PostgreSQL 10, can gracefully do this under the hood with List Partioning. In doing so, the "deleted" values are stored in a different partition (another distinct table is made available to the user), but it doesn't get in the way.

CREATE TABLE offers (...) PARTITION BY LIST (status);
CREATE TABLE offers_deleted PARTITION OF offers
  FOR VALUES IN ('deleted');

Now you could query offers WHERE status='deleted', and under the hood it would focus on just that one partition. Remove that predicate, and you'd query all of the partitions of offers. Or, you could query offers_deleted directly.

Even there though, my advice would generally be it's bad practice to move data because the status is deleted or because it's archived, unless performance demands it. Then you don't do it because you want to, you do it because you have to.


Extra column when migrating Sybase databases to single SQL Server database I'm trying to use Microsoft's SQL Server Migration Assistant for Sybase ('SSMA') to migrate multiple Sybase databases (that have the same schema) to a single SQL Server database.

All of the tables in the new SQL Server database will have an extra column project_id, which should have a fixed value depending on which Sybase database data is being migrated from.

I've migrated the schema using SSMA, and then created the new columns in SSMS. But I the new columns aren't visible in SSMA, and I can't see any way to achieve what I want to do.

How can I migrate data from each of the Sybase databases whilst utilising the extra project_id column?

Turns out SSMA just ignores any extra columns you create after migrating the schema - so I could set the default for the project_id columns before migrating data from each Sybase database and have them populated with those defaults.


Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability I'm in the process of configuring PostgreSQL 9.2 streaming replication with Redhat Clustering Suite for high availability.

I have 1 master and 1 hot standby configured to replicate the data from master to standby using streaming replication.

Now I will have to configure automatic fail over to standby using RedHat Clustering Suite (RHCS).
It looks like PostgreSQL 9.2 is not a supported resource type for RHCS and I will have to add a resource type manually.
But I couldn't find any resources which tells how to go about it.

Following is the approach I'm planning to follow in the status and start command in the resources script.

Status command:
Check the database cluster state is "in production"
using the command: /usr/pgsql-9.2/bin/pg_controldata /var/lib/pgsql/9.2/data | grep "Database cluster state:" | cut -d':' -f2 | sed 's/^ *//g'
If yes return success. If no, return failure.
Start command:
Check the database cluster state of the master and follow the steps below:

If it is "in production" state , do nothing
If it is "in archive recovery" state, touch the trigger_file configured in the recovery.conf file and return true.
If it is "in shutdown" state, rename the recovery.conf and restart the standby.

I would like to get the expert opinion on whether the approach is correct or is there any better/well defined approach for this.

repmgr is a nice place to go indeed. If you use pacemaker/corosync you shoud check this as well, its a Resource Agent handling repmgr : https://github.com/xmm/repmgr.
Pelase note you might need to either patch the provided Resource Agent or patch repmgr.


Managing static (or reference) data between different environments? This is a very specific question to a scenario I am working on, but also likely a common problem among SQL developers/database administrators. I am lucky enough to be able to change the current database processes in my job, and I would greatly appreciate any input

Current Database Use: Our company's website is database driven and requires a large set of static data to be accurate on each environment (testing, staging, production) for the site to function correctly. The data in these tables determines page text, ability to save responses, and what portion of data is loaded onto the page.

Environment Setup: all of our developers use a shared database where they drop in, modify, and remove particular rows from tables holding static data. This allows their HTML/code changes to function properly.

Problem: I am then required to capture and "migrate" these changes to staging and production. I've currently been using Red Gate's Data/Schema Compare, but it has been increasingly difficult for me to identify which changes were meant to be migrated and what was test/experimentation. Thus our team decided to use Visual Studio Database Projects for schema, since developers can now commit their own changes

This still poses an issue for data. Red Gate data compare captures all of the changed data on the testing environment, but there is no confirmation whether that data is correct and if there are any data modifications that should not be pushed to staging. Each build cycle, there is around 400-600 INSERTS/UPDATES/DELETE rows (all coming from the same ~15 tables that the website uses to function).

Solution? I've looked into several means of managing these data changes:

Continue using Red Gate data compare. This doesn't seem like a great option due to: a) being unable to determine which changes are correct and b) there isn't any database versioning since all data is being compiled and pushed between environments.
Require each developer to commit data changes and sequence them in order of arrival in GIT source control (001_,002_, etc). During each build to the next environment, execute schema changes followed by the data changes in the order they were committed/created. Disadvantage: the scripts needs to be written well and should be able to run multiple times without affecting data adversely. Also, time consuming for developers and myself (reviewing each data script to make sure it will function correctly).
Before a build, create MERGE statements for each of the ~15 tables. This will work similar to #1, except I would be able to store what the data looked like during X build (thus there would be some sort of database versioning). Disadvantage: no one is confirming changes, I am simply assuming all changes are accurate.
?

The way I do this is with SSDT projects and a post deployment script that MERGE-s from a VALUES list to the target table.

If it is necessary to store different values in different environments you can use sqlcmd variables defined in the project properties and store a publish profile for each different environment.

It does require a particular development approach though. Developers should know that the contents of these tables is part of the project and that they should be editing the post deployment script and re-publishing rather than editing the data in these tables directly.

You could potentially add triggers to these tables that roll back changes and raise an error as a reminder if you feel that this may be a problem. The triggers could check a CONTEXT_INFO value and allow the modification to succeed if some specific value to allow the post deploy script to succeed.


What is a Compressed Prefix B+-Tree (CPBTree) in SAP HANA? I'm studying the SAP HANA main memory database.
There is an index called CPBTree in it. In its documentation, it is described as follows:

CPB+-tree stands for Compressed Prefix B+-Tree; this index tree type
is based on pkB-tree. CPB+-tree is a very small index because it uses
'partial key' that is only part of full key in index nodes.

This is a bit vague. There are no other explanations about the CPBTree's structure on the Internet.
Is there anyone who can provide an explanation more or refer me to good documentation/URLs/&c.?

I'll assume you know what a B-Tree is. In a B+-Tree the leaf nodes contain all the column of the rows. A compressed B-Tree uses some form of compression to fit more information on each page. Compression is desirable because it maximizes the amount of information passed for each data movement. In a disk-based system the movements are between disk and RAM. For HANA the transfers are between RAM and CPU cache; memory stalls and cache flushes are significant in this architecture.
One possible compression is prefix compression. Here index key values are compared. When a common set of leading characters (a prefix) are identified they are removed from the key and stored separately. At run-time the prefix is implicitly re-attached to the remainder.
For example, say we have an index on the names of people from the Doomsday Book.
William of Braose
William of Ecouis
William of Falaise
William of Keynes
William of Poilley

To store these as-is requires 88 bytes at least. We note that the first 11 characters of each key is identical. This can be extracted as the prefix meaning only the unique part consumes space:
"William of "..
Braose
Ecouis
Falaise
Keynes
Poilley

This needs 44 characters - a 50% reduction in IO, cache miss etc.
This is likely to be effective because a B-Tree is an ordered list of keys. Keys which sort together are likely to be physically adjacent. Further, for large datasets the keys must be long since they have to distinguish between many rows. This means there will be many keys where the most significant (i.e. left-most) bytes are identical. (For small datasets without shared prefixes who cares! They're small and will be processed quickly whatever.)
The same applies to numeric values. Imagine a table whose primary key is a system-assigned incremental integer. After one hundred million rows have been added, the next rows will have values
100,000,001
100,000,002
100,000,003
...

The prefix can be the 4-byte integer "100,000,000" and the unique part held in a single byte each. For numbers this is also known as delta encoding.

I haven't watched it but one would imagine this would be relevant since it's presented by Dr. Plattner, The "P" of SAP.
https://open.hpi.de/courses/imdb2017/items/3S9yMQPPAjYHzgYwFd2DMF


ORA-12514: TNS:listener does not currently know of service requested in connect descriptor on linux I have configured oracle 12c on Rhel 7. When I try to connect database using following command it gets connected with no issues:

sqlplus system/Forest123@orcl

I'm facing problem when I try to put the connection string with hostname and port as shown below:

sqlplus system/Forest123@localhost:1521/orcl

Error occurred is

  ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I have gone through the questions asked before, but none of them helped me solve this problem.

Following are my tnsnames.ora, sqlnet.ora and listener.ora files.

tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_12C.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.localdomain)
    )
  )

listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_12C.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

hosts file

127.0.0.1 localhost
127.0.0.1 localhost.localdomain
127.0.0.1 localhost4
127.0.0.1 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
::127.0.0.1 localhost.localdomain localhost
172.31.15.86 oracle_12C.localdomain oracle_12C

Since you have the following error message that mean either hostname is not correct and/or the domain you are using.

How to know if everything is working fine or not?
You need to execute hostname command, lets say the output is oracle_12C, and execute cat /etc/resolve.con lets say the domain name mydomain.com, then you need to edit /etc/hosts like the following

127.0.0.1 localhost
127.0.0.1 localhost.localdomain
127.0.0.1 localhost4
127.0.0.1 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
::127.0.0.1 localhost.localdomain localhost
172.31.15.86 oracle_12C.mydomin.com oracle_12C

Note if you are not using domain then /etc/hosts/ should be like the following

127.0.0.1 localhost
127.0.0.1 localhost.localdomain
127.0.0.1 localhost4
127.0.0.1 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
::127.0.0.1 localhost.localdomain localhost
172.31.15.86 oracle_12C

Now lets move to tnsnames.ora, inside this file you need to search for your database section (in your question orcl) replace it like the following (note you need to take a backup from this file before doing any changes):

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname as /etc/hosts>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
      )

Note for host if you have doming then HOST = oracle_12C.mydomain.com, else it should be HOST = oracle_12C

Now save the file and close it.

After that lets move to listener.ora,  take a backup from it and edit it like the following:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname as /etc/hosts>)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Now restart your listener by executing lsnrctl stop and lsnrctl start then try to connect to the database by using the following commands

export ORACLE_SID=orcl
sqlplus system/Forest123


How to get the equivalent of array or string GROUP BY aggregate functions in Access? From SQL Aggregate Functions (Office 2007) there are only these GROUP BY aggregate functions in Access SQL:

Count(), Sum(), Avg(),
First(), Last(), Min(), Max(),
StDev(), StDevP(), Var(), VarP()

From PostgreSQL Aggregate Functions there are (among others) also these aggregate functions in PostgreSQL:

array_agg (expression)
  -- input values, including nulls, concatenated into an array

string_agg (expression, delimiter)
  -- input values concatenated into a string, separated by delimiter

How to get the equivalent of array or string GROUP BY aggregate functions in Access?
Is it possible to build Access SQL aggregate functions?
In case it's not clear, if I this data

ID col
-----
1  A
1  B
1  C
2  A
3  A
3  B

how can I get the following aggregation?

ID cols
----------
1  A, B, C
2  A
3  A, B

Do I have to resort to VBA? Any other ideas?

I'm using Access 2007 - 2010, but if things are different in a newer version, please let me know.

Access does come with a set of domain aggregation functions. An overview of these can be found here or in the MSN documentation here. There is, unfortunately, no native domain concatenation function.

Fortunately, Patrick Matthews has published a domain concatenation function here (and reproduced below to guard against link rot) that will accomplish what you're looking for.

From the documentation of DConcat():

SELECT DConcat("Account","Sample") AS Accounts
FROM Sample
GROUP BY DConcat("Account","Sample");

Returns:
Accounts
-----------------------------------------------------------------------------
Acct1, Acct10, Acct2, Acct3, Acct4, Acct5, Acct6, Acct7, Acct8, Acct9

For the specific example in question:

SELECT ID, DConcat("col","YourTable", "ID=1") AS cols
FROM YourTable
WHERE ID=1
GROUP BY ID, DConcat("col","YourTable", "ID=1")

Should return these values:

ID cols
----------
1  A, B, C

Then repeat for each different required ID value.

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)

    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years

    ' Requires reference to Microsoft DAO library

    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.

    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause

    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long

    On Error GoTo ErrHandler

    ' Initialize to Null

    DConcat = Null

    ' Build up a query to grab the information needed for the concatenation

    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")

    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value

    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF

            ' Initialize variable for this row

            ThisItem = ""

            ' Concatenate columns on this row

            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next

            ' Trim leading delimiter

            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)

            ' Concatenate row result to function return value

            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With

    ' Trim leading delimiter

    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)

    GoTo Cleanup

ErrHandler:

    ' Error is most likely an invalid database object name, or bad syntax in the Criteria

    DConcat = CVErr(Err.Number)

Cleanup:
    Set rs = Nothing

End Function


TYPE_SYM syntax error when running the mysql command to create or replace type Here is my command:

CREATE or REPLACE TYPE DRIVINGSCHED_TYPE AS OBJECT
(
vehicle VARCHAR2(20),
datesched DATE,
starttime DATE,
endtime DATE
);

  ERROR: Line 1: syntax error, unexpected TYPE_SYM, expecting VIEW_SYM.
  Statement skipped.

There is a bit of confusion that needs to be cleared up.
MySQL
In MySQL, the SQL Clause CREATE OR REPLACE works with Views only
You cannot create CREATE OR REPLACE any other types in MySQL (someone correct me if this is not so).
That's why ERROR: Line 1: syntax error, unexpected TYPE_SYM, expecting VIEW_SYM. Statement skipped. says VIEW_SYM is expected.
You also cannot create types with the above syntax in MySQL.
PostgreSQL
You can create types in PostgreSQL, but there is no CREATE OR REPLACE syntax for creating types. You can use CREATE OR REPLACE for creating functions and views, but not types.


query optimization help I am fairly new to SQL and am a bit out of my depth with the query I have been working on. I am attempting to modify a query created by someone else to fix an error. the code I have modified is below:

SELECT COUNTY                               COUNTY,
DECODE(RATE,'115A','R','C')             R_C,
SUM(MTH_CHG)                            MO_CHG,
SUM(YTD_CHG)                            YR_CHG,
COUNT(CUST)                             TOTAL_CUST
    FROM RPT_BLOC_VW,
        (SELECT SRAT_CODE               RATE,
        NVL(billed_chg,0) + NVL(adj_chg,0) MTH_CHG,
        0                             YTD_CHG,
        BLOC_CODE,
        FROM RPT_REV_CON_ETL,
        (SELECT DISTINCT cust_code, prem_code AS CUST
            FROM   rpt_rev_con_etl
            WHERE  PROCESS_DATE BETWEEN vStartMonth AND vEndMonth
                   AND UTVSRAT_desc = '115')
        WHERE PROCESS_DATE BETWEEN vStartMonth AND vEndMonth
        AND UTVSRAT_DESC = '115'
    UNION ALL
     SELECT         SRAT_CODE          RATE,
         0                             MTH_CHG,
         NVL(billed_chg,0) + NVL(adj_chg,0) YTD_CHG,
         BLOC_CODE
        FROM RPT_REV_CON_ETL
          WHERE PROCESS_DATE BETWEEN vStartYear AND vEndMonth
        AND DESC = '115'
     ) DATA
     WHERE DATA.BLOC_CODE = RPT_BLOC_VW.BLOC_CODE(+)
 GROUP BY COUNTY, DECODE(RATE,'115A','R','C')
 ORDER BY COUNTY, DECODE(RATE,'115A','R','C') DESC ;

What I am looking for is a way to combine the second and third select statements into one. They have the same From and Where clause so I am fairly certain it should be possible, but I cannot think of a way to do it that doesn't mess up the distinct clause and screw up the count I am trying to get.

I am pretty sure there is something obvious I am overlooking.

-edited to include full query-

With your original code as such:

    SELECT COUNTY                                  COUNTY,
       DECODE(RATE,'115A','R','C')             R_C,
       SUM(MTH_CHG)                            MO_CHG,
       SUM(YTD_CHG)                            YR_CHG,
       COUNT(CUST)                             TOTAL_CUST
    FROM RPT_BLOC_VW,
       (SELECT SRAT_CODE               RATE,
        NVL(billed_chg,0) + NVL(adj_chg,0) MTH_CHG,
         0                             YTD_CHG,
         BLOC_CODE
        FROM RPT_REV_CON_ETL,
          (SELECT DISTINCT
           CUST_CODE,
           PREM_CODE AS CUST
    FROM   RPT_REV_CON_ETL
    WHERE PROCESS_DATE BETWEEN '28-OCT-16' AND '29-NOV-16' AND DESC = '115'
    )
          WHERE PROCESS_DATE BETWEEN '28-OCT-16' AND '29-NOV-16'
        AND DESC = '115'
     ) DATA
     WHERE DATA.BLOC_CODE = RPT_BLOC_VW.BLOC_CODE(+)
 GROUP BY COUNTY, DECODE(RATE,'115A','R','C')
 ORDER BY COUNTY, DECODE(RATE,'115A','R','C') DESC ;

I start by rewriting it to (this helps me think about it a little differently, which is weird, I'm sure, but this is how I approach it)

SELECT county            COUNTY,
       Decode(rate, '115A', 'R', 'C') R_C,
       SUM(mth_chg)      MO_CHG,
       SUM(ytd_chg)      YR_CHG,
       Count(cust)       TOTAL_CUST
FROM   rpt_bloc_vw,
       (SELECT srat_code                            RATE,
               Nvl(billed_chg, 0) + Nvl(adj_chg, 0) MTH_CHG,
               0                                    YTD_CHG,
               bloc_code
        FROM   rpt_rev_con_etl,
               (SELECT DISTINCT cust_code,
                                prem_code AS CUST
                FROM   rpt_rev_con_etl
                WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16'
                       AND desc = '115')
        WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16'
               AND desc = '115') DATA
WHERE  DATA.bloc_code = rpt_bloc_vw.bloc_code(+)
GROUP  BY county, Decode(rate, '115A', 'R', 'C')
ORDER  BY county, Decode(rate, '115A', 'R', 'C') DESC;

If I rewrite your query to use CTEs like so:

WITH DistinctCustomers AS (
    SELECT DISTINCT cust_code,
                    prem_code AS CUST
    FROM   rpt_rev_con_etl
    WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
),
DATA as (
    SELECT srat_code                            RATE,
           Nvl(billed_chg, 0) + Nvl(adj_chg, 0) MTH_CHG,
           0                                    YTD_CHG,
           bloc_code ,
           cust.CUST
    FROM   rpt_rev_con_etl etl
    JOIN   DistinctCustomers cust ON cust.cust_code = etl.cust_code
    WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
)
SELECT county COUNTY,
       Decode(rate, '115A', 'R', 'C') R_C,
       SUM(mth_chg) MO_CHG,
       SUM(ytd_chg) YR_CHG,
       Count(cust) TOTAL_CUST
FROM   rpt_bloc_vw rpt
JOIN   DATA d on d.bloc_code = rpt.bloc_code(+)
GROUP  BY county, Decode(rate, '115A', 'R', 'C')
ORDER  BY county, Decode(rate, '115A', 'R', 'C') DESC;

It looks like, if I read this correctly, that you are first getting the distinct customers that had any processes between the date ranges.

Then you go back for the rate codes and charges for those customers. You could probably just do a join there and not need the distinct (which was probably added because it wasn't written correctly, by my eye, but I don't write a lot of Oracle SQL, just MS SQL).

That gives us this:

WITH DATA as (
    SELECT srat_code                            RATE,
           Nvl(billed_chg, 0) + Nvl(adj_chg, 0) MTH_CHG,
           0                                    YTD_CHG,
           bloc_code ,
           prem_code as Cust
    FROM   rpt_rev_con_etl etl
    WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
)
SELECT county COUNTY,
       Decode(rate, '115A', 'R', 'C') R_C,
       SUM(mth_chg) MO_CHG,
       SUM(ytd_chg) YR_CHG,
       Count(cust) TOTAL_CUST
FROM   rpt_bloc_vw rpt
JOIN   DATA d on d.bloc_code = rpt.bloc_code(+)
GROUP  BY county, Decode(rate, '115A', 'R', 'C')
ORDER  BY county, Decode(rate, '115A', 'R', 'C') DESC;

Since I don't have a table schema, I'm just assuming that what I've done so far is correct. Pretty sure I got this under control, but see the first query to tell me what I did wrong if I got it wrong.

So then this can be reduced even further if we just do some joins across things like so:

SELECT county COUNTY,
       Decode(etl.srat_code, '115A', 'R', 'C') R_C,
       SUM(Nvl(etl.billed_chg, 0) + Nvl(etl.adj_chg, 0)) MO_CHG,
       SUM(0) YR_CHG,
       Count(etl.prem_code) TOTAL_CUST
FROM   rpt_bloc_vw rpt
JOIN   rpt_rev_con_etl etl on etl.bloc_code = rpt.bloc_code(+) AND etl.process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
GROUP  BY county, Decode(etl.srat_code, '115A', 'R', 'C')
ORDER  BY county, Decode(etl.srat_code, '115A', 'R', 'C') DESC;

But I'm pretty sure I got something wrong on that last one, I don't always trust my own reductions. I've tried to show all my steps here starting with the first edit I did to your post on your original SQL code.

Can you tell me how the output of the last SQL I wrote compares to your original query?


SQL Server Agent crashes when scheduled procedures make select in DBLink on an offline Oracle database I have SQL Server 2014 and I'm using SQL Server Agent to start some scheduled procedures during the day. These procedures make some selects (in DBlink) on an Oracle database that is handled by another company. Sometimes the Oracle database is offline and my procedures seem to be stucked and I should restart the entire server the make the SQL Server Agent work again.

What am I doing wrong? I suppose there is a sort of timeout to end the procedure and release the SQL Agent.

Example of select:

SELECT * from openquery(positions,
'SELECT * from oracleTableDbLink.POSITION'
)

You can configure a timeout for a query to a remote server.  The documentation is here
( https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver15 )
You can change this on a database with this script which sets a 100 second timeout
USE AdventureWorks2012 ;
GO
EXEC sp_configure 'remote query timeout', 100 ;
GO
RECONFIGURE ;
GO

There does not seem to be a way to set a query timeout within T-SQL for one statement.  This is discussed in more detail here.
I see another user has suggested configuring the sqlnet.ora file on the client on the SQL server however this should be tested as I believe SQL Server will still wait on a response from the client even if the client has terminated the connection.
While you are testing you do not need to restart the server.  Just restart the Agent service and that will terminate all running jobs.
You might also consider a test query before doing your real work.  Try this, if it returns a value the Oracle database is up.
select 1 from dual;

Due to all places where things can go wrong in a linked server query this answer from Hannah Vernon might assist.
She suggests surrounding the initial test with some error handling that will allow any problems to be found at run time when the dynamic statement runs
BEGIN TRY
    DECLARE @cmd nvarchar(max);
    SET @cmd = 'SELECT * FROM OPENQUERY([server1], ''SELECT 1 AS c;'');';
    EXEC sp_executesql @cmd;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;


MSDTC and its relation to SQL Server So I'm doing some research on MSDTC and its role it does or does not play in various types of calls within SQL Server, and it got me thinking on the following questions:

Are there any 3rd party alternatives/replacements for MSDTC (if they exist) & their pros/cons
Does DTC come into play when I simply query a single table (CRUD or Select) via a linked server?
Is MSDTC required for transactional replication?  I know updatable subscriptions require MSDTC, but not clear if non-updatable subscriptions need/use MSDTC.

From googling, I can't seem to find good answers to these questions, so turning to the community here.  Any help in bettering my understanding of MSDTC is greatly appreciated.

Thank you!

MSDTC provides transaction management services for applications. It can act as a regular transaction manager or an XA-compliant resource manager. SQL Server is the well known app that uses DTC but it isn't really a SQL Server feature or even component. MSDTC is really intended to provide transaction management for two or more distinct resources to ensure transactional consistency. Transaction management  is built into SQL Server for a single resource/database but right now it doesn't natively handle multiple resources. See this old MSDTC blog for a more detailed description. The content is really old but the concepts are still accurate.

As for your questions,

There are many other transaction managers out there but SQL Server is integrated with DTC by default (e.g. starting your query with BEGIN DISTRIBUTED TRANSACTION will automatically enlist DTC as a transaction manager). If you use JDBC, you can also configure SQL Server to support XA by installing the XA component from the JDBC package. There's a driver and some install script to do that (all within the package). That sets up some XPs to provide XA transaction support. There may be other TMs out there for SQL Server but I've not encountered them.
Yes it can, depends on what you're doing. DQ can enlist DTC to manage transactions (it'll automatically promote to a DT) if needed. E.g. READ only operations will not but WRITE will.
Transactional replication without updateable subscriptions do not require MSDTC. It is log based and regardless of push or pull, there is no concept of distributed transactions.


mysql innodb_buffer_pool_size let me understand I couldn't find answer for my situation.

I have a windows server 2008 with 2 cores and 7 GB RAM (Azure D1 VM).

I'm running mariadb 10.0.14 on it. I have 2 databases (1.5GB and 9GB in size).

Statistics are in general:

  AVG 1192590 SELECTs per hour (70% of all)
  AVG  163555 UPDATEs per hour (10% of all)
  AVG   87728 INSERTs per hour (5% of all)
  AVG    5920 DELETEs per hour (0.3% of all)

I had innodb_buffer_pool_size set=4G and the whole server was running terribly slow.
CPU was utilized avg 10-15% but queries was executed very slowly.

Now I have set innodb_buffer_pool_size=1G and it's running much much faster.
The interesting this is that I have commited 1,5 GB RAM by mysqld.exe
So 5 GB RAM are still free.

Why setting innodb_buffer_pool_size larger means slower queries?
What should I configure more to use this free RAM to gain more performance on mysql (mariadb)?

If your mysql is on dedicated server then you can set the buffer pool to the value 4GB. Initially the queries will run little slow because they require disk seeks. As your data set is very small so you can keep most of the data in memory. Once the data is present in memory performance will increase.

As your server is having huge number of reads and less number of writes good buffer pool will give better results. You can allocate around 70% memory to mysql in dedicated setup.

In crease you Key_buffer_size if you have any myisam tables. Increase the value for the below variable to avoid disk based temporary tables.

max_heap_table_size = 64M
tmp_table_size = 64M

Dont change any on session variables they will consume more memory and may decrease the performance. Below are the session level variables for which defaults works better to most work loads.

sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size

If you data not change frequently then enable query cache.

query_cache_size = 256M
query_cache_type = ON
query_cache_wlock_invalidate = ON ( to get accurate data )
query_cache_limit = 10M

Increasing innodb_log_buffer_size and innodb_log_file_size may help to gain the performance. But be careful if you increase the file size, it will increase the recovery time.

  Last but not lease if you are observing performance hit with 4GB
  buffer pool try setting innodb_buffer_pool_instances=2

give it a try!

Mysql has to fetch the data from the disk and keep it in memory for the first time you have accessed the data. As buffer pool contains both index and data inside it, your query performance will increase.

You can set the log file size to the best value as described HERE.

The best further description is given in rollando.


How SQL server generates a Query plan with Auto Create Statistics set to OFF I am wondering how SQL server can generate a query plan with "Auto create statistics" set to "off".

I stumbled on the below context at the below link while searching for an answer(Sorry, I searched, but couldn't find anything better).

https://www.simple-talk.com/sql/performance/execution-plan-basics/

  Table variables do not ever have statistics generated on them, so they
  are always assumed by the optimizer to have a single row, regardless
  of their actual size

Even though the above is applicable only for Table variables, I thought SQL Server will use the same principle to derive a query plan for a table and decided to do a small test.

I cleared the PROC cache and Ran the below query on a table with 1 million records

My test proved me wrong and SQL server reported missing statistics(as expected), But came up with the estimated number of rows as 31622(I expected it to be 1) when there are no statistics. How did the query optimizer come up with this value without statistics?

Table variables is another story, what the server uses when there are no statistics is estimation for unknown input:

So in your case the estimation will be select power(1000000, 0.75) = 31622
( = predicate with non unique column prior to 2014)

Related book: T-SQL Querying by Itzik Ben-Gan


Allow update access to only 2 tables in a database schema I would like to have a login which is capable of accessing a specific schema but should be having only update access to 2/3 tables. Is it possible?

The Database runs on SQL Server 2008 R2. Kindly guide me if possible.

Here is an example script

CREATE USER [UserName] FROM LOGIN [LoginName]

-- Grant SELECT on the schema
GRANT SELECT ON SCHEMA::SchemaName TO [UserName]
-- Grant UPDATE on the individual tables
GRANT UPDATE ON SchemaName.TableName1 TO [UserName]
GRANT UPDATE ON SchemaName.TableName2 TO [UserName]

And please remember that unless the user is some level of administrator, db_owner for example, any DENYs that you do will override any form of grant.  If you do this:

DENY UPDATE ON SchemaName.TableName3 TO [UserName]

Then nothing you can do will grant them update access until you REVOKE the DENY.
You could add the user to db_datareader to give them the SELECT permissions but that will give them SELECT permissions to all schemas not just one.  It just depends on what you are looking for.  That command is like this:

EXEC sp_addrolemember 'db_datareader','UserName'


Listener refused the connection with following error: ORA-12505, TNS: listener does not currently know of SID given in connect descriptor I am a newbie to oracle database. I am trying to make a connection but I receive the following error:

Listener refused the connection with following error: ORA-12505, TNS: listener does not currently know of SID given in connect descriptor.

I have done my research online and after executing the lsnrctl status and lsnrctl service I get the following. Does anyone what I need to do? All kind of help is highly appreciated.

Thanks.

C:\Users\myComputer>lsnrctl start

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-SEP-2012 23:33
:29

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

TNS-01106: Listener using listener name LISTENER has already been started

C:\Users\myComputer>lsnrctl service

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-SEP-2012 23:33
:46

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "oracle.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:166 refused:0 state:ready
         LOCAL SERVER
Service "oracleXDB.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: PATO-PC, pid: 6232>
         (ADDRESS=(PROTOCOL=tcp)(HOST=Pato-PC)(PORT=49537))
The command completed successfully

C:\Users\myComputer>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-SEP-2012 23:34
:24

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                24-SEP-2012 21:08:10
Uptime                    0 days 2 hr. 26 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\myComputer\product\11.2.0\dbhome_2\network\admin\list
ener.ora
Listener Log File         c:\app\myComputer\diag\tnslsnr\Pato-PC\listener\alert\log.xm
l
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "oracle.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
Service "oracleXDB.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
The command completed successfully

How are you trying to make a connection? If it is SQLplus here's what happens:

c:>sqlplus user/password@database

The entry "database" is looked up in the tnsnames.ora file. Here is how this may look:

database=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = databas)
    )
  )

This says that database "database" runs on server "server" with SID "databas".
A connection is made with the listener on server "server" on port 1521 with a request to connect to database with SID "databas".
The listener responds with the message that it does not know a database with SID "databas".

One way of getting some more information about this is to use TNSPING.

C:\>tnsping database

TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 25-SEP-2
012 08:21:21

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
D:\app\rwe\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = SERVER)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = databas)))

This will tell you the location of sqlnet.ora. Your tnsnames.ora file is in the same location. Also you get to see what the entry for database is in your tnsnames.ora file.


Query slow when using function in the WHERE clause This is fast (49ms):

v_cpf_numerico := ext.uf_converte_numerico(new.nr_cpf);

select cd_cliente into v_cd_cliente
from central.cliente where nr_cpf_cnpj = v_cpf_numerico;

This is slow (15 seconds):

select cd_cliente into v_cd_cliente
from central.cliente where nr_cpf_cnpj = ext.uf_converte_numerico(new.nr_cpf);

Function:

create or replace function ext.uf_converte_numerico(_input varchar(30)) returns bigint
as
$$
begin
    _input := regexp_replace(_input, '[^0-9]+', '', 'g');

    if _input = '' then
        return null;
    end if;

    return cast(_input as bigint);
end
$$ language plpgsql;

I am using PostgreSQL 12.
Why is the second variant slow?

Consider this simplified equivalent:

CREATE OR REPLACE FUNCTION ext.uf_converte_numerico(_input varchar(30))
  RETURNS bigint LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT NULLIF(regexp_replace(_input, '[^0-9]+', '', 'g'), '')::bigint;
$func$;

IMMUTABLE, because it is, and for the reasons Laurenz explained.
PARALLEL SAFE in Postgres 10 or later, because it is. Without the label, functions default to PARALLEL RESTRICTED, which disables parallel queries. This may or may not affect the query on display. But the 15 seconds you reported indicate you are operating on big tables. So it can make a huge difference in other queries.
LANGUAGE SQL to enable function inlining, which won't matter much for the query on display (after you labelled it IMMUTABLE), but will simplify query plans and improve overall performance.
NULLIF as minor simplification.

Aside: your input is varchar(30), which still allows out of range errors for bigint. Either consider varchar(18) to be sure. Or just make it text to remove the ineffective restriction.


Is there any better way in oracle to enable parallel other than this? I want to do do following set of operation.

execute immediate "alter session enable parallel query";

execute immediate "alter session enable parallel ddl";

execute immediate "alter session set parallel_force_local=false";

Is there any better way of doing in oracle 11g/12c?

Thanks in advance.

You should alter your tables and indexes and specify the amount of parallel that you want.

How Parallel Execution Works

ALTER TABLES sales PARALLEL 8;
ALTER TABLE customers PARALLEL 4;


Split two delimited strings in same order without function I am trying to split two columns with delimited strings into rows. The positions of the values in each string are related so I am trying to split it out so that the related values are in a row. I am unable to use function as I cannot create objects in the database

Here is sample table and data

CREATE TABLE #temp
(id   INT,
 keys VARCHAR(50),
 vals VARCHAR(50)
);

INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');

and my desired output would be

ID  key  val
1   1    one
1   2    two
1   3    three
2   4    four
2   5    five
2   6    six
3   7    seven
3   8    eight
3   9    nine

I got the query to work if I only split one column, so I define two CTEs with row_number and join on ID and row_number. This does give desired output but my live table is very large and I was hoping for a way to pass through the table only once, instead of twice.

with keys as(
SELECT id,keys,vals,
       keys.keyid.value('.', 'VARCHAR(8000)') AS keyid,
      row_number() over(order by (select null)) as rn
FROM
(SELECT id,keys,vals,
           CAST('<Keys><key>'+REPLACE(keys, ',', '</key><key>')+'</key></Keys>' AS XML) AS tempkeys
    FROM #temp
) AS temp
CROSS APPLY tempkeys.nodes('/Keys/key') AS keys(keyid)),
vals as(
SELECT id,keys,vals,
       vals.val.value('.', 'VARCHAR(8000)') AS valid,
      row_number() over(order by (select null)) as rn
FROM
(SELECT id,keys,vals,
           CAST('<vals><val>'+REPLACE(vals, ',', '</val><val>')+'</val></vals>' AS XML) AS tempvals
    FROM #temp
) AS temp
CROSS APPLY tempvals.nodes('/vals/val') AS vals(val))

SELECT k.id, k.keyid, v.valid
FROM keys AS k
     INNER JOIN vals AS v
     ON k.id = v.id
        AND k.rn = v.rn;

Create the function in msdb or somewhere else.

CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder
(
    @List1  varchar(50),
    @List2  varchar(50),
    @Delim  varchar(10)
)
RETURNS TABLE
AS
    RETURN
    (
      WITH src(r) AS
      (
        SELECT 1 UNION ALL SELECT r + 1 FROM src WHERE r < 10
      ),
      Numbers(Number) AS
      (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM src AS s1, src AS s2 -- add more if you need longer strings
      ),
      parsed(s1,s2,r1,r2)
      AS
      (
        SELECT
          SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1
            + @Delim, n1.Number) - n1.Number),
          SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2
            + @Delim, n2.Number) - n2.Number),
          r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
          r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
        FROM Numbers AS n1, Numbers AS n2
        ON  n1.Number <= LEN(@List1)
        AND n2.Number <= LEN(@List2)
        AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
        AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
      )
      SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
    );

Then, as @gbn noted, reference it by 3-part name wherever your query has to run.

CREATE TABLE #temp
(id   INT,
 keys VARCHAR(50),
 vals VARCHAR(50)
);

INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');

SELECT t.id, f.s1, f.s2 FROM #temp AS t
  CROSS APPLY msdb.dbo.SplitTwoStringsWithSameOrder(keys, vals, ',') AS f
  ORDER BY t.id, f.r1;
GO

DROP TABLE #temp;

Results:

The resulting plan, shown in Plan Explorer (disclaimer: I'm the Product Manager), is not the prettiest thing I've ever seen (click to enlarge a little bit):

But there is exactly one scan of #temp (4% cost). The biggest costs are two sorts and a spool, and there is some I/O due to a worktable which I am not sure is avoidable.

If you KNOW you will only ever have 50 characters in either of these strings, then you can get a much simpler plan with a built-in Numbers table (people object to these, but they're very useful, and they are almost always in memory if you reference them enough). This doesn't help I/O but removing the recursive CTE and other constructs of building the numbers inside the function is quite helpful for CPU etc.

First, the numbers table:

DROP TABLE dbo.Numbers;

;WITH n AS
(
    SELECT
        TOP (50) rn = ROW_NUMBER() OVER
        (ORDER BY [object_id])
    FROM sys.all_columns
    ORDER BY [object_id]
)
SELECT [Number] = rn - 1
INTO dbo.Numbers
FROM n;

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);

Then a second version of the function:

CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder2
(
    @List1  varchar(50),
    @List2  varchar(50),
    @Delim  nvarchar(10)
)
RETURNS TABLE
AS
    RETURN
    (
      WITH parsed(s1,s2,r1,r2)
      AS
      (
        SELECT
          SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1
            + @Delim, n1.Number) - n1.Number),
          SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2
            + @Delim, n2.Number) - n2.Number),
          r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
          r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
        FROM dbo.Numbers AS n1
        INNER JOIN dbo.Numbers AS n2
        ON  n1.Number <= LEN(@List1)
        AND n2.Number <= LEN(@List2)
        AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
        AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
      )
      SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
    );
GO

Here is the simpler plan that results (again, click to enlarge):

The plan still has two sort operations, but the spool is gone, there is still only one scan of #temp, and in my limited tests the cost numbers (absolute cost numbers, not %) were better every time.

I don't know precisely either of these will scale with a lot more rows, but it's worth testing, and if you weigh this against other solutions and it can't scale well, that may be a point your reconsider the design (store these relationally instead of as comma-separated sets).


Using SQL Server SMO with a Port number In SSMS if you are connecting to SQL Server instance set to specific port other than 1433 you simply put ",port number" after the instance name. In the same regard if I am using System.Data.SqlClient.SqlConnection and want to populate a data set with System.Data.DataSet, I would do the same when specifying the Data Source portion of the connection string to pass into that object.

Is there not a way to do this using Microsoft.SqlServer.Management.Smo.Server class?

I have tried variations with no luck like:

$cnString = "Data Source=Server\instance,port;Initial Catalog=master;Trusted_Connection=true;"
$cn = new-object system.data.sqlclient.sqlconnection($cnstring)
$cnSql = New-Object Microsoft.sqlserver.management.common.serverconnection($cn)
$s = New-Object Microsoft.sqlserver.management.smo.server($cnSql)
$s | Select Name, Version

Or just trying:

$s = New-Object Microsoft.sqlserver.management.smo.server "Server\Instance,port"
$s | Select Name, Version

One thing you could do is just set the ServerConnection portion of the Server object to an explicit connection string, specifying the port name.

You could either do this by accessing the Server.ConnectionContext property:

$ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server

$SqlServer.ConnectionContext.ConnectionString = $ConnectionString

$SqlServer.Databases |
    Select-Object Name

Or by passing the ServerConnection object in when you instantiate the Server object:

$ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;"

$ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$ServerConnection.ConnectionString = $ConnectionString

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerConnection)

$SqlServer.Databases |
    Select-Object Name

EDIT: I just re-read your question, and it seems like one of your attempts is basically identical to my second one up above.  I took your code and all I changed was the data source portion of your connection string, and it worked just fine for me:

$cnString = "Data Source=Server\instance,port;Initial Catalog=master;Trusted_Connection=true;"
$cn = new-object system.data.sqlclient.sqlconnection($cnstring)
$cnSql = New-Object Microsoft.sqlserver.management.common.serverconnection($cn)
$s = New-Object Microsoft.sqlserver.management.smo.server($cnSql)
$s | Select Name, Version


There are a couple of ways to pivot the data to get the output you want. There's the PIVOT operator, demonstrated here: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server

The syntax for PIVOT can be difficult to remember. A simpler method that usually performs just as well is to nest CASE expressions inside SUM:

SELECT userid
, SUM(CASE WHEN YEAR(d1) = 2015 THEN amt ELSE 0 END) AS [2015 TA]
, SUM(CASE WHEN YEAR(d1) = 2015 THEN inflation ELSE 0 END) AS [2015 TI]
, SUM(CASE WHEN YEAR(d1) = 2016 THEN amt ELSE 0 END) AS [2016 TA]
, SUM(CASE WHEN YEAR(d1) = 2016 THEN inflation ELSE 0 END) AS [2016 TI]
FROM @information
WHERE d1 BETWEEN '01/01/2015' AND '12/31/2016'
GROUP BY userid
ORDER BY userid

It accomplishes the same pivot without the complexity of the PIVOT operator.


Get durations from time series table where some condition holds consecutively Given a table where each row has a timestamp and some measurement, how can I get all of the intervals during which the measurement consecutively satisfies some condition?

Here's a SQLfiddle (not sure if there's a better site for that nowadays).

So in this case, I'd like to to get all of the durations during which status is true. The starting point should be the first true point chronologically. The end point can be the first false point or the last true point, whichever is easier.

I looked to window functions first. I could come up with separate queries, one that obtained the starting points and one that produced the end points, but it felt like I was going about it the wrong way.

You could try along:

SELECT
  ts
  , CASE status
      WHEN false THEN 'end'
      WHEN true THEN 'start'
      ELSE 'ERROR'
    END AS mode
FROM (
  SELECT
    ts
    , status
    , LAG(status, 1) OVER (ORDER BY ts) AS lg
  FROM log
) AS T
WHERE
  (lg IS NULL)
  OR
  (status <> lg)
ORDER BY ts
;

The sub-select is necessary as window functions are not allowed directly in the WHERE clause.

See it in action: SQL Fiddle (Thanks for preparing the fiddle! ;-) )

Update

To get the duration actually listed, try

WITH
S1 AS (
  SELECT
    ts
    , status
    , LAG(status, 1) OVER (ORDER BY ts) AS lg
    , CASE status WHEN LAG(status, 1) OVER (ORDER BY ts) THEN 0 ELSE 1 END AS toggle
  FROM log
  ORDER BY ts
),
S2 AS (
  SELECT
    ts
    , status
    , lg
    , SUM(toggle) OVER (ORDER BY ts) AS streak
  FROM S1
  ORDER BY ts
),
S3 (ts, last_ts, status, lg, streak) AS (
  SELECT
    ts
    , LAST_VALUE(ts) OVER (PARTITION BY streak
                           ORDER BY ts
                           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                          )
    , status
    , lg
    , streak
  FROM S2
  ORDER BY ts
),
S4 (start, duration) AS (
  SELECT
    ts
    , last_ts - ts
  FROM S3
  WHERE
    status
    AND ((status <> lg) OR (lg IS NULL))
  ORDER BY ts
),
S5 (ts, status, duration) AS (
  SELECT
    ts
    , status
    , LEAD(ts, 1, (SELECT MAX(ts) FROM S3)) OVER (ORDER BY ts) - ts
  FROM S3
  WHERE
    (status <> lg)
    OR
    (lg IS NULL)
  ORDER BY ts
),
S6 (start, duration) AS (
  SELECT
    ts
    , duration
  FROM S5
  WHERE status
)
SELECT
--  * FROM S4
  * FROM S6
;

See it in action: SQL Fiddle

It starts off by identifying the consecutive ranges. In S4, the ranges end when status is TRUE for the last time respectively; in S6 when status changed to FALSE.

This could probably be condensed a little bit. But as window functions cannot be nested (directly) anyway…

Please comment, if and as this requires adjustment / further detail.


Relational Design - One table, two foreign keys or Two tables, one foreign key each Looking for some advice related to optimal design in the following scenario.

There is a Cases table (represents cases of inventory)
There is a LocationInventory table (represents locations with inventory)
Then I have an InventoryNeed table or tables (this is the crux of the question), that needs to account for cases and locations.

Option A:

One table with 2 foreign key columns in which one and only one of the foreign keys would be populated.

Table: Inventory Need

CaseId (FK)
LocationInventoryId (FK)
NeededQuantity

In this case either CaseId or LocationInventoryId would null and the other populated.

Option B:

Two tables for each need type that will frequently be UNION'ed to get summary data.

Table: InventoryNeedsCases

CaseId (FK)
NeededQuantity

Table: InventoryNeedsLocations

LocationInventoryId (FK)
NeededQuantity

Option C:

One table with no referential integrity.

Table: InventoryNeedsCases

NeedType (values of Case or Location)
NeedId (represents the primary key of either Case or LocationInventory based on NeedType).
NeededQuantity

And the winner is? I'd probably narrow it down to say A or B to ensure data integrity...but not sure which is best from there. Or maybe there is an option D (like creating a base table with common columns...)

UPDATED SCENARIO

When I posted this last night, I was only thinking downstream, but there are also upstream dependencies to these same tables. I made some drawings that may hopefully explain it better. With this advent, option B starts to explode the nbr of tables involved, and after reading this SE answer...I am now leaning more towards A.

Pics below. Then red represent Inventory Needs, Green represents Inventory sources to meet those needs. And yellow is the question at hand...how to link the reds and greens efficiently.

Option A Pictorial - with more explanation and context

Option B - removed context for clarity

Yes, there is an option D that would involve a supertype table for the subtype tables Cases and LocationInventories. You can search for the "supertype/subtype" pattern and "shared primary key" for additional info.

Option D is moved below and I'll add the other options for comparison:

Option A

Cases
    CaseID   PK
    ... more columns

LocationInventories
    LocationInventoryID   PK
    ... more columns

InventoryNeeds
    ?        PK
    CaseID   FK -> Cases (CaseID)
    LocationInventoryID   FK -> LocationInventories (LocationInventoryID)
    NeededQuantity
        CK: CHECK ( CaseId IS NULL AND LocationInventoryID IS NOT NULL
                 OR CaseId IS NOT NULL AND LocationInventoryID IS NULL )

Option B

Cases
    CaseID   PK
    ... more columns

LocationInventories
    LocationInventoryID   PK
    ... more columns

InventoryNeedsCases
    ?        PK
    CaseID   FK -> Cases (CaseID)
    NeededQuantity

InventoryNeedsLocations
    ?        PK
    LocationInventoryID   FK -> LocationInventories (LocationInventoryID)
    NeededQuantity

Option C

Without foreign keys, you lose referential integrity. I see no reason to follow this option.

Cases
    CaseID   PK
    ... more columns

LocationInventories
    LocationInventoryID   PK
    ... more columns

InventoryNeeds
    ?        PK
    NeedType CK: CHECK ( NeedType IN ('C', 'L') )
    NeedID   FK
    NeededQuantity

Option D

With the addition of this one table, the constraints are quite simplified. The only thing that gets complicated is the insertion (and deletion) of rows to the two tables (Cases and LocationInventories) that will involve an additional INSERT into (or DELETE from) the Bases Needs table.

Needs
    NeedID   PK

Cases
    CaseID   PK  FK -> Needs (NeedID)
    ... more columns

LocationInventories
    LocationInventoryID   PK  FK -> Needs (NeedID)
    ... more columns

InventoryNeeds
    ?        PK
    NeedID   FK -> Needs (NeedID)
    NeededQuantity

Option E

This is a modified D option that combines the NeedType of C option, without losing any referential integrity. It also avoids the (accidental or not) pitfall of option D, that allows a Need to be inserted in both Case and LocationInventory.

Needs
    NeedID   PK  UQ1
    NeedType     UQ1  CK: CHECK ( NeedType IN ('C', 'L') )

Cases
    CaseID   PK  FK1 -> Needs (NeedID, NeedType)
    NeedType PK  FK1
    ... more columns
        CK: CHECK ( NeedType = 'C' )

LocationInventories
    LocationInventoryID   PK  FK1 -> Needs (NeedID, NeedType)
    NeedType              PK  FK1
    ... more columns
        CK: CHECK ( NeedType = 'L' )

InventoryNeeds
    ?        PK
    NeedID   FK1 -> Needs (NeedID, NeedType)
    NeedType FK1
    NeededQuantity


How to get (format) a Firebird v3 DATEDIFF() to the number of years, months and days all together? In Firebird 3 I can use DATEDIFF() with either YEAR, MONTH or DAY to get the number of years, months or days respectively.

I need a way to get all-in-one years, months and days.

For example: 412 days should be represented as 1 year, 1 month and 16 days.

are you looking something like this:

declare @d1 datetime, @d2 datetime, @y int, @m int, @d int
set @d1 = '2018-10-26'
set @d2 = '2019-12-12'

set @y = datediff(year, @d1, @d2)
set @m = datediff(month, dateadd(year, @y, @d1), @d2)
if dateadd(month, @m, dateadd(year, @y, @d1)) > @d2 set @m = @m - 1

set @d = datediff(day, dateadd(month, @m, dateadd(year, @y, @d1)), @d2)
print cast(@y as nvarchar) + ' year(s) ' + cast(@m as nvarchar) + ' month(s) and ' + cast(@d as nvarchar) + ' day(s)'

Results:

  1 year(s) 1 month(s) and 16 day(s)


"copy to tmp table" for 24 hours, then timeout; when ALTERing to add PK & AI I downloaded the Wiktionary XML dump, and ran mwdumper.jar to insert the data into MySQL.

Following the advice in the manual, I commented out the PRIMARY KEY AUTO_INCREMENT part of the CREATE TABLE statements, for page, revision and text (which have millions of rows in each, and very large BLOB's); in order to aid in a much faster import time.

After the data had been inserted, I ran the CREATE INDEX statements, and these executed fine (in ~30 seconds each).

My problem is, I'm now trying to add the PK's in, and just on the first line, it ran for 86,400 seconds (24 hours), then timed out!

SHOW FULL PROCESSLIST shows State: copy to tmp table.

Here are the queries I'm trying to run - it's stuck on the first one:

ALTER TABLE revision MODIFY COLUMN rev_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE text MODIFY COLUMN old_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE page MODIFY COLUMN page_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT;

When I ran SHOW GLOBAL STATUS while the query was running, the Handler_read_rnd_next variable was increasing, and there were no other connected users or applications running on the db, to otherwise influence that var.

How can I speed this query up? It's running on a quad-core 3.5GHz development machine with 16GB of RAM, but it's barely using any of this power.

The only options that have been changed from the default install are:

max_allowed_packet = 1G
innodb_log_file_size = 100M
innodb_log_buffer_size = 100M

And in MySQL Workbench:

Any advice is appreciated!

Well, there's nothing to be done at this stage. You can't terminate the process (rather, you shouldn't) while running. You cannot modify major memory settings without restarting MySQL.

I, for one, am not enthusiast about loading all table data then loading indexes. However, doing this for the PRIMARY KEY is plain wrong. The advice you read related to secondary indexes. With adding a PRIMARY KEY InnoDB will rebuild your entire table all over again; so I'm afraid your entire first load step was redundant.

Nevertheless 7 hours for PK compared to 30 seconds for secondary index does not make sense. What is your innodb_buffer_pool_size? Which disks? RAID? Which file system?


AutoIncrement non-primary key or Make unique composite key? I have a db with a table that has a rollover value such as OrderNumber and another column is year.

This is the primary key - a composite key - but there are other tables with which I need to use a foreign key relationship - I really would prefer to have an auto increment surrogate key for this table, the trick though is that OrderNumber and Year must be unique. IS there a good way to handle this?

I was reading here for some inspirations. Composite field in SQL Server, does it exist?

As Vérace suggested, you can do both: create a surrogate (auto-increment) key to simplify joins and related table structures, and add a unique constraint on the two current PK columns to continue to enforce the proper data integrity.

In SQL Server, multiple columns can participate in a unique index / constraint:

ALTER TABLE dbo.Orders
ADD CONSTRAINT UQ_Year_OrderNumber
UNIQUE ([Year], OrderNumber);

Thus there is no need to create a computed column to combine the two columns.


MySQL tmp and tmpfs disk I have mysql and i use a tmpfs disk that is 768 M. Mysql uses about 70% of the memory. There is about 1170 M available.

If mysql uses ram to the point there is less than 768; will my RAM disk be limited in size?

In my opinion, the RAM given over to a RAM disk would be better utilized for other caching.  If InnoDB, give it to innodb_buffer_pool_size.  For MyISAM, give a little to key_buffer_size, leave most for data caching, which the OS does.


Why a SQL Server Table takes 1GB more after adding 55MB of data? I've found that one customer database size exploded between yesterday and today.
After investigation I've found a design problem in a table schema but after doing some math I know that 55MB of data have been added to the table but the table size is now 1GB while the original size of the table was 70MB.

For additional information, 55MB of data was added by adding more than 2000 rows with a varchar(max) field filled with the data.

Here is the before/after analysis :

Yesterday

Today

Can you explain to me why a table took 70MB now takes 1GB after adding 55MB of data ?

There is not any index on the varchar(max)

The free space is very low, it is not related to the autogrowth. And the autogrowth is not configured with a percentage but with a fixed growth size.

Here is the creation script of the table :

USE [DuoClient20140003]
GO

/****** Object:  Table [dbo].[tContactHistory]    Script Date: 23/05/2016 10:53:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tContactHistory](
    [KeyCH] [int] IDENTITY(1,1) NOT NULL,
    [DateCH] [datetime] NULL,
    [KeyType] [int] NULL,
    [KeyF] [int] NULL,
    [KeyI] [int] NULL,
    [KeyCA] [int] NULL,
    [Content] [varchar](max) NULL,
    [KeyCom] [int] NULL,
    [KeyMH] [bigint] NULL,
    [KeyH] [bigint] NULL,
 CONSTRAINT [tContactHistory_PK] PRIMARY KEY CLUSTERED
(
    [KeyCH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tAddrCarnet_tContactHistory_FK1] FOREIGN KEY([KeyCA])
REFERENCES [dbo].[tAddrCarnet] ([KeyCA])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tAddrCarnet_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tComments_tContactHistory_FK1] FOREIGN KEY([KeyCom])
REFERENCES [dbo].[tComments] ([KeyCom])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tComments_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tFoyers_tContactHistory_FK1] FOREIGN KEY([KeyF])
REFERENCES [dbo].[tFoyers] ([KeyF])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tFoyers_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tIndivs_tContactHistory_FK1] FOREIGN KEY([KeyI])
REFERENCES [dbo].[tIndivs] ([KeyI])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tIndivs_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tMailHistory_tContactHistory] FOREIGN KEY([KeyH])
REFERENCES [dbo].[tMailHistory] ([KeyH])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tMailHistory_tContactHistory]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tModifsHistory_tContactHistory_FK1] FOREIGN KEY([KeyMH])
REFERENCES [dbo].[tModifsHistory] ([KeyMH])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tModifsHistory_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tTabShort_tContactHistory_FK1] FOREIGN KEY([KeyType])
REFERENCES [dbo].[tTabShort] ([KeyT])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tTabShort_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tContactHistoryKeyI_Chk] CHECK  (([KeyI]>=(1)))
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tContactHistoryKeyI_Chk]
GO

System procedure sp_spaceused tells you how the data is distributed, and is usually more than accurate enough. Run the following simple TSQL:

DECLARE @R TABLE (TabName sysname, [Row Count] bigint, Reserved varchar(128), Data varchar(128), [Idx Size] varchar(128), Unused varchar(128))

INSERT INTO @R
exec sp_spaceused 'tContactHistory'

SELECT TabName
    , [Row Count]
    , cast(round(cast(replace(Reserved, ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Reserved MB]
    , cast(round(cast(replace(Data, ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Data MB]
    , cast(round(cast(replace([Idx Size], ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Idx Size MB]
    , cast(round(cast(replace(Unused, ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Unused MB]
FROM @R

Tell us what you find. If there's a problem with fillfactor, datatypes, or just plain old overzealous indexing, this should give enough of a clue as to where to go next, at least.

Update: the result was:

Well your results are odd. It implies the data is actually used. I also see someone already asked you before how you verified the size of the data, and you replied that you did so by calculating it from the field size. Are you sure you did so correctly? Is it possible that empty strings or special characters were introduced that you can't see, but still take up space? Or that you're dealing with CHAR as opposed to VARCHAR fields with the same end result?

See DATALENGTH function, for instance, to check the length of your variable length columns.

-- For example:
SELECT DATALENGTH(myVarCharColumnName) DLen
FROM tContactHistory
ORDER BY DLen desc
-- Note by checking the column type and storage size from MSDN,
-- you can now use this information to calculate the storage size.

Also, have you tested this hypothesis by for instance copying over part of that data to a new, dummy table and see what happens? Like

SELECT TOP 1000 *
INTO myStorageSizeTest
FROM tContactHistory
WHERE ... -- Insert whatever condition here to only select the new rows where this space problem appeared

Etc. Don't be afraid to test and get creative.


Significance of ".WORLD" on end of Oracle connection What is the significance of having ".WORLD" on the end of an Oracle connection name?  I've found things to still connect without that suffix.  I've also noticed that both the client tnsnames.ora file and the server tnsnames.ora file must both have this suffix (or none at all) in order to communicate.

.WORLD is a type of domain. You need it in tnsnames.ora if this is configured in your sqlnet.ora file (names.default_domain parameter). If this is not present in sqlnet.ora and you have it in tnsnames.ora, you have to append it manually. Sqlnet.ora

  The value of this parameter is appended to the net service name if the net service name doesn't already have a domain.

The .WORLD isn't needed at all if names.default_domain isn't set. In order to get the client working, just make sure the tnsnames.ora file on the server (in the network/admin folder) matches the tnsnames.ora file on the client (in the client network/admin folder).


How to detect a circular reference in a hierarchy table? I have table like this:

CREATE TABLE MyTable (
    Id INT NOT NULL PRIMARY KEY,
    ParentId INT NOT NULL
);

ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id);
select * from MyTable
desc MyTable

INSERT INTO MyTable (Id, ParentId) VALUES (0, 0);
 INSERT INTO MyTable (Id, ParentId) VALUES    (1, 0);
 INSERT INTO MyTable (Id, ParentId) VALUES    (2, 4);
 INSERT INTO MyTable (Id, ParentId) VALUES    (3, 2);
 INSERT INTO MyTable (Id, ParentId) VALUES    (4, 3);

How to find a circular reference in table references?

Here's a thought:

Add a column depth SMALLINT UNSIGNED NOT NULL DEFAULT '0'
Repeatedly bump depth to be at least 1 more than the parent:

UPDATE MyTable AS child
  JOIN MyTable AS parent ON child.ParentId = parent.Id
  SET child.depth = GREATEST(child.depth, parent.depth + 1)
  WHERE child.Id != 0;   -- the root says at depth=0
If rows affected drops to 0, you have no loops.
After awhile, any nodes in loop(s) will have large depth values.


tempdb on raid1 isolated or raid10 shared with high write db PROPOSAL 2

8x600gb raid 10 mdf array

4x600gb raid 10 ldf array

  PROPOSAL 3

8x600gb raid 10 mdf array

2x600gb raid 1 ldf array

2x600gb raid 1 tempdb mdf/ldf array

  READ/WRITE STATS ON CURRENT SAN

db.2

reads= 36m

writes= 341m

db.s

reads= 315m

writes= 343k (as in 343,000)

TEMPDB

reads= 69m

writes= 39m

RAID 1 as opposed to current RAID10 is a question mark as our current SAN uses RAID10 for everything (mdf raid 10 , ldf raid 10).

As can be seen above:
DB.2 is very write intensive in both its log file and mdf I would presume.
DB.s is very read intensive but this maybe left on current san.

I have been looking studies that benchmark using the same test RAID1 and RAID10.

From what i understand based on a score of 1 to 5 (http://www.pcguide.com/ref/hdd/perf/raid/levels/comp-c.html) :

RAID1 reads = 2 out of 5

RAID1 writes = 3 out of 5

RAID10 reads = 4.5 out of 5

RAID10 writes = 3.5 out of 5

If this is to be believed and performance monitor shows the ldf array with no reads but consistent writes then putting the LDF on a RAID1 array should have little performance impact making RAID1 for log files acceptable. Fine, great.

Looking at a RAID1 array for TEMPDB however we can tell by the read write stats (above) that the read write values are heavy on both counts.

Therefore moving tempDB MDF from raid10 array1 and the ldf away from raid10 array2 to merge on a raid 1 single array on a new san then becomes a question of the how well tempDB READS perform on RAID1 (as writes would not be a problem as literature suggest raid1 and raid10 writes speeds are similar).

In short under proposal 3 my TEMPDB reads may suffer but would it be better or worse than contended reads when sharing disk space with a write intensive database like db.2 ???

This is the final question in need to answer.

Now bearing in mind on the current san db.s currently hosts 315m reads which is hosted on the same drives (mdf/ldf array) as db.2 and its relative 341m writes.

In this context relative stats from tempdb 69m reads and 39m writes should not have nearly as big an impact as db.s reads could possibly have. And it is perfectly possible that the drop from raid10 to raid 1 will have an impact on the write performance of tempdb.

So again i refer to this article regarding the isolation of tempdb.
http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

Q: How can i gauge of the drop from RAID10 to RAID1 but isolating tempDB on its own array is better than sharing a RAID10 array with a database that has 315m writes but little reads ? (assuming db.s remains on current san)

SIMPLY:

tempdb 69m reads on raid1 isolated

versus

tempdb 69m reads + db.2 36m reads on a shared raid 10 array.

Thank you.

I found these articles helpful:

Why RAID10 (0+1) superiority is a myth (specifically versus RAID1).
And this is an additional guide on raid level comparison.

Further reading suggests that the benefits of using RAID1 for log files (assuming isolated from data files) is lost when more than 1 log file exists on that RAID1 array. This is due to the sequential nature of the transaction log writes to disk. The sequential write benefit is lost when multiple log files are accesses on the same RAID1 array due to the random nature of access on a spinning disk. This would suggest that RAID10 is the better choice in a multiple DB environment unless you have the disks to isolate each log file.

These stats below sold me on proposal 3, isolating tempDB on RAID1 by stealing 2 disks from LOG array moving log array from RAID10 to RAID1.  Basing much of this on RAID1s ability to maintain good WRITE speed.

TEMPDB is clearly under more stress than I have realised.

These table rankings ring true for snapshot values during normal operation (not just the accumulated totals) as we do have intensive out of hour routines.

TOTAL IO:

db.tempdb.mdf = 144,747,290,352

db.2.mdf =         100,482,243,080

db.2.ldf =           2,571,065,773

db.s.mdf =         1,702,508,040

db.s.ldf =           223,032,162

TOTAL READS:

DB.2.mdf =         84,851,614,280.00

db.tempdb.mdf = 72,271,813,552.00

db.s.mdf =         1,691,504,864.00

db.2.LDF=          93,822,304.00

TOTAL WRITES:

db.tempdb.mdf = 72,475,476,800

db.2.mdf =         15,630,628,800

db.2.ldf =           2,477,243,469

db.tempdb.ldf =   222,946,079

One possible concern maybe the additional of the tempdb ldf and mdf on the same raid1 array but if this is a problem tempdb.ldf can be moved to the log array.

These are helpful links that explain TEMPDB usage, what it does and how it may effect my apps:

http://bradmcgehee.com/wp-content/uploads/presentations/Optimizing%20tempdb%20Performance_SQLCONN.pdf
http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx


What's the point of the relationship diamond in Entity-Relationship diagrams? I'm drawing an ER diagram for an ecommerce site that I'm building, and noticed something: the diamond that I am drawing between two models (or entities), which I understand is meant to symbolize the relationship between the entities, seems irrelevant.

The diagram could be drawn without the diamonds, and it would still show everything that I am conveying if I just connected entities with -|---|0- and similar connectors, without a diamond in between.

So what are the diamonds for?

As James K already said, there is more than one set of rules for forming ERDs, and more than one answer to your question.  But, to deal with your question from a generic point of view, here goes.

Sometimes the diamond is irrelevant.  The line between two boxes depicts the relationship, and often says everything that needs to be said.  Here are some cases where the diamond makes the diagram more legible.

First, relationships with attributes.  Some ERD diagrammers allow attributes to describe relationships in addition to entities.  For example, if an enrollment is a relationship between a student and a course, then the grade the student got might be an attribute of the relationship.  It is certainly not an attribute of just the student, or of just the course.  If you follow this practice,  and if you list attributes inside of boxes, it behooves you to put a diamond in for the enrollment relationship and to put the grade attribute inside of the diamond.

Some ER diagrammers prefer to make enrollment into an entity, instead of leaving it as a relationship.  In that case, the diamond becomes a box.

Second, some relationships are not binary, but ternary, quaternary, or n-ary.  That is, instead of one line connecting two boxes, there are several lines connecting 3, 4, or more boxes.  In this case, those lines all come together somewhere.  It behooves you to put a diamond in where they all come together.

As an example, an airline reservation might have a line connecting it to the passenger, another line connecting it to the flight, and a third line connecting it to the airport where the passenger will board.

Again, some diagrammers prefer to to make n-ary relationships into entities.  And again, that means using a box in place of a diamond.

There's a fancy word for turning a relationship into an entity.  It's called "reifying".

Having said all that, the majority of ERDs I've ever made for myself have no diamonds on them.


Persistent Severity 016 alerts: "Change Tracking autocleanup is blocked on side table" We had a large database running on a SQL Server 2014 Enterprise instance (evaluation license). Due to cost constraints, we had to move it to a new SQL Server 2017 Standard instance (per-core license; just updated to CU6). EDIT: The database is in SQL Server 2014 (120) compatibility, and was transferred by restoring full and log backups.

Everything is working fine except for change tracking. We use change tracking to locate recent changes and keep a denormalized table up to date; the denorm table is used for rapid filling of a grid for the web application.

Change tracking is working, but apparently autocleanup is not. We are getting Severity 016 alerts:

  Change Tracking autocleanup is blocked on side table of tablename. If the failure persists, check if the table tablename is blocked by any process.

These come about every half hour for each of the tables that is complaining (about 4 different tables).

We've tried the manual cleanup described here ... it too presents "side table is blocked" errors.

So here are my choices:

Turn off this specific alert and hope nothing blows up.
Turn off autocleanup; schedule a manual cleanup during a daily maintenance window.
Not sure what else I can do.

Besides "what should I do to stop this?", my side question is "why is this happening with 2017 Std when it didn't happen with 2014 Enterprise?"

It seems to be a known issue and has already been recognized by Microsoft. You may read resolution as below:

Resolution

  A fix for this issue is included in the following updates
  for SQL Server:

  Cumulative Update 10 for SQL Server 2017

  Cumulative Update 3 for SQL Server 2016 Service Pack 2

  Cumulative Update 1 for SQL Server 2014 Service Pack 3

  With this fix, you will not see continuous messages any longer. The
  frequency of the error 22123 messages, if any, will not be more than
  once every 30 minutes. To completely eliminate these messages, enable
  trace flag 8293.

Listed are the version which has this issues:

  SQL Server 2017 Developer
  SQL Server 2017 Enterprise
  SQL Server 2017 Enterprise Core
  SQL Server 2017 Standard Windows
  SQL Server 2016 Developer
  SQL Server 2016 Enterprise
  SQL Server 2016 Enterprise Core
  SQL Server 2016 Standard
  SQL Server 2014 Developer
  SQL Server 2014 Enterprise
  SQL Server 2014 Enterprise Core
  SQL Server 2014 Standard

Please read more from link.

Hope above helps.


Job waiting on CXCONSUMER We have got a procedure on SQL Server agent jobs and this job started to hang on transaction for many hours with wait stats CXCONSUMER and never complete. The problem is after we noticed that it was hanging we kill the job and started manually during and the job was working fine and completing. But right now neither starting manually or on sql agent job seems never completing and hanging on waits with CXCONSUMER.

According to sp_who2, sp_whoisactive and sql server profiler there are no active locks it is waiting for. It keeps yielding CXCONSUMER and it's processor time and does basically nothing.  Here is the execution plan after 3h -

And the other symptoms is the tempdb is unaccesible through properties when this job runs I get this error "Property SpaceAvailable is not available for Database '[tempdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights" despite there are available space on tempdb.

I will appreciate your help in this matter.

Thank you and Best Regards,

There are really two questions in this question:

Why is the query slow?
Why are tempdb properties inaccessible?

Let's start with #2.
Why are tempdb properties inaccessible?
You get this error:

Cannot show requested dialog. (SqlMgmt) Property SpaceAvailable is not
available for Database '[tempdb]'. This property may not exist for
this object, or may not be retrievable due to insufficient access
rights. (Microsoft.SqlServer.Smo)

This will happen when you create a temp table inside a transaction. The CREATE TABLE holds metadata locks on system tables that blocks the queries to populate the database properties window, and causes the opening of that dialog to time out & show this error.
If you run this statement in SSMS, and try to open the tempdb properties window at the same time, I expect it will open just fine:
SELECT o1.object_id
INTO #blep
FROM sys.objects o1
CROSS JOIN sys.objects o2
CROSS JOIN sys.objects o3
CROSS JOIN sys.objects o4
CROSS JOIN sys.objects o5;

However, with the same query dropped into a transaction, and you will get the above error that you reported getting:
BEGIN TRANSACTION
SELECT o1.object_id
INTO #blep
FROM sys.objects o1
CROSS JOIN sys.objects o2
CROSS JOIN sys.objects o3
CROSS JOIN sys.objects o4
CROSS JOIN sys.objects o5;

While you didn't provide the code beyond what is included in the plan, the behavior would indicate that your SELECT...INTO is happening within a transaction, similar to the above example.
I would recommend never creating a temp table within a transaction, due to the blocking on system tables within tempdb. This affects concurrency, in addition to operational issues like the one with the database properties window.
If your procedure requires the use of transactions, you should create the table OUTSIDE the transaction, then begin the transaction & do the necessary work. If you depend on the behavior of SELECT...INTO to define the schema (ex, with a dynamic/unknown schema), use SELECT...INTO...WHERE 1=2; to create the table empty, then proceed after that to begin your transaction and do work.
Now, on to the other question:
Why is the query slow?
I'm not sure which line in the sp_whoisactive output correlates to your query. All of them? Session_id 797 is showing high CXCONSUMER waits, and has been running for 2 hours. I'll assume that is the problematic session. It has performed almost 57.9 million milliseconds of CPU cycles (967 minutes) in 2h 12mi of "real" time (132 minutes). That's about 7x more CPU time than "real" time--which certainly indicates that it is busy doing parallel work.
I can tell from the schema that this is an SAP database. The SAP schema is a bit of a quirky one, with the MANDT column being the leading column on every clustered composite index. This column is basically "company code" and used to separate data for various subsidiaries. SAP supplies this column within the join/filter predicate on EVERY query to ensure it's working with data for the right company. If your company doesn't keep separate books for separate subsidiaries, then the MANDT column is always the exact same value.
Because the non-selective predicate is supplied, it can trick SQL Server into showing a seek on the clustered primary key, when it's really doing a table scan. If every row has a value of AMTWO for MANDT, and you query AND MANDT = 'AMTWO', the optimizer might think "Hey, that's the leading column on the PK, I can seek to that!" But in reality, it seeks to the first occurrence of that value (ie the first row) and reads through to the last occurrence of that value (ie, the last row). Because every row is exactly the same value, so it has to scan every row, even though it shows as a seek in the plan.
The reality is that there's a better index to use, but SQL Server didn't realize that another index (or combination of indexes) would perform better.
Additionally, depending on your SQL Server version and whether you are using Trace Flag 2389, SAP often struggles with the "Ascending key problem" related to their heavy use of dates to key transactional data.
Both of these problems can be resolved by aggressively updating statistics on your SAP database.
There certainly could be something more going on with your query more than stats, but based on my experience with SAP, ensuring you have up-to-date stats would be at the top of my priority list.
You say it "started to hang on transaction for many hours"... how fast was it running before? Was it processing a similar amount of data was fast? The plan indicates it's working off just over a million rows in #repositioning, 200k rows in #stock and 65 million rows in MSEG---so I wouldn't expect the query to be extremely fast to begin with.
After you update stats, I'd be keen to capture actual IO & CPU costs for an execution of this code, using something like Plan Explorer, as that often gives much more insight into the problem, particularly if it's a case where SQL Server has selected a "bad" plan, and a better/faster plan exists but wasn't chosen.


Unexpected behaviour when got error in the middle of mysqldump backup file import i am using mysql 5.5.40 version. i am importing a large .sql file having around 36000 lines using a script. The script contains below statements.

mysql -uxx -p < dumpfile.sql 2 > error.log
cat error.log|mail -s "import error" a@something.com

Sometime after executing the script, I got the mail saying "ERROR 1146 (42S02) at line 35687:Table 'dbname.tablename' doesn't exist. As i am not using force option while importing , it should not execute lines from 35688 to 36000 lines. it should abort when it hit with error. But when i have checked manually, it seems the lines are executed. Is it the expected behavior?

We know its expected behaviour when using source command in mysql command line client.

The behaviour is correct and it is incorrectly observed as per update from the questioner.
This got verified by further debugging the dumpload adding a debugging select syntax after the erroneous SQL in the dump.


Is compression used in RDS SQLServer database I have few Clarification regarding AWS RDS SQL Server.

Do we have any compression technique to compress the data loaded into RDS SQL server?
Is there any option or parameter to compress the data and load into RDS SQL server?

After you have loaded the data into a table, and as long as you have signed up for a 2016 Standard Edition or above, you can use the Data Compression that is included within Sql Server:

CREATE TABLE t1
(
    ... -- whatever columns you need.
)
WITH
(
    DATA_COMPRESSION=PAGE
);

You can also create compressed indexes in a similar fashion:

CREATE INDEX idx_t1 ON t1(...) WITH (DATA_COMPRESSION=PAGE);

For more information, check out the documentation for Enabling Compression on a a Table or Index.


How to create new users for restored databases? I am not too familiar with SQL Server and am facing the following problem: The server was freshly installed and the only existing login is sa.

Then a couple of databases were restored from earlier backups.

Now I'd like to run a few SQL scripts that create new users for these databases, running as sa, but am getting the message that I am not allowed to do this.

The thing you're trying to fix is what's known as "Orphaned Users" - I blogged about this a while ago, at http://thelonedba.wordpress.com/2013/02/22/restoring-into-a-different-environment-security-problem-orphaned-users/

However, without seeing your exact error message, it's hard to help you with specifics.


Question on EDR: a plant may have one or more harvesting seasons I working on the following ERD, trying to understand what is the best way to connect the entities in the example of plants! For now I'm just focusing on the right side of the ERD (yellow, orange, green)!
Logic in English:

Each plant has planting / growing / harvesting requirements!
The planting / growing / harvesting requirements may have different values and descriptions!
All plants have only one table to hold planting requirements.
All plants have only one table to hold growing requirements.
However some plants may have one or more harvesting requirements.

My question:
What is the best way to show the harvesting requirements?
An example:

Cucumber:

planting requirements values
growing requirements values
one harvesting season:

values

Peas

planting requirements values
growing requirements values
two harvesting season:

values
values

UPDATE:
I'm having some issues implementing the above ERD to MySQL!
Here is how I create the two tables:
CREATE TABLE plant(
    plant_id INT AUTO_INCREMENT,
    taxon_name VARCHAR(25),
    common_name VARCHAR(25),
    general_description TEXT,
    image BINARY,
    cultivation_group_id INT,
    nutritional_info_id INT,
    disease_id INT,
    planting_req_id INT,
    growing_req_id INT,
    harvesting_req_id INT,
    PRIMARY KEY (plant_id)
);

CREATE TABLE harvesting_req(
    harvesting_req_id INT AUTO_INCREMENT,
    number_of_harvests INT,
    descriptive_growing_season VARCHAR(25),
    min_length_of_growing_harvest FLOAT,
    max_length_of_growing_harvest FLOAT,
    harvest_start_date DATE,
    PRIMARY KEY(harvesting_req_id)
);

ALTER TABLE plant
ADD FOREIGN KEY(harvesting_req_id)
REFERENCES harvesting_req(harvesting_req_id) ON DELETE SET NULL;

Here is the table for harvesting_req using SELECT * FROM harvesting_req;:

harvesting_req_id
number_of_harvests
descriptive_growing_season
min_length_of_growing_harvest
max_length_of_growing_harvest
harvest_start_date

1
2
Annual
55.0
75.0
2017-01-15

2
2
Annual
55.0
75.0
2017-06-30

But when I try the following query I get only one start date:
SELECT common_name, harvest_start_date
FROM plant, harvesting_req
WHERE plant.harvesting_req_id = harvesting_req.harvesting_req_id;

common_name
harvest_start_date

Peas
2017-01-15

I would tend towards a one-to-many relationship between Plants and Harvests.
i.e. the Harvests table has a foreign key to Plants allowing as many harvests as desired.  The Harvests table would have all the details relating to a single harvest, such as Start Date, Minimum Duration of Harvest, etc.
I'd combine the two green tables.  If the start date for one harvest is earlier than the start date for the 2nd harvest, then that's just showing them correctly ordered in the presentation layer (i.e. by an ORDER BY clause on StartDate).
I've created a simple fiddle to show you how I'd implement the design here.
This is the DDL:
CREATE TABLE plant(
    plant_id INT AUTO_INCREMENT,
    taxon_name VARCHAR(25),
    common_name VARCHAR(25),
    PRIMARY KEY (plant_id)
);

CREATE TABLE harvesting_req(
    harvesting_req_id INT AUTO_INCREMENT,
    plant_id INT,
    number_of_harvests INT,
    harvest_start_date DATE,
    PRIMARY KEY(harvesting_req_id)
);

ALTER TABLE harvesting_req
ADD FOREIGN KEY(plant_id_fk)
REFERENCES plant(plant_id) ON DELETE SET NULL;

JOINing the tables looks like:
SELECT p.taxon_name
    , p.common_name
    , hr.*
FROM plant p
    INNER JOIN harvesting_req hr ON p.plant_id = hr.plant_id;

The output looks like:

taxon_name
common_name
harvesting_req_id
plant_id
number_of_harvests
descriptive_growing_season
min_length_of_growing_harvest
max_length_of_growing_harvest
harvest_start_date

Taraxacum
dandelion
1
1
2

2021-04-15

Taraxacum
dandelion
2
1
3

2021-07-01

I'm not convinced you need a number_of_harvest column in the harvesting_req table since you'd have a row per harvest.