In my experience is better a calculated field than a trigger because a trigger could generate performance issues in the future. Try this piece of code that uses a calculated field:

CREATE TABLE [dbo].[Table_1](
    [id] [int] NOT NULL,
    [name] [varchar](100) NOT NULL,
    [label]  AS (lower(replace([name],' ','_'))),
    [description] [varchar](max) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT INTO [dbo].[Table_1]
([id]
,[name]
,[description])
VALUES
(1
,'Sample Product'
,'Sample Product Description')

GO


Similar data with fields redundant in one table

A good way to do this is to use the Entity-Attribute-Value approach when modelling sparse attributes (https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model).

For you the relationships would probably look like this:

Your tables would then look something like:

Note I haven't specified the PlanTemplate table, this would show all possible fields for a plan.

There are a few caveats with this approach:

It requires that you almost always pivot and join the PlanFieldValue table, performance can be hampered. Usually views are necessary, there is a dependency here on what DBMS you are using. Almost all support pivoting, but there are varying levels of support and performance
Querying and displaying data from the PlanFieldValue on to your UI is onerous, requires using a view and updates/inserts need to be handled differently. More dev time required.
Indexing and performance of the tables is often hampered if millions of rows are in the table, for instance
All attribute information has to be checked manually, for instance field length and nullability needs to be checked manually against the template, possibly using another column in PlanField that has a Nullable flag.

Otherwise its a good approach that I have used quite a few times. One big advantage is you can add new fields without changes to your database structure - a huge bonus if the field values are changeable or likely to be increased.

However if the number of fields for each Plan is low and it is unlikely you will add more fields, a single plan table with individual columns still isn't too bad. It doesn't really matter having some columns blank but it does make it hard to track nullability in the database.


SELECT `Column`,
       Amount,
       COALESCE(LAG(Amount) OVER (ORDER BY `Column` ASC), 0) `Previous Amount`,
       COALESCE(100*(Amount -
                 LAG(Amount) OVER (ORDER BY `Column` ASC)) /
                 LAG(Amount) OVER (ORDER BY `Column` ASC), 0) `Percent Change`
FROM MyTable ;

fiddle

PS. It's extremely bad practice to store number data in VARCHAR field.

PPS. While using any ticks always look for a proper tick type.


ORA-00904: "GET_JAVA_PROPERTY": invalid identifier - 11gR2

Well, you found it somehow, didn't you?

It was never part of the database officially, it is just some custom function you can find on the Internet. For example: https://stackoverflow.com/questions/28164252/how-to-check-jdk-version-in-oracle

create function get_java_property(prop in varchar2)
return varchar2 is
language java name 'java.lang.System.getProperty(java.lang.String) return java.lang.String';


Wrong Memory is displaying in task manager for Default and Named instance in Sqlserver

Perhaps you have Lock Pages In Memory for the "active but low memory" instance? Task manager only show a minuscule memory usage in this case. You can for instance look in the error log file (startup info) if lock pages are used.
So, there's nothing to "fix". You should look at the memory usage through something else then Task Manager. For instance sys.dm_os_process_memory (physical_memory_in_use_kb) or sys.dm_os_sys_info (committed_kb)


Advice on how to approach data cleaning & importing

I think the biggest challenge will be doing the event type transformations efficiently, so I'm going to focus on that and ignore the schema completely.

My first thought (read: I've never actually tried this) is to use dynamic SQL to build up a SELECT statement that will do the data transformation such that values end up in their correct columns (i.e., Col0 --> CompanyId, or whatever). This boils down to a mapping table consisting of the event type, the source column, the target column, and a default value if required.

Once you have the mapping table (or tables, depending on how complex this actually is), you can use it to dynamically build a SQL statement of the form:

SELECT
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col0, 'DefaultCategory')
        END
    ) AS Category,
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col1, 'DefaultCompany')
            WHEN 5000 THEN COALESCE(Col0, 'DefaultCompany')
        END
    ) AS Company,
    (
        CASE EventType
            WHEN 5000 THEN COALESCE(Col1, 'DefaultLocation')
        END
    ) AS Location
    FROM RawSourceTable

Build the statement as part of the ETL process. If you need to add a new event type, just add the required column mappings to the mapping table, and they'll be handled the next time the ETL process runs.

Once the source data is in this kind of state, the rest of the import process should be pretty standard.


How do I run a database heavy query over jdbc without timing out (Oracle)?

You can use PL/SQL and schedule jobs to populate tables or schedule jobs to update materialized views. Then once the data has been updated you can query as much as you want, since the heavy lifting will be done. You should also try tuning the queries. If you have an index with the filter columns and any other columns that you need, then you can avoid reading the index then the table. If you start indexes with the low cardinality columns going towards high cardinality columns, you can reduce the amount of rows looked at. Look at materialized views and including rowid so that you can try to do a fast refresh.


Semi-Annual archiving of SQL Server database

Restore From Backup --> [Drop Unused Tables]

Perform a full backup of database ProductionDb.
Restore from backup to database ArchiveDb2011B.
[optional] Drop unnecessary tables.
Set database ArchiveDb2011B to read-only.

Problems with this approach:

It uses the most disk space.  Our previous archives are roughly 1GB in size.  Our production db is about 4GB in size.  If we skip step 3, we end up with archives four times bigger than what we typically have had.
Dropping tables requires first dropping any related foreign keys.
Triggers remain in place in the archive (the production db lives in a merge replication topology).  Is this a moot point because the archive will be read-only?

Pros:

It is relatively simple.  If we skip step 3, it is dead simple.
Performance should be pretty good because all of the indexes remain in place.  (We have missed indexes in the past using other approaches.  We don't usually realize it's a problem unless we happen to overhear users complaining about how slow a particular archive is.)

We have not used this approach for archiving purposes.  We have used this approach to create dev environments and it works well in that scenario.  It seems like the biggest drawback is the extra disk space used.  Disk space keeps getting cheaper, but it is a four-fold difference over other approaches.


Postgresql on AWS RDS JDBC Connection Refused, psql connection succeeeds

Ultimately the error was in the JAR. A connection was made the DB on two different occasions, and each one from a different config location. I changed one, but not the other - the joy of inheriting code : )

So now my DB connections works. Hopefully the original question has some useful ways to know if a connection can connect.


Best Practice for Oracle SGA and PGA Memory Components Size

What version of Oracle are you using?  Are you actually setting values for the SGA components and/ or the PGA separately?  If so, why?  If you're using a recent version of Oracle, the starting point would normally be to set memory_target and let Oracle size the various memory components automatically.  Unless, of course, you have some reason to need to set minimum values for certain memory components.

As for what to set memory_target to, you just need to leave enough memory for the OS and any other processes that need to run on the machine (including things like any backup manager you might be using, any monitoring software that runs on the server, etc.)  It seems unlikely to me that you'd need 20% of 128 GB for that software.  I'd guess that 8 GB would be more than enough leaving 120 GB for Oracle's memory_target.  You could do better than guessing by taking a look at the actual memory utilization on the server over time.


Oracle 11g: Part Copy of Production data to Development Environment

Your initial idea was not bad at all. What you can do is store the unwanted partitions with their local indexes in separate tablespaces. Use rman for the cloning but use the SKIP TABLESPACE option to not clone the tablespaces with the unwanted partitions. (assuming online backup)
After the clone, the skipped tablespaces have datafiles with status RECOVER.

see RMAN DUPLICATE DATABASE: Options

In the end you just drop the unwanted partitions. To be able to do that you first have to get rid of things like constraints and indexes that need to be re-created later on. This worked in 10gR2. Make sure that you don't drop the last partition of a table, in that case drop the table.

It is a bit of work but certainly possible. If the difference in Volume is huge or there are lots of copies, it might be worth spending some time for it.


Creating a relationship between a document and a collection in MongoDB

Collections do not have IDs. I would suggest you just use the collection names. You can already dynamically access collections by name - so enumerating stored names in this collections array to access the actual DB collections would be no problem.


Estimated plan generation succeeds for batch creating and using #temp table but fails with a permanent table

In SQL Server 2000 the #temp table version does fail with the message

  Invalid object name '#temp'.

However since SQL Server 2005 compiling a plan with a local temporary table does actually create the temporary table behind the scenes.

You can see this by getting the estimated plan for

Create table #temp
(
ID INT IDENTITY (1,1)
)

Insert into #temp
DEFAULT VALUES

SELECT *
FROM #temp
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 3604);

DROP TABLE #temp

Which produces an output like

The negative object id is the id of the object fleetingly created. Polling tempdb.sys.tables in a loop whilst generating the estimated plan and comparing with the TableID subsequently output can show this.

The estimated plan you see for the statements referencing the temp table is of limited use however as even the addition of a single row to the temp table will trigger a statement level recompile and potentially a different plan when you actually execute the SQL.

This behaviour does not apply to global temporary tables or permanent tables.

I presume this is also the same reason as

/*Works fine (assuming T doesn't exist)*/
IF 1 = 1
  SELECT 1 AS X
  INTO   T
ELSE
  SELECT 1 AS X
  INTO   T

/*Fails (There is already an object named '#T' in the database.)*/
IF 1 = 1
  SELECT 1 AS X
  INTO   #T
ELSE
  SELECT 1 AS X
  INTO   #T


How do I save result of a join to another table?

Sounds like you're trying to insert rows to a table that already exists, in which case you'll need something like...

INSERT INTO new_records
SELECT * FROM new_table t JOIN new_record_ids r ON(r.id = t.id)
--this assumes the result of the select has the same column layout as new_records...
--which seems unlikely. replace (SELECT *...) with (SELECT column1, column2, etc...) to match the new_records column layout
ORDER BY t.id
--assuming you meant new_table.id instead of new.id although the ORDER BY clause seems extraneous for an insert anyway

If I misinterpreted and you're trying to select into a new table that has not yet been created, check the docs for CREATE TABLE...AS SELECT syntax.

EDIT:

From the comments under your question it sounds like you're trying to populate a brand new table with the query results, in which case...

CREATE TABLE new_records AS
SELECT t.* FROM new_table t JOIN new_record_ids r ON(r.id = t.id);

...should work just fine. As pointed out in comments, the SELECT INTO docs state:

  The PostgreSQL usage of SELECT INTO to represent table creation is
  historical. It is best to use CREATE TABLE AS for this purpose in new
  code.


Maintaining integrity between 3 tables when reference could be through or direct

