Clustered Index Help, where did i go wrong? I was asked to review a very busy table and identify any areas of improvement.
I have very limited power to change the table just the indexes.
Table information

240 columns
~5 million rows
Read and updated by about 30 applications, which range from websites to polling applications.
Each row represents a contract and its state based on three flags (varchars of size 5, 8 and 8).
A row's life cycle goes from start to end through these three flags, and eventually is done.
A row typically is updated or changed 10 to 30 times throughout this lifecycle.
The primary key is a combination of an id column, a guid column and a company column.
Table has 40+ indexes, the majority of which are duplicates and unused.  This is based on the sys.dm_db_index_usage_stats DMV, running twice a week for the past 7 weeks.

The current clustered index on this table has five columns:

Company column (50 distinct values)
Region column (21 distinct values)
FlagA column (8 distinct values)
FlagB column (24 distinct values)
FlagC column (5 distinct values)

My understanding is a clustered index should adhere to the following properties.  Source

Unique
Static
Narrow
Ever increasing.

The current clustered index is none of those.

There is no unique Id.
The three flags are updated constantly.
There can be 5000 rows any given moment that have the same values for all 5 columns.

So my assumption was to correct this with a clustered index on the Id column - an integer that is NOT an identity but maintained through a counter table (read value, add 1, update counter table).
I created a clustered index on the Id, not using the primary key because I believed adding the guid and company column would not give me any benefit.
I then created a nonclustered index which holds the Company, Region and 3 flags.
In a test environment the stats were looking good, user_updates were lower etc. But the overall performance of applications against this table was terrible. The most common queries against this table are:
SELECT *
FROM table
WHERE ID = 1234;

And
SELECT *
FROM table
WHERE Company = 'company'
AND Region = 'region'
AND flagA= 'A'
AND flagB = 'B'
AND flagC = 'C';

What I have missed? Are there exceptions to the above clustered index rules?
Would the clustered index benefit from adding the company and region to the id?

It is my understanding if the clustered index is on non-static values it needs to constantly reorganize itself and the other nonclustered indexes, which at this point we still have 40 plus...to be later removed. Wouldn't I have seen a gain there?
Added information.

Both environments are SQL Server 2008 R2
Have the same OS, memory etc.
The only difference was the clustered index.
I wrote the SELECT * to be concise in the above example.
Test and production environment are "the same", as best they can be: Production was facing a heavier load, but both were running same applications, load balanced etc. Test was significantly slower.
We ran with the new clustered for a week and a half before we rolled back to the original setup. Initially, stats were updated and specific stored procs were recompiled as slow performance started to crop up.
I do agree that with the multiple column index at least the table was organized better than just on Id. I want to reattempt with Company, Region, and Id as the CI, but leave the three flags out of it.

What have I missed?

Are there exceptions to the above clustered index rules?

The general guidance for selecting a clustered index is good, but there are sometimes additional considerations to be taken into account. Occasionally, these extra factors can be more important than the general 'rules'.

Your scenario is somewhat "special" in that you have a very wide table, with a set of queries that request a (presumably) unpredictable set of columns, although the query predicates are normally the same.

The original clustered index arrangement was likely expensive for operations that change data, since changing any part of the clustering key means changing all nonclustered indexes as well. In addition, physically moving whole rows is costly in terms of log generation, especially where a page split occurs.

That said, once a clustered index has been modified and split a fair bit, it will contain quite a bit of free space, making future movements less intensive, just as if a sensible fill factor had been set in the first place, and maintained as part of normal index maintenance.

It seems likely your production system had fallen into a sort of equilibrium, where page splitting occurred at a steady, reasonable rate. Nonclustered index maintenance would still be relatively expensive, but it seems that was not a dominant factor.

The crucial advantages of the original indexing arrangement were:

The clustered index (Company, Region, FlagA, FlagB, FlagC) matches the predicate of:

SELECT {unpredictable column list}
FROM table
WHERE Company = 'company'
AND Region = 'region'
AND flagA= 'A'
AND flagB = 'B'
AND flagC = 'C';

...while at the same time providing access to whichever columns are listed in the select list.
Queries of the form:

SELECT *
FROM table
WHERE ID = 1234;

...were adequately supported by the nonclustered primary key. This query always returns a single row, so only a single lookup via the clustered index is required after the row is located in the nonclustered index.

Changing to a clustered index on ID, and a nonclustered index on (Company, Region, FlagA, FlagB, FlagC) makes the second query a little more efficient (eliminating one lookup per query), but makes the first query much less efficient (replacing zero lookups with ~5000).

Moreover, it is very possible the optimizer might choose not to use the nonclustered index at all, estimating that a full scan of the table would be cheaper than the 5000 lookups.

You're probably better off leaving the two main indexes as they are for the time being, while you sort out the 40-odd nonclustered indexes, and analyse the workload for the minimal set of covering nonclustered indexes that would be required. Once that data is available, you will be in a better position to consider fundamental indexing changes.

You may also want to check the existing monitoring and maintenance routines for this table. In many scenarios, the 'rule-breaking' clustered index will be just fine, if the table is given a fill factor that just prevents significant page splitting before the next maintenance window comes around.


Wrong output getting from query Hi I have written this query as per my project requirement. But it is generating wrong output.
select (to_char((33)/nullif((17400/3600),0),'FM99,999,999'))::character varying as userdatas

currently it is generating 8 as output. But according to calculation it is different,
= 33/(17400/3600)
= 33/(4.8333333333333)
= 33/4.8333333333333
= 6.8275862068966
It should display 7
Can anyone help me with this issue ?

All your numbers are integers, so the whole expression is done using integer division. You need to either provide numeric constant, e.g. using 3600.0 or cast at least one number to numeric
select 33/(17400/3600::numeric);

returns 6.8276, when applying to_char() it's correctly rounded to 7:
select to_char(33/(17400/3600::numeric),'FM99,999,999')

Online example


Looking for example design of using an SQL database as a queue for throttling web service requests of multiple users I'm designing a system for multiple users to queue their requests to a web service which takes requests such as creating new entries, changing entries, requesting data.  Many of the web service calls we will be using take hierarchical data as parameters (heirarchies designed to match c# object structures) while others such as a request for the list of allowable entry types, require no parameters.   The queue is necessary because the web service has a 5 second or so throttling requirement, and only one web service login will be used by multiple users.

xml Data returned from the web service will eventually come back, be parsed by our own server app, and inserted to a response table of some sort so the user app can know the request was received and also get any data that are returned.

basic picture is

user app (multiple users) --> service request queue <--> server app <-->web service

and responses coming back similarly

user app (multiple users) <-- service response queue <--> server app <-->web service

I'm creating the user app and server app myself, and just trying to get a handle on how to design the service request queue and response queues which will be using sql server and trying to figure out just how many tables these queues will have since requests and responses come in hierarchical form (different hierarchy types are returned as well depending on the request), non hierarchical form both.

Objects like these are specified by the external web service API.  Users are sending different types of requests in different related forms of this object structure, which is going to be stored in the queue system/queues, then picked up by the server app for dequeuing to meet a throttle requirement for the web service.  The server app might also cancel irrelevant requests, such as if too much time has elapsed and it suspects a faulty or non-relevant request, or such as if the user app is not synchronized with the data stream from the web service.

Anyone have any simple example multiple user sql server queues used for this purpose?  Is there any good book on this topic? or web tutorial perhaps? seems necessary for throttling multiple users of any web service...

I did exactly this as a project back in 2008-2009, a web service (billing) that needed to handle 1M+ calls per day. I used SQL Server table as a queue, and the lesson from that project I distilled into the article Using Tables as Queues. Stick to the rules I lay out there, and specially don't try to add any whistles and bells to your table, use it exclusively as a queue. Under load I found that a critical issue was to batch dequeue (dequeue 100 web service call request in one DB operation) and handle the dispatch in the app (place the dequeue request into an in memory list, have the web call handlers pick up work from this list). Doing the web service call async is critical. Also, you must read about the ServicePointManager.DefaultConnectionLimit.


combining multiple rows with arbitrary number of columns I am attempting to format a query but, I don't think I know all the required methods to do what I want currently I have these results

Source
------
item    |PackQty|TotalUsage |JobDescription|job_usage
STE0295 |5      |20         |41721-2       |1
STE0295 |5      |20         |41925-1       |1
STE0295 |5      |20         |41949-1       |1
STE0295 |5      |20         |41997-1       |1

and I want to combine all item keys into a single row like this

STE0295 |5      |20         |41721-2       |1   |41925-1    |1  |41949-1    |1  |41997-1    |1

These results are not the entire set; there are thousand of unique 'item' records and they appear in arbitrary number of rows based on the different job Description. I have used pivots before and also the stuff for xml method but, I feel like I am missing some concepts to achieve this format. Here is the query so far

    select  tv.Item,
       --tv.ItemDescription,
       tv.PackQty,
       TotalUsage = (
                        SELECT sum(tvss.Qty * -1)
                        FROM transactions_VIEW as tvss
                        WHERE tvss.Item = tv.Item
                          AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
                          AND tvss.Supplier IN ('###', '###', '###')
                          AND tvss.TranType IN ('I','B')
                    ),
       tv.JobDescription,
    count(*) as job_usage
from transactions_VIEW as tv
         join Job as j on tv.JobNumber = j.MyNo
WHERE j.Active = 1
  AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
  AND tv.Supplier IN ('###', '###','###')
  AND tv.TranType IN ('I', 'B')
group by tv.Item, tv.JobDescription, tv.ItemDescription, Qty, PackQty

I have looked around but, I don't know the name of the technique to use so I am floundering. Any pointers in the right directions would be awesome.

well it looks like I will use stuff xml again and parse out in application. however if any one knows any clever rearrangement of this query using cte's and cross or outer applys I would love to hear it.

        select distinct tv.Item,
       --tv.ItemDescription,
       tv.PackQty,
       TotalUsage = (
                        SELECT sum(tvss.Qty * -1)
                        FROM transactions_VIEW as tvss
                        WHERE tvss.Item = tv.Item
                          AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
                          AND tvss.Supplier IN ('###', '###', '###')
                          AND tvss.TranType IN ('I','B')
                    ),
       [jobDesc:use] = STUFF((SELECT ';' + CAST(tvs.JobDescription as nvarchar(max)) +':'+CAST(count(*) as nvarchar(max))
                          from transactions_VIEW as tvs
                                   join Job as js on tvs.JobNumber = js.MyNo
                          WHERE js.Active = 1
                            AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
                            AND tvs.Supplier IN ('###', '###','###')
                            AND tvs.TranType IN ('I', 'B')
                            AND tvs.Item = tv.Item
                          Group By tvs.JobDescription FOR XML Path ('')), 1, 1, '')
