This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
Ok. Well Hibernate's query language is HQL not SQL (have a read of the documentation). If you want to run proprietory SQL you will need to run it ass a sql query (check the JavaDocs for the Session class).
One thing to think about though: one of the reasons you use a tool like Hibernate is to avoid tighly coupling your data access code to a specific database, which is what you do when you introduce SQL Server specific syntax and keywords to your query. There is nothing in your query that you can't do in HQL, or using Hibernate's Criteria API.