3 foreign keys version

create table tRegions (
  ID int not null primary key,
  name  varchar(20)
);

create table tCountries (
  ID int not null primary key,
  name varchar(20),
  regionID int,
  --any superset of PK is undoubtly UNIQUE
  constraint cu1 unique (ID, regionID),
  foreign key (regionID) references tRegions(ID)
  );

create table tUsers (
 ID int not null primary key,
 name varchar(20),
 countryID int ,
 regionID int,
 foreign key (regionID) references tRegions(ID),
 foreign key (countryID) references tCountries(ID),
 foreign key (countryID,regionID) references tCountries(ID,regionID )
)

insert tRegions (ID, name)
values
(1,'North America'),
(2,'Central America'),
(3,'East Asia'),
(4,'Europe')

insert tCountries(ID, name, regionId)
values
(1,'USA',1),
(2,'China',3)

insert tUsers (ID, name, countryID, regionId)
values
(1,'Joe', 1  ,1),
(2,'Sue',NULL,2),
(3,'Bob', 2  ,NULL),
(4,'Amy',NULL,NULL)

go
--OK
update tUsers set RegionId=3
where ID=3
select * from tUsers
go
--fails
update tUsers set CountryId=2
where ID=2
select * from tUsers


SQL Server Processor affinity with trace flag 8002 ON

Processor affinity should be set in the specific case that your workload fits into the processor cache. This would be rare in SQL Server but might happen when running analytics inside the DB e.g. with R
I believe that is correct, yes
No it does not - you would need to selectively set affinity for each workload. Typically if you had say 32 cores you might reserve 2 for the OS and divide up the others - but taking care to ensure that you keep cores and caches together. You could create a CPU set that uses a subset of cores on each CPU and kill performance with cross cache chatter!

Generally I would advise against this unless you are very sure that you need it, and have a good understanding of the underlying hardware, how its NUMA boards are arranged, etc. You won’t damage anything but you can degrade performance quite badly. If you need to segregate workloads, then a better option on a modern system is to use Hyper-V in 99% of use cases.

Note also that affinity masks will be deprecated anyway and the new method is NUMA-aware.


Copy Data from Two different databases with FK relation

This could probably be done with some ETL tool but
A manual way comes to mind:
 - just join the test tables together ( I'm guessing the join condition would be STEP_RESULT.UUT_RESULT = UUT_RESULT.ID) to get all the relevant keys.

SELECT a.ID
FROM STEP_RESULT a join UUT_RESULT b
on a.UUT_RESULT = b.ID

So lets say you get a 1000 values of b.ID. Then manually create a select which will have all the values returned from the TEST sql in an IN condition and run it on PROD:

SELECT * FROM PROP_RESULT WHERE STEP_RESULT IN ('ID1','ID2', ...)

Not sure how exporting data in MSSQL works but I'm guessing you could just save that select as a separate table and then just load it into your TEST environment


Deadlock (ORA-00060) are automatically resolved by Oracle and signalled in the alter.log.
Transactional locks have to solved manually.
You can kill session from sql command line using:

SQL> alter system kill session 'sid, serial#';

This will mark session for being killed whener it's possible. Sometime kill is not immediate. IF you want and immediate kill you can, on a unix system, run

kill -9 processid

This will kill immediatly the session forcing the PMON to rollback any uncommitted data. To retrieve processid (SPID) run:

SQL> SELECT spid
FROM v$process
WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr);

A final note: in windows process is slightly different. To kill a session you have to run:

C:\> orakill <SID> <spid>

where <SID>  = the Oracle instance name (ORACLE_SID)
      <spid> = the thread id of the thread to kill


Running EXPLAIN QUERY PLAN on your query gives this:

0|0|0|SCAN TABLE LEGS AS l
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|5|SEARCH TABLE TRAVELDAYS AS d USING AUTOMATIC PARTIAL COVERING INDEX (Day=? AND Value=?)
1|1|4|SEARCH TABLE TRAINS AS t USING AUTOMATIC COVERING INDEX (TrainDaysUID=?)
1|2|0|SEARCH TABLE LEGS AS l USING AUTOMATIC COVERING INDEX (TrainUID=?)
1|3|1|SEARCH TABLE ROUTES AS r USING INTEGER PRIMARY KEY (rowid=?)
1|4|2|SEARCH TABLE STATIONS AS s1 USING INTEGER PRIMARY KEY (rowid=?)
1|5|3|SEARCH TABLE STATIONS AS s2 USING INTEGER PRIMARY KEY (rowid=?)
1|0|0|USE TEMP B-TREE FOR DISTINCT
0|1|1|SEARCH TABLE ROUTES AS r USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE STATIONS AS s1 USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE STATIONS AS s2 USING INTEGER PRIMARY KEY (rowid=?)

Any "AUTOMATIC" index is an index that SQLite thinks is worth creating temporarily; try this:

CREATE INDEX x1 ON TRAVELDAYS(Day, Value);
CREATE INDEX x2 ON TRAINS(TrainDaysUID);
CREATE INDEX x3 ON LEGS(TrainUID);


How does an OLAP cube database differ from an RDBMS?

Cubes are very a different beast from a traditional database.  There are several different kinds of cube storage processes depending on need (OLAP, MOLAP, ROLAP, etc.) which are all done differently depending on how real time the data needs to be.

I actually did a webcast with another Microsoft MVP a couple of weeks ago where we talked a little about the BI process and how it impacts the traditional database, which might be interesting to you.  The recordings (which are free) aren't available yet, but should be soon.


Is dirty-read a problem in an always-commit database?

If you still allow transactions that span multiple statements, you would still potentially have dirty reads.

Imagine that I have a simple banking application where session 1 is trying to transfer $100 from account A to account B.  Session 1 starts a transaction and subtracts $100 from A.  Now session 2 runs a report that tries to make sure that money hasn't gone missing (which is somewhat useful if you are running a bank).  If session 2 is allowed to read the updated balance from A and the un-updated balance from B because session 1 is in the middle of a transaction, it has done a dirty read.  The fact that session 1 is going to eventually commit the change does not change the fact that session 2 was able to get an inconsistent view of data.

Of course, a database that disallowed rollbacks but allowed transactions that spanned multiple statements would be rather problematic.  If I subtract $100 from A and then try to add $100 to B but I encounter an error trying to update B (perhaps I discover that there is a hold on B or that B has been closed), I'm in a pretty unfortunate position.  Assuming I don't want $100 to disappear into thin air (which will make A rather unhappy), my application has to catch the exception and manually implement a rollback by putting the $100 back.  In real applications where transactions involve changes to a bunch of different tables, some of which are done by triggers that the application may not be aware of, that is a non-trivial challenge.


Why is a new user allowed to create a table?

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. Assuming a non-superuser foo_user should be granted this privilege, this should be done with:

GRANT ALL ON schema public TO foo_user;

To know what ALL means for a schema, we must refer to GRANT in the doc, (in PG 9.2 there are no less than 14 forms of GRANT statements that apply to different things...). It appears that for a schema it means CREATE and USAGE.

On the other hand, GRANT ALL PRIVILEGES ON DATABASE... will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables.

Regarding this error: ERROR: no schema has been selected to create in, it happens when trying to create an object without schema qualification (as in create table foo(...)) while lacking the permission to create it in any schema of the search_path.


Data modeling with the goal to get the best performance in Oracle and SQL Server

I think you should consider your requirements a bit further and see which model fits better long term.

Regarding performance: How fast is fast enough? You need a target or goal to work to. And to do that you need to forecast load and volumes.

Ignoring performance and thinking about data models: personally i like the 2nd option because it can give you an audit trail. You can see which items were sold on which days, and can handle things like returns or stock take adjustments. This makes reporting, accounting and auditing easier.


SQL Server 2016 -> 2019 AlwaysOn Rolling Upgrade Migration

This is from Microsoft books online :

Initialize secondary availability groups in a distributed availability group
Distributed availability groups were designed with automatic seeding to be
the main method used to initialize the primary replica on the second
availability group. A full database restore on the primary replica of the
second availability group is possible if you do the following:

Restore the database backup WITH NORECOVERY.
If necessary, restore the proper transaction log backups WITH NORECOVERY.
Create the second availability group without specifying a database name and
with SEEDING_MODE set to AUTOMATIC.
Create the distributed availability group by using automatic seeding.
When you add the second availability group's primary replica to the
distributed availability group, the replica is checked against the first
availability group's primary databases, and seeding catches the database up
to the source. There are a few caveats:

The output shown in sys.dm_hadr_automatic_seeding on the primary replica of
the second availability group will display a current_state of FAILED with the
reason "Seeding Check Message Timeout."

The current SQL Server log on the primary replica of the second availability
group will show that seeding worked and that the LSNs were synchronized.

The output shown in sys.dm_hadr_automatic_seeding on the primary replica of
the first availability group will show a current_state of COMPLETED.
Seeding also has different behavior with distributed availability groups. For
seeding to begin on the second replica, you must issue the command ALTER
AVAILABILITY GROUP [AGName] GRANT CREATE ANY DATABASE command on the replica.
Although this condition is still true of any secondary replica that
participates in the underlying availability group, the primary replica of the
second availability group already has the right permissions to allow seeding
to begin after it is added to the distributed availability group.

Source : https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups?view=sql-server-ver15