from transactions_VIEW as tv
         join Job as j on tv.JobNumber = j.MyNo
WHERE j.Active = 1
  AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
  AND tv.Supplier IN ('###', '###','###')
  AND tv.TranType IN ('I', 'B')
group by tv.Item, tv.JobDescription, tv.ItemDescription, Qty, PackQty


database design for optional association and temporal I'm designing a simple database and I'm stuck with one part. I have a Task, Sweep and Spray entities.

       +-------+
       | Task  |
       |       |
       +-+--+--+
         |  |
         |  |
+------+-+  |    +--------+
|Spray |    +----+ Sweep  |
|      |         |        |
+------+         +--------+

Each task is done by either a Sweep or Spray or both. Each Spray and Sweep has few properties that are only valid for particular Task (temporal) and may change over time. For example, spray color is yellow for one task and might be green for another task.

How do I design the tables so that I can query the tasks to see which task was done by which spray and sweep with which properties? Like:

task1: spray green  | sweep auto
task2:      -       | sweep manual
task2: spray black  | sweep manual
task2: spray blue   |       -

If important attributes of Spray or/and Sweep are changing over the time, I'd rather introduce Spray_version and Sweep_version and make them immutable (every change in Spray or Sweep will create a copy in appropriate *_version table , so old values are never lost). Thus, you refer to *_version tables.

However, in real world I found it's less expensive (from maintenance and readability perspective) to ignore normalization rules and store extra data in Tasks.


Shrinking Database Task I am new to the DBA Part.

I had an 110 GB Database from which I deleted records from a 95GB sized table. Since the SQL Server Stores memory for the future, it still had 110 GB on the disc.

I ran the Shrink Database through GUI, but it was taking a lot of time. So, I cancelled it, but it remained in the greyed state. When shown from sys.sysprocesses, it shows the last batch time some 3 hours ago and is in runnable state. Can I kill the SPID?

To make it worse, Database is in multi-user mode.

Update: After three long hours, the process got rolled back on its own.

Do not use the SHRINK option.

It results in serious performance degradation. Also after you shrink the data file, it will later grow even more.

Please watch this video by Klaus Aschenbrenner (Microsoft Certified Master in SQL Server):

SQL Server Quickie - SHRINK

I would recommend to kill the SPID but be ready for the rollback operation. It can take another 3 hours, maybe less.


Using Replace in SQL I have a table and I need to update some names but I was wondering about the

following queries :

Will both do the same?

Query1

Update mytable
Set Name = Replace(Name,'Jeff','Joe')

Query2

Update mytable
Set Name = 'Joe'
where Name = 'Jeff'

No, they will not do the same thing.

The amount of effort required by the SQL engine is completely different.  In the first query, the engine must go through every row and perform a string replace operation on the Name column.  In the second query, it is searching through the table where the name is "Jeff" and just updating the Name column to be Joe.
String replacement is wildcard.  So in the first query, the Name "Jeffrey" would become "Joerey".


How to set a auto-increment value in SQLAlchemy? This is my code :

    class csvimportt(Base):
     __tablename__ = 'csvimportt'

      #id = Column(INTEGER, primary_key=True, autoincrement=True)
      aid = Column(INTEGER(unsigned=True, zerofill=True),
          Sequence('article_aid_seq', start=1001, increment=1),
          primary_key=True)

I want to set a autoincrement value from 1000. How do I do it?

From the SQLAlchemy 1.2 Documentation:

  Postgresql 10 has a new IDENTITY feature that supersedes the use of SERIAL. Built-in support for rendering of IDENTITY is not available yet, however the following compilation hook may be used to replace occurrences of SERIAL with IDENTITY:

from sqlalchemy.schema import CreateColumn
from sqlalchemy.ext.compiler import compiles

@compiles(CreateColumn, 'postgresql')
def use_identity(element, compiler, **kw):
    text = compiler.visit_create_column(element, **kw)
    text = text.replace("SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY")
    return text


Boyce-Codd Normal Form I am bit confused with BCNF and would appreciate any clarification.

I have a table

The primary key is a composite of all its attributes.
Is this table in BCNF?
My assumption it is not, because BCNF requires any determinant to be a candidate key. However, in this example there is dependency Subjects->Department, i.e. knowing subject we can say which department it is taught at. But, Subjects field is not a candidate key so it violates BCNF. It is assumed that one subject can be taught by only one department.
I might be misunderstanding something, so please anyone can clarify.

Thank you

I think your confusion may be arising from the fact that {Department, Subjects, Programme} really isn't a candidate key.  To be a candidate key, the attributes must guarantee uniqueness of each tuple, and must be minimal--that is, there is no subset of the attributes which guarantees uniqueness.  Since {Subject, Programme} alone guarantee uniqueness, including Department in the key disqualifies it as a candidate key.

Once you remove Department from the primary key, you see that the table is not in BCNF, which requires that for any dependency X -> Y, either Y is a subset of X, or X is a superkey.  In this case, Department is not a subset of Subjects, nor is Subjects a superkey (since alone it cannot uniquely identify a tuple).  Technically, the depicted relation is not even in 3NF, since Department, now identified as a non-prime attribute, does not provide a fact about "the key, the whole key, and nothing but the key".


Finding which DATE columns have no time data using TRUNC I'm in the process of converting an Oracle db to a PostgreSQL one.

The script I used converted all Oracle's DATE columns into TIMESTAMPS, but I suspect that most of those do not hold any time data and could be converted to PostgreSQL DATE data type.

To make sure of it, I used the following query on Oracle:

SELECT myDate, TRUNC(myDate, 'DDD') from myTable where TRUNC(myDate, 'DDD') <> myDate;

This seems to work fine; in most cases I get 0 lines returned. For some tables though, I get a handfull of results that make no sense:

