Whether the data can be manipulated by a user depends on the rights he has. Rights to database objects like tables and views are given either directly to a user or via a role. Roles have the advantage that you can assign them to new users easily without having to deal with the details. I would suggest at least two roles: An admin role and a user role.

CREATE ROLE myApp_Admin;
CREATE ROLE myApp_User;

Assign rights to the roles:

GRANT SELECT, INSERT, UPDATE, DELETE ON state TO myApp_Admin;
GRANT SELECT                         ON state TO myApp_User;
-- ... grant rights to other tables and views here ...

Then, assign these roles to users

GRANT myApp_Admin TO invertigo;
GRANT myApp_User TO someoneElse;

  Concern: data can be manipulated easily and modifying these records would break other parts of the software.

This should not be the case, if the database has been designed carefully. If you are using referential integrity, the users should not able manipulate the data in a way that corrupts the application. If you have a table employee that refers to state, then define a foreign key constraint on this column:

ALTER TABLE employee ADD CONSTRAINT FK_emp_sta_myApp
FOREIGN KEY (state_id)
REFERENCES state.id;

With this constraint it is not possible to delete a state or change its id as long as it is referenced in employee.

A completely different way of solving this problem, is to not have a lookup table (or view) at all in your db, but to keep the lookup information within your application (e.g. as embedded xml resource).

Yet another solution:

Create the table as another oracle owner and grant the select right to your usual application owner. Here you can then create a view.

CONNECT otherOwner;
CREATE TABLE state (...);
GRANT SELECT ON state TO appOwner WITH GRANT OPTION;
-- GRANT OPTION allows appOwner to grant this SELECT right to your users or roles.

CONNECT appOwner;
CREATE VIEW state AS
    SELECT * FROM otherOwner.state
    WITH READ ONLY;

or you could just create a synonym (as appOwner):

CREATE SYNONYM state FOR otherOwner.state;

In both cases your users must not have access to otherOwner.


Is there a query to see if all of my logins and password are moved during migration from SQL Server 2005 to 2008R2

Use something like SSDT to do a schema comparison on the before and after. Check this out: https://msdn.microsoft.com/en-us/library/hh272690(v=vs.103).aspx

Note: This is only for the logins, obviously showing passwords isn't supported


Where clause from OUTER APPLY using LEFT function

How about:

WHERE D.NDISCOMPLFAB LIKE DS.codigoDesenhoSubstitutivo + '_'

...which will allow an index on (NDISCOMPLFAB, Dendis) to be used effectively.

Please check that the column types are the same kind between D.NDISCOMPLFAB and DS.codigoDesenhoSubstitutivo, such as both nvarchar or both varchar. If they are different, use:

WHERE D.NDISCOMPLFAB LIKE CAST(DS.codigoDesenhoSubstitutivo AS ...) + '_'

...where the ... is whatever the type of NDISCOMPLFAB is.

Also, the GROUP BY clause should be redundant because of the TOP 1.


How to cache select with variables? (MySQL)

For efficient fetch of a random row, here is a blog on such.

Do you really select all columns of all rows, as indicated in the timings you mention?

Keep in mind that all entires for a given table in the "Query cache" are deleted whenever any write operation happens on that table.  (This makes the QC rarely of any use in busy production servers.)

A common mistake in many-to-many table is to have an AUTO_INCREMENT id.  Check out that and other many:many tips here.

VIEWs are a convenience, not a way to get efficiency.  Pretend you don't have the VIEW and write the real query you need.  If it is 'fast enough', then I have given you the answer.  If not, let's see it and look for ways to optimize it.  Please provide SHOW CREATE TABLE for each relevant table.


I would be very tempted to store in a no-SQL data store, like Mongo or Couch.  Writes are incredibly fast, scales well, etc.

You might even archive in a mongo collection, then store "processed" results in an RDBMS, which you can then query very quickly with SQL.

To stay in MySQL, you're looking at some sort of partitioning scheme to get this to scale at all.


Postgresql: How to avoid encoding issues when copying a schema from one server to another?

This looks like you are migrating from one OS to another (like Linux / Windows or something like it), because different OSes have different names for locales.

One way I would do it is to find out the exact locale name on the target system which corresponds to the one on the source, then use a text dump (the "normal" output of pg_dump) and on the target system, use psql to log in and set the "client encoding" to whatever the locale name from the first step is, then load the dump with \i.


Implicit conversion: when does it hurt performance a lot?

As Justin implies, the when it's a problem will vary from query to query and execution plan to execution plan. There's no definitive list of scenarios that guarantee you'll experience performance issues as a result of implicit conversion.
One way to tell if a query facing implicit conversion is experiencing performance issues is to check for cardinality estimate issues, generally the two go hand-in-hand when the implicit conversion is causing performance issues. You can do so by checking the Actual Number of Rows vs the Estimated Number of Rows for each operation of the query's execution plan, and if there's a large deviation between the two (generally an order of magnitude or more), then there's cardinality estimate issues. Generally you can look for the operations involved with the thickest lines to quickly spot which operations you can start checking cardinality estimate issues for. As Justin also mentioned, generally this is when the implicit conversion is happening on one of the indexed fields, specifically in a predicate (JOIN, WHERE, or HAVING clause) of the query.
But not all implicit conversion warnings are worth a grain of salt, and if you see such a warning but your query is running optimally, then you likely no need to spend time debugging it, especially if the execution plan doesn't also have cardinality estimate issues.


check for dependencies on other servers in SQL server 2000

I'm fairly sure that there isn't a reliable way to do this. I remember being very surprised when I dropped and recreated a table in SQL Server 2000 and lost all the dependencies to the stored procedures that refer to it.

You can try looking at sysremotelogins and sysservers, but I doubt that will cover the full list.


This should work:

-- Query every name_changes where rownum <= per group limit (2)
select * from (
    -- subquery for attaching rownumber counter for `name:city` groups
    select `date`, name, city , old_mul, new_mul,
        case when @lag != concat(name, ':', city) then @rownum:=1 else @rownum:=@rownum+1 end as rownum,
        @lag:=concat(name, ':', city)
    from name_changes,
    -- init values
    (select @rownum:=0) init_rownum,
    (select @lag:=null) init_la
    order by name, city, d desc
) numbered
where rownum <= 2;

I had a previous answer about mimicking window functions in mysql: How do I write this query? where I explained this in more details.


How do I search by attribute in DynamoDB?

I contacted AWS support, and they said two things:

The read/write capacity of the table needed to be changed from "provisioned" to "on-demand".

Scan is definitely the command to use here, not query; query requires the id, scan does not.
aws dynamodb scan --table-name users --filter-expression "Username = :v1"
    --expression-attribute-values "{ \":v1\" : { \"S\" : \"example\" } }"


Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN

The query is

SELECT SUM(Amount) AS SummaryTotal
FROM   PDetail WITH(NOLOCK)
WHERE  ClientID = @merchid
       AND PostedDate BETWEEN @datebegin AND @dateend

The table contains 103,129,000 rows.

The fast plan looks up by ClientId with a residual predicate on the date but needs to do 96 lookups to retrieve the Amount. The <ParameterList> section in the plan is as follows.

        <ParameterList>
          <ColumnReference Column="@dateend"
                           ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
          <ColumnReference Column="@datebegin"
                           ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
          <ColumnReference Column="@merchid"
                           ParameterRuntimeValue="(78155)" />
        </ParameterList>

The slow plan looks up by date and has lookups to evaluate the residual predicate on ClientId and to retrieve the amount (Estimated 1 vs Actual 7,388,383). The <ParameterList> section is

        <ParameterList>
          <ColumnReference Column="@EndDate"
                           ParameterCompiledValue="'2013-02-01 23:59:00.000'"
                           ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
          <ColumnReference Column="@BeginDate"
                           ParameterCompiledValue="'2013-01-01 00:00:00.000'"
                           ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
          <ColumnReference Column="@ClientID"
                           ParameterCompiledValue="(78155)"
                           ParameterRuntimeValue="(78155)" />
        </ParameterList>

In this second case the ParameterCompiledValue is not empty. SQL Server successfully sniffed the values used in the query.

The book "SQL Server 2005 Practical Troubleshooting" has this to say about using local variables

  Using local variables to defeat parameter sniffing is a fairly common
  trick, but the OPTION (RECOMPILE) and OPTION (OPTIMIZE FOR) hints
  ... are generally more elegant and slightly less risky solutions

  Note

  In SQL Server 2005, statement level compilation allows for compilation
  of an individual statement in a stored procedure to be deferred until
  just before the first execution of the query. By then the local
  variable's value would be known. Theoretically SQL Server could take
  advantage of this to sniff local variable values in the same way that
  it sniffs parameters. However because it was common to use local
  variables to defeat parameter sniffing in SQL Server 7.0 and SQL
  Server 2000+, sniffing of local variables was not enabled in SQL
  Server 2005. It may be enabled in a future SQL Server release though
  which is a good reason to use one of the other options outlined in
  this chapter if you have a choice.

From a quick test this end the behaviour described above is still the same in 2008 and 2012 and variables are not sniffed for deferred compile but only when an explicit OPTION RECOMPILE hint is used.

DECLARE @N INT = 0

CREATE TABLE #T ( I INT );