The choice on whether or not to go with an additional non-node vote (I don't want to say "witness disk" here, because an additional vote can conversely come from a file share as well with the node and file share majority quorum model) depends on how many cluster nodes you have.

If an even number of nodes, then add your additional non-node vote (whether it's a witness disk or a file share).  But if it's an odd number of nodes then you will gain no benefit out of a witness disk or file share voter.

Take a look at this TechNet reference on quorum model recommendations.

It is also worth noting that with the entrance of Windows Server 2012 R2, the capability of a dynamic witness has been introduced.  I realize you aren't using this version of Windows Server, but for the sake of a complete answer, here is an excerpt explaining this new feature in Windows Server 2012 R2.  This dynamic witness would be the exception to my answer:

  In Windows Server 2012 R2, if the cluster is configured to use dynamic quorum (the default), the witness vote is also dynamically adjusted based on the number of voting nodes in current cluster membership. If there are an odd number of votes, the quorum witness does not have a vote. If there is an even number of votes, the quorum witness has a vote.


Assuming you are using ObjectIDs for your _id column, then you will already have an insert based timestamp.  You can even extract it from the shell directly if you wish.  Of course, you may want to leave this as-is to permanently store your original insertion timestamp, but adding another would be relatively trivial.  Because most drivers have the ability to generate their own ObjectID and you have the published spec you can easily insert your own with a little research, and in the language of your choice.

Since ObjecIDs are the default _id value, they are also going to be a good choice for indexing, well supported in the drivers etc.

Alternatively, in terms of implementing pure timestamps in MongoDB, there is also a BSON Timestamp data type.

Storing this (or the aforementioned ObjectID) in a standard field (say "ts" for example) across documents/values would add overhead to a field/doc as you mention, but you should be able to standardize it easily (field.ts would always have a timestamp value) as well as predict the overhead for each field with a timestamp (8 bytes for a timestamp, or 12 bytes for an ObjectID - for more info see bsonspec.org).

Given that you have two native, known datatypes that provide timestamp functionality, they would be my recommendation as a way forward here.

In terms of adding them to existing data, you can choose to "lazily" add them whenever the data is next touched or issue a batch update, depending on your needs - the benefit of a flexible schema.


Is there Windows DLL for PDO_IBM driver available for PHP 5.6?

We solved this problem by using non-object-oriented extension ibm_db2. Fortunately, our code wasn't using many calls or special features so we were able to refactor our code quite quickly, e.g. from:

$stmt = $conn->prepare($sql);

to:

$stmt = db2_prepare($conn, $sql);


Percona PAM with AD authentication using SSSD

All i had to do is add the dba group using the following realm command:

realm permit -g dba

Doing so will automatically change the /etc/sssd/sssd.conf file and add the following line(s):

access_provider = simple
simple_allow_groups = dba

Before running real, access_provider was setup as "ad", and was automatically changed to "simple" as shoen above.


Check and Retrieve those results where a cell(row) contains atleast one value from many values in a column

If I were doing this in SQL Server*, I would use:

USE tempdb;

CREATE TABLE Posts
(
    PostID INT NOT NULL IDENTITY(1,1)
    , PostName VARCHAR(255)
    /*  whatever other fields relate to POSTS */
    , CONSTRAINT PK_Posts PRIMARY KEY CLUSTERED (PostID)
);
CREATE TABLE People
(
    PeopleID INT NOT NULL IDENTITY(1,1)
    , PersonName VARCHAR(255)
    /* whatever other fields relate to PEOPLE */
    , CONSTRAINT PK_People PRIMARY KEY CLUSTERED (PeopleID)
);
CREATE TABLE PostsPeopleXRef
(
    PostID INT NOT NULL
    , PeopleID INT NOT NULL
    , CONSTRAINT FK_PostsPeopleXRef_PostID
        FOREIGN KEY (PostID) REFERENCES Posts(PostID)
    , CONSTRAINT FK_PostsPeopleXRef_PeopleID
        FOREIGN KEY (PeopleID) REFERENCES People(PeopleID)
);

INSERT INTO Posts (PostName) VALUES ('Post Test 1');
INSERT INTO People (PersonName) VALUES ('Jack');
INSERT INTO People (PersonName) VALUES ('Jill');
INSERT INTO People (PersonName) VALUES ('John');
INSERT INTO PostsPeopleXRef (PostID, PeopleID) VALUES (1, 1);
INSERT INTO PostsPeopleXRef (PostID, PeopleID) VALUES (1, 2);
INSERT INTO PostsPeopleXRef (PostID, PeopleID) VALUES (1, 3);

SELECT *
FROM dbo.Posts;

SELECT *
FROM dbo.People;

SELECT P.PostName, E.PersonName
FROM dbo.PostsPeopleXRef X
    INNER JOIN dbo.Posts P ON X.PostID = P.PostID
    INNER JOIN dbo.People E ON X.PeopleID = E.PeopleID
ORDER BY P.PostName
    , E.PersonName;

This results in the following output:

*
: for MySQL, you'll need some changes, like replacing IDENTITY(1,1) with AUTO_INCREMENT, removing the CLUSTERED keyword, adding the ENGINE = InnoDB at the CREATE TABLE statements, etc.


Take care when comparing stored procedure execution times to inline SQL execution times.

A stored procedure gets compiled into ONE execution plan to fit all possible parameters.  Depending on your query this may not be a good plan for Any parameters, but instead the least-worst for all parameters.

When executing Inline, the optimiser can see both the query AND the parameters.  It can then compile a plan specifically for that case.  It can take conditions such as OR @param IS NULL and turn it into OR TRUE and then be able to pick indexes and approaches that fit that specifically.

In short, Inline and stored procedures can and do generate different plans with different performance characteristics.

For your particular case, can you show us the definition of the stored procedure?


First-In and Last-Out times for Night Shift and Day Shift Employees

Could you write a query to use BETWEEN the shift start date time and the shift end date time (both held in variables) that returns all the SSN and check times in that period? If you insert the results of that into a temporary table, you could then have another query on that temporary table for your MIN and MAX values. Doing it like that means you don't need to worry about a change of date because your only concerned with the data in your temporary table. Also, using variables would let you reuse the script for different date and times for shifts.


TDE Encryption on database tripled size of backup files

After enabling TDE Encryption my database backup file .bak is 3x larger than before.

Correct.

Is there a way to improve this?

Without removing TDE? No.

Is this due to compression not being as efficient on an encrypted file?

Yes.
You don't mention your version, but this has apparently been fixed/improved/added in SQL 2016:
Backup Compression for TDE-enabled Databases

As illustrated in Figure 1, in SQL Server 2014 (and prior versions) backup compression doesn’t help reduce the backup size of a TDE-enabled database. However, due to the time spent in attempting the compression, the backup takes much longer to complete.
Things change completely in SQL Server 2016. You not only get great compression of the backup file size, but the backup time is reduced significantly as well, illustrated in Figure 2.

Take note of the warning on this post, there were apparently some potential for corruption that was resolved with a later patch.


Postgres long autovacuum halting database

VACUUM processes launched by autovacuum can be safely killed with:

SELECT pg_terminate_backend(PID_of_backend);

Actually, any client processes in Postgresql can be terminated this way. Uncommitted work by this backend will be simply discarded.

You could then re-run VACUUM manually at a low-traffic time:

VACUUM VERBOSE users;

Check whether Cost-based Vacuum Delay can help you. This would limit the amount of I/O your autovacuum process uses.

Maybe you simply hit your IOPS limit. You should be able to see the numbers on AWS interface. On standalone Linux use iostat -dtkxy 10 to measure I/O. (iostat is usually packaged in sysstat package).

Maybe the VACUUM kicks in again so often because of the aggressive settings in your config.


How can I design a database that easily lets me recreate historical snapshots

Versioning. Not a new problem - used thousands of times per day by developers using any version control system.

Mark any item with 2 fields - ValidFromNo and ValidToNo. The last can be 0.

Keep a table versions that has the version numbers. ValidFromNo is from which version number an item is valid, ValidToNo - which can be null - is the last valid version. Null marks "Currently in use" so you do not have to update all valid items on every new versin.

Simple like that.


sp_help_revlogin for contained database users?

While David is right, the contained user will automatically come along with the contained database if you backup/restore to another location (that's kind of the point), I can see some use cases for this, like creating the same contained user across a set of databases, with the same password, without having to know (or change) the password. Or deploying the same user that has been created on dev / QA / test / staging environments to the production copy of the database, without moving the database.

If you know (or can change) the password, of course, scripting out all the contained users in a database is pretty simple, just take this output and replace whatever with the desired password for each contained user:

SELECT N'CREATE USER ' + QUOTENAME(name)
  + N' WITH PASSWORD = N''whatever'';'
FROM sys.database_principals
WHERE authentication_type_desc = N'DATABASE';

If the requirement is that you don't know the password, though, it is a bit more complex. Based mostly on this post by Argenis Fernandez (which is currently not very snappy):

First, make sure there isn't a server-level login with the same name as the contained user.
Connect to the instance using the Dedicated Administrator Connection
(see this post for troubleshooting steps if you can't).
Switch context to the contained database where your existing user lives. I'm going to assume the contained user is named bob.
Run the following:

USE OldContainedDatabase;
GO

DECLARE @sql nvarchar(max);

SELECT @sql = N'CREATE LOGIN ' + QUOTENAME(name)
  + N' WITH PASSWORD = ' + CONVERT(varchar(256), password, 1)
  + N' HASHED;'
FROM sys.sysowners
WHERE name = N'bob';

-- if the target contained database is on the same server:
EXEC sys.sp_executesql @sql;

-- otherwise:
SELECT @sql; -- run this output on the target server

Connect to the target contained database (no longer need DAC), create the user from the server-level login, migrate that user to a contained user, and drop the login:

USE NewContainedDatabase;
GO

CREATE USER [bob] FROM LOGIN [bob];
GO

EXEC sys.sp_migrate_user_to_contained
  @username     = N'bob',
  @rename       = N'keep_name',
  @disablelogin = N'disable_login';
GO

DROP LOGIN [bob];

I just tested this on my machine and was able to log in to both databases explicitly using the same username and password, but users would have been able to do the same even if I didn't happen to know their password throughout this whole process.


Performing SQL string manipulations on a single column


Which SQL Server/Windows logins require the sysadmin role?

I'm not sure about NT AUTHORITY\SYSTEM. But according to the SQL Server documentation the others are added to the sysadmin role by SQL Server Setup, so I'm guessing it is required.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-2016#DE_Prov


Best patterns for encrypting database columns/tables in SQL Azure

I think that your problem can be solved with the MS framework called "Trust Services".
Buck Woody explains in this article how you can use it to solve problems of encrypted data with SQL Azure. From his article, a short description about how to use Trust Services:

  "With the new Trust Services service, the basic process is that you
  use a Portal to create a Trust Server using policies and other
  controls. You place a X.509 Certificate you create or procure in that
  server. Using the Software development Kit (SDK), the developer has
  access to an Application Layer Encryption Framework to set fields of
  data they want to encrypt. From there, the data can be stored in SQL
  Azure as a standard field – only it is encrypted before it ever
  arrives."

Further reference:

Learn More about Microsoft Codename "Trust Services"
Using Certificate-Based Encryption in Windows Azure Applications
Crypto Services and Data Security in Windows Azure
SQL Azure: Securing SQL Azure


MV on remote/master table fails to resume syncing (after master db host was temporarily unreachable)

If the controlling scheduled DBMS_JOB job fails, it may need to be manually reset/restarted. If you want more flexibility/resiliency with this, use Oracle Scheduler to control the refresh execution, rather than the default DBMS_JOB. Do this by leaving the start and next portions out of the MV definition:
create materialized view target.wo_mv
build immediate
refresh fast
as (
select
    workorderid
    --other columns
from
    master.workorder@my_dblink
);

Then build a Scheduler job with a call to DBMS_REFRESH to refresh your MV:
begin
   dbms_refresh.make(
     name                 => '15_MINUTE_REFRESH',
     list                 => '',
     next_date            => sysdate,
     interval             => '/*15:mins*/ sysdate + 15/(60*24)',
     implicit_destroy     => false,
     lax                  => false,
     job                  => 0,
     rollback_seg         => null,
     push_deferred_rpc    => true,
     refresh_after_errors => true,
     purge_option         => null,
     parallelism          => null,
     heap_size            => null);
end;
/

begin
   dbms_refresh.add(
     name => '15_MINUTE_REFRESH',
     list => 'WO_MV',
     lax  => true);
end;
/

commit;

Be sure to run all commands as the schema/user which owns the materialized view.
Also see here: https://oracle-base.com/articles/19c/dbms_job-jobs-converted-to-dbms_scheduler-jobs-19c#mview-refresh-groups


Insert all dates from a month into a table

You can use variables to mimic a recursive CTE:

SELECT @d:=(@d + interval 1 day)
FROM (SELECT @d:=date('2016-03-31')) r
CROSS JOIN information_schema.tables t
WHERE @d < '2016-04-30';

You need to make sure that you have a table to join against that have a sufficient number of rows for your need.

If you do this on a regular basis you might want to generate a calendar table that you can use to join against.

I heard rumors that recursive CTE:s as well as window functions will be available in a nearby future for MySQL. I have not investigated it very closely though, so it might just be rumours.


Delete records with multiple values for a column

not sure about count(distinct Rate) > 1, think distinct is not necessary (but this depends from Your logic and not change query)

WITH tmp AS
        (select
                ID,
                MAX(Rate) AS Rate,
                MAX(City) AS City
        from t
        group by ID
        having count(distinct Rate) > 1)

select x.*
from t as x
LEFT JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate
WHERE tmp.ID IS NOT NULL

must return expected data

LEFT JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate
    WHERE tmp.ID IS NOT NULL

could be changed by INNER JOIN

INNER JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate

add final form as asked (delete all not), edited:
original query was have

having count(distinct Rate) > 1

as described in question - correct form of expected query, after comment - we need just remove it

WITH tmp AS
        (select
                ID,
                MAX(Rate) AS Rate,
                MAX(City) AS City
        from t
        group by ID
        )

DELETE x
from t as x
LEFT JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate
WHERE tmp.ID IS NULL


I've tried to use the GUI to shrink the database and the files but it doesn't actually change in size.

You said that you truncated large table, in such case you might not be able to shrink "immediately". From Dropping and Rebuilding Large Objects

  When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits. This implementation supports both autocommit and explicit transactions in a multiuser environment, and applies to large tables and indexes that use more than 128 extents.

It also says that

  Because the physical phase occurs after a transaction commits, the storage space of the table or index might still appear as unavailable. If this space is required for the database to grow before the physical phase is completed, the Database Engine tries to recover space from allocation units marked for deallocation. To find the space currently used by these allocation units, use the sys.allocation_units catalog view.

Other reason why you would not be able to shrink immediately because records are marked ghosted. And shrink will actually give you space backup after background Ghost Cleanup operation has completed, some more information about Ghost Cleanup Task

Initial size as such is not initial size actually, its the current size. You can shrink below initial size. What I suggest you is wait for some time till background operation finishes. Use dbcc shrinkfile tsql command to shrink the files and shrink in small chunks.

Shrinking is single threaded and takes time.

PS: If you are not aware, please note that shrinking causes massive logical fragmentation so don't forget to rebuild fragmented indexes after shrinking is done


SQL Server 2008R2 SP1 instance not available for adding features

I agree with Mark's comment.  Perhaps the default instance was installed with SQL 2012 (code name denali) beta or release candidate?  If that's the case then you should be able to use that installer to try to add features but older versions of the binaries (like SQL 2008 R2) would not be able to.


Is there a query to check the current WAL size in PostgreSQL?

If you have superuser access, you can use:
select *
from pg_ls_dir('pg_xlog');

which will return one row for each file in the directory pg_xlog. As the size for a WAL segment is fixed, you can easily calculate the total size by multiplying the number of rows by 16MB:
select count(*) * pg_size_bytes(current_setting('wal_segment_size')) as total_size
from pg_ls_dir('pg_xlog') as t(fname)
where fname <> 'archive_status';

Alternatively you can use pg_stat_file() to return information about the files:
select sum((pg_stat_file('pg_wal/'||fname)).size) as total_size
from pg_ls_dir('pg_xlog') as t(fname);

Starting with Postgres 10 you can use:
select sum(size)
from pg_ls_waldir()


Replicate SQL Server 2008 across domains

nick-mcdermaid: I can tell you that I've set up a push replication from on-prem SQL Server (2008) to Azure SQL. The snapshot took a while but didn't appear to cause any issues.

The subsequent transactional replication worked, but due to the batch load nature of the source, we kept running out of log space and we could not keep it running. So as long as changes to the database are consistent (and you don't have huge spikes of huge amounts of activity) it should work fine.

Cross domain is only relevant if you want to use Windows Authentication. Since Azure SQL doesn't support windows authentication, just plan to use SQL authentication.

  I could do on-prem SQL 2008 directly to Azure? And then on go live - simply stop the replication? I'd need to do more to the source database to make it a stand-alone? Or do I just remove replication - setup users and I’m good to go? I’d obviously do dry runs on a separate test environment - but the idea is do-able? We don’t have huge data spikes at all. It’s a database with only a few concurrent users. Less than 10.

It’s completely doable. You just have to do push subscription from on-prem using SQL authentication. But first be aware of the major gotchas of migrating to SQL Azure. Three obvious ones:

No windows authentication
No cross database calls
No ability to restore from BAK (not a problem for you)

Transactional Replication to Azure SQL DB now in public preview


Pre-production database replication solution

My first thought was to restore the backup.  At 1TB this becomes impractical, however.  Log shipping would not allow you to write to the pre-prod instance either.  I think it should be possible to engineer something around table partitioning - assign each partition to its own filegroup, perform backups after the nightly batch and restore that to pre-prod in piecemeal fashion.  I have not tried this; I'm just hypothesising.

Replication will add more load onto the produciton server to read changed values and publish them.  Having the pre-prod copy writable makes things a little more complicated here.

Instead, for the one-off load, I'd suggest you restore a backup of prod.  It may take a while but it will be complete and simple to script with minimal load on the prod box.

For daily updates I'd go with copying the staging table(s) to pre-prod and re-running the load job there.  There will be no additional strain on the production DB.  You can roll back pre-prod and re-run a batch as often as you like, for performance tuning or debugging.  If the staging DB starts each batch empty it would be simple to script a full backup - copy - restore - rerun sequence.  This will be a good way to test future changes to the loader.

We've had good results with change tracking, using it to copy hundreds of thousands of rows per day.  If your staging DB isn't rebuit each day, this may be a good way forward.  It will put a modest run-time load on the instance hosting the staging DB.


Oracle Enterprise manager express is another language

OEM is a web-based application so the language is determined by the default on web browser like Internet Explorer, make sure to change it to English (note you may see Internet Explorer menu in English but the default language (localization is not)) it can be change from internet option. give it a try


You could do something like:

INSERT INTO test_table
    (first_name,last_name,full_name)
SELECT first_name,last_name
     , CONCAT(coalesce(first_name,''),coalesce(last_name,''))
FROM (
    SELECT 'raju' as FIRST_NAME
          ,'pydi' as LAST_NAME
) AS T;

I still think it's a pretty bad idea to store full_name though. You could create a view like:

CREATE VIEW TEST_VIEW AS (
    SELECT first_name, last_name
         , CONCAT(coalesce(first_name,''),coalesce(last_name,'')) as full_name
    FROM test_table
);


Is it possible to make a reference to the result of an aggregate function in a SELECT clause from the same SELECT clause?

Nope. Only your ORDER BY clause can reference assigned aliases in the same query.

I suggest declaring a CTE that computes the first value, and then computing the second value in a query against that CTE.

For example:

WITH totals AS (
   SELECT SUM(Price * Quantity) AS Total
   FROM   SomeTable
)
SELECT
     Total
   , (Total * 0.95) AS DiscountedTotal
FROM totals;

Think of a CTE as an inline, disposable view. It is valid only for the query that immediately follows it. In that regard, it doesn't give you any performance benefit over doing the same thing with a derived table or with an actual view, or over computing the total twice like in your original query.

Of course, using a CTE does have an advantage over calculating the totals twice in two different queries, and it does look cleaner than all the other approaches.


How does the QUEUE_DELAY option for Server Audits work

SQL Server Audit leverages the new Extended Events architecture introduced in 2008.

If you look at the following DMVs, you can see the related structures appear when the audit is started.

select * from sys.dm_server_audit_status
select * from sys.dm_xe_session_events
select * from sys.dm_xe_session_targets

Note that audits have their own set of special targets, which cannot be used directly through Extended Events. If you start generating some audit events, you'll see the events show up in the actual target, but unlike a ring buffer target, they don't appear in the target_data column in the DMV for security reasons.

Having said that, it's a bit unclear (and apparently undocumented) how the Audit session targets work internally. Based on the fact that Audit is built on Extended Events, and given the behaviour options for when auditing fails, I assume the Audit targets are simply modified ring buffer targets that don't expose the event data back to SQL Server. While this explanation makes the most sense to me, I'm honestly not 100% sure. Hopefully someone with more internals knowlege can comment.

  Can I query that location to see how many items are in the queue?

Not as far as I can tell; again, likely for security reasons.

  If the ON_FAILURE option is set to shut down the server, will these items be logged to the audit once the server is restarted and the location can be accessed again?

There are many scenarios wherein the event could not be recorded. If my assumption about hitting a memory buffer is correct, the failed event(s) will be dropped on the floor as a shutdown will release all SQL Server memory. Anything that didn't make it to disk will be lost.


GRANT CONTROL SERVER when user may be an entity owner

If you suspect the user is an entity owner, you can confirm that by running this query, which shows all objects in the current database, along with their respective owner.  By default, objects are owned by the schema owner, however objects can have their ownership changed to any database principal.

SELECT ObjectName = s.name + N'.' + o.name
    , SchemaOwnerName = dps.name
    , ObjectOwnerName = dpo.name
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    LEFT JOIN sys.database_principals dpo ON o.principal_id = dpo.principal_id
    LEFT JOIN sys.database_principals dps ON s.principal_id = dps.principal_id
ORDER BY s.name, o.name;

To show how this works, I've setup a quick MCVE:

--create a test user
CREATE USER OwnerTest WITHOUT LOGIN;

--create a table, and change the ownership to OwnerTest
CREATE TABLE dbo.Test (id int NOT NULL);
ALTER AUTHORIZATION ON dbo.Test TO OwnerTest;

--check the list of owners
SELECT ObjectName = s.name + N'.' + o.name
    , SchemaOwnerName = dps.name
    , ObjectOwnerName = dpo.name
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    LEFT JOIN sys.database_principals dpo ON o.principal_id = dpo.principal_id
    LEFT JOIN sys.database_principals dps ON s.principal_id = dps.principal_id
WHERE (dps.name <> s.name OR dpo.name IS NOT NULL)
ORDER BY s.name, o.name;

The results:

╔════════════╦═════════════════╦═════════════════╗
║ ObjectName ║ SchemaOwnerName ║ ObjectOwnerName ║
╠════════════╬═════════════════╬═════════════════╣
║ dbo.Test   ║ dbo             ║ OwnerTest       ║
╚════════════╩═════════════════╩═════════════════╝

Attempt to drop the user results in an error:

DROP USER OwnerTest;

  Msg 15183, Level 16, State 1, Line 18
  The database principal owns objects in the database and cannot be dropped.

If we drop the table first, then the user, the operation succeeds:

DROP TABLE dbo.Test;
DROP USER OwnerTest;

  Commands completed successfully.


Localization: One table or multiple tables? (MySQL)

This is clear example of Many-to-Many relation (M:N).
Many messages can have (be translated into) many languages.

Date structure for that has three tables:

create table Message (
  ID int primary key,
  message text
);

create table Language (
  ID int primary key,
  language varchar(50)
);

create table Message_Language (
  message_id int,
  language_id int,
  contraint Message_Language_Message_fk foreign key(message_id) references Message(id),
  contraint Message_Language_Language_fk foreign key(language_id) references Language(id),
  constraint Message_Language_PK (message_id, language_id)
);

It's more modular and you can add more languages easily.

What kind of queries are you performing on the tables.
Provide more info about complete data structure (and other tables).

Are you aware of data normalization in RDBMS?


Live update index in production database

I mean But during index creation/updating period, will they cause
  data corruption because old index cannot be found

None of the documented T-SQL commands can cause data corruption.

The difference between online and offline index operations is in the fact of index's availability: while you do them online, indexes are still available, and when offline, they are not.

This means that your queries just cannot use nonclustered index for a period of its rebuild offline, and in case of clustered index rebuild the whole table is not available when doing it offline.

Online index operations come at a cost: they are slower, they are fully logged (while offline rebuild can be minimally logged if your db's recovery model is simple/bulk logged), and online index rebuild can result in long time blocking under some conditions described here: A SQL Server DBA myth a day: (8/30) unicorns, rainbows, and online index operations by Paul randal


I want to select all records for those persons who have one or more entries that equals zero dues

SELECT DISTINCT ID FROM tbl WHERE DUES = 0

will get the IDs of the naughty people, correct?  So now you need to do a "self-JOIN" back to get the rest of the data:

SELECT DISTINCT a.*
    FROM tbl AS a
    JOIN tbl AS b  USING(ID)
    WHERE b.DUES = 0
    ORDER BY a.ID, a.DATE

Or...

SELECT *
    FROM tbl AS a
    WHERE EXISTS ( SELECT ID FROM tbl
                      WHERE DUES = 0
                      AND ID = a.ID
                 )
    ORDER BY ID, DATE


Your structure looks fine, you would want to query in ascending order by expiry date and value. So if the customer wants to cash in 60 credits and the nearest date has values 10, 40 and 75, you want to "consume" the 10 and 40 and take the remaining from the 75. This makes it easier to use a brute force algorithm to use the soonest and smallest credits first.

This calculation would likely be better placed in the app rather than using SQL. The code would be fed a sorted list of records and the code would issue a series of 0 or more deletes followed by possibly one update.


How to install HANA DB on Oracle Linux 7?

To download SAP HANA DB server you need to download SAP HANA, Express Edition Download Manager first, because there's no direct links available for this server downloading.

For this Go through express edition registration first.

Go to your mailbox and click the link for account activation
Confirm usage terms and you'll see the next window where you could
download DM

Open Download Manager, specify Image as Binary Installer. If you use Windows as your desktop system, you could additionally select Clients (Windows) to connect to HANA server using hana client for windows (Image). Click Download to download required packages.

Copy hxe.tgz archive to Oracle Linux system. (I've put installer to shared folder on windows, and added this folder to /etc/fstab on Linux the next way:
//MyIPAddress/winshare /mnt/winshare cifs username=myuser,password=mypassword,rw,uid=1000,gid=1000 0 0

/mnt/winshare should be created first: sudo mkdir /mnt/winshare. And if you've just added it, you need to mount it: sudo mount /mnt/winshare
Copy: cp /mnt/winshare/hxe.tgz /tmp

Go to /tmp folder and extract it:
$ cd /temp && tar xvfz ./hxe.tgz

Go to /tmp/HANA_EXPRESS_20/DATA_UNITS/HDB_SERVER_LINUX_X86_64 folder and run command line installer using administrator privileges:
$ sudo ./hdblcm

Select install and server:
 SAP HANA Lifecycle Management - SAP HANA Express Edition 2.00.045.00.1575639312
 *******************************************************************************

 Choose an action

   Index | Action            | Description
   ----------------------------------------------
   1     | install           | Install new system
   2     | Exit (do nothing) |

 Enter selected action index [2]: 1

 SAP HANA Database version '2.00.045.00.1575639312' will be installed.

 Select additional components for installation:

   Index | Components | Description
   ------------------------------------------------------------------------------
   1     | all        | All components
   2     | server     | No additional components
   3     | afl        | Install SAP HANA AFL (incl.PAL,BFL,OFL) version 2.00.045.
   4     | epmmds     | Install SAP HANA EPM-MDS version 2.00.045.0000.1575663037

 Enter comma-separated list of the selected indices [1]: 2
 Enter Installation Path [/hana/shared]:
 Enter Local Host Name [hana]:
 Enter SAP HANA System ID [HXE]:
 Enter Instance Number [90]:
 Enter Master Password:
 Confirm Master Password:
 Enter ID of User Group (sapsys) [79]:

 ...
 Do you want to continue? (y/n): y
 ...

 Regenerating SSL certificates...
 Deploying SAP Host Agent configurations...
 Creating Component List...
 SAP HANA Express Edition System installed

Enable HANA DB port in firewall if you want to connect to it from network as it is in my case:
 $ sudo firewall-cmd --permanent --zone=public --add-port=39015/tcp
 $ sudo firewall-cmd --reload

Check connection from windows using telnet:
 telnet MyHanaDBServerIP 39015

Go to folder where you've downloaded hdb_client_windows_x86_64.zip archive, extract it, go to HDB_CLIENT_WINDOWS_X86_64 folder, run hdbsetup.exe installer and install the client

Open windows cmd and go to the location where you've installed the client: cd "c:\Program Files\sap\hdbclient" and run the next command to test connection to server:
hdbsql.exe -n MyHanaDBServerIP:39015 -i 90 -u SYSTEM

and execute a test select query:
select * from "SYS"."M_DATABASES";

Here I used password specified during installation.
Use $ su - hxeadm on your linux server to have access to HANA DB Server environment's variables, paths, utils.


How to copy part of a database with inconsistent privileges?

You could perform the pg_dump with the parameters --no-owner and --no-acl, so no permissions or links to any users are presents in the dump.


Cannot see two tables with the same name

The concept is_visible here appears to mean "the first one you see in the search_path"; and not "can you see it". You can demonstrate this for yourself by changing the ordering of schemas in the search_path.

postgres=# set search_path = "$user", public, b, a;
SET
postgres=# \d
            List of relations
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+------------
 b      | foo       | table | pvandivier
(1 row)

postgres=#

Note that b.foo is returned when b is listed first in the search_path. This is supported as well by the output of the previous query re-run in the same search_path.

 Schema | Name | Type  |   Owner    | pg_table_is_visible | nspname | relkind
--------+------+-------+------------+---------------------+---------+---------
 a      | foo  | table | pvandivier | f                   | a       | r
 b      | foo  | table | pvandivier | t                   | b       | r
(2 rows)

We can now clearly see that pg_table_is_visible() is True for b and False for a. Therefore "is_visible" is an indicator of current accessibility and not an indicator of whether a relation exists.

Note that attempting to determine visibility of a non-existent object has a different set of behaviours.

postgres=# -- attempting to get the oid of a non-existent object
postgres=# select 'c.foo'::regclass;
ERROR:  schema "c" does not exist
LINE 1: select 'c.foo'::regclass;
               ^
postgres=# select 'bar'::regclass::oid;
ERROR:  relation "bar" does not exist
LINE 1: select 'bar'::regclass::oid
               ^
postgres=# -- passing an oid for a non-existent object returns NULL
postgres=# select pg_table_is_visible((random() * 1e7)::int::oid);
 pg_table_is_visible
---------------------

(1 row)

postgres=#

You can also read this somewhat in source at src/backend/catalog/namespace.c. I'm not fluent in C, but the following citations appear to support this reasoning.

pg_table_is_visible() references RelationIsVisible

Datum
pg_table_is_visible(PG_FUNCTION_ARGS)
{
    Oid         oid = PG_GETARG_OID(0);

    if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(oid)))
        PG_RETURN_NULL();

    PG_RETURN_BOOL(RelationIsVisible(oid));
}

RelationIsVisible reveals the following code comments just above the return logic

/*
 * If it is in the path, it might still not be visible; it could be
 * hidden by another relation of the same name earlier in the path. So
 * we must do a slow check for conflicting relations.
 */


How can I call a remote User Defined Function?

Your remote query is nested twice, first in OPENQUERY, then in EXEC. The @somevalue variable is declared at the outermost level, it simply does not exist in the remote query's scope, so the reference is invalid.

But you also cannot use a variable there for two reasons:

First of all, as Colin 't Hart has noted, your linked server is Oracle. The SELECT @var = function(...) syntax is SQL Server and invalid in Oracle.
the OPENQUERY rowset function lets you treat the remote side's result only as a rowset – it is not designed to populate variables, whether along the way or as an alternative to the rowset. So, even if @var-like references were allowed in the underlying query (for instance, if the remote server was SQL Server), with OPENQUERY you would still be unable to reference outer ("this" side's) variables at the remote query's scope, nor would the outer scope be able to access variables declared at the inner scope ("that" side) in case you wanted to use them in your remote query.

To conclude at this point, the best way to get a result from an Oracle scalar function in SQL Server is probably to have your remote query return the result as a rowset while invoking the remote query using a rowset function like OPENQUERY. After you get the rowset from OPENQUERY, you can do with it whatever SQL Server allows you to do, and that includes storing its column values into variables. So, your OPENQUERY query could basically go like this:

SELECT
  @somevalue = somecolumn
FROM
  OPENQUERY(
    'test',
    'your Oracle query'
  )
;

Note the position of @somevalue = – it is moved one level higher, where it is now valid syntax and makes sense. The somecolumn would be the name that you would assign to your Oracle query's single column returning the result. This is probably how your Oracle query should look like:

SELECT getvalue(argument) AS somecolumn FROM DUAL

Now, if you did not need to parametrise argument, the complete Transact-SQL query could look like this:

DECLARE @somevalue nvarchar(16);

SELECT
  @somevalue = myresult
FROM
  OPENQUERY(
    test,
    'SELECT getvalue(545454545) AS myresult FROM DUAL'
  )
;

But as you want to be able to parametrise the argument, you need to make the SELECT FROM OPENQUERY query dynamic: build it as a string:

DECLARE @sql nvarchar(max);

…

SET @sql =
N'SELECT
  …
FROM
  OPENQUERY(
    test,
    ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
  )
';

and then execute the string:

EXEC (@sql);

All that you are already doing. However, when you use the EXEC (...) syntax, you cannot directly receive the result into a variable1, you can only get it as a result set – yes, same limitation as with OPENQUERY. Unlike OPENQUERY, though, you cannot call EXEC in a FROM clause.

So, at this point you are still facing the problem of putting the result of getvalue into a variable.

Basically, there are two ways to go about it. First is to drop the @somevalue = bit from the dynamic query to let the OPENQUERY's result set pass through to the outermost scope. Although you cannot do ... FROM EXEC (...), you can do INSERT ... EXEC (...). So, insert the result set of EXEC into a temporary dataset (like a table variable):

DECLARE @tmpresult TABLE (result nvarchar(16));

INSERT INTO @tmpresult (result)
EXEC (@sql);

then read the column value from the temporary storage into a scalar variable:

SELECT @somevalue = result FROM @tmpresult;

The other, and, in my opinion, better, option is to use EXEC sp_executesql instead of EXEC () to execute the dynamic query. With the sp_executesql system stored procedure you can have parameters for the underlying query, including output parameters. An output parameter is precisely how you can pass the result into your @somevalue variable that you have declared in your main script.

So, keep the @somevalue = part of the dynamic query but declare it when invoking the query rather than in the query. That way the variable will work not just as a local variable but as a parameter. Including OUTPUT into the parameter declaration will make it an output parameter too. So, this is how you go:

SET @sql =
N'SELECT
  @tmpvalue = myresult
FROM
  …
';

EXEC sp_executesql @sql, '@tmpvalue nvarchar(16) OUTPUT', @somevalue OUTPUT;

You can see in the above code that I slightly changed the name of the variable in the dynamic query. I did that to emphasise the fact that the variable in your dynamic query and the one in your main script are different things. The @tmpvalue is the variable with which you work inside the dynamic query, while @somevalue is your main scope's variable that you will be passing as an actual receiver of the value stored in @tmpvalue. That is the way of exchanging data between variables of a dynamic query and those of the calling script. Note also that you must specify OUTPUT both in the parameter declaration and when specifying the receiver variable. This is the same convention as with output parameters of user-defined stored procedures.

So, to summarise, the final code would look something like this:

DECLARE
  @sql      nvarchar(max),
  @somvalue nvarchar(16)
;

SET @sql =
N'SELECT
  @tmpvalue = myresult
FROM
  OPENQUERY(
    test,
    ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
  )
';

EXEC sp_executesql
  @sql,
  N'@tmpvalue nvarchar(16) OUTPUT',
  @somevalue OUTPUT
;

1Actually you can have parameters, including output parameters, with EXEC, but only if you have specified to execute the dynamic query at a remote server:

EXEC (@sql, @var1, @var2, ...) AT linked_server

Usually this is more useful when the remote server is a non-SQL Server one, as for SQL Server instances the EXEC sp_executesql method would likely be preferable.


How to convert timestamp to string in Pentaho Kettle?

Just use the select values step. Convert the data type to a string and specify the mask:

yyyyMMdd

Note the mask is a java simpleDateFormat and you can google that for more details. It is case SENSITIVE.

The mask in PDI is only used for 3 things:

Converting from the real data type to a string
Display purposes when previewing data
Any other time you convert to "text" i.e. CSV output, or bulk loaders.

If you are sending it to a target database as a timestamp then it will be whatever that target database uses as default format.


Does PostgreSQL support wildcard indexes on JSON?

This won't work. In particular, you cannot create a single index that will support conditions like
WHERE (user_properties ->> 'some random property')::date >= '2000-01-01'::date

for arbitrary attributes.
You will have to identify the properties you want to use in comparisons and create specialized indexes on these properties. In the above sample, that would be:
CREATE INDEX ON mytable (((user_properties ->> 'some random property')::date));

It would be better to extract such columns from the JSON and have them be regular table columns.


Installing Enterprise Edition in a virtual machine

Is it possible? Yes.

Is it legal to use in production? No.

What should I do instead?

If it's for production, and your databases are 10GB or less, check out Express Edition. It's free, and has core and memory limitations, but the price is right.
If it's for production, and you absolutely need to run your own VM, you'll need to buy a production license (like Standard or Enterprise.)
If it's for production, and you don't need to run your own VM, you can rent VMs from Amazon EC2, Google Compute Engine, or Microsoft Azure with the SQL Server licensing included. That way you can pay by the hour and change your licensing based on your needs for that particular day.
If it's for development, consider SQL Server Developer Edition. It's free, and it's available as a download, or as a Docker container.

That'll hopefully help keep the licensing mafia off your back. My friends tell me I shouldn't talk about them anymore, though, because they're probably listening right now. (You might ask, am I referring to the licensing mafia listening, or my friends? Both. They're gunning for me.)


Backup via mysqldump. Is this safe to do on production or will it crash the server?

Unless you add --single-transaction and you're using innodb tables this could block writes to the database for the period of the dump. So use --single-transaction (and innodb).

The only risk is if you are using a DO instance that is far too small and causes the mysql server or mysqldump (less likely) to run out of memory.

If you start gaining swap usage during the dump you can kill off the mysqldump process.

These few cases aside, its pretty safe.


Mysql match on two columns and get count

@Abdul's answer just needs HAVING COUNT(*) > 1

The query shold look like this now

SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;

Here is the execution of it:

mysql> create table test(col1 varchar(20),col2 varchar(20));
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO test VALUES('Joe','April'),('Fred','May'),
    -> ('Sam','April'),('Joe','April'),('Mark','Jan'),('Joe','Dec');
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+----------+------+-------+
| COUNT(*) | col1 | col2  |
+----------+------+-------+
|        2 | Joe  | April |
+----------+------+-------+
1 row in set (0.24 sec)

mysql>

If you do not care for the count, remove it from the SELECT clause

mysql> SELECT col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+------+-------+
| col1 | col2  |
+------+-------+
| Joe  | April |
+------+-------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!


Can i alter a database to single user which is configured in log shipping

There's several parts to this question:

Do I need to set the database to single user mode to change the compatibility level? No.

Will changing the compat mode from 90 to 110 break log shipping? No.

Can I change a log shipped database to single user mode? Yes, but don't do that unless you have to. Sometimes, you might not end up being the single user (like if someone else grabs the connection from you, like if your SSMS crashes after you set it to single user mode.)


The issue is not about how many delete operations there are, it’s about how it finds the rows that are to be deleted.

If you look at the query plans you’ll almost certainly let see that your delete with OR is scanning the whole table. This will take out locks to make sure the rows don’t move around while it’s looking for ones to delete. Meanwhile, your inserts will take out locks, as the table and other indexes are maintained. Without the OR, you’re getting Seek operations, and only small parts of the table are locked.


Add data from one table to another when data in two other specific fields are identical

You could try an UPDATE with a FROM clause, from your description of the problem.

Something like:

UPDATE table1 t1
SET new_field_for_fk = t2.pk
FROM table2 t2
WHERE t1.county_name = t2.county_name
  AND t1.farm_name = t2.farm_name ;

Should update all rows based on the matching of the two keys across the tables.


Many parallel queries hang but no deadlock

The issue was caused by the bug in the Npgsql, it now works ok after updating to the latest version.


AlwaysOn Availability Groups: Confusion with some concepts

What it means is that the computer object of the cluster (within Active Directory) will have the same set of the permissions as the user that created the cluster (and in turn, the cluster computer object).

This cluster computer object will then be the security context when a cluster service or application needs to have a computer object created.

Think of it like this: Joe creates a cluster and the cluster computer object is Cluster1.  When a computer object of the cluster needs to be created, it will be under the context of Cluster1. And Cluster1 will have the permission set of Joe.

Take a look at a blog post I wrote on this very topic (SQL Salt).  Maybe that will shed a little more light on this. When you create a listener, for instance, auditing AD will show that it is the cluster computer object that is actually creating the listener computer object.

In many environments this elevated security for computer objects may be undesirable. Therefore you can pre-stage these objects within AD. The end result should be the same.


How can I delete duplicates records in a table efficiently

If you can afford to have a temporary table, then creating new table with the distinct values of the old one is a lot faster that deleting the rows. Something like:

create table no_dupes
as
select min(id) as id,
       col_a,
       col_b,
       col_c,
       min(col_d) as col_d,
       min(col_e) as col_e
from table_with_dupes
group by col_a, col_b, col_c;

Of course this has the drawback of needing to re-recreate all indexes and constraints. But in my experience it way faster - especially if you are deleting a large number of rows in a large table.


Bulk insert statement fails in SSMS with Operating system error code 5 (Access is denied.)

Microsoft's Documentation for BULK INSERT says this about security:

If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data.In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.
When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.
To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.
For more information about this and other security considerations for using BULK INSERT, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server).

The implication is you need to have Kerberos authentication working in order to allow impersonation.  SQL Server needs valid Kerberos SPNs setup for this to work.  I wrote a blog post about how to confirm that Kerberos SPNs are correctly setup over at SQL Server Science.
You'll also need to enable Delegation for the SQL Server Service Account.  This post I wrote shows how to get that set up, and provides minimally complete and verifiable example code you can use to test the setup.


The error
mysqldump: Got errno 32 on write

simply means you ran out of space.
The question is: Where is the space issue coming from ???
--order-by-primary

Using this requires creating a temp table in the folder configured in tmpdir
Bigger mysqldumps need a much larger tmpdir when using --order-by-primary
If you get rid of --order-by-primary, you can handle bigger dumps.
You just asked

what are the implications of removing --order-by-primary ?

There are improvements to the dump's creation
IMPROVEMENT #1 : Faster Dump
Every time you dump a table using --order-by-primary, a temp table must be created. That can pause the mysqldump on the source side. Such pausing will never occur anymore
IMPROVEMENT #2 : No bloated indexes
When you load a table in PRIMARY KEY order, it can generate up to 45% additonal wasted space in the indexes due to lopsided page splits in your BTREE indexes.

Jun 28, 2012 : Benefits of BTREE in MySQL
Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions?
Nov 26, 2012 : Does the Int type primary key insert order affect query performance in MySQL? (discusses this from MyISAM point-of-view)

IMPROVEMENT #3 : AUTO_INCREMENT PRIMARY KEYs
If the majority of your tables has a single integer that is auto incremented and is the PRIMARY KEY, the table is already ordered. No need for the overkill of --order-by-primary
Concerning your next comment

I removed --order-by-primary, got the same error at the same time :(

Please make sure you have extra diskspace on the RDS Instance. Why ?
It is possible for a dump to be bigger when reloaded, especially when exacerbated by having keys ordered.

Sep 09 , 2016 : mysqldump: ERROR 2006: MySQL server has gone away when attempting to push data to RDS instance
May 01, 2014 : Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?


SQL Server 2008r2 replication from multiple sources

SQL Replication isn't going to handle that for you.  You'll have to setup a very custom replication type system, possibly using SQL Service Broker to handle the merging of data into a single system for reporting.


RMAN DUPLICATE from backup without Target or Catalog: How is the aux instance created?

That is the typical error message you get on Windows without properly setting up the environment.

While the steps in the manual may work flawlessly on Linux/UNIX, on Windows you need extra steps. On Windows, you need to create a service with oradim, start it (if it was not started), and after that you can use sqlplus / as sysdba.

The bare minimum is:

set ORACLE_HOME=...
set PATH=%ORACLE_HOME%\bin;%PATH%
set ORACLE_SID=test

oradim -new -sid test

This should automatically start the service, but if it did not:

sc start Oracleservicetest

And after the above, you can login to the idle instance with:

sqlplus / as sysdba


Mongodb crash after multiple query attempts

That query is simply a long running read, without any criteria (so it is running against all data).  As it fetches back the data, it will be done in batches (based on your batch size) and then issue a getmore on the same cursor for the next set of results.

The numYields count does not mean the query is being blocked, it means that it yielded its lock when needed. This is usually done for a write, and usually when the original query had to page fault to disk to get data, then it resumes (when querying all data in a collection, this is going to happen often unless all your data + indexes fit in RAM).

Therefore, the query is not being blocked, in fact the getmore operations show that it is progressing over time - most long running reads will have a similar profile, especially if you are writing to the database at the same time.

It is also not likely that this query is the cause of any crash (it's just a read), it's more likely something else that is causing the crash, and you are equating this query with the crash because it happens to be running at the time when the crash occurs (people often suspect the serverStatus command for the same reason - it is run once a minute by MMS).  I would recommend posting the full messaging around the crash as a separate question for proper diagnosis.

For what it's worth, with snapshot set to true, and the fact that it is reading all data, I suspect this is a mongodump query (it defaults to using snapshot to avoid duplicates being dumped when data is moved).


Is it feasible to escape ' (single quote) with ESCAPE clause, if yes, then how to do it?


Even in Management Studio that ships with SQL Server 2012 (BTW please stop using SSMS 2005 to manage 2008+ instances), the Generate Scripts option will not offer Service Broker objects, whether you select "script all" or select individual objects. You'll need to use other methods - SMO, PowerShell, or right-clicking within the Service Broker node of Object Explorer and scripting items individually. You can speed this up a bit using Object Explorer Details (in the right-hand pane you can select multiple objects and script them), but this is still one operation per object type.

PS this was complained about years ago, but sadly, closed as Won't Fix.

http://connect.microsoft.com/SQLServer/feedback/details/515831/generate-sql-scripts-wizard-missing-object-types

On the plus side, David is right below: SqlPackage.exe will definitely script out service broker objects (it just does it in the wrong order). See my answer to a more recent question here:

How to script all the service broker elements for each database it is on, in the current server?


Assuming that there is not a performance issue with having data and sql living on the same node, V2 will be viable and cheaper.

I generally do not like having caches in front of caches, so I question the need for memcached.


the first diagram you can't realized in a rdms, you need to make sales always to an entity, to represent the relation ship.
if we take the third diagram, you would have all the wanted data, but you couldn't make foreign keys work, because of the ring relationship.( every row that is referenced mus exist before, this doesn't work in a ring relationship).
now you have two possibilities:

you have  to represent the trinary relationship in an entity, and you wouldn't have the eer 1 any more, as sales is an entity with foreign keys to all three tables(entities)
you put the relationship in your application code

the first gets you your second approach.
The second doesn't show the relationship with foreign keys, so you would add the information by adding comments to the tables.


On the slave, you can configure MySQL to skip certain errors by using the slave-skip-errors configuration item.

In the case above, you'd add the following to your my.cnf;

slave-skip-errors = 1452

But I'd be extremely wary about doing this, as the referential integrity is there for a reason, and without it, your slave could not be satisfactorily promoted to take the place of the master in the case of something going wrong with your master hardware...

http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_slave-skip-errors


What is the easiest way to get started using databases with real data?

Charles,

You mentioned MS Excel in your comment so it's pretty much safe to assume you're in a Microsoft environment. You definitely have much power if you know how to mess with a database management system.

If you're doing some serious data analysis, I'd say go for enterprise databases like Oracle, SQL Server, MySQL, DB2, etc., which are Relational Databases. It's good to note that there are also non-relational databases out there that are gaining traction in the database market.

Since you're probably on a Microsoft environment, I suggest you go for SQL Server. You can ask your IT department if you have an enterprise database already in place. If none, you can download the "express" version of SQL Server (see link on the other comment). While you're there read about the limitation of an express edition. The express edition is FREE.

The express edition is a fully functioning, production-ready version of the SQL Server although it's limited in some ways (storage capacity, memory usage, etc). You can even run a Reporting Services off the express version. Look for "SQL Server Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search" --> download here: http://www.microsoft.com/sqlserver/en/us/editions/2012-editions/express.aspx

If you think that there is a necessity for an enterprise-wide database in your company (assuming your company doesn't have it yet), go ahead and buy the Developer version. The developer version contains everything that the Enterprise version of SQL Server has. If you are testing what SQL Server can do in an enterprise setting, this is the version you want to get your hands on.

Note that you cannot use the developer version for production. It's intended for testing only. The good thing is, when your company database is ready for prime time - production - you can easily "switch" the developer version to the licensed SQL Server instance.

As you start learning SQL Server, it is just fitting to start learning TSQL, which is SQL Server's implementation of the SQL language.

You mentioned you want to learn running some queries, so I suggest you start with:

Data Manipulation Language (DML) - SELECT, INSERT, UPDATE, DELETE,
MERGE
Data Definition Language (DDL) - CREATE, ALTER, DROP database
objects (Tables, Views, Constraints, Index, Stored Procedure, etc)

The SQL Server 2012 Books online is also a great starting point in learning SQL Server: http://msdn.microsoft.com/en-us/library/ms130214.aspx

The SQL Server community is also a great resource. Follow me on Twitter @MarlonRibunal. Some tips on where to find necessary SQL Server skills:

Books
Blogs

SQL Server Central
SQL ServerPedia (Now Toad World)
List of SQL Server Bloggers (Blog Directory)

User Groups - there is this Professional Association for SQL Server (PASS). Good resource for anything about SQL Server - events, people, etc. Explore the site to learn what the organization is all about.
Events

SQL Server User Group Meetings/Meetups - Check if there's a PASS Chapter near you - http://www.sqlpass.org/PASSChapters.aspx
SQL Saturday Event - free, one day quality conference/training for SQL Server (database administraion, development, business intelligence, etc.)

#sqlhelp hashtag on Twitter - your helpline on Twitter. I suggest you start following those people that used the #sqlhelp tag - either they were asking for help related to SQL Server or answering a question.
SQL University - collection of topic-specific blogs
SQL Server Study Group Meetup - assemble your members and study for SQL Server Certification exams or just for enhancement of SQL Server knowledge


The prefix that was used:

SET @@auto_increment_increment=1;

Is the same as:

SET @@SESSION.auto_increment_increment=1;

When modifying this setting, it becomes relevant only in your current session.

To make a more permanent fix try:

SET GLOBAL auto_increment_increment=1;

Is the same as:

SET @@GLOBAL.auto_increment_increment=1;


Why is tnsping not reliable from ports 20, 21, 22?

Read this carefully: Using the TNSPING Utility to Test Connectivity from the Client

Take a look at the Stack Overflow answer: When using tnsping, can I specify a port number along with the SID?

You can specify destination port in tnsnames.ora or I think you can use EZCONNECT syntax.


Here is the sample query to convert the given varchar2 value to date and calculated the difference in hour.

select (to_date('20160316124052', 'yyyy-mm-dd hh24:mi:ss')-to_date('20160316080002', 'yyyy-mm-dd hh24:mi:ss'))*24 DateAndTime from dual;

Update

SELECT (sysdate-to_date(START_TIME,'YYYYMMDDHH24MISS'))*24 FROM LOGGING WHERE NAME='TERR_PAST' AND UPPER(STATUS) = 'EXECUTED';


Get the Largest Count of Consecutive Value

First of all, you can't trust that table to work.  A table, by definition, is unordered.  SQL is free to return the ones and zeros in any order.

So...  You need to have another column that can be used to sort on -- a date or a game-number, or something.

Then you can ask the question because you can do a SELECT ... ORDER BY ....

The rest is messy.  You should consider, instead, writing the algorithm in your favorite programming language other than SQL.

Add an AUTO_INCREMENT column and index it.
Do a self join on a.id = b.id-1 to find where the transitions are (0 to 1 or 1 to 0)
Extract only the transition ids.
Repeat the auto_in game, but instead of finding transitions, subtract the old ids.  This gives you the run lengths.
SELECT ... ORDER BY .. LIMIT 1 to find the longest run, together with some of the history of where it came from.


migrating DBs from SQL server 2005 to SQL server 2012

Depending on the edition since the database size is big, you can look into

Logshipping - set up with full backup and subsequent log backups. During the cutover, just take the final tail log backup and restore the 2012 server databases with RECOVERY.
Mirroring - Set up as async and during cutover, change the mode to SYNC and failover.

Once the failover is done, remove logshipping or mirroring.

I agree with alroc that the dbatools.io is extremely useful, but in your situation you are better off going with above and separately use the cmdlets for migrating jobs, logins.

For more detailed steps, refer to my answer here.


How to get next/previous partition count using window function?

Do the aggregation first. Then use the lead() and lag() window functions. Looks like you were aiming for something like this:

SELECT *
     , lead(curr_count) OVER w AS next_count
     , lag(curr_count)  OVER w AS prev_count
FROM (
   SELECT l.city, d.year, d.month, count(*) AS curr_count
   FROM   fact     f
   JOIN   crime    c ON c.crime_key = f.crime_key    -- see below
   JOIN   date     d ON d.date_key = f.date_key
   JOIN   location l ON l.location_key = f.location_key
   GROUP  BY l.city, d.year, d.month
   ) sub
WINDOW w AS (PARTITION BY city ORDER BY year, month)
ORDER  BY city, year, month;

You can use a CTE or a subquery (like I did) to apply the logic in two steps. May be easier to understand at first. But you can add window functions over aggregate functions in the same query level. See:

Best way to get result count before LIMIT was applied

So this is equivalent and shorter (probably same query plan / performance):

SELECT l.city, d.year, d.month
     , count(*)              AS curr_count
     , lead(count(*)) OVER w AS next_count
     , lag(count(*))  OVER w AS prev_count
FROM   fact     f
JOIN   crime    c USING (crime_key)    -- see below
JOIN   date     d USING (date_key)
JOIN   location l USING (location_key)
GROUP  BY l.city, d.year, d.month
WINDOW w AS (PARTITION BY l.city ORDER BY d.year, d.month);

Use equivalent explicit join syntax. Much easier to read.

The USING shorthand in the join clause of my second query is only legit if column names are unambiguous. Else stick to the explicit ON syntax in the first query.

Why join to table crime? Does the join eliminate or multiply rows? Else you can remove it completely.

Use table aliases to keep the noise low.

You had PARTITION BY location.city, date.year, date.month ORDER BY date.month. But I suggest you really want PARTITION BY city ORDER BY year, month to make the query work across year boundaries?

It would probably be more efficient to use a single column of type date (4 bytes) instead of year and month. (But don't name it just "date".)

Note how I skipped ORDER BY city, year, month in the second query. You normally get rows sorted that way anyway due to the window definition, but there are no guarantees without explicit ORDER BY. And you did not specify that order is required. If so, you might want to be explicit about this and add ORDER BY.


Oracle Enterprise Manager 11g: How to see statistics of Interfaces usage?

If you are talking about Listener statistics on Oracle Enterprise Manager then-

1) Click on Listener name on Home screen of OEM.

2) Then Click on 'Established Connections per minute' under 'State' section.

3) Now you have got the page with some statistics of listeners.

For your last question "What kind of actions are mostly done?" , I think You need to use database auditing so that you can track the activities of users connecting using the listener.


Copy table structure excluding nullability into local temporary table

For table variables there is not much you can do. However, for temporary tables (even local ones -- i.e. those with names starting with a single #) there is quite a bit that can be done.

With regards to the behavior of local temporary tables not surviving the end of the process in which they were created, the place where people seem to get stuck is in assuming that they can only be created. That might be true for table variables (which is why they won't help here), but not so true for temporary tables (local or global): they can also be altered ! And, because local temporary tables are available to sub-processes, and because any changes made to them in a sub-process survive the ending of that sub-process, they can be created in the "current" scope and then altered in a sub-process. The end result will be the desired structure in the current scope :-) !

So you don't need to approach this problem in terms of needing to create the local temporary table with the perfect structure, just so long as there is a programmatic means of determining what changes should be made. And for this particular request that is (thankfully) not terribly difficult. Once the temporary table is created, we just look at the columns of the source table to get their names, datatypes, and optional collation (the three pieces needed for the ALTER TABLE statement). Then we just construct a Dynamic SQL string with the ALTER TABLE statements that adjust the nullability as desired. Execute that and all will be right with the world.

Set up the test:

For this test we will use a local temporary table as the table to copy, but that is just to make for an easier test, and a permanent table will behave the same way.

SET NOCOUNT ON;

IF (OBJECT_ID(N'tempdb..#SourceTable') IS NOT NULL)
BEGIN
  PRINT 'Dropping #SourceTable...';
  DROP TABLE #SourceTable;
END;

CREATE TABLE #SourceTable
(
  Col1 INT,
  Col2 NVARCHAR(37) COLLATE Hungarian_100_CI_AI_SC NOT NULL,
  Col3 DATE,
  Col4 TIME(3) NOT NULL,
  Col5 FLOAT(20) NOT NULL,
  Col6 DECIMAL(10,3) NOT NULL,
  Col7 NUMERIC(14)
);

IF (OBJECT_ID(N'tempdb..#TargetTable') IS NOT NULL)
BEGIN
  PRINT 'Dropping #TargetTable...';
  DROP TABLE #TargetTable;
END;

Run the test:

Finding the nullability is rather easy. But you can't issue an ALTER COLUMN statement without specifying the datatype. The datatype is provided in proper format by the sys.dm_exec_describe_first_result_set() DMF, but that was introduced in SQL Server 2012, hence it won't help on SQL Server 2008 R2. So, we have to rebuild the actual datatypes, which means you need to: divide max_length by 2 for NCHAR and NVARCHAR; use scale by itself for time, datetime2, and datetimeoffset; and use both scale and precision for decimal and numeric ( <sarcasm> so much fun </sarcasm> ).

NOTE: Please remember to remove the two references to tempdb. in the code below when using this outside of a testing scenario. Those references -- noted in inline comments in the code -- are only there due to the source table (the table to be copied) being a temporary table and existing in tempdb.

SELECT *
INTO   #TargetTable
FROM   #SourceTable
WHERE  1 = 0;

DECLARE @AlterQuery NVARCHAR(MAX);
SET @AlterQuery = N'';

SELECT  @AlterQuery = @AlterQuery
        + N'ALTER TABLE #TargetTable ALTER COLUMN [' + col.[name] + N'] '
        + CASE
            WHEN col.user_type_id IN (34, 35, 36, 40, 48, 52, 56, 58, 59, 60, 61, 62, 98,
                            99, 104, 122, 127, 128, 129, 130, 189, 241, 256) THEN tp.name
            WHEN col.system_type_id = 240 THEN tp.name -- User-Defined Types (UDTs)
            WHEN col.user_type_id IN (231, 239) THEN tp.name + N'(' +
                       CONVERT(NVARCHAR(5), col.max_length / 2) + N')' -- nvarchar, nchar
            WHEN col.user_type_id IN (165, 167, 173, 175) THEN tp.name + N'(' +
                    CONVERT(NVARCHAR(5), col.max_length) + N')' -- (var)binary, (var)char
            WHEN col.user_type_id IN (41, 42, 43) THEN tp.name + N'(' +
                CONVERT(NVARCHAR(5), col.scale) + N')' -- time, datetime2, datetimeoffset
            WHEN col.user_type_id IN (106, 108) THEN tp.name + N'(' +
                            CONVERT(NVARCHAR(5), col.[precision]) + N', ' +
                            CONVERT(NVARCHAR(5), col.scale) + N')' -- decimal, numeric
          END
        + ISNULL(N' COLLATE ' + col.collation_name, N'')
        + N' NULL;' + NCHAR(0x0D) + NCHAR(0x0A) -- CR + LF
FROM     tempdb.sys.columns col -- remove "tempdb."
INNER JOIN sys.types tp
        ON tp.user_type_id = col.user_type_id
WHERE    col.[object_id] = OBJECT_ID(N'tempdb..#TargetTable') -- remove "tempdb..#"
AND      col.is_identity <> 1 -- exclude the IDENTITY field
AND      col.is_nullable = 0 -- only ALTER the columns that actually need it
ORDER BY col.column_id ASC;

PRINT @AlterQuery; -- debug

EXEC (@AlterQuery);

Check the results:

The following query will display the columns and their datatypes and nullability. You will see that all fields now have is_nullable showing as 1 :-).

SELECT col.column_id,
       col.[name],
       tp.[name],
       col.max_length,
       col.[precision],
       col.scale,
       col.is_nullable
FROM   tempdb.sys.columns col
INNER JOIN sys.types tp
        ON tp.user_type_id = col.user_type_id
WHERE  col.[object_id] = OBJECT_ID(N'tempdb..#TargetTable')
ORDER BY col.column_id ASC;


Query Output Window not honoring fixed length

It is honoring the fixed character length.  However, it does not appear to be using a monospaced font so it looks like it isn't.  The character length is fixed for the column.  The character width is font dependent.  DB Mail is using a monospaced font.

https://en.wikipedia.org/wiki/Monospaced_font

  A monospaced font, also called a fixed-pitch, fixed-width, or
  non-proportional font, is a font whose letters and characters each
  occupy the same amount of horizontal space.[1] This contrasts with
  variable-width fonts, where the letters and spacings have different
  widths.

Notice that the space taken up by an M is more than taken up by an I