trunc(…     myDate
17/07/95    17/07/95
24/08/42    24/08/42
29/06/02    29/06/02
26/07/88    26/07/88

Update: My SQL client wasn't showing the full values, some lines did have some time data. The above lines, however are actually:

trunc(…                myDate
17/07/5595 00:00:00    17/07/5595 00:00:00
24/08/5542 00:00:00    24/08/5542 00:00:00
29/06/5602 00:00:00    29/06/5602 00:00:00
26/07/5588 00:00:00    26/07/5588 00:00:00

Clearly those are typos, but I don't know why they would be returned by my query.

This is because the data representation of dates depends on your NLS settings.

You can change the representation of dates for your current SQL statements before you issue your first command by using the following command:

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Or seeing as you are using the slash notation:

alter session set nls_date_format = 'dd/mm/yyyy hh:mi:ss am';

If you then having a column containing a date format, then it should be returned as:

29/06/2002 1:50 pm

References

Setting Up a Globalization Support Environment (Oracle Docs)


SQL Server - How does contained database user password expiration work? I've recently started working with contained databases and I do not understand if/how contained database users have passwords that expire.

MSDN documentation says they do - With a SQL Server login, there's the option box to Enforce Password Expiration but this does not exist (as far as I can tell) for contained database users.

These contained database accounts have been around for a while and our domain policy is to reset passwords every 6 months, but we have never had to change these passwords, and the applications are connecting just fine using the same password for the past couple of years.

My first question is how do I know that "Password Expiration" is being enforced for contained database users? With SQL Logins there's a is_expiration_checked column in sys.sql_logins.

With traditional SQL logins I can use following function to get the time a SQL Server login was changed

LOGINPROPERTY('login','PasswordLastSetTime')

Is there something similar for contained database users?

Ok, so it looks like only password complexity requirements apply and not expiration - https://docs.microsoft.com/en-us/sql/relational-databases/security/password-policy?view=sql-server-2017 - "Note SQL Database enforces password complexity. The password expiration and policy enforcement sections do not apply to SQL Database."


num_rows in dba_tables not reflecting the real number of rows in tables Oracle 9i I'm querying dba_tables in order to get a listing of tables and their rowcount with following sql request :

select * from dba_tables where owner='dbSID';

But I notice that even for some non empty tables, NUM_ROWS column is returning null values.

Could somebody please explain this strange behavior of dba_tables ?

It's an estimate. See http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091.
If you run the following :

exec dbms_stats.gather_schema_stats('owner');

within Oracle SQL*Plus. You'll get then the exact value of row numbers (until subsequent inserts / deletes change the actual row count so it no longer matches the one gathered).

If you need a precise value you'll need to use SELECT count(*) ...


Oracle using index on partitioned table when full scan would have been better Trying to make some sense out of this observation in Oracle.

Query: SELECT ... FROM MY_TABLE WHERE PART_KEY=x and DT_KEY between 20120101 and 20120731

Uses an index scan on PART_KEY/DT_KEY, even though autotrace shows that the index scan hits more blocks (one at a time, with single-block reads) than a full scan would have hit (in a single multi-block read).  Stats are up to date.

The strange part is if I try with a copy of the same table and index WITHOUT partitioning, there seems to be a much higher threshold of selectivity before using the index - the range I gave above did a full scan, and only a very narrow range of values for DT_KEY between d1 and d2 would use the index.  I verified the index would be used in the non-partitioned case as well but Oracle seems more biased towards doing a full scan.

How is Oracle making the decision to use the index instead of a full scan, and what else should I be looking at?  Stats are up to date since I collected them right away.

Table structure

CREATE TABLE MY_TABLE (
   PART_KEY NUMBER(10) NOT NULL,
   DT_KEY NUMBER(8) NOT NULL,
   ...
)
PARTITION BY LIST(PART_KEY) (
  PARTITION P1 ...
)
AS SELECT .... FROM [source table]

CREATE INDEX MY_INDEX ON MY_TABLE(PART_KEY, DT_KEY) LOCAL;

EXEC DBMS_STATS.GATHER_TABLE_STATS('[USERNAME]', 'MY_TABLE');

You'd need to do a 10053 trace to see exactly how Oracle is computing its estimated cardinalities.  I would assume, though, that Oracle's cardinality estimate for this particular range is too low leading it to use an index where a partition scan would actually be more efficient.  Assuming that is the case, I would guess that the problem comes from storing date data in a numeric data type.

If Oracle knows that a particular partition has a min(dt_key) of, say, 20100101 and a max(dt_key) of 20140101, it is going to believe that there is a range of 40,000 values.  If there are no histograms and you ask for the range between 20120101 and 20120731, the optimizer will expect that you're going to retrieve 730 of 40,000 possible values or 1.825%.  If you used a date to store your date data, however, Oracle would know that there are only 4*365=1460 values and that you're actually retrieving 212 of them or 14.5%.  Without knowing the actual range, these are just example calculations, of course, but they show the principle that using the wrong data type can lead to the optimizer getting confused.

Assuming this speculation is correct, you can probably resolve the problem by changing dt_key to a date.  You can probably also resolve it by gathering a histogram on dt_key.


Oracle 10 UPDATE query I get  0 rows updated message when I ran the query below.  Why? Any ideas?

Update salespersons set salespersons.salary = salespersons.salary * 1.12
where salespersons.empid =
(SELECT salespersons.empid
  FROM (SELECT *
          FROM (SELECT *
                  FROM (SELECT s.empid employeeid,
                               s.ename employeename,
                               SUM(i.price * oi.qty) - s.salary Topprofit
                               FROM salespersons s
                               LEFT JOIN orders o ON s.empid = o.empid
                               LEFT JOIN orderitems oi ON o.orderid = oi.orderid
                               LEFT JOIN inventory i ON oi.partid = i.partid
                        GROUP BY s.empid, s.ename, s.salary)
                ORDER BY Topprofit DESC)
         WHERE ROWNUM = 2
        ORDER BY Topprofit ASC)
 WHERE ROWNUM = 1 and Topprofit is not null);

The following SQL will never return any row:

select * from <whatever> where rownum = 2;

The rownum pseudo-column numbers the rows in the result set. The first candidate row before the where filter would get assigned a rownum of 1 and be discarded because of the where clause. The second candidate row would the again be assigned a rownum of 1 and be discarded, etc.

Also be careful with combining rownum and order by clauses. The default doesn't do what you want, it orders after the row numbers have been assigned. (So an order by after selecting a single row with a rownum filter doesn't make sense.) Please look at the ROWNUM documentation for examples of how to do "top N" queries.

You can select a specific row number by "materializing" it in a sub-query:

select * from (
  select foo, bar, rownum rnk
  from some_table
  order by bar, foo
) where rnk = 42;

You'd also be better off filtering out the nulls earlier if nulls are at all a possibility (filter them out in the most nested query if you can). And the above trick doesn't work that well with group by, so using the ROW_NUMBER() analytic function would be more appropriate.

Assuming you do want to give a nice raise to the employee ranked 2nd by Topprofit, you could base your query on something like this:

SELECT
  s.empid employeeid,
  SUM(i.price * oi.qty) - s.salary Topprofit
  ROW_NUMBER() OVER (ORDER BY SUM(i.price * oi.qty) - s.salary desc) rnk
FROM salespersons s
  LEFT JOIN orders o ON s.empid = o.empid
  LEFT JOIN orderitems oi ON o.orderid = oi.orderid
  LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY
  s.empid, s.ename, s.salary
HAVING
  SUM(i.price * oi.qty) - s.salary is not null

Wrap that in select employeeid from (...) where rnk = 2 and you should be good to go.


Using computed column in CharIndex I am trying to first Concat two columns and then perform a search operation using CHARINDEX on that calculated column as below :

 SELECT CONCAT(FirstName, ' ', LastName) as FullName FROM Customer WHERE CHARINDEX('Martin', FullName, 1) <> 0

however, I am unable to use the calculated column (FullName) inside the CHARINDEX function.

The error I am getting is :

  Invalid column name 'FullName'.

What mistake am I committing here?

In addition to Scott's answer, you can just move the concat to the where clause as well.

SELECT CONCAT(FirstName, ' ', LastName) as FullName
FROM Customer
WHERE CHARINDEX('Martin', CONCAT(FirstName, ' ', LastName), 1) <> 0

Or, as a derived table.

SELECT FullName
FROM(
SELECT CONCAT(FirstName, ' ', LastName) as FullName
FROM Customer) x
WHERE CHARINDEX('Martin', FullName, 1) <> 0

As for why you can't use the alias in the WHERE clause, it's because queries don't start with SELECT. Your WHERE is evaluated before the column list is selected.

Julia Evans: SQL Queries don't start with select
Similar Question on Stackoverflow


Postgres 9.2 select multiple specific rows in one query I have a table with three columns,

Id Serial.
Value real.
timein Timestamp.

I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two rows per day, if it does contain two rows, I want to select the second row plus all the rows following it in the period of time.

To clarify:

id | value | timein
1  | 20.5  | 2013-04-25 11:25:42
2  |  4.2  | 2013-04-26 09:10:42
3  |  3.1  | 2013-04-26 15:45:42
4  |  100  | 2013-04-27 14:52:42
5  | 15.5  | 2013-04-28 17:41:42
6  | 23.3  | 2013-04-29 07:32:42
7  | 21.4  | 2013-04-29 14:32:42

If I wanted to select the values from day 26 (only the second row) plus all the values until day 29 and so on, can it be done in one query?

UNION ALL
I would go with a simple UNION ALL query here:
(
SELECT *
FROM   tbl
WHERE  timein >= '2013-04-26 0:0'
AND    timein <  '2013-04-27 0:0'
ORDER  BY timein DESC
LIMIT 1
)
UNION ALL
(
SELECT *
FROM   tbl
WHERE  timein >= '2013-04-27 0:0'
AND    timein <  '2013-04-30 0:0'
ORDER  BY timein
);

This is a single query to Postgres.
Parentheses are required in this case.
NOT EXISTS
Alternative, most likely slower:
SELECT *
FROM   tbl t
WHERE  timein >= '2013-04-26 0:0'
AND    timein <  '2013-04-30 0:0'
AND    NOT EXISTS (
   SELECT 1 FROM tbl t1
   WHERE  t1.timein >= '2013-04-26 0:0'
   AND    t1.timein <  '2013-04-27 0:0'
   AND    t1.timein > t.timein
    )
ORDER  BY timein;

-> SQLfiddle.


Is it possible to use LAG() with WHERE? We are creating a warehouse system. In this system, we will have a Extract page (I don't know if this is the right word to describe it, but it's the same as the log in our bank account, where we have all transactions, with the values and then, subtract it to the total value, it's a bank extract I guess).

We need a table to be like this:

The table will have all transactions from fiscal notes (with entries and exits of products). If you notice, you can see the ID 1 will appear in position 1, 2, and 4. I did try to use LAG, but with the LAG function, it's getting the ID 3 value.

How can I get the lag value, but with the desired product id? I need the last total value of PR_ID = 1.

I tried this:

begin tran
insert into almoxarifado.Movimentacao
  (produto_id,documento,data,saldo_anterior,entradas,saidas)
select
Lotes.produto_id as produto_id,
NF.numero as documento,
nf.data_cadastro as data,
>>The lag value would be here, I need this field, to be the last total value,
>>but from this specific product.
Lotes.quantidade as entradas,
0 as saidas
from Almoxarifado.Entradas
join Compras.Notas_Fiscais NF on Entradas.nota_fiscal_id = NF.id
join Compras.Notas_Fiscais_Produtos NFP on NFP.nota_fiscal_id = NF.id
join Almoxarifado.Lotes on Lotes.nota_fiscal_produto_id = NFP.id
 where convert(date,NF.data_cadastro) = '2017-01-10'

Every row needs to have its own total. Let's say day 2, we had 3 water gallons, it gives me a total of 10. then, day 4 I got 3 gallons, I need the row with the total = 10, and in this new row, the total of 13.

It's like a bank extract, where you see your expenses, and the total on each row. Because, the next step would create a view, that will select this table, by product, so , I can see every total, every entry and product exit during a period of time.

Let me know if this solve your problem.

I've set up a rextester example to test it.

I've used this data:

create table #mov(produto_id int, documento int, data_cadastro datetime, entradas int, salidas int);

insert into #mov values
(1, 1, '2017-01-01', 10, 0),
(1, 1, '2017-01-02', 10, 2),
(2, 1, '2017-01-01', 10, 0),
(1, 1, '2017-01-03', 10, 0),
(3, 1, '2017-01-02', 10, 0);

To get the cumulative sum, apply SUM() OVER clause in this way:

select
    produto_id,
    documento,
    entradas,
    salidas,
    sum(entradas-salidas) over (partition by produto_id order by produto_id, data_cadastro
    rows between unbounded preceding and current row) as acumulato
from
    #mov

This is the result:

+------------+-----------+----------+---------+-----------+
| produto_id | documento | entradas | salidas | acumulato |
+------------+-----------+----------+---------+-----------+
|      1     |     1     |    10    |    0    |     10    |
+------------+-----------+----------+---------+-----------+
|      1     |     1     |     0    |    2    |     8     |
+------------+-----------+----------+---------+-----------+
|      1     |     1     |    10    |    0    |     18    |
+------------+-----------+----------+---------+-----------+
|      2     |     1     |    12    |    0    |     12    |
+------------+-----------+----------+---------+-----------+
|      3     |     1     |     5    |    0    |     5     |
+------------+-----------+----------+---------+-----------+

If you need the last acumulato in the same row, you can use:

;with cteSum as
(
    select
        produto_id,
        data_cadastro,
        documento,
        entradas,
        salidas,
        sum(entradas-salidas) over (partition by produto_id order by produto_id, data_cadastro
        rows between unbounded preceding and current row) as acumulato
    from
        #mov
)
select produto_id, documento, entradas, salidas, acumulado,
       lag(acumulato,1,0) over (partition by produto_id order by produto_id, data_cadastro) as last_acm
from cteSum;

+------------+-----------+----------+---------+-----------+----------+
| produto_id | documento | entradas | salidas | acumulato | last_acm |
+------------+-----------+----------+---------+-----------+----------+
|      1     |     1     |    10    |    0    |     10    |     0    |
+------------+-----------+----------+---------+-----------+----------+
|      1     |     1     |     0    |    2    |     8     |    10    |
+------------+-----------+----------+---------+-----------+----------+
|      1     |     1     |    10    |    0    |     18    |     8    |
+------------+-----------+----------+---------+-----------+----------+
|      2     |     1     |    12    |    0    |     12    |     0    |
+------------+-----------+----------+---------+-----------+----------+
|      3     |     1     |     5    |    0    |     5     |     0    |
+------------+-----------+----------+---------+-----------+----------+


How long does it take to do RESTORE WITH NORECOVERY I am in the unfortunate situation of having to do a database restoration.

My full backup was at 9.30pm (157MB) and there were incrmentatl backups at 11am and 3pm.  The incremental backups are less than 30kb each.  It is now 6pm.

When I restored the full backup WITH RECOVERY, it took less than a minute.  But I could not apply the two transaction logs.

When I restored the full backup WITH NORECOVERY, it also took less than a minute to say it was successful, but then the database remained in the Restoring... state.  It has been like that for the last 40 minutes.  Task Manager shows almost no activity.  Is this normal?  Should I abort?

it also took less than a minute to say it was successful, but then the database remained in the Restoring... state

It surely did, you're now supposed to restore an arbitrary number of transaction logs, all WITH NORECOVERY, and the last one WITH RECOVERY.

See Understanding How Restore and Recovery of Backups Work in SQL Server and Restoring full and differential database backups.


Get rows that are referenced in another table I have two entities. product and bom.

CREATE TABLE product (
  id integer NOT NULL,
  CONSTRAINT product_pkey PRIMARY KEY (id)
)

/* Bill of materials*/
CREATE TABLE bom (
  id serial NOT NULL,
  parent_item_id integer NOT NULL,
  child_item_id integer NOT NULL,
  quantity integer NOT NULL
)

The entity product register single products and composite products (products made up of other products). The products that are composite, are referenced in the entity bom through parent_item_id and child_item_id.

I am using this structure because I can create composite products made of other composite products.

Now, What I want is to perform a SELECT that return all the products that are composed of another products.

More specifically, the records in the product table that are referenced in the table bom through product.id and bom.parent_item_id.

So far I have the next SQL code:

SELECT product.id
FROM product
INNER JOIN bom ON product.id = bom.parent_item_id;

My problem is that this JOIN give me duplicate product entries, obviously because product.id appears in bom as many product is made of.

I want just one entry per product that is referenced in bom

Sounds as if you are looking for something like this:

SELECT product.id
FROM product
where exists (select *
              from bom
              where bom.parent_item_id = product.id);

Alternatively

SELECT id
FROM product
where id in (select parent_item_id
             from bom);


Command line tool / script to backup a remote SQL Server database to local disk I've been researching and googling for 10 hours and couldn't find anything, so I decided to ask here. I'm looking for a command line tool or a script (running under windows) that can backup a remote SQL Server to a local file (.sql, SQL Server backup, even CSV, all formats are acceptable).

P.S. I can't use remote SMB share for backing up DB. Also no GUI, just command line or scripts

P.P.S. The DB is in remote location and I want to backup the data in Database to any format possible in my local machine (Windows server, no SMB share connection in between available/possible)

There are two common ways to do this:

Easy way - with "normal" database backups - when you run the BACKUP DATABASE command, the SQL Server service on the remote box needs to write the backup to a file. The service needs to be able to write to the destination path. To do this:

Set up a shared folder on your local machine
Give the SQL Server service permissions to write to that share (either by specifically granting it, or by letting everyone write to it - this isn't a security lecture, obviously)
Run the SQL Server backup, and use the UNC path to your local machine's share for the backup target, like:

If you're doing it via T-SQL, it might look like:

BACKUP DATABASE MyReallyImportantDB TO DISK = '\\MyDesktop\SharedFolderName\MyBackup.bak'

Hard way - exporting the data - if the remote SQL Server can't write to your local machine for some reason, like network firewalls or permissions, then you can export the data. A couple of common tools are:

Exporting a BACPAC file
SQL Server Import and Export Wizard

But be aware that those methods can be much slower and not transactionally consistent. They're best for smaller databases - say, under 10GB.

Updated requirements - you noted no GUI, no file system access, and no backups. Given those requirements, you'll be doing some manual work:

Use an ETL tool like SSIS to export the data
Build a custom app (C#, Java, whatever) to export the data
Use a data sync tool like Redgate SQL Data Compare


Shrink my database log file, make sense? I am now taking log backups daily after a long gap(1.5 years). Now when I run,

DBCC SQLPERF('logspace')

Result,

Database  Log Size (MB)   Log Space Used (%)    Status
master     1.742188     64.09753               0
tempdb     51.17969     33.19341               0
model      0.9921875        48.4252            0
msdb       4.554688     55.6711            0
MyDb       15618.68     0.7029594              0

Should I shrink the log file. If yes then how?

If you're really strapped for disk space, you can of course shrink your database log file.

Switch your recovery model to SIMPLE and then shrink the database file using the DBCC SHRINKFILE('logfilename') command.

If you're running a production system, make sure that you switch your recovery model back to FULL immediately after shrinking the log file, and run a full database backup, in order to get a new point in time recovery point. You can then schedule your log file backups to run every few hours ( depending on your need ).


How to log the values that cause a unique constraint violation I have a lengthy INSERT statement running on Oracle that fails with:

ORA-00001: unique constraint (MYPROJECT.MYTABLE_PK) violated

The constraint is:

CONSTRAINT "MYTABLE_PK" PRIMARY KEY ("ENCOUNTER_NUM", "CONCEPT_CD", "PROVIDER_ID", "START_DATE", "MODIFIER_CD")

The problematic INSERT statement is logged, but not the values causing the constraint violation.

Is there a way to log or otherwise save and view the values that violated the constraint?

This is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

You can use DML error logging

begin
  dbms_errlog.create_error_log( dml_table_name => 'MYTABLE' );
end;
/

insert into mytable
  <<your select statement>>
  log errors into err$mytable
  reject limit unlimited;

This will log all the rows that violated a constraint to the err$mytable table.


Query to find and replace text in all tables and fields of a mysql db I need to run a query to find and replace some text in all tables of a mysql database.

I found this query, but it only looks for the text in the tbl_name table and just in the column field.

update tbl_name set column=REPLACE(column, 'fuschia', 'fuchsia');

I need it to look in all tables and all fields: (everywhere in the database)

You need to use the information_schema database to generate the script.

Collect all columns from every table of every database that have the following criteria:

Exclude the following databases:

information_schema
performance_schema
mysql

COLUMN_TYPE values with one of the following characteristics:

starts with CHAR(
starts with VARCHAR(
ends with TEXT (TEXT, MEDIUMTEXT, LONGTEXT)

Here is the query to get those columns

SELECT table_schema,table_name,column_name FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND (column_type LIKE 'char(%' OR column_type LIKE 'varchar(%' OR column_type LIKE '%text');

Using the above query, construct a set of queries that outputs SQL for converting fuschia to fuchsia:

SELECT CONCAT('UPDATE ',table_schema,'.',table_name,
' SET ',column_name,'=REPLACE(',column_name,',''fuschia'',''fuchsia'');')
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND (column_type LIKE 'char(%'
OR column_type LIKE 'varchar(%'
OR column_type LIKE '%text');

Take that query and send its output to a text file. Import text into mysql:

SQL="SELECT CONCAT('UPDATE ',table_schema,'.',table_name,"
SQL="${SQL}' SET \`',column_name,'\`=REPLACE(\`',column_name,'\`,''fuschia'',''fuchsia'');') "
SQL="${SQL} FROM information_schema.columns WHERE "
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema') "
SQL="${SQL} AND (column_type LIKE 'char(%' "
SQL="${SQL} OR column_type LIKE 'varchar(%'"
SQL="${SQL} OR column_type LIKE '%text');"
mysql -u... -p... -ANe"${SQL}" > GlobalReplace_fuschia_to_fuchsia.sql
less GlobalReplace_fuschia_to_fuchsia.sql

If the file looks good, feel free to execute it.

Give it a Try !!!


Joins between linked servers are dangerous, so can we just load the data into a local table before doing the join? I was just reading the following article which states that it's a worst practice to do JOINs between two linked servers.   The only alternative I can see is to get the necessary data from both linked servers and load them into temporary tables in an SQL Server instance and then do the join.  Is this advisable?  Or is there some other course of action?

I've used this technique in data synch jobs.  It helps if you don't need the entire table from the remote server (just a few columns or you can filter it down in your WHERE clause).  And, by bringing the data into a temp table on the instance where you're doing the most work, you can index the temp table to your needs and get statistics that are helpful to the work you're doing.

Of course, if you're trying to do this on a very frequent basis, it might not be the best solution for you.  Maybe SSIS?


Update Schema in SSIS Export Package When Source Schema Changes I have servers with lots of client databases, all with very similar, but not necessarily identical schemae.

Some customers want their data in MSAccess on a monthly basis.  This was getting tedious to do on a manual basis, so I figured I would create SSIS packages for these exports, add them to a job, and be done with it.

I was hoping I could just create one DTSX file in the filesystem and change the database name, but I ran into schema differences between instances already so that won't work.

One issue that occurred to me is that when our developers change the source database schema, I'm guessing the SSIS packages won't know about the new tables.

Is there an easy way to deal with this?

Easy? Not really - SSIS is very picky about the metadata that is used in the data flow, so changing schema is challenging to deal with.

My typical solution for these types of scenarios is to generate the packages through either the API, or using Biml (BI Markup Language). {Disclaimer: My employer (Varigence) created Biml.}

The API is a fair amount of work, and assumes you are comfortable with .NET development. Biml is an alternative that doesn't require as much .NET knowledge. You can use Biml through BIDS Helper, which is a free download.


Where is my audit as Oracle sys audit is enable On an Oracle 11gr1 database, SYS audit is enabled:

SQL> show parameter audit

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
audit_file_dest                      string                           /oracle/admin/jde9dv/adump
audit_sys_operations                 boolean                          TRUE
audit_syslog_level                   string
audit_trail                          string                           DB_EXTENDED

But when I query the SYS.AUD$ table there is nothing returned:

SQL> select count(*) from sys.aud$;

  COUNT(*)
----------
         0

When I go to /oracle/admin/jde9dv/adump folder, there is a lot of files but none contain my recent SYS activity ...

Where are my audit records ?

Thanks

I know I am late to the party but anyways:

your AUDIT_TRAIL value should be set to DB,EXTENDED and not DB_EXTENDED.

You can change it with:

alter system set audit_trail=db,extended scope=spfile;

Bear in mind that after setting it to DB,EXTENDED you have to restart your instance for changes to take effect.

Find more on very handy Oracle's documentation:

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams016.htm#REFRN10006


Creating User for a single contained database I am pretty new working with MSSQL and have 2012 version at the moment. What I'm trying to do is create a user for a contained database, and give that user access only to that database.

I will invoke this SQL from JAVA using a prepared statement.

To create a contained user in SQL Server 2012 that authenticates at the database, you can do the following:

use YourDatabase
go

create user YourContainedUser
with password = 'yourPassword'
go

You will have to ensure that contained database authentication is enabled on the instance first, and that the database is set with partial containment:

exec sp_configure 'contained database authentication', 1
go
reconfigure
go

alter database YourDatabase
set containment = partial
go

Once those above commands are executed you should be able to create your contained user with the CREATE USER statement as explained above.

EDIT:  Great comment and points by @AaronBertrand:

  Also your connection string absolutely must use the database name as part of the credentials - if you don't specify the database, SQL Server isn't going to check all the databases and try to figure out which one you may have meant. And your code can't perform any cross-database queries in case that limitation isn't clear.


How can I see what called a stored procedure? Is there any way we can find out from where a stored procedure was executed?

I am calling a specific stored procedure from the web, but when I check the SQL Server profiler it showing  that entry 4 times; I am suspecting some thing is in the background is getting that method. So if possible what is the source of execution of that stored procedure will be really help to narrow down the issue?

I am using SQL Server 2012

Internally within a stored procedure there is no way (that I'm aware of) to determine the calling proc. There are two workarounds that I use

1.
This one wont actually be identifiable for a profiler run but can be useful in auditing.

When calling any SP call it with isnull(@procID,@@procID) (into a value called @procID), this will mean that it will hold the stored procedure ID (which can get you the schema and name) of whatever stored procedure was first called, by anything (be it a person or automated call (note this can technically be spoofed by someone manually adding in the  @procID to the initial call)

This is quite a bit of work depending on how many things you want to audit. and generates overheads in your db but will get you exactly what your after just in.

2.
The easiest way is each of the different routes into your system has its own login, so you have a login for updating users credentials, a different login for another action. This once again can be a lot of work, but will get you the information you want directly from the profiler, it also will make your system a lot more secure if the logins are stored securely as if one login is compromised then there is only that very specific are is effected someone doesn't have access to your entire system

Aditionally depending on how it is being called there is the potential (if you're using php this isnt possible if you have a more complex back end with iss / .net in the background you may be able to set the program_name field for the login, once again this will show up in the profiler and if you have the proc name your calling you can amend that into the program name (WebLogin-schema.usp_procname for example)

Hope this somewhat helps
Ste


How do I force a Nested Loop join on Materialized View and Table in Oracle 12c There is a third party app which generates a high-cost query that has a join on multiple tables.

I created a materialized view on some of the tables using the same join that is originated in the Application query and used rewrite hint. The hint is respected by the optimizer and the materialized view is used. What should I do to make optimizer perform a nested loop join between the materialized view and other tables?

Eg:

SELECT  /*+ rewrite */
       tab1.col1, tab1.col2, tab1.col3
  FROM tab1,
       tab2,
       tab3,
       tab4
 WHERE     tab1.col1 = tab2.col1
       AND tab2.col2 = tab3.col1
       AND tab4.col1 = tab3.col1
       AND tab4.col1 = :1;

I created a Materialized view as below:

CREATE MATERIALIZED VIEW tst_mv
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
    SELECT tab1.col1, tab1.col2, tab1.col3
      FROM tab1, tab2, tab3
     WHERE tab1.col1 = tab2.col1 AND tab2.col2 = tab3.col1;

I tried with the hint as /*+ rewrite use_nl(tst_mv tab4) */ but no luck. Any pointers?

Is that really your query and MV? tab3.col1 is not in it, but joining tab4 requires it because of tab4.col1 = tab3.col1. tab3.col1 = tab2.col2, so tab2.col2 = tab4.col1, but tab2.col2 is also not in the MV.

If I create this:

create table tab1(col1 number, col2 number, col3 number);
create table tab2(col1 number, col2 number);
create table tab3(col1 number);
create table tab4(col1 number);
alter session set query_rewrite_integrity=stale_tolerated;
CREATE MATERIALIZED VIEW tst_mv
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
    SELECT tab1.col1, tab1.col2, tab1.col3
      FROM tab1, tab2, tab3
       WHERE tab1.col1 = tab2.col1 AND tab2.col2 = tab3.col1;

explain plan for
SELECT  /*+ rewrite */
       tab1.col1, tab1.col2, tab1.col3
  FROM tab1,
       tab2,
       tab3,
       tab4
 WHERE     tab1.col1 = tab2.col1
       AND tab2.col2 = tab3.col1
       AND tab4.col1 = tab3.col1
       AND tab4.col1 = :B1;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1776227436

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    91 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN            |      |     1 |    91 |     8   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |    78 |     6   (0)| 00:00:01 |
|*  3 |    HASH JOIN          |      |     1 |    65 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL | TAB1 |     1 |    39 |     2   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL | TAB2 |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |    BUFFER SORT        |      |     1 |    13 |     4   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS FULL | TAB4 |     1 |    13 |     2   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS FULL   | TAB3 |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("TAB2"."COL2"="TAB3"."COL1" AND
              "TAB4"."COL1"="TAB3"."COL1")
   3 - access("TAB1"."COL1"="TAB2"."COL1")
   5 - filter("TAB2"."COL2"=TO_NUMBER(:B1))
   7 - filter("TAB4"."COL1"=TO_NUMBER(:B1))
   8 - filter("TAB3"."COL1"=TO_NUMBER(:B1))

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

TST_MV is not used.
If I create another MV with tab3.col1 included, then add use_nl, that MV is used with a nested loops join:

CREATE MATERIALIZED VIEW tst_mv2
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
    SELECT tab1.col1, tab1.col2, tab1.col3, tab3.col1 as tab3_col1
      FROM tab1, tab2, tab3
       WHERE tab1.col1 = tab2.col1 AND tab2.col2 = tab3.col1;

explain plan for
SELECT  /*+ rewrite use_nl(tab4) */
       tab1.col1, tab1.col2, tab1.col3
  FROM tab1,
       tab2,
       tab3,
       tab4
 WHERE     tab1.col1 = tab2.col1
       AND tab2.col2 = tab3.col1
       AND tab4.col1 = tab3.col1
       AND tab4.col1 = :B1;

 select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1384405329

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    65 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |         |     1 |    65 |     4   (0)| 00:00:01 |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| TST_MV2 |     1 |    52 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL           | TAB4    |     1 |    13 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - filter("TST_MV2"."TAB3_COL1"=TO_NUMBER(:B1))
   3 - filter("TAB4"."COL1"=TO_NUMBER(:B1))

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


What is the difference between MySQL and PostgreSQL in terms of writing SQL queries? Other than performance and security is there really any difference between MySQL and Postgres like SQL queries, built-in functions, procedures and so on?

I'm asking this because I'm familiar with PHP with MySQL (phpMyAdmin) but not with Postgres (phpPgAdmin)

(Regarding PostgreSQL 9.3 and MySQL 5.6, written in 2014; if you're looking at other versions, this may be outdated):

Lots more features.

CHECK constraints
True SERIALIZABLE isolation
Arrays (including index support for arrays)
Window functions (lead, lag, row_number, etc)
Common table expressions (WITH queries) including recursive CTEs and writeable CTEs
The hstore hash type and its index support
SQL/XML support
json support
A rich set of functions and operators for many types
Partial and function/expression indexes, including partial unique indexes and unique expression indexes
Composite types
User defined aggregates and operators
Set-returning user defined functions
User defined types
Transactional DDL (this is wonderful)
GIN and GiST indexes for complex indexing needs, including K-nearest-neighbour indexing
PostGIS
UNLOGGED tables (though MyISAM meets this need in MySQL)

See the comprehensive user manual for details.

However:

There are no session variables in PostgreSQL, unlike MySQL. (You can abuse GUCs for this, but not to do the same sort of things you do with MySQL session variables).
PostgreSQL has no INSERT ... ON DUPLICATE KEY UPDATE ("upsert")
At the moment, PostgreSQL has different replication options than MySQL. MySQL has no point-in-time recovery, but PostgreSQL has no logical replication (yet, we're working on it).
GROUP BY ... ROLLUP for summaries of groupings. Doing the same thing in PostgreSQL requires a union, usually over two queries over a CTE.
PostgreSQL doesn't have a built-in event scheduler. You generally just use cron or the Windows Task Scheduler; there's also PgAgent from PgAdmin-III.


In SQL Server, why can a backward scan of clustered index cannot not use parallelism? I've been reading about SQL Server internals and every book or blog mentions this about backward scans.

  A backward scan of a clustered index cannot use parallelism

The only post that said something is this one below. The post says that the SQL Server team did not implement the required optimizations for a backward scan. https://www.itprotoday.com/sql-server/descending-indexes

Since the leaf level pages are linked using a doubly linked list, I do not understand why a backward scan is different to a forward scan. Any clarification is really appreciated.

The referenced article specifically states the reason backward ordered scans were not parallelized in SQL Server 2008 (as of CU6) isn't technical but because the feature hadn't been requested by customers and the dev team didn't bother to implement it.

Note that the article was written nearly 10 years ago in the context of the now unsupported SQL Server 2008 version. There have been significant changes to the storage engine and optimizer. That said, I still see a parallel plan for the ASC query and a serial plan for the DESC version from the article's demo query on SQL Server 2017:

SELECT *
FROM dbo.Orders
WHERE orderid <= 100000
ORDER BY orderdate ASC;

SELECT *
FROM dbo.Orders
WHERE orderid <= 100000
ORDER BY orderdate DESC;

Running those same queries under SQL 2019 CTP 3.2 shows a serial plan for both unless I changed the query to WHERE orderid <= 50000, where I then observed the same behavior as SQL Server 2017. So it seems that either the parallel backwards scan has still not yet been implemented or a different scenario is needed to observe it.


How to restore 11gR2 fullbackup to 12c database in rman I need to transfer my database from Oracle 11gR2 to 12c database. It will be in same computer. I took full backup of Oracle 11gR2 database. Then I remove Oracle 11gR2. I installed Oracle 12c and I tried to restore my backup. I could restored spfile, controlfile, datafiles.. But I couldn't do

RMAN> ALTER DATABASE OPEN RESETLOGS;
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "I". "UNUSABLEBEGINNING# ": invalid identifier
Process ID: 3377
Session ID: 125 Serial number: 5

What should I do to open database? Should I do Pre-Upgrade script ? I couldn't understand to do pre-upgrade script step..

This is not going to work. Oracle binaries of a certain version correlate to the Oracle Database dictionary of the same version.

RMAN is a backup and recovery tool. It is NOT an upgrade tool.

You need to restore and recover you database on 11g binaries. From there you can upgrade your database to 12c.

You can have many versions of Oracle Database installed on the same host. The different binaries go into different ORACLE_HOME.


Is there any difference on backup process whether it is recovery model is Full or Simple? In past i have done server migration and i moved my databases by backup via this script template.

BACKUP DATABASE [ExampleDB] TO  DISK = N'D:\Backups\ExampleDb.bak

And i restored bak files on new server. Unexpectedly a user reported data lost after migration and i validated it by checking data in old server. It was around 8 hours data lost. This was very unexpected moment for me.

My question is having backup via that script will give me full backup whether it is in Full or Simple recovery model ?

In another way, Is there any difference between backup files when i get backup whether recover model is Simple or Full ?

A full database backup will give you everything in the database regardless of recovery model.  Upon restore, any transactions that were still in-flight when the backup completed will be undone.

Recovery model really comes into play during restores.

With SIMPLE recovery, you can only take full and differential backups and that is the granularity that you can restore.

With FULL recover, you still take full (and optional differential) backups, but you also take transaction log backups and can use the combination of the full, diffs and logs to restore to a point in time.


SQL Server 2014 Avaliability Group DbMgrPartnerCommitPolicy I have an environment with 2 Windows Server 2012 R2 servers, both with SQL Server 2014 Expensive edition.

Whenever a change happens in the AGs (such as: AlwaysOn Availability Groups connection with secondary database established for primary database 'AGDatabase' on the availability replica 'SecondaryReplicaThatIRestarted'), I get an error like this, in my SQL error log:

  Source: SPID52s

  Message
  DbMgrPartnerCommitPolicy::SetSyncState:00000003BFC0B0E0:1

Ending with a result like this:

Imgur: Screenshot of the errorlog

EDIT:

Nothing in the Windows Error log, not even the mentioned error, which leads me to believe it's something SQL internal that's being leaked out into the errorlog.

Any ideas of what this could be caused by? I've set up multiple setups similiar to this, and this is the first time I've seen it.
I plan on patching the SQL to SP1 this week, however I don't see the error in the release notes.

For future readers :
DbMgrPartnerCommitPolicy::SetSyncState:00000003BFC0B0E0:1
The last integer describes the AG state
0 - Not Joined to AG
1 - Not Synchronized
2 - Suspended
4 - Synchronized
8 - Redo (redoing log)

Ref : DbMgrPartnerCommitPolicy::SetSyncState May Seem More Mysterious than It Actually Is


Encrypting a Database row-by-row I have a large database (millions of rows in hundreds of tables) that contains sensitive data (Think SSNs, account numbers, etc). I am tasked with safeguarding the data and have chosen to encrypt only sensitive data and not every field. This encryption is handled by the application, and not the database server, so all solutions require that the encryption process stays in the application.

Because there are existing JOINs in the application, I can't just encrypt every value separately because 2 identical values will yield different encrypted strings (and thus won't JOIN). I've chosen to key the data (a hash of the value, so the key will always be the same for each unique value) so that I can store the key in place of the original data and use it to look up the encrypted data when needed.

So I need to go through the database, row-by-row, and encrypt certain fields, storing the encryption in a separate table, and then storing the key in place of the original data.

My problem is that looping over the data is very slow. 10-15 minutes per 1000 values. I've optimized the task by grouping the values so that I only have to encrypt each unique value 1 time and then update all instances of that value with the key in 1 UPDATE.

Is there a different approach (keeping all the encryption in the Application layer) that can speed up my task of encrypting the sensitive fields?

You're paying the extra price of network IO and there's not a whole lot you can do if you need to remove the encryption (really the key storage/management) from the database. If your MySQL instance is on a Unix host, you can try connecting with a Unix socket file instead of TCP/IP. Just host the app on the same machine during the encryption process, finish the DB then move the app back to wherever it should be.

If your MySQL DB is running on Windows then you're out of luck. You could still put the app on the same machine or isolate them from the rest of the network so you minimize impact from network congestion or switch latency but those are likely to yield limited improvement unless your network was really, really busy or if you were using some old 100Mbps switch.


Send CSV file with db_sendmail I am trying to send a csv file with 15000 records via the database mail in SQL Server 2014. The problem is that when I open my email the csv only contains 209 records. I have tried the same thing in SQL Server 2012 and it works as expected - it sends the 15000 records in the csv.
I have tested this on several servers with 2014 edition on them, and I have the same issue on all of them. Does anyone have an idea if something changed in the 2014 edition? Or can give me some tips on what to look at next?

The query breaks off at different points - for example one of them breaks off at 209 records as i said above, another one at 307. the last record gets truncated. the attachment size it's about 64 kb - which is well below the 4MB limit i've configured the database Maximum File Size bytes parameter. what i am doing basically is creating a job that is meant to execute a stored procedure and send the results in a csv in an email. The stored procedure is something like:

    select col1, col2, col3
    from table A1
    where col1 > 1000
    order by col1

Found the answer- it's actually a bug on 2014 RTM, please see:
https://connect.microsoft.com/SQLServer/feedback/details/850260/sql-2014-sp-send-dbmail-64kb-query-result-limit

The bug is fixed in SQL Server 2014 CU4, see KB3004195.


Roles and Deny Precedence So i have a Ad group on a database which i want to deny from viewing a table but i want give access to one person from AD Account.

I'm not sure if it is possible.
I highly think its not.
but maybe i could be wrong so i am asking. Is it possible for that to happen without removing the username from the ad account?could you provide me with the best solution on how to approach this ?
note i have already created the roles.

A group inherits permissions to all the members that it contains. So NO, you cant do that with AD Group.

You can use database roles but that has to be on individual logins in your case.

Best is to

Remove that login from AD group and grant indivudual permission or make it part of database role and grant permissions to that role.
Ask that user to use sql authentication so that the person can view table.


How to copy a Postgres database within the same DB cluster? I want to replicate a Postgres database without dumping data using pg_dump. Both source and target database are on the same machine (Test and Production server).

I would like to create a master_db_copy as exact copy of the production server master_db.

I tried from the command line:

pg_dump -vO master_db | gzip > master_db
gunzip -cV master_db.sql.gz | psql master_db_copy

Is there a better / faster way?

The fastest way to copy a complete database within the same DB cluster is CREATE DATABASE with a TEMPLATE. This just copies a whole DB on the file level.
CREATE DATABASE master_db_copy TEMPLATE master_db;

You must be superuser or owner of the template (master_db in the example) - unless it's marked as dedicated template in pg_database.datistemplate.
And the template cannot have concurrent connections to it. Dedicated template databases make sure of this by setting pg_database.datallowconn. You might use pg_terminate_backend() to close connections. See:

How to clone a test database from a production one in one single action?

The manual:

Two useful flags exist in pg_database for each database: the columns
datistemplate and datallowconn. datistemplate can be set to
indicate that a database is intended as a template for CREATE DATABASE. If this flag is set, the database can be cloned by any user
with CREATEDB privileges; if it is not set, only superusers and the
owner of the database can clone it. If datallowconn is false, then
no new connections to that database will be allowed (but existing
sessions are not terminated simply by setting the flag false).
datistemplate = true. [...]


"Heap Blocks: " Number meaning on Query Plan for a bitmap heap + index scan What does the number next to Heap Blocks represent in the query plan? I'm not sure if it means the # of blocks (8kb per) of data located on disk representing your data, or in some way the bitmap, which correlates more closely to idea of exact/lossy.
I initially thought it was the former, but I saw in this video (https://youtu.be/UXKYAZOWDgk?t=753) that even with 4mb of work_mem, he could still hold ~2800 heap blocks, which would amount to like 22mb, correct?

4MB was enough to hold references to 2964 heap blocks (plus sub-references to 3531 tuples falling within those blocks).  It doesn't hold the blocks themselves.  The blocks themselves would be held in shared_buffers (Or cycled through shared_buffers if it were insufficient to hold them all), not in work_mem.


Keep transaction log size under control during batch processing tl;dr

I think my verbosity has obscured the real question I'm asking here, so I apologise for that. My main issue is that the checkpoint command appeared to be working for at least 800 iterations of the loop. The log size remained static at around 1GB during that period. Then, sometime overnight the checkpoint evidently failed to free the space within the log file and it consumed the drive. No database backups where scheduled. I'm at a loss as to what caused this behaviour.

Is there anything I can do to ensure that the log space is freed on completion of each loop iteration under both FULL and SIMPLE recovery modes?

I am trying to alter the schema (and make some data changes) on an extremely large table without causing any disruption to applications and services that require this table and also to keep the transaction log at a manageable size.

So I have decided to process the table data within a loop, either taking a log backup or issuing a checkpoint statement depending on the recovery model at the end of each iteration. Each loop migrates some data into a new table (both old and new tables are UNION ALLd behind a view to ensure availability while the data is only partially migrated).

However the transaction log is blowing out on the development server under SIMPLE recovery mode, but in a very odd way. I monitored the log for the first ~800 iterations of the loop (out of a total of ~3000) before going home and the log remained at a size consistent with the number of records it was processing per iteration and so the method appeared to be working. However, on arrival the next morning, I noticed that the log had exploded and consumed the entire disk with only 400 iterations to go.

I do not understand why the process appeared to work initially, and then failed at some seemingly arbitrary point. I would have expected the log to grow steadily if my logic was wrong, but this did not happen.

I truncated the log so it started at ~500KB. It grew to around 1.2GB after the first loop and stayed at approximately that size for the first few hundred iterations. Based on that it seemed as though the method was sound.

My script is summarised with the following pseudoish code:

select distinct [fk] into #tmp from [huge_table];

while exists (select 1 from #tmp) begin
    select top (1) @id = [fk] from #tmp;

    -- Actual query is more complicated...
    delete [huge_table]
    output deleted.* into [new_table]
      from [huge_table] a
     where a.[fk] = @id;

    -- Keep log down
    if @recovery = N'SIMPLE'
        checkpoint;
    else begin
        set @logpath = '\\path\to\backups\log_' + cast(@c as varchar) + '.trn';
        backup log 'database_name' to disk = @logpath;
        set @c = @c + 1;
    end

    delete top (1) #tmp;
end

My choices at this point appear to be either a) fix the problem somehow, or b) assume that the same degenerate behaviour will not occur under FULL recovery mode in Production. I'm naturally repulsed by option B, but not sure how to go about option A.

How can I ensure that the size of the transaction log behaves itself. Is there something I am missing?

My only idea currently is to append to the end of the loop something like:

if @logsize > 10GB (or some other arbitrary number)
    dbcc shrinkfile (N'database_log_file', 0, truncateonly);

But, aside from being awful in and of itself, I can't think of any way to translate this approach to a database under FULL recovery mode, so it's really not an option for me at this point.

EDIT: Also, I checked the log_reuse_wait_desc column after the failure but the result for the database in question was NOTHING, so that provided no enlightenment. I was able to do a log truncation via dbcc shrinkfile after the fact, so the log was not in any state that prevented truncation.

Even with the SIMPLE recovery model, the trans log can still blow up if there's a long running transaction going on.  I'm wondering if something on your Dev server was running while your script was doing its thing and cause the trans log to not be able to truncate since the MinLSN was from some long running transaction.

To answer the question though, unless you restrict access to the DB (like running your script using ALTER DATABASE  SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE or something similar like SINGLE_USER), there's no way you can force the log file to free space as other transactions may be running and the trans log has to stay consistent for those transactions.

Check TechNet (or BOL: Checkpoints and the Active Portion of the Log) for more information.


Seting timezone per database in MySQL I wanted to know if MySQL let the admin set a timezone per database ?

I have three different databases for three different customers. Each of them being in their own timezone.

I would like to load the exact same event for each customer and would like it to run everyday at 00:00:01 for their respective timezone. Is it possible ?

I can load three different sql script specifying in each their respective timezone, but it doesn't scale well and it make me have three version of almost the same file.

No...
MySQL time zone support includes only a global default time zone and a per-connection time zone.  Since any given connection can dynamically USE (as default) any database for which it has privileges, or no database at all, and tables within a single query can cross databases, the concept of a per-database timezone wouldn't be particularly meaningful, much less unambiguous.
...but...
However, if your setup script sets the time zone in its session before defining the scheduled event, that time zone will be used by the scheduler to transform the time you requested the scheduled event into UTC.

Times in the ON SCHEDULE clause are interpreted using the current session time_zone value. This becomes the event time zone; that is, the time zone that is used for event scheduling and is in effect within the event as it executes. These times are converted to UTC and stored along with the event time zone in the mysql.event table. This enables event execution to proceed as defined regardless of any subsequent changes to the server time zone or daylight saving time effects.
— http://dev.mysql.com/doc/refman/5.6/en/create-event.html


SQL Server R2 and HyperThreading - Max Worker Threads and performance SQL Im getting this error:

This file is generated by Microsoft SQL Server
version 10.50.2500.0
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.

Computer type is Intel(R) Xeon(R) CPU           X5650  @ 2.67GHz.
Bios Version is HP     - 2
24 X64 level 8664, 2 Mhz processor (s).
Windows NT 6.1 Build 7601 CSD Service Pack 1.

Memory
MemoryLoad = 98%
Total Physical = 24565 MB
Available Physical = 386 MB
Total Page File = 49129 MB
Available Page File = 23110 MB
Total Virtual = 8388607 MB
Available Virtual = 8353094 MB
2012-03-05 09:50:27.67 Server      ***Unable to get thread context for spid 0
2012-03-05 09:50:27.67 Server      *

2012-03-05 09:50:27.67 Server      *
2012-03-05 09:50:27.67 Server      * BEGIN STACK DUMP:
2012-03-05 09:50:27.67 Server      *   03/05/12 09:50:27 spid 2556
2012-03-05 09:50:27.67 Server      *
2012-03-05 09:50:27.67 Server      * Non-yielding Scheduler
2012-03-05 09:50:27.67 Server      *
2012-03-05 09:50:27.67 Server      *
2012-03-05 09:50:27.68 Server      Stack Signature for the dump is 0x00000000000000FB

I read a post where SQL 2008 R2 perfomance was giving problems with proccesors that have hyperthreading. Do you think this is the main cause of my problems? Would be a nice idea to disable the hyperthreading?

I've seen failure to manage worker threads imperil more than one instance on various versions of SQL Server, at multiple businesses. The net result is memory pressure (which can manifest in different ways). In your case you can see clearly that a worker didn't yield, and what would be forcing it to yield is memory pressure. I haven't seen that result in a total FE like you've got, but again, memory pressure can manifest differently.

Set your workers to the appropriate level based on this MSDN article. Once you do, I would not be surprised to learn it solved your problem completely.

As for disabling hyperthreading, I don't think this is a good idea but opinions vary greatly on the topic. For sure I don't think it's related to your problem though.


Ok so this is what I would recommend:

Use a custom aggregate for a moving average.  the aggregate would use essentially an array to store values and return the average of the values in the array.   New values would be appended to the end, and old values dropped off.  One could use array_agg with unnest(), limit, and offset to do the work.
Use a windowing function to tie the moving average to the row.
Do a calculation between values in the above to generate your pattern....
Create another aggregate which gives you a moving pattern returning an array
Write an operator matching the array and use a windowing function call to tack this on to the returned record

I do think that the above can all be done in SQL with SQL-language functions.  It is, however, way too complicated to even get into sample code here.  However I can't help but wonder whether PL/R might not be helpful here as well.


Why use separate databases for high traffic/volume tables? While looking at the database structure of an application I am using I recognized that it uses 3 different databases on the same SQL Server instance for different things.

The first one contains the main data that changes rarely.

The second one contains event logs with high traffic and volume and the last one is an archive database for old event logs.

I was wondering what the benefit of this structure might be, because the databases are running on the same instance and the database files are located on the same disk. Therefore I would not expect any performance improvements from this.

I thought, maybe I am overlooking something and somebody can point me to benefits that I did not think of.

Update:
Some good points were made regarding maintenance and security. But I am still wondering if it is possible to get a performance improvement.

A more general question would be: Can the performance of one table suffer from other large tables in the same database (due to fragmentation or for some other reason) or are those effects probably negligible.

You've got two different questions in here that I'll answer separately.

Q: Can the performance of one table suffer from other large tables in the same database?

Yes, during insert/updates/deletes. SQL Server only has one log file per database. When you insert/update/delete, your transaction doesn't complete until your data is written into the log file.

Let's say we have two tables:

Documents - stores large XML representations of stuff. Has just two fields, an ID and an XML field, but that doesn't mean the records are small. When we do an insert into here, we need to write the entire XML to the log file, and our XML documents tend to be 5-10MB each.
Orders - stores incoming orders from our web site. It's a small table - no XML or VARCHAR(MAX) fields, just a few IDs for customer and product. When we do an insert here, it's just 1K.

If both of those tables are in the same database, and we try to place orders while we're writing documents too, the performance of order transactions can be slower.

Now, this is an extreme example, but generally this is why I recommend storing mission-critical, high-value, small-width-record tables in a separate database from less-critical, lower-value, large-width-record tables. However, if you need to restore both sets of tables to the exact same point in time, or fail them over together between servers, then they need to be in the same database. SQL Server doesn't guarantee transactional consistency between databases on failover with things like AlwaysOn Availability Groups.

Q: If I have fast-changing data, slow-changing data, and never-changing data, should I separate them into different databases?

Yes, for a few different reasons:

Different backup schedules - I need to make sure the fast-changing data is backed up as fast as possible. Generally people use a single backup job to do their full backups for all of their databases, and often they don't do transaction log backups at the same time they're doing full backups. If I've got a 10GB fast-changing database and a 1TB never-changing database, and if I use a single full backup job, that means I may not be getting any transaction log backups while my 1TB full backup is going on. That's a bigger window for potential data loss. You could mitigate this with separate backup jobs or tighter schedules. What you really want to do is avoid backing up never-changing data - less IO loads, less backup files to shuffle off to tape.
Different index rebuild & stats update jobs - I don't want to be doing index rebuilds/defrags on large databases that aren't changing. Ideally I rebuild all their indexes with 100% fill factor, update the statistics with fullscan, and set the database to read-only. Presto, dense pages, faster performance without locking hassles. (Plus, as a paranoid DBA, I can just make sure nobody's changing it that way.)
Different HA/DR strategies - if I've got a large, stable database that rarely changes, I might put it in read-only mode, and then restore that read-only copy to my disaster recovery environment. When I'm copying data from my primary data center to my secondary, then I only have to worry about the frequently-changing data.


Declare Variable to use as placeholder text for tablenames, not using declare table type Below, is a sample excerpt of a reoccurring code I run monthly.  Each run, a new table is created based on the Month. For example, the created table name isYEAR2016..report_Nov2016, but for next month the tablename will end in _Dec2016.  Instead of find&replace to change all table names(10 occurrences), I want to have one declare variable at the beginning of my code so that I only have to change the table name once and it gets reflected throughout the code.

declare @table varchar(30)
set @table ='YEAR2016..report_Nov2016'
select  recipient_ID, recipient_Name, recipient_DOB,program_code
into @table
from YEAR2016..claim_table

Separate batches

alter table @table
add Medicaid char(1)
GO
update @table set Medicaid =''
GO
update @table set Medicaid ='Y' where program_code ='MK A'

From what I understand, declare @table table () should be used, but that won't accomplish my intention, as a table needs to be created in the database.  I'm also aware,with declare table type, into @table won't work and needs to be Insert into @table.

Is it possible in TSQL to do what I'm envisioning?

In SQL Server, you need to keep table names and column names static in code that isn't dynamic.

To do what you are trying to do, you'd have to use Dynamic SQL - declare variables for your SQL Statement, populate the SQL statement dynamically - using your @TableName variable to supply the table name - then execute the dynamic SQL.

There are a lot of gotchas to think about with Dynamic SQL. A good place to start is a rather lengthy piece on the good and bad side of Dynamic SQL from Erland Sommarskog in this article, "The Curse and Blessings of Dynamic SQL"


Azure Database Size Same Before and After Deleting Most of the Rows I have a database hosted on Azure which is showing as nearly full, it is at 1017MB/1024MB. The table contained around 27000 rows, I have deleted 20000 of those rows to try and free up space, however the amount of available space is the same as before I deleted the rows.

The Database only has one table the create script is below as you will see it only has text stored in the database and should not be as high as this.

CREATE TABLE [dbo].[VersionLog](
[Id] [varchar](255) NOT NULL,
[Date] [datetime] NOT NULL,
[WanIP] [varchar](255) NOT NULL,
[LocalIP] [varchar](255) NOT NULL,
[ShopName] [varchar](255) NOT NULL,
[MachineName] [varchar](255) NOT NULL,
[ApplicationName] [varchar](255) NOT NULL,
[VersionNumber] [varchar](255) NOT NULL,
[ServerName] [varchar](255) NOT NULL,
CONSTRAINT [Id] PRIMARY KEY CLUSTERED
 (
    [Date] ASC,
    [LocalIP] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

EDIT:

I re-indexed the table and have waited over the weekend to see if the database would shrink as per the suggestion below, although it has remained at the same size.

I have also attached a picture of how much each table in the database is using up. (in MBs)

I think this may be answered here:
https://stackoverflow.com/questions/19677883/how-to-shrink-azure-sql-server-db-18mb-of-data-charged-for-5gb-of-server-space

To summarize
You need to reindex, then wait after you have done the deletes.

I don't think azure counts the size of logs in the space used.

Also see here to check what is using the space (I have summarized below)
http://blogs.msdn.com/b/dilkushp/archive/2013/07/28/fragmentation-in-sql-azure.aspx
Once you have done the reindex to check out various sizes run the following script.

-- Size of each table in MB
select obj.name, sum(reserved_page_count) * 8.0 / 1024 as "size in MB" from sys.dm_db_partition_stats part, sys.objects obj where part.object_id = obj.object_id group by obj.name
Order By sum(reserved_page_count) desc

-- Total Database size in Meg
select sum(reserved_page_count) * 8.0 / 1024 as "size in MB" from sys.dm_db_partition_stats


Will Entity Framework and/or Linq-To-SQL queries show up as "Adhoc Queries" in SSMS' "Server Dashboard" Report? Its pretty much all in the title. I have a SQL Server which is showing that 90%+ of CPU Usage and Logical IO Performed is due to "Adhoc Queries" The rest rest of the usage shows up under the actual DB name. This is all from the "Server Dashboard" report.

I have a few databases on this server which are accessed directly by vendor applications of which I have no control. They are very low usage in general; I have two databases that are accessed and used by internal applications written in Entity Framework and Linq-To-SQL. I'm wondering if that is why my "Adhoc Queries" are showing such high use instead of the names of my internal databases.

The reason I'm asking is because I've been noticing somewhat degregated performance and the CPU on my server is running 95% on sqlserver.exe process for extended periods of time, with minimal "down spikes" to 10-15% use.

Yes. Linq-To-Sql and EF submit dynamic (Ad-Hoc) queries to the database. You can prove this by running a profiler trace and debugging the app.

The good news is that you'll always get the most up-to-date query plan, based on current statistics. The bad news is that the plan will have to be compiled on each execution. This is rarely good, and only when the query commonly returns varying amounts of data.

I see this every day. Developer Bob is more concerned with RAD (kicking the app out the door and looking like a rock star) than app performance. He uses this new, bleeding edge, auto-magical sql generation code tool. Queries are fast from the app because there's only 2500 records in the database and everything fits in memory. 12 months down the road, app volume has went up 100000 %, it's now super dog slow, and the db gets blamed. The solution is to either re-write the app with proper sql or throw hardware at the problem. In my opinion, auto-magical sql insures that your' app will not scale well with significant volume.

"Dynamic code generation is the fastest way to build slow SQL and the slowest way to build fast SQL. " Brent Ozar

Your biggest challenge is not in compiled code speed. It's in your ability to efficiently read and parse datasets which do not fit in memory.


Why aren't read-only queries executing concurrently? I have a Django app using either sqlite or Postgres.  (I've tried both and encountered the same problem.)  Requesting a particular endpoint results in a read-only query like this that takes 1.5-3 seconds before the response.

The problem occurs when Django receives simultaneous requests to this endpoint, which is common in my application.  I expect Django to process the requests in parallel and still return a response in 3-4 seconds.  Instead, I get no response for any of the queries until 9-12 seconds.  In know that Django supports concurrent requests, so I suspect this is a database issue.

What am I missing?

The problem is that I was using Django's built-in development server (runserver).  The problem goes away using Gunicorn.  It doesn't matter if the database backend is Sqlite or Postgres.


AWS Postgres - max pid? Not sure what sort of platform AWS runs 9.x Postgres on. What's the maximum value for the process pid?

Im looking into an issue around the use of pg_backend_pid in a trigger.

AWS doesn't run PostgreSQL.

They run Amazon Relational Database Service (RDS), based on PostgreSQL
You can run PostgreSQL yourself on EC2, using something like the Ubuntu Cloud images.

  What's the maximum value for the process pid? Im looking into an issue around the use of pg_backend_pid in a trigger.

I assume the maximum value for process pid is the maximum pid value for Linux.

But, for PostgreSQL it's stored in an interger.

select pg_typeof(pg_backend_pid());
 pg_typeof
-----------
 integer
(1 row)

Which means the max value is pow(2,31)-1


Is it possible to view a SQL query in the PgAdmin III Graphical Query Builder? I often use pgadmin III's graphical query builder to design my SQL queries. I can then toggle back to the SQL Editor tab, and grab the SQL.

Is it possible to paste a SQL query in to the SQL Editor tab, then toggle to Graphical Query Builder to see the graphical representation? I'm coming from an MS Access background, and wondering if similar functionality exists.

It's a one-way street. You can use the GQB to generate SQL code but not the other way round.

I have been involved with pgAdmin development for a couple of years and I don't know of any features in the opposite direction. So I am pretty sure.


Is it harmful to update columns which aren't changed? Say I have a table with some columns:

CREATE TABLE t
(
    key int PRIMARY KEY,
    c1  int,
    c2  text,
    c3  timestamptz
);

I now fetch a row from the table:

SELECT * FROM t WHERE id = 180;

In my application, I now only change the value of one column, let's say c2. Is it harmful performance-wise to do:

UPDATE t
SET    t1 = <exact the same value as before>,
       t2 = <new value>,
       t3 = <exact the same value as before>
WHERE  key = 180;

instead of:

UPDATE t
SET    t2 = <new value>
WHERE  key = 180;

?

Assuming network overhead can be ignored (e.g. you're not updating megabytes of text with the same values), it should not affect performance.  However, if you have triggers for UPDATE with column lists, they will behave differently, depending on whether the column is mentioned as a target of UPDATE.  For instance,

create  trigger test_au after update of t1 on t
    for each row
    execute procedure after_update_t1();

update t set t2=<new value> where key = 180; -- trigger does not fire
update t set t1=<exact the same value as before>,
 t2=<new value> where key = 180; -- trigger does fire

I also tested for HOT updates - no difference in Postgres 10.5. If the value of the indexed column doesn't change, it does a heap only update, at least according to pg_stat_user_tables - the same as if the indexed column doesn't participate in the update at all.


How do I get more control over backup file retention using Ola Hallengren's DatabaseBackup solution? I've noticed some unexpected behavior using the database backup job in Ola's Maintenance Solution. On the test instance I'm working on, I have the user database backup job scheduled to run at 2:00am every morning. I have the @CleanupTime parameter set to 48 and I haven't specified the @CleanupMode parameter.

If I'm understanding this correctly, that should run daily and keep two copies of the the database backup... Since the default is "AFTER_BACKUP", it should delete the oldest file after the new backup completes, only leaving the oldest file if the newest backup fails. So it should start with two days' of files in the folder, create a third file (for the current day) and then delete the oldest file of the three.

What's odd is that sometimes it leaves two days' worth of files and sometimes it leaves three, even though all of the backups are successfully completing. It's not a huge issue but, in the interest of saving space on that drive, I'd prefer to only keep two backups at any given time. Any tips or suggestions would be appreciated.

Edit:
As requested in the comments below, here is the list of parameters in that job step...

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'ALL_DATABASES',
@Directory = NULL,
@BackupType = 'FULL',
@Verify = 'N',
@CleanupTime = 48,
@CheckSum = 'Y',
@LogToTable = 'Y'

This can occur when you have variations in the runtime of your backups. If your backup job executes faster one day it may be less than 48 hours since the oldest file was created, so it will not remove it and leave you with 3 files.

Example:

Backup1 created on 2019-06-17 at 21:01 - deletes backups created on or before 2019-06-15 21:01
Backup2 created on 2019-06-17 at 21:03 - deletes backups created on or before 2019-06-16 21:03
Backup3 created on 2019-06-17 at 20:59 - deletes backups created on or before 2019-06-17 20:59 which does not include Backup1

The easiest solution I've found for this is to subtract a few hours from the CleanupTime value. Setting to 40 or even 45 in the example above would mean Backup1 was deleted after Backup3 was taken.


Best way to explain the importance of Raid 10 to SysAdmins Hi I'm trying to impress upon a group of administrators as to why the extra money should be spent on raid 10 vs say raid 5.  Other then just pointing at MS Best Practices does anyone know of any good testing documentation to illustrate the point ?

This is just a reference. A great session recording at SQL PASS Summit 2011 by Wesley Brown on this subject. It is an hour and 15 minutes of just storage talk. He goes over RAID configurations and then how they pertain to SQL Server and the pros and cons of each:
Understanding Storage Systems and SQL Server

You will need to register with SQL PASS in order to view it but it is well worth it. To find the recording go to this link and look for course "DBA-103". I don't think the same slide deck he uses is available for download but he posted the deck used at a few SQL Saturday events here.