/*Reference to #T means this statement is subject to deferred compile*/
SELECT *
FROM   master..spt_values
WHERE  number = @N
       AND EXISTS(SELECT COUNT(*) FROM #T)

SELECT *
FROM   master..spt_values
WHERE  number = @N
OPTION (RECOMPILE)

DROP TABLE #T

Despite deferred compile the variable is not sniffed and the estimated row count is inaccurate

So I assume that the slow plan relates to a parameterised version of the query.

The ParameterCompiledValue is equal to ParameterRuntimeValue for all of the parameters so this is not typical parameter sniffing (where the plan was compiled for one set of values then run for another set of values).

The problem is that the plan that is compiled for the correct parameter values is inappropriate.

You are likely hitting the issue with ascending dates described here and here. For a table with 100 million rows you need to insert (or otherwise modify) 20 million before SQL Server will automatically update the statistics for you. It seems that last time they were updated zero rows matched the date range in the query but now 7 million do.

You could schedule more frequent statistics updates, consider trace flags 2389 - 90 or use OPTIMIZE FOR UKNOWN so it just falls back on guesses rather than being able to use the currently misleading statistics on the datetime column.

This might not be necessary in the next version of SQL Server (after 2012). A related Connect item contains the intriguing response

  Posted by Microsoft on 8/28/2012 at 1:35 PM
  We've done a cardinality
  estimation enhancement for the next major release that essentially
  fixes this. Stay tuned for details once our previews come out.    Eric

This 2014 improvement is looked at by  Benjamin Nevarez towards the end of the article:

A First Look at the New SQL Server Cardinality Estimator.

It appears the new cardinality estimator will fall back and use average density in this case rather than giving the 1 row estimate.

Some additional details about the 2014 cardinality estimator and ascending key problem here:

New functionality in SQL Server 2014 – Part 2 – New Cardinality Estimation


MySQL vs PostgreSQL: Benchmarking COUNT(*) execution speed

The way that both RDBMS do the count differs. In InnoDB we have the following behaviour by default:

  To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of
  the table, which takes some time if the index is not entirely in the
  buffer pool.

For Postgres, you may want to try to see if an index-only scan (which is closer to the InnoDB behaviour) can help you on this. More info here. Due the amount of rows and the bad cardinality of that value (almost 15% of the table according stats), I can't warranty that it will work, but you can try:

SELECT COUNT(t2_id) FROM t1 WHERE t2_id = 7;


Restarting a Publisher involved in Merge replication

Will transactions be safely cued on the Subscribers - I imagine this will potentially cause transaction log growth?

Transactions will be safely cued on the subscribers and transaction log growth will be negligible.  Merge Replication utilizes DML triggers along with change tracking tables in the publication and subscription databases that contains metadata to determine which changes need to be propagated.  So you might see some growth in the Merge metadata tables while the publisher is offline.

What is a reasonable time period for the Publisher to be offline - do I need to worry about expiry?

Subscriptions will expire if the they do not synchronize with the publisher within the publication retention period.  The default retention period is 14 days.  You can check what your current publication retention period is by executing sp_helpmergepublication at the publisher on the publication database and inspecting retention and retention_period_unit in the result set.  Another way to check your publication retention period is to right-click the publication in SSMS -> Properties.  On the General page there is a section labelled Subscription expiration.

If you restart the publisher and it comes back online within the publication retention period then Merge Replication will pick up where it left off.


Ola Hallengren DatabaseBackup error on locating Windows directory

Community wiki answer:

Use a domain account with access rights on the share, then this should work. NT_Service will not be able to reach it.

If using a domain account is not possible for some reason, you can try granting the machine account permission to the share. The machine account follows the following format: DOMAIN\MachineName$. The $ is needed to denote it's a computer and not a user.


You're getting this error because you're trying to update a row to service table that does not have a valid value for the company_id field based on the values currently stored in company table.

If you post some more code someone can help you diagnose the specific cause.


This works, there may be something more elegant:

 insert
 select 1 as client, 'A' as [status], 500 as 'Size'
 into #temp
 insert into #temp
 select 1 as client, 'B' as [status], 1500 as 'Size'
  insert into #temp
 select 2 as client, 'A' as [status], 2000 as 'Size'
 insert into #temp
 select 2 as client, 'B' as [status], 800 as 'Size'

 select t.client, ta.size as 'A', tb.size as 'B'
 from #temp as t
 INNER JOIN (select client, sum(size) as size from #temp where status = 'A' group by client) as ta on t.client = ta.client
 INNER JOIN (select client, sum(size) as size from #temp where status = 'B' group by client) as tb on t.client = tb.client
 group by t.client, ta.size, tb.size


If you are unable to adapt the queries, some of these columns in the where clause will remain not sargable.

To make your query sargable, You would need to try and remove the DATEADD() functions in the where clause, around Col146.
This makes the (possible) index on Col146 not sargable.

Consider this as a possible change:

AND Col146 <= DATEADD(dd,-@P4,CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME))
AND Col146 >= DATEADD(dd,-@P8,CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME))

Making sure that all functions are applied to the variables. You should not take my word for it and double check this.

Index changes.

If you could make your query sargable & the sort is not expensive.

CREATE INDEX IX_Table1_Col39_Col146_Col75
ON Table1(Col39,Col146,Col75)
INCLUDE(Col18
,Col31
,Col30 )
WHERE Col18  IS NOT NULL

However, If a lot of rows are returned after the search predicates, you might have to index your SORT (Order by) As well, since that one will use a lot of CPU depending on the  amount of rows passing through. We would have to see an actual query plan to determine this.

We are not able to change the query, no matter how much we beg and plead for mercy

You could try and index that what is sargable and try to remove the SORT operator.

CREATE INDEX IX_Table1_Col39_Col75_Col18_Col31_Col30
ON Table1(Col39 ASC ,Col75 ASC ,Col18 ASC,Col31 DESC, Col30 ASC)
INCLUDE(  Col146 )
WHERE Col18  IS NOT NULL;

Note that the <> operator is not that sargable, you could probably try and change it to;

CREATE INDEX IX_Table1_Col39_Col146_Col75
ON Table1(Col39 ASC ,Col18 ASC,Col31 DESC, Col30 ASC)
INCLUDE(  Col146,Col75 )
WHERE Col18  IS NOT NULL;

What about the other columns?

The include list is something I will leave to you, depending on the expected resultset's, the key lookup could be something to live with or not.

The optimizer might not even use the index if it thinks that the key lookup would be too expensive.

Testing the theories

To see if what I am saying holds any truth, I conducted a test, ofcourse, YMMV. It is simply to see if it is even possible to remove  the SORT operator and that we are able to seek on certain filters.

DDL + DML Below.

Index #1

CREATE INDEX IX_Table1_Col39_Col75_Col18_Col31_Col30
ON Table1(Col39 ASC ,Col75 ASC ,Col18 ASC,Col31 DESC, Col30 ASC)
INCLUDE(  Col146 )
WHERE Col18  IS NOT NULL;

While we are able to seek on col75 and col39, the sort + residual predicate remains:

And that is not what was expected unfortunately.

There is light at the end of the Tunnel (Index #2)

CREATE INDEX IX_Table1_Col39_Col18_Col31_Col30
ON Table1(Col39 ASC  ,Col18 ASC,Col31 DESC, Col30 ASC)
INCLUDE(  Col146,Col75)
WHERE Col18  IS NOT NULL;

I can rerun my modified test query (without all the selected columns)

         Declare
        @P0 VARCHAR(8000)
        ,@P1 VARCHAR(8000)
        ,@P2 VARCHAR(8000)
        ,@P3 VARCHAR(8000)
        ,@P4 INT
        ,@P5 VARCHAR(8000)
        ,@P6 VARCHAR(8000)
        ,@P7 VARCHAR(8000)
        ,@P8 INT
        ,@P9 VARCHAR(8000)

Set @P0 = '1blabla'
Set @P1 ='20190315'
Set @P2 ='20190315'
Set @P3 ='20190315'
Set @P5 ='20190315'
Set @P6 ='20190315'
Set @P7 ='20190315'

SET @P9 = '0bla'
Set @P4 =1
Set @P8 =1
SELECT *
FROM table1 Where
(
 Col18 IS NOT NULL
        AND Col75 <> @P0
           AND DATEDIFF(dd, Col146, CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME)) <= @P4
        AND DATEDIFF(dd, Col146, CAST(SUBSTRING(@P5, 1, 4) + '-' + SUBSTRING(@P6, 5, 2) + '-' + SUBSTRING(@P7, 7, 2) + 'T00:00:00.000' AS DATETIME)) >= @P8

     )
    AND Col39 = @P9
ORDER BY Col18 ASC
    ,Col31 DESC
    ,Col30 ASC;

To get a pretty seek with no sort or other operators (And a pesky residual predicate.)

Your result could look more like this:

Conclusion

Based on these tests, which may very well be 100% different for you, this is the index that I would initially go for:

CREATE INDEX IX_Table1_Col39_Col18_Col31_Col30
ON Table1(Col39 ASC  ,Col18 ASC,Col31 DESC, Col30 ASC)
INCLUDE(  Col146,Col75)
WHERE Col18  IS NOT NULL;

DDL + DML Used in this example

        SET NOCOUNT ON;
        DECLARE @i int = 0
        WHILE @i < 100
        BEGIN
        INSERT INTO Table1(Col39,   Col75,Col146,Col18,Col31,Col30)
        VALUES(cast(@i as varchar(3))+'bla',
               cast(@i as varchar(3))+'blabla',
               DATEADD(DD,-@i,GETDATE()),
               cast(@i as varchar(3))+'blablabla',
               cast(@i as varchar(3))+'blablablabla',
               cast(@i as varchar(3))+'blablablablabla');
        SET @i += 1;
        END
        SET NOCOUNT OFF ;
        INSERT INTO Table1
        SELECT * From Table1;
        GO 7
        Beginning execution loop

   (6500 rows)

    GO


compare results between two select queries MySQL

pt-table-checksum and its friends are very good at validating that the data is (or is not) the same.


Deadlock during Slony uninstall and concurrent access of DB

I suspect you are going to have to disable application access during the uninstall process.  The basic problem is that Slony uses large numbers of triggers to maintain the binary logs for shipping.  These triggers are going to be hard to delete while there is concurrent access.


CHECK constraint to enforce ###.###.#### pattern match

The regex in your question is not entirely unambiguous

  In most flavors that support Unicode, \d includes all digits from all
  scripts. Notable exceptions are Java, JavaScript, and PCRE. These
  Unicode flavors match only ASCII digits with \d.

So in many flavours it would match ١١١.١١١.١١١١ (that character being Arabic-Indic Digit One)

I'm assuming that you just want to match latin 0-9. You can use

DECLARE  @T TABLE
(
Col CHAR(12) CHECK (
                   Col LIKE REPLACE('ddd.ddd.dddd','d','[0-9]')
                                  COLLATE Latin1_General_100_BIN2
                   )
)

I'm using the binary collate clause so strings like ¾¾¾.¾¾¾.¾¾¾¾ or 10².10².1000 don't pass the check (as can happen on some collations).

Neither of these would match the desired CLR regex and although using the set syntax ([0123456789]) rather than the range syntax ([0-9]) resolves the first one  it is still collation dependent whether 2 matches ² or not.

I'm using REPLACE rather than hard-coding the LIKE pattern because I find it more readable what the pattern is checking. It is easier to see three digits and a dot followed by three digits another dot then four digits in that format than parsing [0-9][0-9][0-9].[0-9] [0-9][0-9].[0-9][0-9][0-9][0-9].

It also makes it easier to swap out implementation.

I you do decide that you want the full blown semantics of the CLR regular expression after all you could just change '[0-9]' above to the below (and likely change the column to NCHAR(12) in that case)

'[' +
 /*48-57 Basic Latin*/
N'0-9'+
 /*1632-1641 Arabic-Indic*/
N'٠-٩'+
 /*1776-1785 Extended Arabic-Indic*/
N'۰-۹'+
 /*1984-1993 Nko*/
N'߀-߉'+
 /*2406-2415 Devanagari*/
N'०-९'+
 /*2534-2543 Bengali*/
N'০-৯'+
 /*2662-2671 Gurmukhi*/
N'੦-੯'+
 /*2790-2799 Gujarati*/
N'૦-૯'+
 /*2918-2927 Oriya*/
N'୦-୯'+
 /*3046-3055 Tamil*/
N'௦-௯'+
 /*3174-3183 Telugu*/
N'౦-౯'+
 /*3302-3311 Kannada*/
N'೦-೯'+
 /*3430-3439 Malayalam*/
N'൦-൯'+
 /*3558-3567 Sinhala*/
N'෦-෯'+
 /*3664-3673 Thai*/
N'๐-๙'+
 /*3792-3801 Lao*/
N'໐-໙'+
 /*3872-3881 Tibetan*/
N'༠-༩'+
 /*4160-4169 Myanmar*/
N'၀-၉'+
 /*4240-4249 Myanmar Shan*/
N'႐-႙'+
 /*6112-6121 Khmer*/
N'០-៩'+
 /*6160-6169 Mongolian*/
N'᠐-᠙'+
 /*6470-6479 Limbu*/
N'᥆-᥏'+
 /*6608-6617 New Tai Lue*/
N'᧐-᧙'+
 /*6784-6793 Tai Tham Hora*/
N'᪀-᪉'+
 /*6800-6809 Tai Tham Tham*/
N'᪐-᪙'+
 /*6992-7001 Balinese*/
N'᭐-᭙'+
 /*7088-7097 Sundanese*/
N'᮰-᮹'+
 /*7232-7241 Lepcha*/
N'᱀-᱉'+
 /*7248-7257 Ol Chiki*/
N'᱐-᱙'+
 /*42528-42537 Vai*/
N'꘠-꘩'+
 /*43216-43225 Saurashtra*/
N'꣐-꣙'+
 /*43264-43273 Kayah Li*/
N'꤀-꤉'+
 /*43472-43481 Javanese*/
N'꧐-꧙'+
 /*43504-43513 Myanmar Tai Laing*/
N'꧰-꧹'+
 /*43600-43609 Cham*/
N'꩐-꩙'+
 /*44016-44025 Meetei Mayek*/
N'꯰-꯹'+
 /*65296-65305 Fullwidth*/
N'０-９'+
N']'  COLLATE Latin1_General_100_BIN2


How can I first sort by color & then in alphabetical order in ssrs?

The order by clause in your SQL query should have worked.

Since it isn't working that means that something else is interfering with the sort order.

The next place to look is in your Tablix Properties under sorting.

Then look at any Group Properties. Make sure you check each row.

If you still can't find it, right click on the report in Visual studio and view code. You can then Ctr-F and search for "Sort" and find any sort expressions in the .rdl.

If you find an object you want to look at, you can then use search properties to select the object by name.


Oracle 11.2.0.4.0 32 bits full client installation

This is not an Oracle, but a Microsoft Windows issue. When you install a 32-bit ODBC driver on a 64-bit operating system, and try to configure ODBC data sources from the Start menu or Control panel, it opens the utility (C:\Windows\System32\odbcad32.exe) for managing 64 bit drivers and data sources with them.

If you want to manage ODBC with a 32-bit driver, run this instead:

C:\Windows\SysWOW64\odbcad32.exe

Newer versions of Windows offer 2 seperate menu entries for managing 32-bit and 64-bit ODBC.

  Why cannot Oracle have a simple installer where you install everithing
  you need?, instead of having to donwload multiple packages, merging
  them and running console installers.

Here is the full, graphical installer (win32_11gR2_client.zip) for 11.2:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

The 11.2.0.4 version can be downloaded from https://support.oracle.com/ with a valid support contract, which is required for downloading updates.


Postgres: Given some parent id's how can I SELECT X child records for each?

Assuming the "first" is defined through the id column in the child table, something like this should work:

select p.id, p.name, c.name
from parent p
  join (
     select c.name,
            c.parent_id,
            row_number over (partition by c.parent_id order by c.id) as rn
     from child c
  ) ch
   on ch.parent_id = p.id
  and ch.rn <= 3
where p.id in (1,3,4);


Use the NUMBER data type instead of NUMERIC(18,2), which is a SQL Server data type.  Also, the CHAR column should be TEXT...  Use the visual designer to design your table; it will force you to choose from valid data types, and will automatically generate the correct syntax.

See the MSDN Microsoft Access Data Types page for details.

It seems like you are familiar with SQL Server T-SQL syntax, why don't you use SQL Server Express, which is free, instead of Microsoft Access?


... adding to my recent comment ...

http://sqlfiddle.com/#!9/8e8f57/4

select DATE(added), SUM(amount) ,currency
from transactions
where  DATE(added)  > subdate(CURDATE(),10)
  AND status = 'APPROVED'
GROUP BY  currency,DATE(added)
ORDER BY DATE(added) desc;

delivers daily amounts separated by currency as you wanted


Cannot SUM the value of a sales amount field - various criteria, sales rep id and date ranges

There are more compact ways to write this, but this illustrates a way to do it.
First select the territories and periods that Anne was looking after that territory.
Then pull all the sales where the transaction date falls in one of the periods when Anne was covering the territory where the sale was made.
Then sum up the sales.
WITH
 territory_periods AS (
SELECT SalesRep, TerritoryID, FromDate, ToDate
  FROM tblSalesRep
 WHERE SalesRep = 'Anne'
)
,sales AS (
SELECT p.SalesRep, s.Amount
  FROM tblSales s
  JOIN territory_periods p
    ON s.TerritoryID = p.TerritoryID
   AND s.TransactionDate BETWEEN p.FromDate AND p.ToDate
)
SELECT SalesRep, Sum(Amount)
  FROM sales
GROUP
    BY SalesRep
;


Why does SQL Injection not happen on this query inside a stored procedure?

This code works properly because it is:

Parameterized, and
Not doing any Dynamic SQL

In order for SQL Injection to work, you have to build a query string (which you are not doing) and not translate single apostrophes (') into escaped-apostrophes ('') (those are escaped via the input parameters).

In your attempt to pass in a "compromised" value, the 'Male; DROP TABLE tblActor' string is just that, a plain-ol' string.

Now, if you were doing something along the lines of:

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT fields FROM table WHERE field23 = '
          + @InputParam;

EXEC(@SQL);

then that would be susceptible to SQL Injection because that query is not in the current, pre-parsed context; that query is just another string at the moment. So the value of @InputParam could be '2015-10-31'; SELECT * FROM PlainTextCreditCardInfo; and that might present a problem because that query would be rendered, and executed, as:

SELECT fields FROM table WHERE field23 = '2015-10-31'; SELECT * FROM PlainTextCreditCardInfo;

This is one (of several) major reason to use Stored Procedures: inherently more secure (well, as long as you don't circumvent that security by building queries like I showed above without validating the values of any parameters used). Though if you need to build Dynamic SQL, the preferred way is to parameterize that as well using sp_executesql:

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT fields FROM table WHERE field23 = @SomeDate_tmp';

EXEC sp_executesql
  @SQL,
  N'SomeDate_tmp DATETIME',
  @SomeDate_tmp = @InputParam;

Using this approach, someone attempting to pass in '2015-10-31'; SELECT * FROM PlainTextCreditCardInfo; for a DATETIME input parameter would get an error when executing the Stored Procedure. Or even if the Stored Procedure accepted @InputParameter as NVARCHAR(100), it would have to convert to a DATETIME in order to pass into that sp_executesql call. And even if the parameter in the Dynamic SQL is a string type, coming into the Stored Procedure in the first place any single apostrophe would automatically get escaped to a double apostrophe.

There is a lesser known type of attack in which the attacker tries to fill up the input field with apostrophes such that a string inside of the Stored Procedure that would be used to construct the Dynamic SQL but which is declared too small can't fit everything and pushes out the ending apostrophe and somehow ends up with the correct number of apostrophes so as to no longer be "escaped" within the string. This is called SQL Truncation and was talked about in an MSDN magazine article titled "New SQL Truncation Attacks And How To Avoid Them", by Bala Neerumalla, but the article is no longer online. The issue containing this article — the November, 2006 edition of MSDN Magazine — is only available as a Windows Help file (in .chm format). If you download it, it might not open due to default security settings. If this happens, then right-click on the MSDNMagazineNovember2006en-us.chm file and select "Properties". In one of those tabs there will be an option for "Trust this type of file" (or something like that) which needs to be checked / enabled. Click the "OK" button and then try opening the .chm file again.

Another variation of the Truncation attack is, assuming a local variable is used to store the "safe" user-supplied value as it had any single-quotes doubled so as to be escaped, to fill up that local variable and place the single-quote at the end. The idea here is that if the local variable is not properly sized, there won't be enough room at the end for the second single-quote, leave the variable ending with a single single-quote that then combines with the single-quote that ends the literal value in the Dynamic SQL, turning that ending single-quote into an embedded escaped single-quote, and the string literal in the Dynamic SQL then ends with the next single-quote that was intended to begin the next string literal. For example:

-- Parameters:
DECLARE @UserID      INT = 37,
        @NewPassword NVARCHAR(15) = N'Any Value ....''',
        @OldPassword NVARCHAR(15) = N';Injected SQL--';

-- Stored Proc:
DECLARE @SQL NVARCHAR(MAX),
        @NewPassword_fixed NVARCHAR(15) = REPLACE(@NewPassword, N'''', N''''''),
        @OldPassword_fixed NVARCHAR(15) = REPLACE(@OldPassword, N'''', N'''''');

SELECT @NewPassword AS [@NewPassword],
       REPLACE(@NewPassword, N'''', N'''''') AS [REPLACE output],
       @NewPassword_fixed AS [@NewPassword_fixed];
/*
@NewPassword          REPLACE output          @NewPassword_fixed
Any Value ....'       Any Value ....''        Any Value ....'
*/

SELECT @OldPassword AS [@OldPassword],
       REPLACE(@OldPassword, N'''', N'''''') AS [REPLACE output],
       @OldPassword_fixed AS [@OldPassword_fixed];
/*
@OldPassword          REPLACE output          @OldPassword_fixed
;Injected SQL--       ;Injected SQL--         ;Injected SQL--
*/

SET @SQL = N'UPDATE dbo.TableName SET [Password] = N'''
           + @NewPassword_fixed + N''' WHERE [TableNameID] = '
           + CONVERT(NVARCHAR(10), @UserID) + N' AND [Password] = N'''
           + @OldPassword_fixed + N''';';

SELECT @SQL AS [Injected];

Here, the Dynamic SQL to be executed is now:

UPDATE dbo.TableName SET [Password] = N'Any Value ....'' WHERE [TableNameID] = 37 AND [Password] = N';Injected SQL--';

That same Dynamic SQL, in a more readable format, is:

UPDATE dbo.TableName SET [Password] = N'Any Value ....'' WHERE [TableNameID] = 37 AND [Password] = N';

Injected SQL--';

Fixing this is easy. Just do one of the following:

DON'T USE DYNAMIC SQL UNLESS ABSOLUTELY NECESSARY! (I'm listing this first because it really should be the first thing to consider).
Properly size the local variable (i.e. should be twice the size as the input parameter, just in case all characters passed-in are single-quotes.
Don't use a local variable to store the "fixed" value; just put the REPLACE() directly into the creation of the Dynamic SQL:

SET @SQL = N'UPDATE dbo.TableName SET [Password] = N'''
           + REPLACE(@NewPassword, N'''', N'''''') + N''' WHERE [TableNameID] = '
           + CONVERT(NVARCHAR(10), @UserID) + N' AND [Password] = N'''
           + REPLACE(@OldPassword, N'''', N'''''') + N''';';

SELECT @SQL AS [No SQL Injection here];

The Dynamic SQL is no longer compromised:

UPDATE dbo.TableName SET [Password] = N'Any Value ....''' WHERE [TableNameID] = 37 AND [Password] = N';Injected SQL--';

Notes about the Trunction example above:

Yes, this is a very contrived example. There is not much one can do in only 15 characters to inject. Sure, maybe a DELETE tableName to be destructive, but less likely to add a back-door user, or change an admin password.
This type of attack probably requires knowledge of the code, table names, etc. Less likely to be done by random stranger / script-kiddie, but I did work at a place that was attacked by a rather upset former employee who knew of a vulnerability in one particular web page that nobody else was aware of. Meaning, sometimes attackers do have intimate knowledge of the system.
Sure, resetting everyone's password is likely to be investigated, which might tip the company off that there is an attack happening, but it might still provide enough time to inject a back-door user or maybe get some secondary info to use/exploit later.
Even if this scenario is mostly academic (i.e. not likely to happen in the real world), it's still not impossible.

For more detailed information related to SQL Injection (covering various RDBMS's and scenarios), please see the following from the Open Web Application Security Project (OWASP):
Testing for SQL Injection

Related Stack Overflow answer on SQL Injection and SQL Truncation:
How safe is T-SQL after you replace the ' escape character?


Better way to concatenate a sequence of string replacements in Amazon RDS?

With plperlu and Regexp::Assemble

It depends on the length of the string and the length of the input. You can create a fully optimized regexp, using Perl's Regexp::Assemble and run it with plperlu.

The below code caches the regex for the entire session, so subsequent calls should be stupid fast.

CREATE OR REPLACE FUNCTION replace_from_all(text, text[])
RETURNS TEXT
AS $$
  use strict;

  my ( $text, $rearray ) = @_;
  my $re;

  my $allre = join '', @$rearray;
  if ( exists $_SHARED{$allre} ) {
    $re = $_SHARED{$allre};
  }
  else {
    require "Regexp/Assemble.pm";
    my $ra = Regexp::Assemble->new;
    $ra->add($_) for @$rearray;
    $re = $_SHARED{$allre} = $ra->re;
  }

  $text =~ s/$re//ge;
  return $text;
$$
LANGUAGE plperlu
IMMUTABLE;

Now you can call it with,

SELECT replace_from_all('foobar', ARRAY['o', 'b'] );

Alternatively, you hard core the regexes in there, but if you're going to do that you may as well chop out Regexp::Assemble, and just paste a precompiled regex.

With plperl

CREATE OR REPLACE FUNCTION replace_from_all(text, text[])
RETURNS TEXT
AS $$
  use strict;

  my ( $text, $rearray ) = @_;
  my $regexes;

  my $allre = join '', @$rearray;
  if ( exists $_SHARED{$allre} ) {
    $regexes = $_SHARED{$allre};
  }
  else {
    $regexes = $_SHARED{$allre} = [map qr/$_/, @$rearray];
  }

  foreach my $re ( @$regexes ) {
    $text =~ s/$re//ge;
  }
  return $text;
$$
LANGUAGE plperl
IMMUTABLE;

Speed

For this example I took the following sample data which is 300,000 rows

CREATE TABLE foo
AS
  SELECT t.x FROM generate_series(1,1e5)
  CROSS JOIN ( VALUES
    ('You don''t like diacriticals àèìòùáéíóú'),
    ('You do like noiseless numbers'),
    ('And whatever you like to try also, removing commas, , , ')
) AS t(x);

For comparison, the above version by @joanolo takes 8.04 seconds

SELECT norm(x) FROM foo;

Using plperl with,

SELECT replace_from_all(x, ARRAY['[^0-9a-z\s-]', 'n?oise?.*'])
FROM foo;

It takes 5.0 seconds.


I was changing tmp_table_size and max_heap_table_size both via PhpMyAdmin and via SET GLOBAL ... in my scripts. And it didn't work.

Changing those varaibles via my.ini, restarting server and then loading DB into memory allowed for bigger DB.


Sql Server Stored Procs vs Linq with C# and unit testing

Take a look at tSQLt (http://tsqlt.org/) which is a great free tool to create unit tests in SQL Server.

You can fake tables (by executing tSQLt.FakeTable) within your tests and then create mock data as you describe. Tests are themselves stored procedures and any changes made as part of your testing suite are rolled back post test.

You run your tests by executing the tSQLt.RunAll stored procedure and it gives you visual feedback summary of your test results.

To get started, navigate to http://tsqlt.org/downloads/ and download. Running Example.sql will create an example database called [tSQLt_Example], create the testing framework and some example tests to see what is possible.

Also available is the Redgate paid product SQL Test (http://www.red-gate.com/products/sql-development/sql-test/). This is a GUI wrapper around tSQLt but is not required to use tSQLt.


Can someone help me with this MySQL code?

Seems like you want to inner join the two tables on their Primary Keys.  Does this run for you:

CODE:

SELECT
    A.ActionID, A.FeedbackID, A.StaffID, A.ActionDate, A.ActionDetails,
    S.StaffName, S.Department

FROM Action AS A
    INNER JOIN Staff AS S ON Action.ActionID = Staff.ActionID


How to debug locked transaction on a remote system?

No, you can't undo, rollback or reverse a commit.

STOP THE DATABASE!

(Note: if you deleted the data directory off the filesystem, do NOT stop the database. The following advice applies to an accidental commit of a DELETE or similar, not an rm -rf /data/directory scenario).

If this data was important, STOP YOUR DATABASE NOW and do not restart it. Use pg_ctl stop -m immediate so that no checkpoint is run on shutdown.

You cannot roll back a transaction once it has commited. You will need to restore the data from backups, or use point-in-time recovery, which must have been set up before the accident happened.

If you didn't have any PITR / WAL archiving set up and don't have backups, you're in real trouble.

Please have a look here for Detailed Information:


How should I reinit a new db (windows 8 64b)?

you would require mysql folder from the data_old directory. As it looks for mysql related tables whenever you initiate mysql on with some other folder.    Perform following steps in sequence

  1 stop MySQL instance for old instance

  2 copy data folder or system databases folders(mysql,performace_schema,information_schema,test) folder from  "D:\wamp\bin\mysql\mysql5.6.17\data_old"   to the new path " D:\wamp\bin\mysql\mysql5.6.17\data"

  3 mysqld --install  --defaults-file =Absolute Path to .ini file

If you are successful, the message “Service successfully installed.” will appear

If so you should be able to connect to new MySQL instance with new port.

Hope it helps


What is the search_path for a given database and user?

You can find configuration settings for roles and databases in the catalog table pg_db_role_setting.

This query retrieves any settings for a given role or database:

SELECT r.rolname, d.datname, rs.setconfig
FROM   pg_db_role_setting rs
LEFT   JOIN pg_roles      r ON r.oid = rs.setrole
LEFT   JOIN pg_database   d ON d.oid = rs.setdatabase
WHERE  r.rolname = 'myrole' OR d.datname = 'mydb';

If nothing is set, the next lower instance determines the default state of the search_path, which is postgresql.conf in this case or command-line options at server start. Related:

How does the search_path influence identifier resolution and the “current schema”

To unset any settings of a role or database - the search_path in this particular example:

ALTER ROLE myrole RESET search_path;

Or:

ALTER DATABASE mydb RESET search_path;

Or:

ALTER ROLE myrole in DATABASE mydb RESET search_path;

Never manipulate data in the system catalog (pg_catalog.*) manually. Use DDL commands as instructed in the manual for ALTER ROLE and ALTER DATABASE.
Essentially, the RESET command deletes a row from pg_db_role_setting allowing the base setting to take effect again. I wouldn't call that convoluted.


Microsoft Sql Server - How to replicate a table from Linked Server?

It doesn't sound like any of the built-in SQL features are going to meet your needs here:

SQL replication makes a lot of changes to the source db, and probably won't work over a linked server
Mirroring and/or always-on availability groups likewise require lots of underlying configuration to the source server
Log shipping, if you had access to transaction log backups, would bring over the entire database state, not just the tables you want

You're going to have to either use a 3rd-party software (like Redgate SQL Compare), or roll your own.

Personally I'd build an SSIS package to pull down new or updated data from the required tables (hopefully you have some easy "last_update" fields to go off of) and run it via a SQL job every few minutes. There is no SQL wizard for this, you'll have to know what you're doing or hire someone who does.


Combine multiple similar queries into one

metric columns are redundant in survey table. we can create a new metric type column to differentiate multiple metrics and to normalize survey table. we can even do this normalization in inline view.

following sample query may explain this. it use SurveyNormalized inline view and it take aggregate group by metricType.

CREATE TABLE #Surveys(id int, VAR int, [weight] float, metric_1 int, metric_2 int, [week] int)

INSERT INTO #Surveys VALUES(1, 9, 0.56 , 4, 2, 8), (2, 3, 1, 3, 1, 7), (3, 7, 0.98 , 3, 5, 8)

SELECT
    weight, sample_size, detractor,
    (promoter * 100) / weight AS promoter_p,
    (promoter - detractor) * 100 / weight AS score,
    t.metricType
FROM
    (
        SELECT
            COUNT(id) AS sample_size,
            SUM(weight) [weight],
            SUM(CASE WHEN var BETWEEN 9 AND 10 THEN 1 END) AS promoter,
            SUM(CASE WHEN var BETWEEN 0 AND 6  THEN 2 END) AS detractor,
            MAX(week) wk,
            metricType
        FROM (
                SELECT id, var, weight, metric_1 AS metric, 'metric_1' AS metricType, week
                FROM #Surveys
                WHERE (var BETWEEN 0 AND 10) AND (metric_1 BETWEEN 1 AND 5)
                UNION
                SELECT id, var, weight, metric_2 AS metric, 'metric_2' AS metricType, week
                FROM #Surveys
                WHERE (var BETWEEN 0 AND 10) AND (metric_2 BETWEEN 1 AND 5)
            )SurveyNormalised
            GROUP BY SurveyNormalised.metricType

    ) t


Best approch to replicate data from one db to other db on same server

If the data in the reporting database does not have to be real-time, the I suggest you do not implement replication but instead go with log shipping.


MariaDB Grants for set session variables


Third Normal Form. Multiple attributes determine another?

A relation is in 3NF if for every non-trivial functional dependency, every determinant is a superkey, or, if it is not a superkey, every determinate is a prime attribute (i.e. it is part of a candidate key).

In your example, (Name, Classification) --> Supplier violates the 3NF if and only if the following two conditions are both satisfied:

(Name, Classification) is not a candidate key.
Supplier is not a prime attribute.

For the truth of the two conditions, you have to know either a set of Functional Dependencies which is a cover of all the FDs of the relation (and from those one can find all the candidate keys, and consequently, all the prime attributes of the relation), or, by knowing the meaning of the data, you know that they are true (for instance, you know that a couple of values Name, Classification can appear multiple times inside the relation, and that no candidate key exists that include Supplier).


Displaying Distinct Max Value autonumber rows from tables in MySql

Assuming that fir is the master table and (sno) its primary key:

SELECT p.sno, p.short_order, p.fir_sno
FROM fir AS f
  JOIN pre_trials AS p
    ON  p.fir_sno = f.sno
    AND p.sno =
        ( SELECT pi.sno
          FROM pre_trials AS pi
          WHERE pi.fir_sno = f.sno
          ORDER BY pi.sno DESC
          LIMIT 1
        ) ;

A composite index on (fir_sno, sno) would help greatly with efficiency of the query.

Also note that I used DESC while your code has ASC beacuse in your explanation and wanted results you mention you want the row with maximum sno.

Look also at a similar question, for further explanation of how the query works: Select ONE most recent post for each author


01/01/1753 date is corrupt in Excel after export from SSRS

Excel specifically provides a message: Dates and times that are negative or too large are displayed as ######.

So, that is a Excel limit.  Further, you can type in strings that look like dates, but Excel may not think of them as dates. For example, if I make a column of date type and enter into the columns -53689, 01/01/1753, and 01/01/1900 (using my settings) I get this:

####################
01/01/1753
            1/1/1900

So, it is easy to see that these are not all being interpreted the same by Excel.


Explanation for important reserved space

Space Reserved is the size you setup using either:

SSMS = Database Properties / Files Database file / Initial Size (MB)
Alter Database [XXX] MODIFY FILE ( NAME = N'XXX', SIZE = ...KB)
Autogrowth changes it as well

You can check these setting using SSMS or this query:

SELECT file_id, name, type_desc, physical_name, size = size/128, max_size = max_size/128
FROM sys.database_files ;

Space Used is the size of your existing data pages in your data file. You can use this query:

SELECT name, groupid, filename,
   Cast(size/128.0 AS DECIMAL(10,2)) AS [Allocated MB],
   Cast(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Used MB],
   Cast([maxsize]/128.0 AS DECIMAL(10,2)) AS [Max Size MB],
   Cast([maxsize]/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space in MB]
FROM sysfiles
ORDER BY groupid DESC


Postgres CREATE OR REPLACE VIEW... can it block?

Yes, that would block, and there is no way around that.


Inheritance and foreign keys in Postgres

Parent table1: Person (columns: ID, Name). Child tables: Man, Woman.
Parent table2: Relationship (columns: ID, Partner1 and Partner2). Child tables: Gay, Lesbian.

I'm staunchly against the inheritance modeled being used by end users, but even here this isn't a valid use case of it. Your gender isn't a child table. It's an attribute on the table. The same can be said of your relationship.

Just add a column, gender and add another column relationship

  To me, this (the fact that foreign keys do not apply across inherited tables and must be done separately) is a feature, and not a limitation. And a very useful feature too, as can be seen in the case of the aforementioned example: When the child table Lesbian references the child table Woman (instead of the parent table Person), it is very easy to prevent errors of the sort where there's a lesbian relationship between two men!

I think is going to devolve into a political question but if people's gender can't change (horrible assumption) then the relationship is known between the two of them by the gender of the people. Why would you even want an attribute on the relationship table. The class gay or lesbian would be inferred from the genders of the participants. Yes if you put it there you can have an error where there is a lesbian relationship between two men, but that's an error introduced because your data should be inferred.

Which of these two seems more logical?

f(p1,p2) = class of relationship
f(p1,p2,relationship_class) = class of relationship

See also

Using table inheritance instead of mapping tables


What is the best way to get all data for a date range, plus the last event just before the range?

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this.

The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on the entire table. So as always, test with your own situation. This query also assumes that the MAX date is either unique or that equivalent rows would be acceptable.

USE AdventureWorks2012
GO
;
WITH Base AS (
   SELECT
      TransactionHistory.*
      ,ProductVendor.BusinessEntityID
      ,MAX(CASE WHEN TransactionDate < '2008-08-01' THEN TransactionDate END)
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS PreviousVendorTransaction
      ,COUNT(CASE WHEN TransactionDate >= '2008-08-01' THEN 1 END )
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS VendorAfterCutoff
   FROM
      Production.TransactionHistory
      -- Doesn't make the most sense, but I need a repeating relation
      INNER JOIN Purchasing.ProductVendor
         ON TransactionHistory.ProductID = ProductVendor.ProductID
),
Filtered AS (
   SELECT
      *
   FROM
      Base
   WHERE
      Base.TransactionDate >= '2008-08-01'
      OR (TransactionDate = PreviousVendorTransaction AND VendorAfterCutoff > 0)
)
SELECT DISTINCT
   TransactionID
   ,ProductID
   ,ReferenceOrderID
   ,ReferenceOrderLineID
   ,TransactionDate
   ,TransactionType
   ,Quantity
   ,ActualCost
   ,ModifiedDate
FROM
   Filtered

Edit:

Hmm, I think I may have to take back my comment on structuring it differently if there are indexes. The other suggestions that I have are probably fairly minor.

Make sure the query is using the indexes you're expecting it to. Start and End date to build temp table, end date to drive the previous event loop.
If the query to build the temp table is doing a lookup on the clustered index, it may be better to hold off and do that as part of the main query.
Try using a cte instead of a temp table. I think that a cte might be more competitive with the way that the query is structured below.
If you are returning a lot of events, it might be better to pull out the event table lookup to the main query to give the optimizer the option of doing a merge join.
I don't see a way of optimizing the previous event lookup short of an indexed view.

Here's a query that combines a few of those ideas.

SELECT
    e.[EventID]
INTO #EventTemp
FROM
    [Events] AS e
WHERE
    ( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate )
    OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate )

;
WITH PrevEvent AS (
    SELECT
        EmpPrevEvent.[EventID]
    FROM
    (
        SELECT DISTINCT
            ee.[EmployeeID]
        FROM
            #EventTemp
            INNER JOIN [EventEmployee] AS ee ON
                #EventTemp.[EventID] = ee.[EventID]
    ) AS Emp
    CROSS APPLY (
        SELECT TOP 1
            e.[EventID]
        FROM
            [Events] AS e
            INNER JOIN [EventEmployee] AS ee ON
                e.[EventID] = ee.[EventID]
        WHERE
            ee.[EmployeeID] = Emp.[EmployeeID]
            AND e.[EventEnd] < @StartDate
        ORDER BY
            e.[EventEnd] DESC
    ) AS EmpPrevEvent
)
SELECT
    e.[EventID],
    e.[EventStart],
    e.[EventEnd],
    e.[EventTypeID]
FROM
    [Events] AS e
WHERE
    e.EventID IN (
        SELECT EventID
        FROM #EventTemp
        UNION
        SELECT EventID
        FROM PrevEvent
    )


First of all, as you are getting sub-second performance how much time do you want to spend on this?  If you spend an hour on it, and you tune it to half a second, how long will it take to pay for itself?  If this query runs several times a second, then I'm with you, let's tune it.  Otherwise it's not really worth it.

Having said that, there are a number of warning signs in your query and plan which need closer attention, if you have time.  This is a strange looking query - is it dynamically generated?

1) Rowcount Discrepancy

The dbo.IGroupes table is showing Estimated Rows (7) * Estimated Executions (100.99), so ~707 rows, versus 630,346 Actual Rows.  Rowcounts which are more of an order of magnitude difference tend to be a bit of a warning sign.  So why do you think this is?  What is the relationship between the dbo.Documents table and the dbo.IGroupes table?  Should you be getting a distinct list from here instead?  This would also mean the estimated cost for this operator is way off too.  There are other rowcount discrepancies and Plan Explorer does a good job of showing this:

2) Optimizer Timeout

If you click the SELECT operator in the plan and look at the properties, you will see the property "Reason for Early Termination of Statement Optimization" marked as "Time Out".  This means the optimizer ran out of "time" (more like iterations than time) and returned with the lowest estimated cost plan it had at the time.  This is generally an indication the query is too complicated and the workaround is to simplify.  Looking at your query you have the dummy 1=1 clause and a number of other OR clauses.  Can this be simplified?

A few other points: you should be aware that if you have the "Include Actual Execution Plan" option on, this negatively affects the values from SET STATISTICS TIME ON.  This is the Observer Effect.  Are your values from a run with the plan on or off?

One of my normal approaches to query tuning is to break it up.  This helps identify true bottlenecks.  It may be that later on I put the query back together.  Here's one example, but it's worth noting in my test rig, there's no real performance difference between the original query and this one:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
      IDENTITY( INT, 1, 1 ) rn
    , CAST( d.DocumentID AS INT ) DocumentID
    , d.IsReEfiled
    , d.IGroupID
    , d.ITypeID
    , d.RecordingDateTime
    , d.CreatedByAccountID
    , d.JurisdictionID
    , d.LastStatusChangedDateTime
    , d.IDate
    , d.InstrumentID
    , d.DocumentStatusID
    , d.DocumentDate
    , d.UserId

INTO #tmp
FROM Documents d
WHERE ( d.DocumentStatusID = 9 )
  AND ( d.CreatedByAccountID = @0 OR d.DocumentStatusID = @1 OR d.DocumentStatusID = @2 )
  AND ( d.IGroupID = @3 Or d.IGroupID = @4 )
  AND (d.JurisdictionID = @5 Or d.DocumentStatusID = @6 Or d.DocumentStatusID = @7 )
ORDER BY d.LastStatusChangedDateTime DESC

SELECT
      t.DocumentID
    , t.IsReEfiled
    , t.IGroupID
    , t.ITypeID
    , t.RecordingDateTime
    , t.CreatedByAccountID
    , t.JurisdictionID
    , t.LastStatusChangedDateTime
    , t.IDate
    , t.InstrumentID
    , t.DocumentStatusID
    , ig.Abbreviation AS IGroupAbbreviation
    , u.Username
    , j.JDAbbreviation
    , inf.DocumentName
    , it.Abbreviation AS ITypeAbbreviation
    , t.DocumentDate
    , it.Abbreviation AS DocumentStatusAbbreviation
    , ds.Name AS DocumentStatusName
    , ( SELECT COUNT(*) FROM #tmp ) AS TotalRecords
FROM #tmp t
    INNER JOIN dbo.ITypes it ON t.ITypeID = it.ITypeID
    INNER JOIN dbo.Users u ON t.UserID = u.UserID
    INNER JOIN dbo.IGroupes ig ON t.IGroupID = ig.IGroupID
    INNER JOIN dbo.DocumentStatuses ds On t.DocumentStatusID = ds.DocumentStatusID
    INNER JOIN dbo.Jurisdictions j on t.JurisdictionID = j.JurisdictionID
    LEFT JOIN dbo.InstrumentFiles inf On t.DocumentID = inf.DocumentID
WHERE rn > @8 AND rn <= @9
ORDER BY rn
GO

If you want to carry on, please provide the schema and indexes from your tables, plus either the stats or an idea of the rowcounts.


Building an execution plan takes too long on SQL Server

I have an SQL Server 2014 (12.0.2000.8)

RTM version? I recall running into excessive compilation duration (minutes) for some queries. The issue was fixed post RTM. I suggest you patch your server to a supported patch level (SP3+).


SQL Server Enable filestream without having SQL Server Configuration Manager(so via command line only)

Regedit

You could change the registry settings for the instance's filestream settings.
Below is an example with the master..xp_regwrite procedure.

If filestream is disabled for the instance this shows:

The same is true when viewing with the configuration manager:

Via SSMS / SQLCMD / ...

As a result, you can change the EnableLevel value for the Filestream registry key. Remember to change MSSQL14.MSSQLSERVER in the example below to your sql server instance.

EXEC master..xp_regwrite
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Filestream',
'EnableLevel',
'REG_DWORD',
3

This changes the value to full filestream access:

This can be validated with the configuration manager:

As always, change the registry at your own risk. Taking a backup of the registry is also a good idea.

After changing these settings, you would have to restart your sql server instance and configure the filestream_access_level .

Extra info

Level 2:

EXEC master..xp_regwrite
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Filestream',
'EnableLevel',
'REG_DWORD',
2

And as expected, level 1:

EXEC master..xp_regwrite
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Filestream',
'EnableLevel',
'REG_DWORD',
1


The delay you are seeing when loading from a snapshot is not cause by how indexes are laid out on disk, it's far more likely that you are seeing the delay because when you start an instance from a snapshot, the data is loaded only on first use, and will be significantly slower than subsequent uses - that is a basic limitation of using snapshots in this way and really has little to do with the application that is trying to access disk.  That's why you will see guides on "how to warm up an EBS volume" and the like (there are penalties on writing for the first time too).  If you do that (warm up the disk with another application like dd for example) and the performance issue goes away, then you have pretty decent proof that the layout of data has nothing to do with the issue.

Along those lines, MongoDB has the touch command that will allow you to warm up the data before you use it in anger (you can touch data, data and indexes or just indexes).  Again, after you initially attach the volume, it will be slow, and touch is going to take a while, but at least after that warming up phase, your results should be somewhat consistent.

In terms of how things are stored on disk, you have the basics correct in terms of file allocation but there is a logical structure within the files, extents, that are the real units of storage.  That and far more is covered in detail by this presentation by Mathias Stearn - one of the kernel developers at MongoDB.

Indexes are just another (structured) form of data in MongoDB and they are stored in linked extents throughout the file.  Fragmentation can become an issue (that's what the compact command is for) as can disk space used (the repair command is used to reclaim) but you haven't described a workload that would immediately make me think you are hitting a fragmentation issue which is why I suspect something else (like the first use penalty) is your root cause.


Innodb switch innodb_file_per_table to OFF

What version are you using?  What makes you say it "doesn't work"?

Please note that ibdata1 will not shrink during the process.  Only a dump and restore can fix that -- with a deletion of the ibdata1 file in the middle.  But note...  Unless you tweak the dump, the load will be all-or-none (file_per_table or not).

I recommend changing only "large" files (over a few MB) to file_per_table.  Why?  With "tiny" tables, there is overhead of opening the .ibd files that, though small, might cancel out the benefits of file_per_table.

The 80-20 rule says that 20% of your tables take 80% of the disk space.  So another way to pick "large" is to pick the largest 20% of the tables.

In the future (version 8.0), I am looking toward tablespace-per-database as minor recommendation for tiny tables.


What's the best practice to avoid collisions in throwaway temp tables?

The standard approach in Oracle is that global temporary tables are first-level objects.  They should be created at install time when you create all your permanent tables.  Doing DDL at runtime is very much frowned upon.  I'm not sure how this increases maintenance or how it makes stored procedures "no longer wholly self-contained"-- you don't need to do any maintenance on a temporary table and stored procedures presumably already have references to tables so I'm not sure what problem adding a reference to a temporary table creates.

It is unusual, though, in Oracle to use a temporary table in the first place.  I've been developing in Oracle for years and I can count the number of temporary tables I've created there on one hand.  Perhaps you would be better served by a collection of some type.  Perhaps you just need a view that implements whatever logic you're using in SQL Server to populate the temporary table.  Perhaps you want a pipelined table function.  Perhaps you even want a materialized view.  It will depend on why you're creating the temporary table to begin with.  Here is another thread that talks about alternatives to temporary tables in Oracle in more detail.


It is generally a terrible idea to construct SQL queries the way you currently do, as it opens the door to all sorts of SQL injection attacks. To do this properly, you'll have to use Prepared Statements instead. This will also resolve all sorts of escaping issues that you're evidently having at the moment.

String[] port = new String[] {"3306"};
    PreparedStatement statement = connection.prepareStatement("String sql="Select SID from SERVICE where PORT= ?");

    for (String name: names) {
        statement.setString(1, port);
        ResultSet resultSet = statement.executeQuery();
        ...
        ...
        statement.clearParameters();
    }


Does the order of the column name assignation in the join part have an impact on indexes in MySQL?


Do you have a local firewall running? You will need to add an exception for SQLDeveloper. It starts a listener that listens for the database/session debugging events. If it can't connect back to you, there-in lies the problem.


Microsoft SQL Server Not Showing Server Name in object Explorer

It looks like you have only installed SQL Server Management Studio (SSMS), and not SQL Server.

SSMS is the tool used to connect to and manage SQL Server Instances, if you haven't installed a SQL Server instance you have nothing to run your queries against!

Press Windows Key + R (Run) and type services.msc, check to see if the 'SQL Server' appears and that it is running.


Different Result When Insert Data Contain Quote on Localhost and Online MySQL Hosting

Looks PHP issue, I bet you have Magic Quotes turned off on local (good!) whereas your hosting privider has turned them on - http://www.php.net/manual/en/security.magicquotes.what.php.
When they're turned on it's the same like if you'd write:

$content    = htmlentities(addslashes($_POST['content']) );//HERE: addslashes is basically the same like your mysql_real_escape_string() - this means contents of $_POST get escaped automatically
$content_esc = mysql_real_escape_string($content);
$save = mysql_query("INSERT INTO tbl_post (title,content,date,publish) values('$title_esc','$content_esc','$date','$publish')");

I also guess that next time you're better asking such a question on StackOverflow instead;)


SQL Server Lock Escalation clarity required

1: No, the trace flag is enough to disable lock escalation. No need to do an ALTER TABLE for every table. If you had to do an ALTER TABLE, then the trace flag would be meaningless.  Per the official documentation on that trace flag:

Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks.
...
...trace flag 1211 prevents escalation in every case, even under memory pressure...

2: No, no need to disable page locks in order to get rid of lock escalation.
I, of course, agree with the others about how you don't want to use this trace flag. I mainly wanted to provide an answer to the question you posted.


How do I manage SSL credentials for multiple Postgres connections?

use the sslcert and sslkey paramterer in the connection string to indicate the files you want.

https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS

eg:

 con=psycopg2_connect(dsn='host=host1 user=yourname sslcert=host1.crt sskkey=host1.key')

probably something using string::format would be more practical.

.

If that seems too messy

Using a service definition to store the parameters is another option
define the connection paramaters (like above) for each server in pg_services.conf (create that file) and use connection strings like

con=psycopg2_connect(dsn='service=host1')

https://www.postgresql.org/docs/10/static/libpq-pgservice.html

other clients (like psql) can also use the services thus defined, so this way will save you typing in the future.

but it seems redash is configured using environment variables  with the database as as a URL perhaps like this:

like this with a service definition

export REDASH_DATABASE_URL=postgres:///?service=database1

or

like this explicitly:

export REDASH_DATABASE_URL="postgres://db_user@host.name.or.ip/db_name?sslcert=host1.crt&sskkey=host1.key"

from a bries sfan of the docs redash seems to use postgres for db_user and db_name

so far as I know libpq does not use a different interface for database names, connection strings, and database urls, where one can be used any other form can be used instead, however the redash docs say to use a URL.

https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-CONNSTRING


What could lead to excesive PAGEIOLATCH_EX

Thanks GabSQL, I found on wait_complete the resource information, after running the test and capture the data, executed the following script to summarize how many times the pageiolatch_EX has to wait by each index.

;WITH cte
AS (
    SELECT ed = CONVERT(XML, target_data)
    FROM sys.dm_xe_session_targets xet
    INNER JOIN sys.dm_xe_sessions xe ON xe.[address] = xet.event_session_address
    WHERE xe.name = N'Track_wait_stat'
        AND xet.target_name = N'ring_buffer'
    )
SELECT resource_description = x.ed.query('.').value(N'(event/data[@name="wait_resource"]/value)[1]', N'varchar(20)')
INTO #t
FROM cte
CROSS APPLY cte.ed.nodes(N'RingBufferTarget/event') AS x(ed);

SELECT obj.name, obj.index_id, count(1) as Occurrences
FROM (
    SELECT Page_Id = right(t.resource_description, charindex(':', reverse(t.resource_description) + ':') - 1)
        ,databaseId = LEFT(t.resource_description, charindex(':', t.resource_description)-1)
        ,FileId = Substring(t.resource_description, charindex(':', t.resource_description) + 1, charindex(':', t.resource_description, charindex(':', t.resource_description) + 1) - charindex(':', t.resource_description) - 1)
    FROM #t t
    ) x
JOIN sys.dm_os_buffer_descriptors AS bd ON x.databaseId = bd.database_id AND bd.page_id = x.Page_Id
INNER JOIN (
    SELECT object_name(object_id) AS name
        ,index_id
        ,allocation_unit_id
    FROM sys.allocation_units AS au
    INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
        AND (
            au.type = 1
            OR au.type = 3
            )

    UNION ALL

    SELECT object_name(object_id) AS name
        ,index_id
        ,allocation_unit_id
    FROM sys.allocation_units AS au
    INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id
        AND au.type = 2
    ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
GROUP by obj.name, obj.index_id
drop table #t

This info tels me the index who is waiting is the nonclustered with SubscriberId as key. This lead to look at fragmentation and it was of 47%, I was suprised because I have a job who do the maintenance, but for some tricky condition and a server migration few months ago, this index (and others too) where left behinde.

I rebuilded to get the fragmentation to 0 and restart the engine to flush out the cache (is a testing enviroment). After test it again a few times, the performance was improve, but  still there is a delay (10 min vs 30 min) and the waits are on the same index.

Something I noticed is that for the list that runs fast, it uses much less RAM (half), and since this a test enviroment, for the slow one it reach the maximum, so this could be the reason why it take a little more of to finish.

I think I could work with this delays now.

Thanks all.


How to grant permissions on a table-valued function

Scalar functions require EXECUTE permissions, however when you've converted to a Table Valued Function the permissions required change to SELECT.

You must now GRANT SELECT ON functionName TO another_user;

From BOL:

  Users other than the owner must be granted EXECUTE permission on a function (if the function is scalar-valued) before they can use it in a Transact-SQL statement. If the function is table-valued, the user must have SELECT permissions on the function before referencing it.


powershell problem when running on a remote server - access is denied

The account being local Administrator is a requirement for using the Enter-PSSession:

  To connect to a remote computer, you must be a member of the Administrators group on the remote computer.

As far as workarounds, I did find this answer on SU. I would read the article in detail that the answer points to fully understand, but this is the main command to adjust permissions:

  If you decide you want to allow others, what you do is run the
  command:

Set-PSSessionConfiguration -Name Microsoft.PowerShell -showSecurityDescriptorUI

  Notice that this action could have a serious impact on your system so
  we ask you to confirm that you really want to do this.


Unspecified error (MS Visual Database Tools) when creating a table using GUI

To answer my own question. After a some testing i noticed that the user needs to have Control permission on the database in order to be able to create tables using GUI. That is why db_securityadmin and db_owner worked.

Now i have tried before posted question to create a new login and user, that worked just fine. For some reason this domain user was problematic, so i explicitly granted him that permission. I dont think this happens occasionally, but if anyone encounters this issue that i have, i hope this helps. And some clarification if anyone has would be greatly appreciated.


ORA-01031: insufficient privileges while connecting with sys as sysdba

Are you connecting as

 sys as sysdba

or

 / as sysdba

?

If you are using the first option, is it prompting you for a password, and are you entering the password for the user?  Did you create a local Windows user account as the user, not the user id used to install the software?  Oracle recommends a user with administrators group install the software, and a local Windows user be defined for the sys and system accounts.


Archiving fga_log$: sys.dbms_audit_mgmt.clean_audit_trail doesn't delete anything

As Mat has pointed out, I hit Bug 9164488 (DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL does not delete sys.aud$ and sys.fga_log$ tables [ID 9164488.8]) which should be fixed in Release 11.2.0.2.

In the meantime, running on 11.2.0.1, I was able to circumvent the bug with a purge job in order to do the work. As soon as I ran

begin
  sys.dbms_audit_mgmt.create_purge_job (
     audit_trail_type           => sys.dbms_audit_mgmt.audit_trail_fga_std,
     audit_trail_purge_interval => 999,
     audit_trail_purge_name     =>'Purge_Test',
     use_last_arch_timestamp    => true
  );
end;
/

clean_audit_trail would do the work, as previously expected.


Implement row-level security as a user table or millions of roles?

With that many users, it's probably only practical to manage it using a shared role with your own user table. In fact, I know of very few consumer-facing applications that manage their users using database roles. The thing is, you almost certainly need to store more information per user than what Postgres stores for roles. So if you just manage your users as data in your own table, you're done. But if you have to create a role for each user too, then you're increasing the work you need to do. So I'd stick with a user table.


MySQL temporary column in SELECT statement

Based on your current code, it appears that a product can have at most one match in either customer_price_increment or general_price_increment. With that fact in mind, I would probably try a different approach.

First, I would rewrite the base query returning all the prices like this:

SELECT
  p.id AS product_number,
  p.name AS product_name,
  p.redemption_price,
  COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
  COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
  p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
FROM product AS p
LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id

To filter on sale_price, I would just use the above query as a derived table, so that I could reference the sale_price alias and avoid repeating the entire expression in the WHERE clause:

SELECT *
FROM (
    SELECT
      p.id AS product_number,
      p.name AS product_name,
      p.redemption_price,
      COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
      COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
      p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
    FROM product AS p
    LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
    LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id
) AS s
WHERE sale_price > 250
;

I believe you could also make the first query a view and then just select from it filtering the results as necessary:

SELECT *
FROM sale_prices_view
WHERE sale_price > 250
;


How to add a PostgreSQL 10 identity column to an existing table with rows?

I cannot reproduce this with current Postgres 10.3. (This error should never occur.) Simple test:

dbfiddle here

I suspect you are running an outdated point release. Postgres 10.2 fixed some bugs that fit your case exactly. The manual:

  Fix assorted failures to apply the correct default value when
  inserting into an identity column (Michael Paquier, Peter Eisentraut)

  In several contexts, notably COPY and ALTER TABLE ADD COLUMN, the
  expected default value was not applied and instead a null value was inserted.

If so, an upgrade should fix your problem.
Consider the project policy:

  We always recommend that all users run the latest available minor release for whatever major version is in use.


Error Message Saying "Invalid Object Name"

Your SQL Query is in the wrong database. You are trying to select from the AdventureWorksLT2012 database, but you're connected to the Master database.

If you look at the top left of your window, you will see a bar at the bottom of the ribbon that contains a drop-down list. In it, you can see "master" is the current database.

There are three ways to resolve this in your queries. After you do so, the red lines under your table and column names should all vanish, as they're warning you that the tables and columns don't exist in the current selected database.

Change the drop-down to the correct database. This will fix your query window for that window and that window only, but is the most common solution.
Change your queries to reference the database. This would be queries like Select * from AdventureWorksLT2012.SalesLT.SalesOrderDetail for example. Setting the DB name in your query means it will no longer matter which database your current context is.
Start the query off with a USE [AdventureWorksLT2012]; Go; statement. This will switch your context to the correct database. After running the query, the screen will show this database in the drop down list I mentioned in option 1.


What is the performance benefit when using a temporary table to update a regular table?

First, let's look at a different aspect.  Is your table ENGINE=InnoDB?  If so, and you have not done anything (yet) about "transactions"?  Then 5 Updates is 5 transactions.  There is a significant overhead for each transactions.  Hence, this would be a lot better:

BEGIN;
UPDATE ...;
UPDATE ...;
...
COMMIT;

You had 5 statements versus 3.  I turned the 5 into 7.

If, instead, there were 500 updates to do, that would be 502 versus 3.  Now the overhead of extra round-trips to the server becomes significant.  At this point (502 statements versus 3), creating the tmp table, etc, becomes significantly better.

(There is no need for BEGIN...COMMIT on the tmp approach; the UPDATE is a 'transaction' unto itself, assuming autocommit=1.)


You can utilize the returning keyword which will return the value of the created serial column. If you wrap you Insert in a With expression, you can then access the returned id and insert into the second table.

WITH getval(id) as
    (INSERT INTO table_a (some_col) VALUES (some_val) RETURNING id)
INSERT into table_b (id) SELECT id from getval;

where id is the value of the serial resulting from the insert into table a. More in the docs, though it is a somewhat hidden feature.

Note, you can also use returning * to return the entire updated row, not just the serial column, should you need it.


SQL Server Agent Alerts using severity 16

Finally found the answer to my question.

You need to enable the is_event_logged in the sys.messages table.

Example code:

EXEC sp_altermessage @message_id = 2812, @parameter = 'WITH_LOG', @parameter_value = 'true'

Here is the blog post I found to answer my question
Reference


Drop user in redshift which has privilege on some object

There are some queries that give you the privileges currently granted to users on any objects:

Schema ACL

select
    nspname                      as schemaname
  , array_to_string(nspacl, ',') as acls
from
    pg_namespace
where
    nspacl is not null
and nspowner != 1
and array_to_string(nspacl, ',') like '%u_A=%' -- REPLACE USERNAME
;

Table ACL

select
    pg_namespace.nspname as schemaname
  , pg_class.relname as tablename
  , array_to_string(pg_class.relacl, ',') as acls
from pg_class
left join pg_namespace on pg_class.relnamespace = pg_namespace.oid
where
    pg_class.relacl is not null
and pg_namespace.nspname not in (
    'pg_catalog'
  , 'pg_toast'
  , 'information_schema'
)
and array_to_string(pg_class.relacl, ',') like '%u_A=%' -- REPLACE USERNAME
order by
    pg_namespace.nspname
  , pg_class.relname
;

Privilege Explanation

      r -- SELECT ("read")
      w -- UPDATE ("write")
      a -- INSERT ("append")
      d -- DELETE
      D -- TRUNCATE
      x -- REFERENCES
      t -- TRIGGER
      X -- EXECUTE
      U -- USAGE
      C -- CREATE
      c -- CONNECT
      T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
      * -- grant option for preceding privilege

  /yyyy -- role that granted this privilege


A way of knowing why is this error happening would be to check the SQL Server log and look for details into why is this happening:

Try to login again with SQL Server Authentication, after getting your Error 18456 change to Windows Authentication and in your Object Explorer go to: Management -> SQL Server Logs -> Current - XX/XX/XXXX XX:XX:XX
Now search for your newly logged error and identify the error State

With this State number assest the nature of the error using the following table:

As you can see in this example I failed to login because my UserID is incorrect:

Also make sure you have hibrid authentication. Check the server properties from the Object Explorer and go to Security section and make sure Server Authentication is set to SQL Server and Windows Authentication mode

More detail


Meaning of SERVERPROPERTY ('ResourceLastUpdateDateTime')

Based on the BOL article you referenced to, the date is not going to change unless a Service Pack or CU creates new system objects. However the date returned may not necessarily coincide with the date you install that CU or SP.

  The Resource database makes upgrading to a new version of SQL Server
  an easier and faster procedure. In earlier versions of SQL Server,
  upgrading required dropping and creating system objects. Because the
  Resource database file contains all system objects, an upgrade is now
  accomplished simply by copying the single Resource database file to
  the local server.

The only time you will notably see that value change is when you apply a service pack or upgrade SQL Server. It will likely change the same time you see the ResourceVersion value change. Which if I recall I only see the version number change with service packs.

I have an instance of SQL Server 2008 R2 that shows a build of 10.50.6220, but the resource database shows 10.50.6000. Which the date returned is 08/09/2014 13:40:43, that is not when I applied the SP but probably the time frame that Microsoft's SQL Server Team finalized their testing. SP3 was released 09/26/2014.


Join on NULL Key Column Optimizing as Table and Index Scans

The behavior that you're seeing is caused by the lack of statistics on the table variable. When I want to learn more about why the query optimizer chose a particular plan I sometimes add hints and compare the queries side by side. That approach is helpful here.

First I'll create a table which is close enough in structure to yours to see the same behavior:

CREATE TABLE dbo.Order_Details_Taxes (
    OrdTax_PLTax_LoadDtl_Key decimal(15,0),
    FILLER VARCHAR(30)
);

INSERT INTO dbo.Order_Details_Taxes WITH (TABLOCK)
SELECT NULL, REPLICATE('Z', 30)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE INDEX [IX_OrdTax_PLTax_LoadDtl_Key] ON Order_Details_Taxes (OrdTax_PLTax_LoadDtl_Key);

To see how the query optimizer costs the different join types I can get an estimated plan for the following:

declare @Keys table (KeyValue decimal(15,0))
insert into @Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue;

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue
OPTION (LOOP JOIN, MAXDOP 1);

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue
OPTION (HASH JOIN, MAXDOP 1);

Here is a screenshot of the estimated plans:

SQL Server doesn't know anything about the value of the row in the table variable, so it creates the nested loop plan using the density of the statistics on OrdTax_PLTax_LoadDtl_Key. All of the rows have the same value in the stats so the density is 1. One of the general assumptions of the query optimizer's models is that data exists if the end user is looking for it. So your index seek is expected to return the same number of rows as the scan and has the same cost, despite the fact that the histogram only contains NULLs. In this case, the optimizer doesn't go back and apply special knowledge about NULLs to change the plan. You could argue that the optimizer could be improved to do this, but this does seem like an uncommon scenario.

The difference in costs of the plans ultimately comes down to the costs of the join operators themselves. For whatever reason the query optimizer costs the loop join higher than the merge join. The hash join is costed high as well, but for that one SQL Server expects to need to compute millions of hashes so the higher cost is more understandable imo.

What happens if you get the same plan with a temp table that doesn't have stats? The right way to do this is to disable automatic statistics creation for the table but I'll take a shortcut:

if object_id ('tempdb..#Keys') is not null
    drop table #Keys
create table #Keys (KeyValue decimal(15,0))
CREATE STATISTICS s1 on #Keys (KeyValue) WITH NORECOMPUTE;
insert into #Keys (KeyValue) values (null)

Everything looks the same as the table variable plan:

That's why I said the behavior is caused by the lack of statistics. When you use a temp table and allow auto stats creation the optimizer has a histogram on the temp table's column. It can use that information to generate more accurate cardinality estimates for the nested loop join plan and the index seek:

The histogram suggests that no columns will be matched so you end up with the minimum cardinality estimate of 1 row out of the seek. The costs of the loop join and the seek are reduced accordingly, and the nested loop join plan has by far the lowest cost out of the three join types.

Having some NULL values in the outer table of a join is a significantly more common scenario than joining to a table with all NULLs. In other words, I would expect more better model support for comparing two histograms that both contain NULL compared to a histogram to just NULLs compared to an unknown value. With better model support you can get better cardinality estimates, and in this case the better cardinality estimates result in a significantly more efficient query plan.


Selecting values that are not associated with one side of a many to many relationship

You need all combinations of Category and Value (a CROSS JOIN) except those that appear in the CategoryValue. This can be done with a LEFT JOIN / IS NULL

SELECT
    c.CategoryID, v.ValueID, v.ValueName
FROM
    Category AS c
  CROSS JOIN
    Value AS v
  LEFT JOIN
    CategoryValue AS cv
      ON  cv.CategoryID = c.CategoryID
      AND cv.ValueID = v.ValueID
WHERE
   cv.CategoryID IS NULL ;

or a NOT EXISTS:

SELECT
    c.CategoryID, v.ValueID, v.ValueName
FROM
    Category AS c
  CROSS JOIN
    Value AS v
WHERE
   NOT EXISTS
     ( SELECT *
       FROM CategoryValue AS cv
       WHERE cv.CategoryID = c.CategoryID
         AND cv.ValueID = v.ValueID
     ) ;

or an EXCEPT solution:

SELECT
    c.CategoryID, v.ValueID, v.ValueName
FROM
    Category AS c
  CROSS JOIN
    Value AS v

EXCEPT

SELECT
    cv.CategoryID, v.ValueID, v.ValueName
FROM
    CategoryValue AS cv
  JOIN
    Value AS v
      ON  cv.ValueID = v.ValueID ;


It's not stuck. It just does not print every rebuilded index.
You can view status of the process using DBA_RESUMABLE.

  select * from dba_resumable;


Sum date between two date and group by month

Another approach:
CREATE TABLE dbo.DateRanges
(
    [Name] varchar(20) NOT NULL,
    [Start Date] date NOT NULL,
    [End Date] date NOT NULL,

    PRIMARY KEY ([Name], [Start Date]),
    CHECK ([End Date] >= [Start Date])
);

INSERT dbo.DateRanges
    ([Name], [Start Date], [End Date])
VALUES
    (CONVERT(varchar(20), 'Joe'), CONVERT(date, '20/04/2021', 103), CONVERT(date, '20/05/2021', 103)),
    (CONVERT(varchar(20), 'John'), CONVERT(date, '01/05/2021', 103), CONVERT(date, '28/05/2021', 103));

Solution:
SELECT
    [Month] = Months.m,
    [Count] =
        SUM
        (
            -- Number of days in the current month
            1 + DATEDIFF
            (
                DAY,
                -- Latest of [Start Date] and current month start date
                IIF(DR.[Start Date] <= MonthRange.StartDate, MonthRange.StartDate, DR.[Start Date]),
                -- Earliest of [End Date] and current month end date
                IIF(DR.[End Date] >= MonthRange.EndDate, MonthRange.EndDate, DR.[End Date])
            )
        )
FROM dbo.DateRanges AS DR
JOIN
(
    -- Month numbers
    VALUES
        (01), (02), (03), (04), (05), (06),
        (07), (08), (09), (10), (11), (12)
) AS Months (m)
    -- Months that overlap the [Start Date], [End Date] range
    ON Months.m BETWEEN MONTH(DR.[Start Date]) AND MONTH(DR.[End Date])
    OR Months.m BETWEEN MONTH(DR.[End Date]) AND MONTH(DR.[Start Date])
CROSS APPLY
(
    -- Start and end day of each month
    VALUES
        (
            DATEFROMPARTS(YEAR(DR.[Start Date]), Months.m, 1),
            EOMONTH(DATEFROMPARTS(YEAR(DR.[Start Date]), Months.m, 1))
        )
) AS MonthRange (StartDate, EndDate)
GROUP BY
    Months.m
ORDER BY
    Months.m;

Output:

Month
Count

4
11

5
48

Online demo:
db<>fiddle demo


Using aggregate function on data masked column returns zeroes

Since dynamic data masking is performed right before returning the query result to the user, applying functions such as AVG will not have the masked data as a resultset.
You can see this in the execution plan, the masking function appears right before the select (in other words, returning the data to the user):

While the actual aggreggate happens in the compute scalar right behind the masking function:

With [EXPR1008] and [EXPR1009] being a count and a sum:
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
<ColumnReference Column="Expr1009" />
<ScalarOperator ScalarString="SUM([Test].[dbo].[Salaries].[AmountUSD])">

And, with the datamask function being applied after the aggregate functions, it is set to 0 as a result of the DataMask([Expr1002],0x05000000,(1),(0),(0),(0)) function.
This does not mean that all DataMask function executions on aggreggates and the corresponding datatypes will return 0, only that in this case it does.


ensureIndex failed to create index on primary shard: Cannot accept sharding commands if not started with --shardsvr

I check on same version on windows server with mongodb 3.4.6. Please find below scripts.

1) run shard servers

"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --shardsvr --replSet rs0 --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod1" --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod1\log.int1" --port 27000 --logappend
"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --shardsvr --replSet rs0 --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod2" --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod2\log.int2" --port 27001 --logappend
"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --shardsvr --replSet rs0 --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod3" --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod3\log.int3" --port 27002 --logappend
"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --shardsvr --replSet rs0 --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod4" --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod4\log.int4" --port 27003 --logappend

2) config servers

"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --configsvr --replSet rsconfig --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg0" --port 26050 --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg0\log.cfg00" --logappend
"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --configsvr --replSet rsconfig --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg1" --port 26051 --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg0\log.cfg01" --logappend
"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --configsvr --replSet rsconfig --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg2" --port 26052 --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg0\log.cfg02" --logappend

3) mongos mongo router

"C:\Program Files\MongoDB\Server\3.4\bin\mongos.exe" --configdb rsconfig/BSS-BC4-Benchmark2:26050,BSS-BC4-Benchmark2:26051,BSS-BC4-Benchmark2:26052 --logappend --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongos\log.mongos0" --bind_ip 127.0.0.1,10.10.180.39

4) Connect any shard instance in replica and initiate replica set using rs.initiate()

rs.initiate(
  {
    _id : "rs0",
    members: [
      { _id : 0, host : "Mongo-Test-PC:27000" },
      { _id : 1, host : "Mongo-Test-PC:27001" },
      { _id : 2, host : "Mongo-Test-PC:27002" },
      { _id : 3, host : "Mongo-Test-PC:27003" }
    ]
  }
);

5) Connect any config server and initiate replica set for config server using rs.initiate() - name must be different from shard replica

rs.initiate();
rs.status();
cfg = rs.conf();
cfg.members[0].priority = 3;
rs.reconfig(cfg);
rs.add({ host:"Mongo-Test-PC:26051", priority: 2});
rs.add({ host:"Mongo-Test-PC:26052", priority: 1});
rs.status();

6) Connect mongos and ser read pref and add shard.

db.getMongo().getReadPref();
db.getMongo().setReadPref('secondary');
db.getMongo().setReadPref('primaryPreferred');
db.getMongo().getReadPref();

sh.addShard("rs0/Mongo-Test-PC:27000");

sh.status();

create database shardtest
sh.enableSharding('shardtest');

use shardtest

db.createCollection("shardcollection");

Hope this will help you.


Creating schema for a shop that sells items from multiple tables

I recommend reading the following link on Database Inheritance: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/09/30/how-to-model-inheritance-in-databases.aspx

Databases don't naturally fit hierarchies - they do work, but it's not their strong suit.

In your case I would change shops_and_items for

**products**(product_id (PK), shop_ID (FK), ... then columns for common fields: name, price, stock, delivery time)

Then...

Option 1 :: Fewer Product Categories

If you only intend to have furniture and clothing then you could do

**clothes**(product_ID(FK and PK), size, colour, gender etc)
**furniture**(product_ID(FK and PK), height, width, depth, material etc)

Note that product_ID is a foreign key and primary key... a product would either have a corresponding record in clothes OR furniture. This doesn't need to be auto incrementing. A product has one clothes item or one furniture item (not both).

Option 2 :: Many Product Categories

If you intend to expand to lots of different products (electrical, food, ) and possibly sub-products then you will have a lot of tables and it will be a mess to manage! In this case it's easier to go down a meta-data route like WordPress http://codex.wordpress.org/images/9/9e/WP3.0-ERD.png

WordPress uses wp_posts for a post, page, any many other plugin-defined types (map, questionnaire, gallery etc), if they need extra properties then they are stored in wp_postmeta.

You would keep the products table but add on:

**product_meta**(product_id(FK), meta_name, meta_value)

e.g.
4 (Surfer T-Shirt), colour, white
4, size, Medium
4, gender, male
18 (TV Stand), material, metal
18, height 40cm
18, depth 33cm
18, width 60cm

This might need some more validation before the data goes into the database as meta_value will most likely be a varchar column so you will need to standardise how you interpret values. Will your code use M/F for Male/Female? What units for measurments?

I recommend grabbing some open source shopping carts and installing them just to find out how they structure their SQL code - they will have their own solutions for sub-categories and custom properties of products.


As documentation says, it is not available yet.

  DEFAULT_VALUE    LONG      Reserved for future use

ALL_ARGUMENTS

Bug:183707  PLSQL COMPILE DOES NOT POPULATE DEFAULT ARGUMENT COLUMNS IN ARGUMENT$


Deadlock Error from SQL Server 2000 to SQL Server 2008

After upgrading to SQL Server 2008, did you run the below?

DBCC UPDATEUSGAE
UPDATE STATS WITH FULLSCAN
REBUILD ALL INDEXES

The statistics blob is changed significantly from 2000 to SQL Server 2008 and the above steps are very important after the upgrade. I have seen many ppl run into performance problems but not deadlocks. I would try doing all of the above if you haven't so far and try to reproduce the problem afterwards.

That said, what is the results of SELECT @@VERSION on the 2008 box? Are you updating one row or several rows?


drop table in a begin try begin catch block - is there any loss of performance?

It's better to check for the table existence rather than trying to drop it and ignore any errors. This is generally considered a bad practice in all programming languages. In SQL Server and all RDBMSs you have metadata, so there are certainly better ways of doing this.

An example:

IF OBJECT_ID('tempdb..#tmp_sp_help_alert') IS NOT NULL
    DROP TABLE #tmp_sp_help_alert;

Moreover, temporary tables are automatically deleted when they get out of scope (for local temporary tables, when the procedure ends), so it might be overkill to test for table existence. For the same reason, it might also be unnecessary to drop the table at the end of the scope.


How can I handle cases where a group of rows have the same identifier in mysql?

You need to create a separate key, unrelated to the timestamp, when inserting the rows into a table. MySQL has a handy auto increment feature for this purpose.


For version 5+

SELECT OBJECTID,
       CASE WHEN FRUIT = @curType
            THEN @curRow
            ELSE @curRow := @curRow + 1 END AS GROUP_NUM,
       @curType := FRUIT FRUIT
FROM FRUITS, (SELECT @curRow := 1, @curType := '') r
ORDER BY FRUIT

For version 8+

SELECT OBJECTID, FRUIT, DENSE_RANK() OVER (ORDER BY FRUIT) GROUP_NUM
FROM FRUITS

fiddle